You are here: Home / Technologies / MySQL / Continuent / Controlled failover simplicity with MySQL

Controlled failover simplicity with MySQL

by

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...

Comments

  1. 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)

Trackbacks