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 switchoverSo 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= " Test it out with the new MySQL 5.6 RC... Oracle would love your feedback.
