Hi Martin, > 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 >
We use replication solely for the purpose of creating backups. Because we use NetApp filers, there's no need to use mysqldump (which would take very long since we have of lot of data in MySQL). What I do is the following. Every hour: - stop slave threads - stop slave server - make snapshot of NetApp qtree and store it in an 'hourly' directory - start slave server - start slave threads Once a day: - do the same thing, store the snapshot in a 'daily' directory. The hourly snapshots are rotated, and we keep five of these. Every day a 'daily' snapshot is made which is kept online for 30 days. I haven't used Baron's tools yet, but I will definitely give them a try, since rebuilding the slave from scratch after replication cannot be reliably restarted (it has issues sometimes) takes much too long with the amount of data we have. Regular (daily) snapshots are created on the master as well, with a running server. This causes data inconsistencies (we use InnoDB as well) but that is of no concern: what it does for us is that it keeps backups of the master binary logs. That way I don't have to run the slaves with 'log-slave-updates', which saves diskspace (couple of gigs a day). I wrote a script that purges the master's binary logfiles, to prevent the disks from filling up with them. This script is run once a day, and goes something like this: - start - check the master servers to find out the names/ports of their slaves - check on the slaves that replication is running and that there's no replication lag - if the slave is not running: alert the admins by email, and quit - purge the binary logs on the master till 'now() - interval 1 days' - end Regards, Martijn -- The information contained in this communication and any attachments is confidential and may be privileged, and is for the sole use of the intended recipient(s). Any unauthorized review, use, disclosure or distribution is prohibited. Unless explicitly stated otherwise in the body of this communication or the attachment thereto (if any), the information is provided on an AS-IS basis without any express or implied warranties or liabilities. To the extent you are relying on this information, you are doing so at your own risk. If you are not the intended recipient, please notify the sender immediately by replying to this message and destroy all copies of this message and any attachments. ASML is neither liable for the proper and complete transmission of the information contained in this communication, nor for any delay in its receipt. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
