Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
have you looked at mysqlhotcopy? –  user29502 Dec 18 '09 at 1:50
    
@fsb how does mysqlhotcopy support what the OP wants? –  Tomas Feb 6 at 11:11

8 Answers 8

up vote 7 down vote accepted

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.

share|improve this answer
    
Unfortunately this option looks like it is the most work, but I guess its the only real option if I want minimal impact on the main server. –  andyuk Dec 17 '09 at 22:02
2  
It's nearly the only way to get a properly quiesced backup if you don't wish to impact operations. Since slowing down mysqldump will either cause a long lock to be held or tables to be out-of-sync. –  Dan Carley Dec 21 '09 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.

share|improve this answer
    
Just tested and I works like a charm! Thanks for sharing Brandon! –  sucotronic Feb 22 '13 at 11:52

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.

share|improve this answer

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.

share|improve this answer

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..

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.