As part of a recent engagement, I described the relative products to manage a MySQL pair (i.e. an Active/Passive MySQL masters configuration). This included the steps to undertake a controlled failover for supporting software maintenance using manual procedures. The upcoming Effective MySQL: Replication Techniques in Depth book details each step and all conditions to review over a dozen pages. While the steps are straightforward and generally well known, scripting this for your environment takes a certain amount of work to ensure your information is correct, and application connectivity loss is kept to a minimum.
In Continuent Tungsten (which I have just been reviewing these past few weeks), I achieved the same result with a single command.
$ echo "switch" | /opt/continuent/tungsten/tungsten-manager/bin/cctrl
In addition to all the checks and balances to ensure data is consistent and no information can be lost, Continuent Tungsten Connectors ensure ALL database connections are maintained, i.e. they are not dropped. This is ideal for an application that uses persistent connections, e.g. Java applications. This is a feature that other options do not provide. This command also supports additional read slaves with no additional work.
This post has been in draft for a while, the current chatter on disasters, controlled and uncontrolled failover, and the disaster preparedness for your company information is a very important. There are several options for implementing a more highly available (HA) MySQL solution depending on your business continuity requirements.
The fine print
Here is the full output of the command, between 3 servers, alpha, beta and gamma.
$ echo "switch" | /opt/continuent/tungsten/tungsten-manager/bin/cctrl Tungsten Enterprise 1.5.2 build 69 connect to 'nyc@alpha' nyc: session established [LOGICAL] /nyc > switch SELECTED SLAVE: beta@nyc PURGE REMAINING ACTIVE SESSIONS ON CURRENT MASTER 'alpha@nyc' PURGED A TOTAL OF 0 ACTIVE SESSIONS ON MASTER 'alpha@nyc' FLUSH TRANSACTIONS ON CURRENT MASTER 'alpha@nyc' PUT THE NEW MASTER 'beta@nyc' ONLINE PUT THE PRIOR MASTER 'alpha@nyc' ONLINE AS A SLAVE RECONFIGURING SLAVE 'gamma@nyc' TO POINT TO NEW MASTER 'beta@nyc' SWITCH TO 'beta@nyc' WAS SUCCESSFUL [LOGICAL] /nyc > Exiting...