Hi Martijn, Master will not have any information of SLAVE how r u doing this in you second step.
"- check the master servers to find out the names/ports of their slaves" regards anandkl On 1/2/08, Martijn van den Burg <[EMAIL PROTECTED]> wrote: > > 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] > >
