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.