MHA online switching and mha Switching

MHA online switching and mha Switching

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: 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] ### it is recommended to deploy the master_ip_online_change_script script, which will automatically block and kill the original master session and set the original master to read-only
Tue Apr 21 15:42:13 2015-[warning] master_ip_online_change_script is not defined. Skipping disabling writes on the current master. # This demo is not specified
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 shoshould start replication from here. Statement shoshould be:
Change master to MASTER_HOST = 'vdbsrv3 or 192.168.1.8 ', MASTER_PORT = 3306, MASTER_LOG_FILE = 'mysql-bin.20.16 ',
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.

### Switch result
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 |
+ ----------- + --------- + ------ + ----------- + -------------------------------------- +