Wednesday, October 17, 2012

MySQL Failover Utility

I was giving a replication talk at Ohio Linux Fest last week and we touched a little bit on the MySQL Failover Utility and how it works with MySQL 5.6 .

The first thing that this along with Innodb and other replication enhancements are big GPL offerings from Oracle to the community.

First the usage:

[root@mysql-master python]# ./mysqlfailover --help
Usage: mysqlfailover --master=roo@localhost --discover-slaves-login=root --candidates=root@host123:3306,root@host456:3306

mysqlfailover - automatic replication health monitoring and failover


....

  --candidates=CANDIDATES
                        connection information for candidate slave servers for
                        failover in the form:
                        <user>:<password>@<host>:<port>:<socket>. Valid only
                        with failover command. List multiple slaves in comma-
                        separated list.
  --discover-slaves-login=DISCOVER
                        at startup, query master for all registered slaves and
                        use the user name and password specified to connect.
                        Supply the user and password in the form
                        user:password. For example, --discover-slaves-
                        login=joe:secret will use 'joe' as the user and
                        'secret' as the password for each discovered slave.
  --exec-after=EXEC_AFTER
                        name of script to execute after failover or switchover


So I set up a test, I will monitor the master @ 192.168.0.17 and have a slave @ 192.168.0.7 all from a server @ 192.168.0.34.

Personally I would run this in screen, with a password on it, so I can have access to it easily from any location.

# screen
# ./mysqlfailover --master=root:<PASSWORD>@192.168.0.17 --discover-slaves-login=root --candidates=root:<PASSWORD>@192.168.0.7

Keep in mind that the password would be easily found in history and ps -ef views.

Now once it executes you realize that you never had GTID on in the first place..... 

ERROR: Topology must support global transaction ids and have GTID_MODE=ON

This is simple enough but 'gtid_mode' is a read only variable so it must be done via the my.cnf file.

vi /etc/my.cnf
[mysqld]
gtid_mode                                             =ON
disable-gtid-unsafe-statements          =1

show variables like '%gtid%';
+--------------------------------+-----------+
| Variable_name                  | Value     |
+--------------------------------+-----------+
| disable_gtid_unsafe_statements | ON        |
| gtid_done                      |           |
| gtid_lost                      |           |
| gtid_mode                      | ON        |
| gtid_next                      | AUTOMATIC |
| gtid_owned                     |           |
+--------------------------------+-----------+


 So now that those are all updated...

Again if your not in a screen this might be a good point to do so..
#screen
# ./mysqlfailover --master=root:<PASSWORD>@192.168.0.17 --discover-slaves-login=root --candidates=root:<PASSWORD>@192.168.0.7

# Discovering slaves for master at 192.168.0.17:3306
# Checking privileges.
# Checking privileges on candidates.

 We get a simple display to start

MySQL Replication Failover Utility
Failover Mode = auto     Next Interval = Wed Oct  3 13:52:01 2012

Master Information
------------------
Binary Log File       Position  Binlog_Do_DB  Binlog_Ignore_DB 
mysql56rc-bin.000016  151                                      

Replication Health Status
+---------------+-------+---------+--------+------------+---------+
| host          | port  | role    | state  | gtid_mode  | health  |
+---------------+-------+---------+--------+------------+---------+
| 192.168.0.17  | 3306  | MASTER  | UP     | ON         | OK      |
+---------------+-------+---------+--------+------------+---------+
Q-quit R-refresh H-health G-GTID Lists U-UUIDs

Transactions executed on the servers:
+---------------+-------+---------+-----------------------------------------+
| host          | port  | role    | gtid                                    |
+---------------+-------+---------+-----------------------------------------+
| 192.168.0.17  | 3306  | MASTER  | 98CCBD8A-EB09-11E1-B4D5-08002701A7D7:1  |
+---------------+-------+---------+-----------------------------------------+

UUIDs
+---------------+-------+---------+---------------------------------------+
| host          | port  | role    | uuid                                  |
+---------------+-------+---------+---------------------------------------+
| 192.168.0.17  | 3306  | MASTER  | 98ccbd8a-eb09-11e1-b4d5-08002701a7d7  |
+---------------+-------+---------+---------------------------------------+


Since we are in screen we can control-A D and exit the screen and resume it later whenever we want to check on the status.

# screen -r

Now lets crash the master...   a quick power off of the virtual box should do it.

The output now refreshes and soon displays the new master.

# Discovering slaves for master at 192.168.0.7:3306

MySQL Replication Failover Utility
Failover Mode = auto     Next Interval = Wed Oct 17 08:50:19 2012

Master Information
------------------
Binary Log File       Position  Binlog_Do_DB  Binlog_Ignore_DB 
mysql56rc-bin.000020  191                                      

Replication Health Status
+--------------+-------+---------+--------+------------+---------+
| host         | port  | role    | state  | gtid_mode  | health  |
+--------------+-------+---------+--------+------------+---------+
| 192.168.0.7  | 3306  | MASTER  | UP     | ON         | OK      |
+--------------+-------+---------+--------+------------+---------+


Now if you added the "--exec-after= " you will be able to execute a script that fits your environment. This script can be used to adjust the reference point for your application to the new master automatically.

Test it out with the new MySQL 5.6 RC... Oracle would love your feedback.