Ananda, My apologies for the late reply.
To answer your question: I use 'show slave hosts'. It returns: +-----------+-----------+------+-------------------+-----------+ | Server_id | Host | Port | Rpl_recovery_rank | Master_id | +-----------+-----------+------+-------------------+-----------+ | 33 | nlvdhq21 | 3309 | 0 | 32 | | 34 | nlvdhq203 | 3309 | 0 | 32 | +-----------+-----------+------+-------------------+-----------+ Kind regards, Martijn > -----Original Message----- > From: Ananda Kumar [mailto:[EMAIL PROTECTED] > Sent: Wednesday, January 02, 2008 14:06 PM > To: Martijn van den Burg > Cc: Martin Goldman; mysql@lists.mysql.com > Subject: Re: Backing up via slave > > 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] > <http://lists.mysql.com/[EMAIL PROTECTED]> > > > > > -- 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]
