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
---------------------------------------------------
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 
(text/plain)
