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.
