Resyncing slaves with slaves
When dealing with replicated setups with two or more slaves sharing a master, it appears as if a lot of people overlook the obvious. You don’t need to take your master down to resync a slave. I was hoping I wouldn’t need to post about this, but I see people taking down their masters when they have perfectly healthy slaves way too often to let it slip.
You’ve got everything you need on the other slave(s). Provided that it’s in good health, you’ve got all the data, the master’s binlog file name and position. Run SHOW SLAVE STATUS\G on the slave, take note of Relay_Master_Log_File and Exec_Master_Log_Pos which are the same as what you’d get from SHOW MASTER STATUS\G on the master instance, minus the lag which is irrelevant in this case. Then proceed to sync the data from the healthy slave and use the above values in the CHANGE MASTER TO statement (obviously setting MASTER_HOST to the real master, not the other slave).
Happy higher availability!
Posted in MySQL
July 5th, 2008 at 3:55 am
One detail: it is not Master_Log_File. (That is the position of the I/O thread, not the SQL thread). You want to use Relay_Master_Log_File/Exec_Master_Log_Pos.
July 5th, 2008 at 11:37 am
Of course, you’re absolutely right!
Since on a healthy slave, they very rarely differ, it’s never really crossed my mind despite the rather obvious name!
But yeah, there’s always those split split second moments even on a healthy slave when a master have started a new binlog. I’ll edit the post, thanks for pointing it out.