MySQL official Auto-failover function test detailed

Environment Introduction:
Master:demoenv-trial-1
Slaves:demoenv-trial-2 demoenv-trial-3

1. Install Percona server, on all servers:
$ sudo yum install http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm
$ sudo yum install Percona-server-shared-compat
$ sudo yum install percona-server-server-56

$ sudo yum install http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
$ sudo yum install mysql-utilities

2. Configure/ETC/MY.CNF, on all servers:
Note: Make sure Server-id is different and report-host is the same as its own host name
$ sudo vim/etc/my.cnf

[Mysqld]
# Basic Setting
DataDir =/opt/mysql/data
Tmpdir =/opt/mysql/tmp
Socket =/opt/mysql/run/mysqld.sock
Port = 3306
Pid-file =/opt/mysql/run/mysqld.pid

# InnoDB Setting
Default-storage-engine = INNODB
innodb_file_per_table = 1
Log-bin =/opt/mysql/binlogs/bin-log-mysqld
Log-bin-index =/opt/mysql/binlogs/bin-log-mysqld.index
Innodb_data_home_dir =/opt/mysql/data
Innodb_data_file_path = Ibdata1:10m:autoextend
Innodb_log_group_home_dir =/opt/mysql/data
Binlog-do-db = TestDB

# server ID
Server-id=1

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

# Other Settings
[Mysqld_safe]
Log-error =/opt/mysql/log/mysqld.log
Pid-file =/opt/mysql/run/mysqld.pid
Open-files-limit = 8192

[Mysqlhotcopy]
Interactive-timeout

[Client]
Port = 3306
Socket =/opt/mysql/run/mysqld.sock
Default-character-set = UTF8

3. Create the desired directory on all servers:
$ sudo mkdir-p/opt/mysql/{data,tmp,run,binlogs,log}
$ sudo chown Mysql:mysql/opt/mysql/{data,tmp,run,binlogs,log}

4. Initialize the database on all servers:
$ sudo-i
# Su-mysql
$ mysql_install_db--user=mysql--datadir=/opt/mysql/data/
$ exit
# exit
$ sudo/etc/init.d/mysql Start

5. Create authorized user root@ '% ' to mysqlreplicate for master-slave configuration, on all servers:
$ mysql-uroot

Mysql> Grant all on *.* to root@ '% ' identified by ' Pass ' with GRANT option;
Mysql> quit;

6. Create the users needed for replication on all servers:
$ mysql-uroot

mysql> grant replication Slave on *.* to ' rpl ' @ '% ' identified by ' RPL ';
Mysql> quit;

7. Configure master-slave replication, you can select any one of the Www.111Cn.net server operations:
[Dong.guo@demoenv-trial-1 ~]$ Mysql-uroot

mysql> use MySQL;
mysql> drop user root@ ' demoenv-trial-1 ';
Mysql> quit;

[Dong.guo@demoenv-trial-1 ~]$ mysqlreplicate--master=root:pass@ ' demoenv-trial-1 ': 3306--slave=root: pass@ ' demoenv-trial-2 ': 3306--rpl-user=rpl:rpl

# Master on Demoenv-trial-1: connected.
# slave on Demoenv-trial-2: ... connected.
# Checking for binary logging on master ...
# Set up replication ...
# ... done.

[Dong.guo@demoenv-trial-1 ~]$ mysqlreplicate--master=root:pass@ ' demoenv-trial-1 ': 3306--slave=root: pass@ ' demoenv-trial-3 ': 3306--rpl-user=rpl:rpl

# Master on Demoenv-trial-1: connected.
# slave on demoenv-trial-3: ... connected.
# Checking for binary logging on master ...
# Set up replication ...
# ... done.

8. Verifying data integrity of master-slave replication
[Dong.guo@demoenv-trial-1 ~]$ Mysql-uroot

mysql> CREATE DATABASE TestDB;
Mysql> quit;

[Dong.guo@demoenv-trial-1 ~]$ mysql-uroot-ppass-h ' demoenv-trial-2 '-e ' show databases; '

+-------------------+
| Database |
+-------------------+
| information_schema|
| MySQL |
| performance_schema|
| Test |
| TestDB |
+-------------------+
[Dong.guo@demoenv-trial-1 ~]$ mysql-uroot-ppass-h ' demoenv-trial-3 '-e ' show databases; '

+-------------------+
| Database |
+-------------------+
| information_schema|
| MySQL |
| performance_schema|
| Test |
| TestDB |
+-------------------+
[Dong.guo@demoenv-trial-1 ~]$ mysqlrplshow--master=rpl:rpl@ ' demoenv-trial-1 ': 3306--discover-slaves-login=root: Pass

# Master on Demoenv-trial-1: connected.
# Finding slaves for master:demoenv-trial-1:3306

# Replication Topology Graph
demoenv-trial-1:3306 (MASTER)
|
+---demoenv-trial-2:3306-(SLAVE)
|
+---demoenv-trial-3:3306-(SLAVE)
[Dong.guo@demoenv-trial-1 ~]$ mysqlrplcheck--master=root:pass@ ' demoenv-trial-1 '--slave=root: pass@ ' Demoenv-trial-2 '

# Master on Demoenv-trial-1: connected.
# slave on Demoenv-trial-2: ... connected.
Test Description Status
---------------------
Checking for binary logging in master [pass]
Are there binlog exceptions? [WARN]

+--------+--------+-----------+
| Server | do_db | ignore_db |
+--------+--------+-----------+
| Master |           TestDB | |
+--------+--------+-----------+

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 setting [pass]
Checking slave delay (seconds behind master) [pass]
# ... done.
9. Close Master Test Auto-failover
[Dong.guo@demoenv-trial-1 ~]$ mysqlfailover--master=root:pass@ ' demoenv-trial-1 ': 3306--discover-slaves-login=root :p Ass--rediscover

# Discovering slaves for master at demoenv-trial-1:3306
# Discovering Slave at demoenv-trial-2:3306
# Found slave:demoenv-trial-2:3306
# Discovering Slave at demoenv-trial-3:3306
# Found slave:demoenv-trial-3:3306
# Checking privileges.
# Discovering slaves for master at demoenv-trial-1:3306
MySQL Replication Failover Utility
Failover Mode = auto Next Interval = Sun Oct 20 06:58:52 2013

Master Information
------------------
Binary Log File Position binlog_do_db binlog_ignore_db
bin-log-mysqld.00000 299 TestDB

Gtid executed Set
92df196b-3906-11e3-b6b6-000c290d14d7:1

Replication Health Status
+-----------------+------+--------+-------+-----------+---------------+
| host             | Port | role   | State | Gtid_mode | health        |
+-----------------+------+--------+-------+-----------+---------------+
| demoenv-trial-1 | 3306 | MASTER | up    | on        | ok            |
| demoenv-trial-2 | 3306 | slave  | up    | on        | ok            |
| demoenv-trial-3 | 3306 | slave  | up    | on        | ok            |
+-----------------+------+--------+-------+-----------+---------------+

Q-quit R-refresh h-health G-gtid Lists u-uuids
The terminal that executes the command is then suspended, and another terminal needs to be opened to shut down master:
[Dong.guo@demoenv-trial-1 ~]$ Mysqladmin-uroot-ppass shutdown

Then, on the terminal that just hangs, you can see:

Failover starting in ' Auto ' mode ...
# candidate Slave demoenv-trial-2: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 the all slaves.
# Switching slaves to new master.
# disconnecting new master as slave.
# Starting slaves.
# performing START on the all slaves.
# Checking slaves for errors.
# Failover complete.
# Discovering slaves for master at demoenv-trial-2:3306

Failover console would restart in 5 seconds.

MySQL Replication Failover Utility
Failover Mode = auto Next Interval = Sun Oct 20 07:01:25 2013

Master Information
------------------
Binary Log File Position binlog_do_db binlog_ignore_db
bin-log-mysqld.00000 299 TestDB

Gtid executed Set
92df196b-3906-11e3-b6b6-000c290d14d7:1

Replication Health Status
+-----------------+------+--------+-------+-----------+---------------+
| Host | Port | Role | State | Gtid_mode | Health |
+-----------------+------+--------+-------+-----------+---------------+
| Demoenv-trial-2 | 3306 | MASTER | Up | On | OK |
| demoenv-trial-3 | 3306 | SLAVE | Up | On | OK |
+-----------------+------+--------+-------+-----------+---------------+

Q-quit R-refresh h-health G-gtid Lists u-uuids
The server ' Demoenv-trial-2 ' becomes the new master.

10. Insert Data test master copy
[Dong.guo@demoenv-trial-2 ~]$ Mysql-uroot

mysql> use TestDB;
Database changed
mysql> CREATE TABLE ' HostGroup ' (
-> ' hostgroup_id ' tinyint (4) not NULL auto_increment,
-> ' Hostgroup_name ' char () DEFAULT NULL,
-> ' hostgroup_next ' tinyint (4) not NULL,
-> ' Colo_name ' char (4) Not NULL,
-> PRIMARY KEY (' hostgroup_id ')
->) Engine=innodb DEFAULT Charset=utf8 collate=utf8_unicode_ci;
Query OK, 0 rows affected (0.10 sec)

Mysql> quit;

[Dong.guo@demoenv-trial-2 binlogs]$ mysql-uroot-ppass-h ' demoenv-trial-3 ' testdb-e ' show tables; '

+-----------------+
| tables_in_testdb|
+-----------------+
| HostGroup |
+-----------------+

11. Try to restore the old master

[Dong.guo@demoenv-trial-1 ~]$ sudo/etc/init.d/mysql start
[Dong.guo@demoenv-trial-1 ~]$ mysqlreplicate--master=root:pass@ ' demoenv-trial-2 ': 3306--slave=root: pass@ ' demoenv-trial-1 ': 3306

# Master on Demoenv-trial-2: connected.
# slave on Demoenv-trial-1: ... connected.
# Checking for binary logging on master ...
# Setting Up Replication ...
# ... done.

On a recently suspended terminal, you can see:

...
Replication Health Status
+-----------------+------+--------+-------+-----------+---------------+
| host            | Port | role   | State | Gtid_mode | health        |
+-----------------+------+--------+-------+-----------+---------------+
| demoenv-trial-2 | 3306 | MASTER | up    | on        | ok            |
| demoenv-trial-1 | 3306 | slave  | up    | on        | ok            |
| demoenv-trial-3 | 3306 | slave  | up    | on        | ok            |
+-----------------+------+--------+-------+-----------+---------------+

Q-quit R-refresh h-health G-gtid Lists u-uuids
Restore the Old Master
[Dong.guo@demoenv-trial-1 ~]$ mysqlrpladmin--master=root:pass@ ' demoenv-trial-2 ': 3306--new-master=root: pass@ ' demoenv-trial-1 ': 3306--demote-master--discover-slaves-login=root:pass Switchover

# Discovering slaves for master at demoenv-trial-2:3306
# Discovering Slave at demoenv-trial-1:3306
# Found slave:demoenv-trial-1:3306
# Discovering Slave at demoenv-trial-3:3306
# Found slave:demoenv-trial-3:3306
# Checking privileges.
# Performing switchover from Master in demoenv-trial-2:3306 to slave at demoenv-trial-1:3306.
# Checking candidate Slave Prerequisites.
# Checking slaves configuration to master.
# Waiting for slaves to catch up to Old master.
# Stopping slaves.
# performing STOP on the all slaves.
# demoting Old Master to being a slave to the new master.
# Switching slaves to new master.
# starting all slaves.
# performing START on the all slaves.
# Checking slaves for errors.
# switchover complete.
#
# Replication Topology Health:
+-----------------+------+--------+-------+-----------+---------------+
| Host | Port | Role | State | Gtid_mode | Health |
+-----------------+------+--------+-------+-----------+---------------+
| Demoenv-trial-1 | 3306 | MASTER | Up | On | OK |
| Demoenv-trial-2 | 3306 | SLAVE | Up | On | OK |
| demoenv-trial-3 | 3306 | SLAVE | Up | On | OK |
+-----------------+------+--------+-------+-----------+---------------+
# ... done.
On a recently suspended terminal, you can see:

MySQL Replication Failover Utility
Failover Mode = auto Next Interval = Sun Oct 20 07:30:07 2013

Master Information
------------------
Binary Log File Position binlog_do_db binlog_ignore_db
bin-log-mysqld.00000 710 TestDB

Gtid executed Set
8a58172b-1efd-11e3-8cf1-000c2950fe0c:1 [...]

Replication Health Status
+-----------------+------+--------+-------+-----------+----------------------------------+
| Host | Port | Role | State | Gtid_mode | Health |
+-----------------+------+--------+-------+-----------+----------------------------------+
| Demoenv-trial-2 | 3306 | MASTER | Up | On | OK |
| Demoenv-trial-1 | 3306 | SLAVE | Up | WARN | Slave is isn't connected to master.|
| demoenv-trial-3 | 3306 | SLAVE | Up | WARN | Slave is isn't connected to master.|
+-----------------+------+--------+-------+-----------+----------------------------------+

Q-quit R-refresh h-health G-gtid Lists u-uuids
You can see that failover has stopped working after manually recovering the old master's www.111cn.net.

Press Q, then reboot auto-failover:
[Dong.guo@demoenv-trial-1 ~]$ mysqlfailover--master=root:pass@ ' demoenv-trial-1 ': 3306--discover-slaves-login=root :p Ass--rediscover

...

Replication Health Status
+-----------------+------+--------+-------+-----------+---------------+
| host             | Port | role   | State | Gtid_mode | health        |
+-----------------+------+--------+-------+-----------+---------------+
| demoenv-trial-1 | 3306 | MASTER | up    | on        | ok            |
| demoenv-trial-2 | 3306 | slave  | up    | on        | ok            |
| demoenv-trial-3 | 3306 | slave  | up    | on        | ok            |
+-----------------+------+--------+-------+-----------+---------------+

Q-quit R-refresh h-health G-gtid Lists u-uuids
It can work again.

12. A brief summary of Auto-failover:
It is included in the Mysql-utilities package;
It can only work on the MySQL 5.6 version because of the need for GITDS support;
It can automatically select a slave as the new master when the existing master dies;
However, it does not automatically restore the old master and stops working after manually recovering the old master.