Test the official MySQL Auto-Failover Function

Source: Internet
Author: User
Tags percona server
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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.