Cleanly updating a MySQL Replication Master using Slave promotion

I have 2 MySQL database servers configured as a Master-Slave replication pair. BoxA is the master, BoxB is a slave. There is a CNAME, mysql, pointing to the Master. I want to update the OS with a minimum of user-visible interruption.

As always, there are a variety of precautions one could take to further ensure the availability of your database service and it is up to each environment to determine whether their case warrants extra effort. The process as I describe it here does leave the database service subject to a single point of failure: if BoxB dies while it is the master, then data will likely be lost. The easiest way to mitigate this is to have another machine slaving from BoxB at all times.

The general approach:

Verify that backup procedures are working as expected and restores are possible
Update the OS on the slave
Ensure Slave is writing binary logs (but not logging slave updates)
Promote the slave, BoxB, to become the master
Update the OS on BoxA
Make BoxA a slave of BoxB
Return BoxA to Master status and BoxB to slave status

The first two items will be specific to each environment and anyway are out of the scope of this article.

I will use 'BoxA$' to indicate a root shell prompt on BoxA, the original master and 'BoxB$' to indicate a root shell prompt on BoxB, the original slave; following lines will indicate likely output.

Preparation

Ensure that binary logging is enabled on the slave and slave updates are not logged:

BoxA$ mysql -u root -p -e 'show variables;' | egrep 'log_(bin|slave)' | grep -v trust
log_bin ON
log_slave_updates       OFF

Verify that the slave is up to date, by preventing further updates to the master and checking that the slave is 0 seconds behind the master:

BoxA$ mysql> FLUSH TABLES WITH READ LOCK;
BoxB$ mysql -u root -p -e 'show slave status\G' | grep -i seconds
Seconds_Behind_Master: 0

Promote a Slave to Master status

All that is needed to be done to make this slave a master is to stop slaving.:

BoxB$ mysql> STOP SLAVE;

Then point the CNAME to the BoxB, so that everyone trying to access the database server will be able to do so. At this point, clients will be able to read and write to the database and if you have subsidiary slaves, this is potentially a stable state stopping point, although you now have less replication than before.

Make BoxA a Slave of BoxB

Once BoxA has its new OS, you need to make it a slave of BoxB. This is the same as when you set up replication in the first place. You lock the master, dump the data recording the position in the binary log, unlock the master, load the data into the slave and start slaving with a 'CHANGE MASTER' command.

Return BoxA to Master status

Don't forget to 'RESET MASTER' or it might pickup old updates?

Return BoxB to Slave status

The simple method is to merely act as if you were setting up a new slave. If you can be confident that nothing has changed between the 2 machines during the time it has taken (in other words that you never released the read lock) you can just change the configuration of BoxB with a 'CHANGE MASTER' command.

Coping with more than one slave

BoxB should have log_slave_updates OFF.
If any other slaves are pointed at the CNAME, mysql, they should continue to slave from the binary logs without needing special attention.

AllGoodBits.org