Thanks Baron. That actually looks like a pretty useful tool for monitoring replication -- I'll have to give that a shot.
I did use ZRM once a few years ago, and I even thought about trying to use it this time around, but I couldn't find anything in the docs that explicitly described my particular use case. Does it support this? Thanks, Martin On 12/24/07, Baron Schwartz <[EMAIL PROTECTED]> wrote: > > Hi, > > On Dec 24, 2007 1:35 AM, Martin Goldman <[EMAIL PROTECTED]> wrote: > > Hi folks, > > > > I have two MySQL servers running in a master-slave configuration, and I > want > > to set up a process for backing up our application's data in which > backups > > are sent to a server at another location. Ideally, I'd like to do a full > > backup once a week, and then incremental backups every 6 hours. It seems > to > > make the most sense for this to happen on the slave. I was thinking it > could > > work something like this: > > > > Weekly job: > > - Stop slave > > - Flush and delete binlogs on slave > > - Use mysqldump to generate full backup on slave > > - Create a directory on the remote server for this week's backups, and > copy > > the full backup file over to it > > - Start slave > > > > Every-6-hours job: > > - Stop slave > > - Flush binlogs on slave > > - Copy over any newly created binlog files from the slave to the current > > weekly directory on the remote server > > - Start slave > > > > Then, if I needed to restore the backup, I'd: > > - Copy the weekly directory from the remote server to the MySQL server > > - Play back the full backup on the MySQL server > > - Play back the binlogs on the MySQL server > > > > I'm just curious as to whether the more experienced folks here think > this is > > a logical approach, and if so, whether there are any caveats in > particular > > to watch out for. (I've already stumbled upon the fact that I need to > set > > log-slave-updates in order to have binlogs on my slave to be > incrementally > > backed up.) Any thoughts? If this is a totally boneheaded approach, how > > would you recommend going about it? > > No, this is a reasonable and common way to do it. The major gotcha, > in my opinion, is that your slave can have different data from the > master. I wrote mk-table-checksum to verify slaves are in sync with > the master. (http://maatkit.sourceforge.net). > > You may also want to delay a slave by an hour or two, to make > point-in-time recovery easier. There's another tool in Maatkit for > that. > > Have you looked into ZRM from Zmanda? It's really nice. >
