On Dec 4, 2008, at 9:00 AM, Matt Graham wrote:
From: Alex Dean <[EMAIL PROTECTED]>On Dec 3, 2008, at 3:16 PM, [EMAIL PROTECTED] wrote:Back then when I researched the issue (about a year or so ago), youhad to take the server down to get a consistent backup with the toolsprovided by MySQL. I haven't updated my research since...Setting up a replication slave just for backups is pretty simple. Then you can take down the slave to make a backup at any time without affecting the master at all.Yes, the Fine Manual for MySQL talks about that in great detail. But using a replication slave means you need another box, which you don'talways have. My personal site does "mysqldump -A | gzip -9 > $DATE.gz"every morning at 0-dark-thirty when no one's using it... which works, but wouldn't work if I had 30G of data and a ton of users using it all the time.
You don't really need another box, just another mysql instance listening on a different port. That would add the i/o overhead of writing your data twice, but if you're short of hardware it might work out. Of course you'd then need to scp/rsync the data files somewhere, since a backup on the same hardware as primary is almost pointless, but you don't need mysql running on 2 boxes.
If you use mysqldump, there's some option like --with-lock or --for- backup, or something similar that acquires a global lock before dumping data. That ensures you get a consistent backup, at the cost of cutting off all write access while it's running. My personal site does that as well, since the total dump is only a few hundred MB and takes very little time to run.
Also, if you stop the slave and make a file-based backup without excluding the ib_logfile* files, then restore that backup on another box, then start the server, you get an extraordinarily stupid error message. Sigh.
If memory serves, that's not completely true. If the other server is configured for different ib logfile sizes (I forget the actual config option name), you'll get an error. If both boxes have the same my.cnf values, it should 'just work'.
But, if you've shut mysql down cleanly, those log files don't need to be part of the backup at all and could be excluded.
alex
PGP.sig
Description: This is a digitally signed message part
--------------------------------------------------- PLUG-discuss mailing list - PLUG-discuss@lists.plug.phoenix.az.us To subscribe, unsubscribe, or to change your mail settings: http://lists.PLUG.phoenix.az.us/mailman/listinfo/plug-discuss
