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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
