MHA 在线切换过程

雨点打透心脏的1/2处 2022-08-07 17:47 119阅读 0赞

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][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][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 |  
\+-----------+---------+------+-----------+--------------------------------------+

[http_code.google.com_p_mysql-master-ha_wiki_masterha_master_switch]: http://code.google.com/p/mysql-master-ha/wiki/masterha_master_switch
[http_blog.csdn.net_leshami]: http://blog.csdn.net/leshami