MHA online switching process

MHA online switching process

In addition to automatic monitoring and switching, MySQL MHA provides online failover, which is mostly used for hardware upgrades and MySQL database migration. This mode provides fast switching and elegant blocking of writing, regardless of whether to shut down the original server. The entire switching process takes about 0.5-2 s, greatly reducing the downtime. This article demonstrates MHA online switching and provides the basic steps for online switching.

1. MHA online switching mode and requirements
$ 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. Online switchover steps
A. Check MHA configuration and confirm the current master
B. Determine the new master
C. Blocking write to the current master
D. Wait until all slave servers are synchronized with the existing master
E. Grant write permission on the new master and switch slave database in parallel.
F. Reset the slave of the new master.

3. Demonstrate online Switching
### Obtain masterha_master_switch help
[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.

### Verify whether masterha_manager is enabled currently
[Root @ vdbsrv4 ~] # Masterha_check_status -- conf =/etc/masterha/app1.cnf
App1 is stopped (2: NOT_RUNNING ).

### Before switchover
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 |
+ ----------- + --------- + ------ + ----------- + -------------------------------------- +

### Implement online Switching
[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: