Test the official MySQL Auto-Failover Function

Reference: www. clusterdb. commysqlreplication-and-auto-failover-made-easy-with-mysql-utilities Environment Introduction: master: demoenv-trial-1slaves: demoenv-trial-2demoenv-trial-31. install PerconaServer on all servers: $ sudoyuminstallht

References: http://www.clusterdb.com/mysql/replication-and-auto-failover-made-easy-with-mysql-utilities Environment Introduction: master: demoenv-trial-1 slaves: demoenv-trial-2 demoenv-trial-3 1. install Percona Server on all servers: $ sudo yum install ht

References:
Http://www.clusterdb.com/mysql/replication-and-auto-failover-made-easy-with-mysql-utilities

Environment Introduction:
Master: demoenv-trial-1
Sles: 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 that the server-id is different and the report-host is the same as the host name.
$ Sudo vim/etc/my. cnf

[mysqld]# basic settingdatadir = /opt/mysql/datatmpdir = /opt/mysql/tmpsocket = /opt/mysql/run/mysqld.sockport = 3306pid-file = /opt/mysql/run/mysqld.pid# innodb settingdefault-storage-engine = INNODBinnodb_file_per_table = 1log-bin = /opt/mysql/binlogs/bin-log-mysqldlog-bin-index = /opt/mysql/binlogs/bin-log-mysqld.indexinnodb_data_home_dir = /opt/mysql/datainnodb_data_file_path = ibdata1:10M:autoextendinnodb_log_group_home_dir = /opt/mysql/databinlog-do-db = testdb# server idserver-id=1# gtids settingbinlog-format = ROWlog-slave-updates = truegtid-mode = onenforce-gtid-consistency = truereport-host = demoenv-trial-1report-port = 3306master-info-repository = TABLErelay-log-info-repository = TABLEsync-master-info = 1# other settings[mysqld_safe]log-error = /opt/mysql/log/mysqld.logpid-file = /opt/mysql/run/mysqld.pidopen-files-limit = 8192[mysqlhotcopy]interactive-timeout[client]port = 3306socket = /opt/mysql/run/mysqld.sockdefault-character-set = utf8

3. Create a 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 an authorized user root @ '%' to configure master-slave Replication through mysqlreplicate, on all servers:
$ Mysql-uroot

mysql> grant all on *.* to root@'%' identified by 'pass' with grant option;mysql> quit;

6. Create the users required 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 server for operation:
[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 @ 'demo-trial-1': 3306 -- slave = root: pass @ 'demo-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 @ 'demo-trial-1': 3306 -- slave = root: pass @ 'demo-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. verify the 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 Graphdemoenv-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 on 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. Disable the master to test auto-failover.
[Dong. guo @ demoenv-trial-1 ~] $ Mysqlfailover -- master = root: pass @ 'demoenv-trial-1': 3306 -- discover-slaves-login = root: pass -- 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:3306MySQL Replication Failover UtilityFailover Mode = auto     Next Interval = Sun Oct 20 06:58:52 2013Master Information------------------Binary Log File       Position  Binlog_Do_DB  Binlog_Ignore_DB  bin-log-mysqld.00000  299       testdb                          GTID Executed Set92df196b-3906-11e3-b6b6-000c290d14d7:1Replication 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

Then, the terminal that executes the command is suspended. You need to enable another terminal to close the master:
[Dong. guo @ demoenv-trial-1 ~] $ Mysqladmin-uroot-ppass shutdown

Next, you can see on the suspended terminal:

Failover starting in 'auto' mode...# Candidate slave demoenv-trial-2:3306 will 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 demoenv-trial-2:3306Failover console will restart in 5 seconds.MySQL Replication Failover UtilityFailover Mode = auto     Next Interval = Sun Oct 20 07:01:25 2013Master Information------------------Binary Log File       Position  Binlog_Do_DB  Binlog_Ignore_DB  bin-log-mysqld.00000  299       testdb                          GTID Executed Set92df196b-3906-11e3-b6b6-000c290d14d7:1Replication 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' is changed to a new master.

10. insert data to test master-slave Replication
[Dong. guo @ demoenv-trial-2 ~] $ Mysql-uroot

mysql> use testdb;Database changedmysql> CREATE TABLE `hostgroup` (    ->   `hostgroup_id` tinyint(4) NOT NULL AUTO_INCREMENT,    ->   `hostgroup_name` char(20) 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 @ 'demo-trial-2': 3306 -- slave = root: pass @ 'demo-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 the 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 @ 'demo-trial-2': 3306 -- new-master = root: pass @ 'demo-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 at 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 all slaves.# Demoting old master to be 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        |+-----------------+------+--------+-------+-----------+---------------+| 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 the suspended terminal, you can see:

MySQL Replication Failover UtilityFailover Mode = auto     Next Interval = Sun Oct 20 07:30:07 2013Master Information------------------Binary Log File       Position  Binlog_Do_DB  Binlog_Ignore_DB  bin-log-mysqld.00000  710       testdb                          GTID Executed Set8a58172b-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 not connected to master.|| demoenv-trial-3 | 3306 | SLAVE  | UP    | WARN      | Slave is not connected to master.|+-----------------+------+--------+-------+-----------+----------------------------------+Q-quit R-refresh H-health G-GTID Lists U-UUIDs

You can see that failover has stopped working after manual recovery of the old master.

Press Q and restart auto-failover:
[Dong. guo @ demoenv-trial-1 ~] $ Mysqlfailover -- master = root: pass @ 'demoenv-trial-1': 3306 -- discover-slaves-login = root: pass -- 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 MySQL 5.6 because GITDs is required;
It can automatically select a Server Load balancer instance as the new master instance. When the existing master instance dies;
However, it cannot automatically restore the old master and stops working after manual restoration.

Original article address: MySQL official Auto-Failover function test, thanks to the original author for sharing.