EmergencyEMERGENCY? Get 24/7 Help Now!

Failover with the MySQL Utilities: Part 2 – mysqlfailover

 | July 3, 2014 |  Posted In: Insight for DBAs, MySQL


In the previous post of this series we saw how you could use mysqlrpladmin to perform manual failover/switchover when GTID replication is enabled in MySQL 5.6. Now we will review mysqlfailover (version 1.4.3), another tool from the MySQL Utilities that can be used for automatic failover.


  • mysqlfailover can perform automatic failover if MySQL 5.6’s GTID-replication is enabled.
  • All slaves must use --master-info-repository=TABLE.
  • The monitoring node is a single point of failure: don’t forget to monitor it!
  • Detection of errant transactions works well, but you have to use the --pedantic option to make sure failover will never happen if there is an errant transaction.
  • There are a few limitations such as the inability to only fail over once, or excessive CPU utilization, but they are probably not showstoppers for most setups.


We will use the same setup as last time: one master and two slaves, all using GTID replication. We can see the topology using mysqlfailover with the health command:

Note that --master-info-repository=TABLE needs to be configured on all slaves or the tool will exit with an error message:


You can use 2 commands to trigger automatic failover:

  • auto: the tool tries to find a candidate in the list of servers specified with --candidates, and if no good server is found in this list, it will look at the other slaves to see if one can be a good candidate. This is the default command
  • elect: same as auto, but if no good candidate is found in the list of candidates, other slaves will not be checked and the tool will exit with an error.

Let’s start the tool with auto:

The monitoring console is visible and is refreshed every --interval seconds (default: 15). Its output is similar to what you get when using the health command.

Then let’s kill -9 the master to see what happens once the master is detected as down:

Looks good! The tool is then ready to fail over to another slave if the new master becomes unavailable.

You can also run custom scripts at several points of execution with the --exec-before, --exec-after, --exec-fail-check, --exec-post-failover options.

However it would be great to have a --failover-and-exit option to avoid flapping: the tool would detect master failure, promote one of the slaves, reconfigure replication and then exit (this is what MHA does for instance).

Tool registration

When the tool is started, it registers itself on the master by writing a few things in the specific table:

This is nice as it avoids that you start several instances of mysqlfailover to monitor the same master. If we try, this is what we get:

With the fail command, mysqlfailover will monitor replication health and exit in the case of a master failure, without actually performing failover.

Running in the background

In all previous examples, mysqlfailover was running in the foreground. This is very good for demo, but in a production environment you are likely to prefer running it in the background. This can be done with the --daemon option:

and it can be stopped with:

Errant transactions

If we create an errant transaction on one of the slaves, it will be detected:

However this does not prevent failover from occurring! You have to use --pedantic:


  • Like for mysqlrpladmin, the slave election process is not very sophisticated and it cannot be tuned.
  • The server on which mysqlfailover is running is a single point of failure.
  • Excessive CPU utilization: once it is running, mysqlfailover hogs one core. This is quite surprising.


mysqlfailover is a good tool to automate failover in clusters using GTID replication. It is flexible and looks reliable. Its main drawback is that there is no easy way to make it highly available itself: if mysqlfailover crashes, you will have to manually restart it.

Stephane Combaudon

Stéphane joined Percona in July 2012, after working as a MySQL DBA for leading French companies such as Dailymotion and France Telecom. In real life, he lives in Paris with his wife and their twin daughters. When not in front of a computer or not spending time with his family, he likes playing chess and hiking.


  • The mysqlfailover process is not a SPOF. If it fails the system as a whole continues to run. I think the same is true for MHA. This and also the manual restart issue can be fixed by running mysqlfailover with Solaris SMF, systemd, etc. or by running it on a cluster.

  • Daniël,

    I may not have used the right word: what I meant is that setting up mysqlfailover is not enough to guarantee automated failover for your database cluster. Is it acceptable? Sometimes it is, sometimes it’s not.
    Of course this can be fixed by adding another layer, but it adds some complexity.

  • Hi

    Thanks for such a lovely explanation.
    Really useful!
    Can you please elaborate around the another layer we can add over mysqlfailover which although adds complexity but ensures more guarantee!

    Many thanks!

  • Gurbrinder,

    You could use for instance Pacemaker and Corosync to make mysqlfailover highly available. However there is some glue to write for it to work.
    Another solution could be to use Pacemaker to detect a master failure and let it trigger mysqlrpladmin to perform failover at the MySQL level.

  • Bhavesh,

    You can use the change master to statement:
    mysql> change master to master_host=’new_master_ip’, master_user=’your_repl_user’, master_password=’your_repl_pwd’, master_auto_position=1;
    mysql> start slave;

    As GTIDs are used it’s not necessary to specify binlog coordinates.

  • Hi

    Thanks a ton!
    We use VIP so is it any coding or mechanism by which VIP also failovers at same time when mysqlrpladmin command does it magic of switch over?

  • Gurbrinder,

    mysqlrpladmin will only take care of reconfiguring MySQL replication but you can use the –exec-after and –exec-before options to run external scripts that will move the VIP

  • Hello

    First of all thank you for your post. It is very useful. I have a mysql cluster with gtid replication and my problem is when slave lose sync, automatically it becomes as a master server and this is a problem for us because we have a load balancer that redirect querys based on server’s role and we can have two masters at same time which it is a problem. Is there any way to make that slave server loses sync it does not change the role and will be the same?

    Thank you in advance

  • Javier,

    I don’t understand what you mean when you say that a slave becomes a master when it is out of sync. Could you clarify?

  • Thanks for posting this article. Very helpful.

    I was wondering what your thoughts are for running mysqlfailover on each of the slave hosts. Because of the conflict resolution built into the tool it seems as though only one at a time. Of course, we will try testing it, but was wondering what your thoughts on doing so.

    Thanks, again.

  • Joe,

    You should only have a single mysqlfailover running at each time. Otherwise all the mysqlfailover instances may want to trigger failover at the same time and you may break your topology.

  • Hi, please help me with this error.

    [root@ip-172-31-6-140 ~]# mysqlfailover –master=slave2 –slaves=slave1 health
    # Checking privileges.
    2015-01-22 09:35:50 AM CRITICAL Query failed. 1694 (HY000): Cannot modify @@session.sql_log_bin inside a transaction
    ERROR: Query failed. 1694 (HY000): Cannot modify @@session.sql_log_bin inside a transaction

  • Hi ,

    I have setup replication with one master and one slave. Replication is working perfectly. Then I tried to excecute the mysqfailover command, it does not list slave. I have got the following result

    MySQL Replication Failover Utility
    Failover Mode = auto Next Interval = Tue May

    Master Information
    Binary Log File Position Binlog_Do_DB Binlog
    mysql-bin.000016 9568

    GTID Executed Set

    Replication Health Status
    0 Rows Found.
    Q-quit R-refresh H-health G-GTID Lists U-UUIDs U

    when i try to excecute mysqlrplcheck and mysqlrplshow, it lists my slave and master.

    Can anyone help me .

    I am very knew in this. I have one doubt, Where we need to excecute the mysqlfaiover command – (slave or master) .

  • Configuration files.

    Master my.ini

    expire_logs_days = 30
    log-bin = “C:/logmysql/mysql-bin.log”

    Slave my.ini


  • Thanks for this article!
    I have setup replication with 1 master and 4 slave (5 different physical servers).
    When i stop the master database (but the physical server stay up) the failover take about 15 seconds.
    But when the server is powered off the failover process take about 8 minutes!!!!

  • How to configure the ‘elect’ setting. Can anyone give example? I am running one master 2 slaves traditional topology and would only want one slave to be considered for master promotion in case of master failure.

Leave a Reply