MySQL's auto-failover feature

Today to experience MySQL's auto-failover function, here is a tool MySQL Utilities, it is very powerful. This tool provides the following features:
(1) Management tools (clone, copy, compare, diff, Export, import)
(2) Copy tool (installation, configuration)
(3) General tools (disk usage, redundant indexing, search metadata)
And we use it to implement Master-slave's automatic failover, which begins

master:192.168.13.194
slave:192.168.13.159
This failover needs to be built on the basis of Gtid so the MySQL version must be 5.6 or above

Install Mysql-utilities First
sudo yum install mysql-utilities

Add Gtid parameters to My.cnf (both master and slave)

Binlog-format = ROW
Log-slave-updates = True
Gtid-mode = On
Enforce-gtid-consistency = True
Report-host = 192.168.13.194
Report-port = 3306
Master-info-repository = TABLE
Relay-log-info-repository = TABLE
Sync-master-info = 1

On either node (this command automatically completes the master-slave configuration)
[[email protected] ~]$ mysqlreplicate--master=root:[email protected] ' 192.168.13.194 ': 3306--slave=root:[email Protected] ' 192.168.13.159 ': 3306--rpl-user=root:xxxx
# Master on 192.168.13.194: ... connected.
# slave on 192.168.13.159: ... connected.
# Checking for binary logging on master ...
# Setting Up Replication ...
# ... done.

[Email protected] ~]$ mysql-uroot-pxxxx-h ' 192.168.13.194 '-e ' show databases; '
Warning:using a password on the command line interface can is insecure.
+--------------------+
| Database |
+--------------------+
| Information_schema |
| Ss_test |
| MySQL |
| Performance_schema |
| Pre_test_market |
| Test |
+--------------------+
[Email protected] ~]$ mysql-uroot-pxxxx-h ' 192.168.13.159 '-e ' show databases; '
Warning:using a password on the command line interface can is insecure.
+--------------------+
| Database |
+--------------------+
| Information_schema |
| Ss_test |
| MySQL |
| Performance_schema |
| Pre_test_market |
| Test |
+--------------------+

View master and slave structures
[[email protected] ~]$ mysqlrplshow--master=root:[email protected] ' 192.168.13.194 ': 3306--discover-slaves-login= root:xxxx;
# Master on 192.168.13.194: ... connected.
# Finding slaves for master:192.168.13.194:3306

# Replication Topology Graph
192.168.13.194:3306 (MASTER)
|
+---192.168.13.159:3306-(SLAVE)

View Master and slave status
[[email protected] ~]$ mysqlrplcheck--master=root:[email protected] ' 192.168.13.194 '--slave=root:[email protected] ' 192.168.13.159 '
# Master on 192.168.13.194: ... connected.
# slave on 192.168.13.159: ... connected.
Test Description Status
---------------------------------------------------------------------------
Checking for binary logging on Master [pass]
Is there binlog exceptions? [WARN]

+---------+---------------------------------------+------------+
| Server | do_db | ignore_db |
+---------+---------------------------------------+------------+
| Master |            Pre_test_market,test,ss_test | |
| Slave |            Pre_test_market,test,ss_test | |
+---------+---------------------------------------+------------+

Replication user exists? [Pass]
Checking server_id values [pass]
Checking server_uuid values [pass]
is slave connected to master? [Pass]
Check Master information file [pass]
Checking InnoDB compatibility [pass]
Checking storage engines compatibility [pass]
Checking lower_case_table_names settings [pass]
Checking slave delay (seconds behind master) [pass]
# ... done.

Prepare failover (Note: mysqlfailover sometimes failover Mode = fail, normal should be auto to resolve this error plus parameter--force)
Examples are as follows:
[[email protected] ~]$ mysqlfailover--master=root:[email protected] ' 192.168.13.194 ': 3306--discover-slaves-login= Root:xxxx--rediscover
# Discovering slaves for master at 192.168.13.194:3306
# Discovering Slave at 192.168.13.159:3306
# Found slave:192.168.13.159:3306
Multiple instances of failover console found for master 192.168.13.194:3306.
If This is a error, restart the console with--force.
Failover mode changed to ' FAIL ' for this instance.
Console would start in ten seconds..........starting console.
# Checking privileges.
# Discovering slaves for master at 192.168.13.194:3306

MySQL Replication Failover Utility
Failover Mode = fail Next Interval = Mon June 9 23:04:22 2014

Normal Mode:
[[email protected] ~]$ mysqlfailover--master=root:[email protected] ' 192.168.13.194 ': 3306--discover-slaves-login= Root:xxxx--rediscover
# Discovering slaves for master at 192.168.13.194:3306
# Discovering Slave at 192.168.13.159:3306
# Found slave:192.168.13.159:3306
# Checking privileges.
# Discovering slaves for master at 192.168.13.194:3306

MySQL Replication Failover Utility
Failover Mode = auto Next Interval = Mon June 9 23:56:32 2014

Master Information
------------------
Binary Log File Position binlog_do_db binlog_ignore_db
mysql-bin.000041 191 pre_test_market,test,ss_test

GTID Executed Set
A7e4c60d-62ca-11e3-8710-080027e08a30:1-8

Replication Health Status
+-----------------+-------+---------+--------+------------+---------+
| Host | Port | Role | State | Gtid_mode | Health |
+-----------------+-------+---------+--------+------------+---------+
| 192.168.13.194 | 3306 | MASTER | Up | On | OK |
| 192.168.13.159 | 3306 | SLAVE | Up | On | OK |
+-----------------+-------+---------+--------+------------+---------+

Close Master1
[Email protected] ~]$ mysqladmin-u root-proot shutdown
Warning:using a password on the command line interface can is insecure.

Start automatic failover

Q-quit R-refresh h-health G-gtid Lists u-uuids
140609 23:30:22 mysqld_safe mysqld from PID File/mysql/mysqld.pid ended
Failed to reconnect to the master after 3 attemps.

Failover starting in ' Auto ' mode ...
# candidate Slave 192.168.13.159:3306 would 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.
# Discovering slaves for master at 192.168.13.159:3306
Error connecting to a slave as [e-mail protected]: Cannot connect to the slave server.
Error Can ' t connect to MySQL server on ' 192.168.13.194:3306 ' (111 Connection refused)

Warning:there is slaves that had connection errors.

Failover console would restart in 5 seconds.

MySQL Replication Failover Utility
Failover Mode = auto Next Interval = Mon June 9 23:31:40 2014

Master Information
------------------
Binary Log File Position binlog_do_db binlog_ignore_db
mysql-bin.000051 1688 Pre_test_market,test,ss_test

GTID Executed Set
A7e4c60d-62ca-11e3-8710-080027e08a30:1-8

Replication Health Status
+-----------------+-------+---------+--------+------------+---------+
| Host | Port | Role | State | Gtid_mode | Health |
+-----------------+-------+---------+--------+------------+---------+
| 192.168.13.159 | 3306 | MASTER | Up | On | OK |
+-----------------+-------+---------+--------+------------+---------+
The switchover was successful. Slave formally switch to master

Recovery Master1
[Email protected] ~]$ Mysqld_safe &
140609 23:32:04 mysqld_safe Logging to '/mysql/mysqld.log '.
140609 23:32:04 Mysqld_safe starting mysqld daemon with databases From/mysql/data

Add the original Master1 to the new
[[email protected] ~]$ mysqlreplicate--master=root:[email protected] ' 192.168.13.159 ': 3306--slave=root:[email Protected] ' 192.168.13.194 ': 3306--rpl-user=root:xxxx
# Master on 192.168.13.159: ... connected.
# slave on 192.168.13.194: ... connected.
# Checking for binary logging on master ...
# Setting Up Replication ...
# ... done.

MySQL Replication Failover Utility
Failover Mode = auto Next Interval = Mon June 9 23:34:04 2014

Master Information
------------------
Binary Log File Position binlog_do_db binlog_ignore_db
mysql-bin.000051 1688 Pre_test_market,test,ss_test

GTID Executed Set
A7e4c60d-62ca-11e3-8710-080027e08a30:1-8

Replication Health Status
+-----------------+-------+---------+--------+------------+---------+
| Host | Port | Role | State | Gtid_mode | Health |
+-----------------+-------+---------+--------+------------+---------+
| 192.168.13.159 | 3306 | MASTER | Up | On | OK |
| 192.168.13.194 | 3306 | SLAVE | Up | On | OK |
+-----------------+-------+---------+--------+------------+---------+

Master-Slave switchover reverts to status
[[email protected] ~]$ mysqlrpladmin--master=root:[email protected] ' 192.168.13.159 ': 3306--new-master=root:[email Protected] ' 192.168.13.194 ': 3306--demote-master--discover-slaves-login=root:xxxx Switchover
# Discovering slaves for master at 192.168.13.159:3306
# Discovering Slave at 192.168.13.194:3306
# Found slave:192.168.13.194:3306
# Checking privileges.
# Performing switchover from master at 192.168.13.159:3306 to slave at 192.168.13.194:3306.
# Checking candidate Slave Prerequisites.
# Checking slaves configuration to master.
# Waiting for slaves to catch-to-old master.
# Stopping slaves.
# performing STOP on all slaves.
# demoting Old Master to is a slave to the new master.
# Switching slaves to new master.
# starting all slaves.
# performing START on all slaves.
# Checking slaves for errors.
# switchover complete.
#
# Replication Topology Health:
+-----------------+-------+---------+--------+------------+---------+
| Host | Port | Role | State | Gtid_mode | Health |
+-----------------+-------+---------+--------+------------+---------+
| 192.168.13.194 | 3306 | MASTER | Up | On | OK |
| 192.168.13.159 | 3306 | SLAVE | Up | On | OK |
+-----------------+-------+---------+--------+------------+---------+
# ... done.

As we recovered from the new. So the failover interaction mode fails to return to normal in the new entry
MySQL Replication Failover Utility
Failover Mode = auto Next Interval = Mon June 9 23:35:35 2014

Master Information
------------------
Binary Log File Position binlog_do_db binlog_ignore_db
mysql-bin.000051 1688 Pre_test_market,test,ss_test

GTID Executed Set
A7e4c60d-62ca-11e3-8710-080027e08a30:1-8

Replication Health Status
+-----------------+-------+---------+--------+------------+------------------------------------+
| Host | Port | Role | State | Gtid_mode | Health |
+-----------------+-------+---------+--------+------------+------------------------------------+
| 192.168.13.159 | 3306 | MASTER | Up | On | OK |
| 192.168.13.194 | 3306 | SLAVE |            WARN | |  Slave is not connected to master. |
+-----------------+-------+---------+--------+------------+------------------------------------+

[[email protected] ~]$ mysqlfailover--master=root:[email protected] ' 192.168.13.194 ': 3306--discover-slaves-login= Root:xxxx--rediscover--force
# Discovering slaves for master at 192.168.13.194:3306
# Discovering Slave at 192.168.13.159:3306
# Found slave:192.168.13.159:3306
# Checking privileges.
# Discovering slaves for master at 192.168.13.194:3306

MySQL Replication Failover Utility
Failover Mode = auto Next Interval = Mon June 9 23:36:10 2014

Master Information
------------------
Binary Log File Position binlog_do_db binlog_ignore_db
mysql-bin.000041 191 pre_test_market,test,ss_test

GTID Executed Set
A7e4c60d-62ca-11e3-8710-080027e08a30:1-8

Replication Health Status
+-----------------+-------+---------+--------+------------+---------+
| Host | Port | Role | State | Gtid_mode | Health |
+-----------------+-------+---------+--------+------------+---------+
| 192.168.13.194 | 3306 | MASTER | Up | On | OK |
| 192.168.13.159 | 3306 | SLAVE | Up | On | OK |
+-----------------+-------+---------+--------+------------+---------+

Finally, a few simple words:
The premise of Auto failover first is that MySQL must be 5.6 and must start Gtid.

After master crash, the slave can be automatically promoted to new master, but the old Master will need to be added manually after the recovery. And the failover interaction mode will also fail to function properly from the new display. It's the first time today. ^_^

MySQL's auto-failover feature