Switch master

Revision as of 02:17, 17 February 2012

The Kinda Easy But Could Be Easier Way (asher users this, domas did something similar)

• cd to your svn checkout of trunk/tools/switch-master
• Have a ~/.my.cnf setup with the root mysql password
• Create a config file according to config.sample for the cluster you're switching. Make sure the secondary master in in the slaves section! (it can be added in for the form of sX-secondary)
• Edit db.php, moving the new master to position 0.
  • Commit to svn in this form (the post-switch version)
  • Resume editing to comment out the old / current master (to drain it of all connections before the switch instead of sending a bunch of new slave queries to it - this results in a quicker swap) and to add this cluster to readOnlyBySection.
  • sync-file wmf-config/db.php "switching master for sX to dbXX"
• run ./switch and step through it
• svn revert db.php
• sync-file wmf-config/db.php "completed master switch for sX"
• make sure to !log the new master binlog and position for the cluster
• edit dns to update the sX-master cname
• edit puppet to update the $masters array in mysql.pp

The easy way (needs some updating)

• DO NOT USE THIS IF THE MASTER IS DOWN ALREADY (unless you really know what you are doing)

• NOTE: This tool doesn't currently switch the secondary master in the replication tree - make sure that is switched as well!! You should also update the $masters section in mysql.pp, and update the $cluster-master or $cluster-secondary dns cnames - they must be accurate for heartbeat monitoring.

cd /home/wikipedia/src/mediawiki/tools/switch-master
php switch.php <old master> <new master>

The hard way

The script above (switch.php) does the following tasks, which you can do manually if you feel that way inclined:

• Check that the new master has binlog enabled. All slaves should be configured this way, for convenience. All servers, including masters, should have read_only mode on by default.
• Run RESET MASTER on the new master
• Log in to the mysql on the old master as root. Check for any long running queries. Kill them. If there is a single long-running query such as a backup, which will restart after you kill it, then get its thread ID ready in a kill command, ready to press enter later.
• Set read-only mode in db.php, sync it.
• Set read-only mode on the old master using SET GLOBAL read_only=1;
• Run FLUSH TABLES on the old master. This will block until all queries complete, so you may need to kill some now.
• Check for slave lag. Slave lag must be zero on the new master before you continue.
• Run STOP SLAVE on the new master
• Run SHOW MASTER STATUS on the new master to get the new replication position
• Run a CHANGE MASTER TO query on each of the slaves including the old master
• Set read/write mode on the new master using SET GLOBAL read_only=0;
• Edit db.php again, turning off read-only mode and simultaneously switching the loads configuration.
• Run RESET SLAVE on the new master, to prevent it from replicating the old master after restart.

What could be easier than managing a replicated cluster in MySQL?