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
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!