MHA 線上切換過程，mha切換過程

MHA 線上切換過程，mha切換過程

    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 |
+-----------+---------+------+-----------+--------------------------------------+