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 '[email protected]' nyc: session established [LOGICAL] /nyc > switch SELECTED SLAVE: [email protected] PURGE REMAINING ACTIVE SESSIONS ON CURRENT MASTER '[email protected]' PURGED A TOTAL OF 0 ACTIVE SESSIONS ON MASTER '[email protected]' FLUSH TRANSACTIONS ON CURRENT MASTER '[email protected]' PUT THE NEW MASTER '[email protected]' ONLINE PUT THE PRIOR MASTER '[email protected]' ONLINE AS A SLAVE RECONFIGURING SLAVE '[email protected]' TO POINT TO NEW MASTER '[email protected]' SWITCH TO '[email protected]' WAS SUCCESSFUL [LOGICAL] /nyc > Exiting...
Peter Laursen says
This is an important point IMHO: “This is ideal for an application that uses persistent connections, e.g. Java applications”. And actually also C/C++ applications most often also do (and not sure, but I think it applies to .NET too).
Too often MySQL debaters are only thinking (or rather they are not!) PHP, Perl, Python when discussing MySQL.
(I am not targeting anyone specifically, but I think I saw some blog recently advising to kill idle connections. Idle connections are commonplace with Java and C/C++ – and they should be allowed to be there)