On Dec 4, 2008, at 9:00 AM, Matt Graham wrote:

> From: Alex Dean <alex@crackpot.org>
>> On Dec 3, 2008, at 3:16 PM, kitepilot@kitepilot.com wrote:
>>> Back then when I researched the issue (about a year or so ago), you
>>> had to take the server down to get a consistent backup with the  
>>> tools
>>> provided 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't
> always 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