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? Thanks, Martin
