List:General Discussion« Previous MessageNext Message »
From:Eric Robinson Date:December 4 2009 11:34am
Subject:Here's an Idea for Re-Syncing Master and Slave During Production Hours without Interrupting Users (Much)
View as plain text  
 
Let's face it, sometimes the master and slave get out of sync, even when
'show slave status' and 'show master status' indicate that all is well.
And sometimes it is not feasible to wait until after production hours to
resync them. We've been working on a method to do an emergency
hot-resync during production hours with little or no user downtime. What
do you guys think of this approach? It's only for Linux, though...

1. Shut down the slave and remove its replication logs (master.info and
*relay* files).

2. Do an initial rsync of the master to the slave. Using rsync's
bit-differential algorithm, this quickly copies most of the changed data
and can be safely be done against a live database. This initial rsync is
done before the next step to minimize the time during which the tables
will be read-locked.

3. Do a 'flush tables with read lock;reset master' on the master server.
At this point, user apps may freeze briefly during inserts or updates. 

4. Do a second rsync, which goes very fast because very little data has
changed between steps 2 and 3. 

5. Unlock the master tables.

6. Restart the slave.

When you're done, you have a 100% binary duplicate of the master
database on the slave, with no worries that some queries got missed
somewhere. The master was never stopped and users were not severely
impacted. (Mileage may vary, of course.) 

We've tried this a few times and it has seemed to work well in most
cases. We had once case where the slave SQL thread did not want to
restart afterwards and we had to do the whole thing again, only we
stopped the master the second time. Not yet sure what that was all
about, but I think it may have been a race issue of some kind. We're
still exploring it.

Anyway, comments would be appreciated.

--
Eric Robinson


Disclaimer - December 4, 2009 
This email and any files transmitted with it are confidential and intended solely for
mysql@stripped. If you are not the named addressee you should not disseminate,
distribute, copy or alter this email. Any views or opinions presented in this email are
solely those of the author and might not represent those of . Warning: Although  has
taken reasonable precautions to ensure no viruses are present in this email, the company
cannot accept responsibility for any loss or damage arising from the use of this email or
attachments. 
This disclaimer was added by Policy Patrol: http://www.policypatrol.com/
Thread
Here's an Idea for Re-Syncing Master and Slave During Production Hours without Interrupting Users (Much)Eric Robinson4 Dec
  • Re: Here's an Idea for Re-Syncing Master and Slave During ProductionHours without Interrupting Users (Much)Tom Worster4 Dec
RE: Here's an Idea for Re-Syncing Master and Slave During Production Hours without Interrupting Users (Much)Eric Robinson4 Dec
  • Re: Here's an Idea for Re-Syncing Master and Slave During ProductionHours without Interrupting Users (Much)Tom Worster4 Dec
  • RE: Here's an Idea for Re-Syncing Master and Slave DuringProduction Hours without Interrupting Users (Much)Gavin Towey4 Dec
    • Re: Here's an Idea for Re-Syncing Master and Slave During ProductionHours without Interrupting Users (Much)Tom Worster4 Dec
RE: Here's an Idea for Re-Syncing Master and Slave During Production Hours without Interrupting Users (Much)Eric Robinson4 Dec
  • RE: Here's an Idea for Re-Syncing Master and Slave DuringProduction Hours without Interrupting Users (Much)Gavin Towey4 Dec
  • Re: Here's an Idea for Re-Syncing Master and Slave During Production Hours without Interrupting Users (Much)Baron Schwartz8 Dec
RE: Here's an Idea for Re-Syncing Master and Slave During Production Hours without Interrupting Users (Much)Eric Robinson4 Dec
RE: Here's an Idea for Re-Syncing Master and Slave During Production Hours without Interrupting Users (Much)Eric Robinson10 Dec
  • Re: Here's an Idea for Re-Syncing Master and Slave During Production Hours without Interrupting Users (Much)Baron Schwartz10 Dec