Use a combination of non-locking table arguments for mysqdlump and the “nice” command to give mysqldump lower priority over other processes.
bash# nice -n 19 mysqldump --single-transaction --quick -uroot -p app3 > app3.2010022801.sql
–single-transaction doesn’t lock the tables when they are being dumped (by default -lock-tables would be enabled)
nice is a Unix command to tweak the priority of a process, from -20 (highest priority) to 19 (lowest priority)
You also probably want to compress the file to speed up transfer across the network to a backup server:
bash# tar -czvf app3.2010011801.sql.tar.gz app3.2010011801.sql
Using nice on complex queries that would actually eat up all the CPU works for every SQL query, not only mysqldump.
If you have such a complex query, instead of trying to run it from inside a mysql shell, run it from the bash as follows:
bash# nice -n 19 mysql -e "use mydb; select * from purchase where id in (select id from users where sex='female');"
… and compare with running it directly from the mysql prompt.
This is particularly handy for complex queries using JOINs between big tables.