8 Answers
8

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.

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.
–
andyukDec 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 CarleyDec 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:

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.

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.

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.

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.