I want to backup a database, but during the day when there is load on the server. It's vital that the backup doesn't impact apache and other databases running on the same server.
It should be possible to use the mysqldump command, but run the command at low priority.
How can I do this?
Update: Looks like simply using nice with mysqldump doesn't work since mysqldump spawns a new process.
If you have a spare server around that can cope with the write load of your server, you can set up replication to that server, and then backup from the slave server. This also has the advantage that you can stop replication while you do the backup and get a consistent snapshot of your data across all databases, or all tables in one database without impacting the database server. This is the set up I always recommend for backing up MySQL if you have the resources.
As a nice bonus, you now have a read-only slave you can use for slow long-running queries.
mysqldump will either cause a long lock to be held or tables to be out-of-sync.
Dec 21, 2009 at 11:26
Andy, by now I'm guessing you've had plenty of time to find a solution. I've recently come up with a solution to this that's working great for me at tsheets, and figured I'd share it.
cstream is a general-purpose stream-handling tool like UNIX dd, usually used in commandline-constructed pipes. The thing that makes cstream useful for us is that it allows you to specify the maximum bandwidth for all input. This means you can limit the disk IO of your mysqldump command with a simple command like this:
mysqldump --single-transaction --quick -u <USER> -p<PASS> <Database> | cstream -t 1000000 > backup.sql
Assuming you're backing up a database that uses all InnoDB tables, the above command is safe (won't affect other queries) and will do your mysqldump while limiting it's disk reads to just one megabyte per second. Adjust the bandwidth with the -t paramater to whatever value will allow your environment to perform the backup without impacting your customer's experience.
FWIW you should also be able to do this with pv (http://linux.die.net/man/1/pv)
mysqldump --single-transaction --quick -u -p | pv --rate-limit 1m > destination (or | nc or | tar cfj backup.bz2 -)
The nice thing about this is the various options for monitoring progress and the -R option which allows you to pass options to an already running process, e.g.; --rate-limit to alter the transfer rate.
if you use innodb you can try xtrabackup with the --throttle option.
you can as well look at ionice and run mysqldump with it.
or maybe you want to enable binary logging in mysql and run full dump once per week / night, while copying bin-logs to safe location every 1-2hours. and.. read-only slave for backup-only purposes is an option as well.
If you are running it on Linux, or another *nix variant you could do it with the following:
nice -n ## mysqldump
This will give it a lower scheduling priority. Scheduling priority's range from -20 (highest priority) to 19 (lowest priority) the default for nice is 10 if the -n argument is omitted..
cron.d/mysql_dump:
17 22 * * * backup touch /home/backup/all_databases.sql && chmod 600 /home/backup \
/all_databases.sql && ionice -n 7 /usr/bin/mysqldump -u root -ppassword --opt --all- \
databases > /home/backup/all_databases.sql && nice -n 20 bzip2 -f \
/home/backupall_databases.sql
ionice for I/O priority and nice for cpu priority.
Run vmstat 5, check the column that says WA. High value means the CPU is waiting for I/O. Use ionice, if its just CPU load, use nice.
Response to update:
Seems you are correct. It calls mysql which isn't niced. I guess you could use renice for this and set the nice value after it is run.
I don't have a sufficiently big database to dump atm to make a useful script for you though.
Replication, as your link suggest, is one way to go. Replicate, stop and dump, on anthoer host.
Put your MySQL data on an LV, and use a niced mylvmbackup job which takes a snapshot of the LV and tars up the MySQL data files. This way you don't lock the tables, minimising the impact on your applications to just the IO load.
You could always try not running it during peak hours. Whatever priority you use, the dump is still going to require a lock on all your tables.
