MySQL MHA 在线切换是MHA除了自动监控切换换提供的另外一种方式,多用于诸如硬件升级,MySQL数据库迁移等等。该方式提供快速切换和优雅的阻塞写入,无关关闭原有服务器,整个切换过程在0.5-2s 的时间左右,大大减少了停机时间。本文演示了MHA 在线切换并给出了在线切换的基本步骤。
1、MHA在线切换方式及要求 $ masterha_master_switch --master_state=alive --conf=/etc/app1.cnf --new_master_host=host2 a、SQL threads on all slaves are running b、Seconds_Behind_Master on all slaves are less or equal than --running_updates_limit seconds c、On master, none of update queries take more than --running_updates_limit seconds in the show processlist output
2、在线进行切换基本步骤 a、检测MHA配置置及确认当前master b、决定新的master c、阻塞写入到当前master d、等待所有从服务器与现有master完成同步 e、在新master授予写权限,以及并行切换从库 f、重置原master为新master的slave
3、演示在线切换 ###获取masterha_master_switch帮助 [root@vdbsrv4 ~]# masterha_master_switch --help Usage: # For master failover
masterha_master_switch --master_state=dead --global_conf=/etc/masterha_default.cnf --conf=/usr/local/masterha/conf/app1.cnf --dead_master_host=host1
# For online master switch
masterha_master_switch --master_state=alive --global_conf=/etc/masterha_default.cnf --conf=/usr/local/masterha/conf/app1.cnf
See online reference (http://code.google.com/p/mysql-master-ha/wiki/masterha_master_switch) for details.
###校验当前是否启用masterha_manager [root@vdbsrv4 ~]# masterha_check_status --conf=/etc/masterha/app1.cnf app1 is stopped(2:NOT_RUNNING).
###切换前 mysql> show slave hosts; +-----------+---------+------+-----------+--------------------------------------+ | Server_id | Host | Port | Master_id | Slave_UUID | +-----------+---------+------+-----------+--------------------------------------+ | 1001 | vdbsrv2 | 3306 | 1 | 75bef614-e342-11e4-921d-000c295fb2eb | | 1002 | vdbsrv3 | 3306 | 1 | 091f79b8-e386-11e4-93d5-000c2943c830 | +-----------+---------+------+-----------+--------------------------------------+
###实施在线切换 [root@vdbsrv4 ~]# masterha_master_switch --conf=/etc/masterha/app1.cnf --master_state=alive --new_master_host=vdbsrv3 \ > --orig_master_is_new_slave --running_updates_limit=10000 --interactive=0 Tue Apr 21 15:42:13 2015 - [info] MHA::MasterRotate version 0.56. Tue Apr 21 15:42:13 2015 - [info] Starting online master switch.. Tue Apr 21 15:42:13 2015 - [info] Tue Apr 21 15:42:13 2015 - [info] * Phase 1: Configuration Check Phase.. Tue Apr 21 15:42:13 2015 - [info] Tue Apr 21 15:42:13 2015 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Tue Apr 21 15:42:13 2015 - [info] Reading application default configuration from /etc/masterha/app1.cnf.. Tue Apr 21 15:42:13 2015 - [info] Reading server configuration from /etc/masterha/app1.cnf.. Tue Apr 21 15:42:13 2015 - [info] GTID failover mode = 0 Tue Apr 21 15:42:13 2015 - [info] Current Alive Master: vdbsrv1(192.168.1.6:3306) Tue Apr 21 15:42:13 2015 - [info] Alive Slaves: Tue Apr 21 15:42:13 2015 - [info] vdbsrv2(192.168.1.7:3306) Version=5.6.22-log (oldest major version between slaves) log-bin:enabled Tue Apr 21 15:42:13 2015 - [info] Replicating from 192.168.1.6(192.168.1.6:3306) Tue Apr 21 15:42:13 2015 - [info] vdbsrv3(192.168.1.8:3306) Version=5.6.22-log (oldest major version between slaves) log-bin:enabled Tue Apr 21 15:42:13 2015 - [info] Replicating from 192.168.1.6(192.168.1.6:3306) Tue Apr 21 15:42:13 2015 - [info] Executing FLUSH NO_WRITE_TO_BINLOG TABLES. This may take long time.. Tue Apr 21 15:42:13 2015 - [info] ok. Tue Apr 21 15:42:13 2015 - [info] Checking MHA is not monitoring or doing failover.. Tue Apr 21 15:42:13 2015 - [info] Checking replication health on vdbsrv2.. ###Author : Leshami Tue Apr 21 15:42:13 2015 - [info] ok. ###Blog : http://blog.csdn.net/leshami Tue Apr 21 15:42:13 2015 - [info] Checking replication health on vdbsrv3.. Tue Apr 21 15:42:13 2015 - [info] ok. Tue Apr 21 15:42:13 2015 - [info] vdbsrv3 can be new master. Tue Apr 21 15:42:13 2015 - [info] From: vdbsrv1(192.168.1.6:3306) (current master) +--vdbsrv2(192.168.1.7:3306) +--vdbsrv3(192.168.1.8:3306)
To: vdbsrv3(192.168.1.8:3306) (new master) +--vdbsrv2(192.168.1.7:3306) +--vdbsrv1(192.168.1.6:3306) Tue Apr 21 15:42:13 2015 - [info] Checking whether vdbsrv3(192.168.1.8:3306) is ok for the new master.. Tue Apr 21 15:42:13 2015 - [info] ok. Tue Apr 21 15:42:13 2015 - [info] vdbsrv1(192.168.1.6:3306): SHOW SLAVE STATUS returned empty result. To check replication filtering rules, temporarily executing CHANGE MASTER to a dummy host. Tue Apr 21 15:42:13 2015 - [info] vdbsrv1(192.168.1.6:3306): Resetting slave pointing to the dummy host. Tue Apr 21 15:42:13 2015 - [info] ** Phase 1: Configuration Check Phase completed. Tue Apr 21 15:42:13 2015 - [info] Tue Apr 21 15:42:13 2015 - [info] * Phase 2: Rejecting updates Phase.. Tue Apr 21 15:42:13 2015 - [info] ###建议部署master_ip_online_change_script 脚本,该脚本会自动阻塞以及kill原master session,置原master为只读 Tue Apr 21 15:42:13 2015 - [warning] master_ip_online_change_script is not defined. Skipping disabling writes on the current master.#此演示未指定 Tue Apr 21 15:42:13 2015 - [info] Locking all tables on the orig master to reject updates from everybody (including root): Tue Apr 21 15:42:13 2015 - [info] Executing FLUSH TABLES WITH READ LOCK.. Tue Apr 21 15:42:13 2015 - [info] ok. Tue Apr 21 15:42:13 2015 - [info] Orig master binlog:pos is mysql-bin.000023:651371104. Tue Apr 21 15:42:13 2015 - [info] Waiting to execute all relay logs on vdbsrv3(192.168.1.8:3306).. Tue Apr 21 15:42:13 2015 - [info] master_pos_wait(mysql-bin.000023:651371104) completed on vdbsrv3(192.168.1.8:3306). Executed 0 events. Tue Apr 21 15:42:13 2015 - [info] done. Tue Apr 21 15:42:13 2015 - [info] Getting new master's binlog name and position.. Tue Apr 21 15:42:13 2015 - [info] mysql-bin.000016:301477519 Tue Apr 21 15:42:13 2015 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='vdbsrv3 or 192.168.1.8', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000016', MASTER_LOG_POS=301477519, MASTER_USER='repl', MASTER_PASSWORD='xxx'; Tue Apr 21 15:42:13 2015 - [info] Tue Apr 21 15:42:13 2015 - [info] * Switching slaves in parallel.. Tue Apr 21 15:42:13 2015 - [info] Tue Apr 21 15:42:13 2015 - [info] -- Slave switch on host vdbsrv2(192.168.1.7:3306) started, pid: 13099 Tue Apr 21 15:42:13 2015 - [info] Tue Apr 21 15:42:13 2015 - [info] Log messages from vdbsrv2 ... Tue Apr 21 15:42:13 2015 - [info] Tue Apr 21 15:42:13 2015 - [info] Waiting to execute all relay logs on vdbsrv2(192.168.1.7:3306).. Tue Apr 21 15:42:13 2015 - [info] master_pos_wait(mysql-bin.000023:651371104) completed on vdbsrv2(192.168.1.7:3306). Executed 0 events. Tue Apr 21 15:42:13 2015 - [info] done. Tue Apr 21 15:42:13 2015 - [info] Resetting slave vdbsrv2(192.168.1.7:3306) and starting replication from the new master vdbsrv3(192.168.1.8:3306).. Tue Apr 21 15:42:13 2015 - [info] Executed CHANGE MASTER. Tue Apr 21 15:42:13 2015 - [info] Slave started. Tue Apr 21 15:42:13 2015 - [info] End of log messages from vdbsrv2 ... Tue Apr 21 15:42:13 2015 - [info] Tue Apr 21 15:42:13 2015 - [info] -- Slave switch on host vdbsrv2(192.168.1.7:3306) succeeded. Tue Apr 21 15:42:13 2015 - [info] Unlocking all tables on the orig master: Tue Apr 21 15:42:13 2015 - [info] Executing UNLOCK TABLES.. Tue Apr 21 15:42:13 2015 - [info] ok. Tue Apr 21 15:42:13 2015 - [info] Starting orig master as a new slave.. Tue Apr 21 15:42:13 2015 - [info] Resetting slave vdbsrv1(192.168.1.6:3306) and starting replication from the new master vdbsrv3(192.168.1.8:3306).. Tue Apr 21 15:42:13 2015 - [info] Executed CHANGE MASTER. Tue Apr 21 15:42:13 2015 - [info] Slave started. Tue Apr 21 15:42:13 2015 - [info] All new slave servers switched successfully. Tue Apr 21 15:42:13 2015 - [info] Tue Apr 21 15:42:13 2015 - [info] * Phase 5: New master cleanup phase.. Tue Apr 21 15:42:13 2015 - [info] Tue Apr 21 15:42:13 2015 - [info] vdbsrv3: Resetting slave info succeeded. Tue Apr 21 15:42:13 2015 - [info] Switching master to vdbsrv3(192.168.1.8:3306) completed successfully.
###切换结果 mysql> show slave hosts; +-----------+---------+------+-----------+--------------------------------------+ | Server_id | Host | Port | Master_id | Slave_UUID | +-----------+---------+------+-----------+--------------------------------------+ | 1 | vdbsrv1 | 3306 | 1002 | f2824060-e2cb-11e4-8f18-000c2926f457 | | 1001 | vdbsrv2 | 3306 | 1002 | 75bef614-e342-11e4-921d-000c295fb2eb | +-----------+---------+------+-----------+--------------------------------------+