Summary

Failover vs switchover

mysqlrpladmin can help you promote a slave to be the new master when the master goes down and then automate replication reconfiguration after this slave promotion. There are 2 separate scenarios: unplanned promotion (failover) and planned promotion (switchover). Beyond the words, it has implications on the way you have to execute the tool.

Setup for this test

To test the tool, our setup will be a master with 2 slaves, all using GTID replication.mysqlrpladmincan show us the current replication topology with thehealthcommand:

$ mysqlrpladmin --master=root@localhost:13001 --discover-slaves-login=root health# Discovering slaves for master at localhost:13001# Discovering slave at localhost:13002# Found slave: localhost:13002# Discovering slave at localhost:13003# Found slave: localhost:13003# Checking privileges.## Replication Topology Health:+------------+--------+---------+--------+------------+---------+| host | port | role| state| gtid_mode| health|+------------+--------+---------+--------+------------+---------+| localhost| 13001| MASTER| UP | ON | OK|| localhost| 13002| SLAVE | UP | ON | OK|| localhost| 13003| SLAVE | UP | ON | OK|+------------+--------+---------+--------+------------+---------+# ...done.

As you can see, we have to specify how to connect to the master (no surprise) but instead of listing all the slaves, we can let the tool discover them.

Simple failover scenario

What will the tool do when performing failover? Essentially we will give it the list of slaves and the list of candidates and it will:

After killing -9 the master, let’s try failover:

$ mysqlrpladmin --slaves=root:@localhost:13002,root:@localhost:13003 --candidates=root@localhost:13002 failover

This time, the master is down so the tool has no way to automatically discover the slaves. Thus we have to specify them with the--slavesoption.

However we get an error:

# Checking privileges.# Checking privileges on candidates.ERROR: You must specify either the --rpl-user or set all slaves to use --master-info-repository=TABLE.

The error message is clear, but it would have been nice to have such details when running thehealthcommand (maybe a warning instead of an error). That would allow you to check beforehand that the tool can run smoothly rather than to discover in the middle of an emergency that you have to look at the documentation to find which option is missing.

Let’s choose to specify the replication user:

$ mysqlrpladmin --slaves=root:@localhost:13002,root:@localhost:13003 --candidates=root@localhost:13002 --rpl-user=repl:repl failover# Checking privileges.# Checking privileges on candidates.# Performing failover.# Candidate slave localhost:13002 will become the new master.# Checking slaves status (before failover).# Preparing candidate for failover.# Creating replication user if it does not exist.# Stopping slaves.# Performing STOP on all slaves.# Switching slaves to new master.# Disconnecting new master as slave.# Starting slaves.# Performing START on all slaves.# Checking slaves for errors.# Failover complete.## Replication Topology Health:+------------+--------+---------+--------+------------+---------+| host | port | role| state| gtid_mode| health|+------------+--------+---------+--------+------------+---------+| localhost| 13002| MASTER| UP | ON | OK|| localhost| 13003| SLAVE | UP | ON | OK|+------------+--------+---------+--------+------------+---------+# ...done.

Simple switchover scenario

Let’s now restart the old master and configure it as a slave of the new master (by the way, this can be done withmysqlreplicate, another tool from the MySQL Utilities). If we want to promote the old master, we can run:

$ mysqlrpladmin --master=root@localhost:13002 --new-master=root@localhost:13001 --discover-slaves-login=root --demote-master --rpl-user=repl:repl --quiet switchover# Discovering slave at localhost:13001# Found slave: localhost:13001# Discovering slave at localhost:13003# Found slave: localhost:13003+------------+--------+---------+--------+------------+---------+| host | port | role| state| gtid_mode| health|+------------+--------+---------+--------+------------+---------+| localhost| 13001| MASTER| UP | ON | OK|| localhost| 13002| SLAVE | UP | ON | OK|| localhost| 13003| SLAVE | UP | ON | OK|+------------+--------+---------+--------+------------+---------+

Notice that the master is available in this case so we can use thediscover-slaves-loginoption. Also notice that we can tune the verbosity of the tool by using--quietor--verboseor even log the output in a file with--log.

We also used--demote-masterto make the old master a slave of the new master. Without this option, the old master will be isolated from the other nodes.

Extension points

In general doing switchover/failover at the database level is one thing but informing the other components of the application that something has changed is most often necessary for the application to keep on working correctly.

This is where the extension points are handy: you can execute a script before switchover/failover with--exec-beforeand after switchover/failover with--exec-after.

For instance with these simple scripts:

# cat /usr/local/bin/check_before#!/bin/bash/usr/local/mysql5619/bin/mysql -uroot -S /tmp/node1.sock -Ee 'SHOW SLAVE STATUS' > /tmp/before# cat /usr/local/bin/check_after#!/bin/bash/usr/local/mysql5619/bin/mysql -uroot -S /tmp/node1.sock -Ee 'SHOW SLAVE STATUS' > /tmp/after

We can execute:

$ mysqlrpladmin --master=root@localhost:13001 --new-master=root@localhost:13002 --discover-slaves-login=root --demote-master --rpl-user=repl:repl --quiet --exec-before=/usr/local/bin/check_before --exec-after=/usr/local/bin/check_after switchover

And looking the /tmp/before and /tmp/after, we can see that our scripts have been executed:

# cat /tmp/before# cat /tmp/after*************************** 1. row *************************** Slave_IO_State: Queueing master event to the relay logMaster_Host: localhostMaster_User: replMaster_Port: 13002[...]

If the external script does not seem to work, using –verbose can be useful to diagnose the issue.

What about errant transactions?

We already mentioned that errant transactions can createlots of issueswhen a new master is promoted in a cluster running GTIDs. So the question is: howmysqlrpladminbehaves when there is an errant transaction?

Let’s create an errant transaction:

# On localhost:13003mysql> CREATE DATABASE test2;mysql> FLUSH LOGS;mysql> SHOW BINARY LOGS;+------------------+-----------+| Log_name | File_size |+------------------+-----------+| mysql-bin.000001 | 69309 || mysql-bin.000002 | 1237667 || mysql-bin.000003 | 617 || mysql-bin.000004 | 231 |+------------------+-----------+mysql> PURGE BINARY LOGS TO 'mysql-bin.000004';

and let’s try to promote localhost:13003 as the new master:

$ mysqlrpladmin --master=root@localhost:13001 --new-master=root@localhost:13003 --discover-slaves-login=root --demote-master --rpl-user=repl:repl --quiet switchover[...]+------------+--------+---------+--------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| host | port | role| state| gtid_mode| health|+------------+--------+---------+--------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| localhost| 13003| MASTER| UP | ON | OK|| localhost| 13001| SLAVE | UP | ON | IO thread is not running., Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.', Slave has 1 transactions behind master.|| localhost| 13002| SLAVE | UP | ON | IO thread is not running., Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.', Slave has 1 transactions behind master.|+------------+--------+---------+--------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Oops! Although it is suggested by the documentation, the tool does not check errant transactions. This is a major issue as you cannot run failover/switchover reliably with GTID replication if errant transactions are not correctly detected.

The documentation suggests errant transactions should be checked and a quick look at the code confirms that, but it does not work! So it has beenreported.

Some limitations

Apart from the missing errant transaction check, I also noticed a few limitations:

Conclusion

mysqlrpladminis a very good tool to help you perform manual failover/switchover in a cluster using GTID replication. The main caveat at this point is the failing check for errant transactions, which requires a lot of care before executing the tool.