mariusv.com

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!