首頁 > 數據庫 > MySQL > 正文

MySQL高可用方案MHA在線切換的步驟及原理

2022-08-01 18:57:29
字體:
來源:轉載
供稿:網友
      在日常工作中,會碰到如下的場景,如mysql數據庫升級,主服務器硬件升級等,這個時候就需要將寫操作切換到另外一臺服務器上,那么如何進行在線切換呢?同時,要求切換過程短,對業務的影響比較小。
 
       MHA就提供了這樣一種優雅的方式,只會堵塞業務0.5~2s的時間,在這段時間內,業務無法讀取和寫入。
  
集群信息
 
角色                             IP地址                 ServerID      類型
 
Master                         192.168.244.10   1                 寫入
 
Candicate master          192.168.244.20   2                 讀
 
Slave                           192.168.244.30   3                 讀
 
Monitor host                 192.168.244.40                      監控集群組
  
MHA具體的搭建步驟和原理,可參考另外一篇博客:
 
MySQL高可用方案MHA的部署和原理
  
在線切換的步驟
 
1. 關閉MHA監控
 
# masterha_stop --conf=/etc/masterha/app1.cnf
 
2. 在線切換
 
# /usr/local/bin/masterha_master_switch --conf=/etc/masterha/app1.cnf --master_state=alive --new_master_host=192.168.244.20 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000
 
其中,
 
      --orig_master_is_new_slave是將原master切換為新主的slave,默認情況下,是不添加的。
 
     --running_updates_limit默認為1s,即如果主從延遲時間(Seconds_Behind_Master),或master show processlist中dml操作大于1s,則不會執行切換。
 
在線切換的輸出
 
MySQL高可用方案MHA在線切換的步驟及原理
Tue Apr  ::  - [] MHA::MasterRotate version  ::  - [ ::  - [ ::  - [] * Phase  ::  - [ ::  - [warning] Global configuration  /etc/ ::  - [] Reading application default configuration from /etc/masterha/ ::  - [] Reading server configuration from /etc/masterha/ ::  - [] GTID failover mode =  ::  - [] Current Alive Master: .(.: ::  - [ ::  - []   .(.:)  Version=.--bin:enabledTue Apr  ::  - []     Replicating from .(.: ::  - []     Primary candidate  ::  - []   .(.:)  Version=.--bin:enabledTue Apr  ::  - []     Replicating from .(.:.(:)? (YES/no): yes
Tue Apr  ::  - [] Executing FLUSH NO_WRITE_TO_BINLOG TABLES. This may take   ::  - [ ::  - [ ::  - [] Checking replication health on . ::  - [ ::  - [] Checking replication health on . ::  - [ ::  - [] . ::  - [.(.:+--.(.:+--.(.:.(.:+--.(.:+--.(.:.(.:) to .(.:)? (yes/ ::  - [] Checking whether .(.:) is ok  ::  - [ ::  - [] .(.: ::  - [] .(.: ::  - [] ** Phase  ::  - [ ::  - [] * Phase  ::  - [ ::  - [] Executing master ip online change script to disable  ::  - []   /usr/local/bin/master_ip_online_change --command=stop --orig_master_host=. --=. --orig_master_port= --orig_master_user= --orig_master_password= --new_master_host=. --new_master_ip=. --new_master_port= --new_master_user= --new_master_password= --orig_master_ssh_user=root --new_master_ssh_user=root   --orig_master_is_new_slaveTue Apr  ::   ::   Set read_only= ::   ::   . ::  - [ ::  - [ ::  - [ ::  - [ ::  - [] Orig master binlog:pos is mysql-bin.: ::  - []  Waiting to execute all relay logs on .(.: ::  - []  master_pos_wait(mysql-bin.:) completed on .(.:). Executed  ::  - []    ::  - [] Getting new masterTue Apr  ::  - []  mysql-bin.: ::  - [=, MASTER_PORT=, MASTER_LOG_FILE=, MASTER_LOG_POS=, MASTER_USER=, MASTER_PASSWORD=;Tue Apr  ::  - [] Executing master ip online change script to allow  ::  - []   /usr/local/bin/master_ip_online_change --command=start --orig_master_host=. --=. --orig_master_port= --orig_master_user= --orig_master_password= --new_master_host=. --new_master_ip=. --new_master_port= --new_master_user= --new_master_password= --orig_master_ssh_user=root --new_master_ssh_user=root   --orig_master_is_new_slaveTue Apr  ::   Set read_only=. on the new master: . ::  - [ ::  - [ ::  - [] * Switching slaves  ::  - [ ::  - [] -- Slave switch on host .(.:) started, pid:  ::  - [ ::  - [] Log messages from . ::  - [ ::  - []  Waiting to execute all relay logs on .(.: ::  - []  master_pos_wait(mysql-bin.:) completed on .(.:). Executed  ::  - []    ::  - []  Resetting slave .(.:) and starting replication from the new master .(.:)..Tue Apr  ::  - [ ::  - [ ::  - [] End of log messages from . ::  - [ ::  - [] -- Slave switch on host .(.: ::  - [ ::  - [ ::  - [ ::  - [ ::  - []  Resetting slave .(.:) and starting replication from the new master .(.:)..Tue Apr  ::  - [ ::  - [ ::  - [ ::  - [ ::  - [] * Phase  ::  - [ ::  - []  .: Resetting slave  ::  - [] Switching master to .(.:) completed successfully.
MySQL高可用方案MHA在線切換的步驟及原理
 
 
MHA在線切換的原理
 
1. 檢查當前的配置信息及主從服務器的信息
 
    包括讀取MHA的配置文件/etc/masterha/app1.cnf及檢查當前slave的健康狀態
 
2. 阻止對當前master的更新
 
   主要通過如下步驟:
 
   1> 等待1.5s($time_until_kill_threads*100ms),等待當前連接斷開。
 
   2> 執行 read_only=1,阻止新的DML操作
 
   3> 等待0.5s,等待當前DML操作完成。
 
   4> kill掉所有連接。
 
   5> FLUSH NO_WRITE_TO_BINLOG TABLES
 
   6> FLUSH TABLES WITH READ LOCK
 
3. 等待新master執行完所有的relay log
 
Waiting to execute all relay logs on 192.168.244.20(192.168.244.20:3306)..
4. 將新master的read_only設置為off,并添加VIP
 
5. slave切換到新master上。
 
   1> 等待slave(192.168.244.30)應用完原主從復制產生的relay log,然后執行change master操作切換到新master上。
 
   2> 釋放原master上加的鎖。
 
   3> 因masterha_master_switch命令行中帶有--orig_master_is_new_slave參數,故原master也切換為新master的從。
 
6. 清理新master的相關信息。
 
    主要是執行了reset slave all操作,清除之前的復制信息。
  
MHA在線切換需滿足的條件
 
MHA在執行在線切換之前,會判斷當前的主從復制信息,只有滿足了以下條件,才能執行切換動作:
 
1. 所有SLAVE的IO線程和SQL線程都在運行。
 
2. 所有slave的Seconds_Behind_Master小于或等于running_updates_limit的值,該參數如果沒有顯示指定的話,則默認為1s
 
3. 在master上,通過show processlist輸出,沒有一個DML操作的時間大于running_updates_limit的值。
  
在線切換時,打開general log,各個服務器的操作信息
 
注:在執行masterha_master_switch命令時,會有兩次確認操作
 
1. It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the master before switching. Is it ok to execute on 192.168.244.10(192.168
.244.10:3306)? (YES/no):
 
2. Starting master switch from 192.168.244.10(192.168.244.10:3306) to 192.168.244.20(192.168.244.20:3306)? (yes/NO):
 
以下輸出中間都有兩次空白,其中第一次空白之前的輸出對應第一次確認之前,第二次之前的輸出對應第二次確認之前。
  
原master 192.168.244.10
 
MySQL高可用方案MHA在線切換的步驟及原理
170412 16:52:38    23 Connect    monitor@node4 on
                   23 Query    set autocommit=1
                   23 Query    SELECT CONNECTION_ID() AS Value170412 16:52:39    24 Connect    monitor@node4 on
                   24 Query    set autocommit=1
                   24 Query    SELECT CONNECTION_ID() AS Value                   24 Query    SET wait_timeout=86400
                   24 Query    SELECT @@global.server_id As Value                   24 Query    SELECT VERSION() AS Value                   24 Query    SELECT @@global.gtid_mode As Value                   24 Query    SHOW GLOBAL VARIABLES LIKE 'log_bin'
                   24 Query    SHOW MASTER STATUS                   24 Query    SELECT @@global.datadir AS Value                   24 Query    SELECT @@global.slave_parallel_workers AS Value                   24 Query    SHOW SLAVE STATUS                   24 Query    SELECT @@global.read_only As Value                   24 Query    SELECT @@global.relay_log_purge As Value           
170412 16:54:06    24 Query    FLUSH NO_WRITE_TO_BINLOG TABLES                   24 Query    SELECT GET_LOCK('MHA_Master_High_Availability_Monitor', '0') AS Value                   24 Query    SHOW PROCESSLIST                   
170412 16:55:51    24 Query    SHOW SLAVE STATUS                   24 Query    CHANGE MASTER TO MASTER_HOST='dummy_host'170412 16:55:52    24 Query    SHOW SLAVE STATUS                   24 Query    RESET SLAVE /*!50516 ALL */
                   24 Query    SELECT RELEASE_LOCK('MHA_Master_High_Availability_Monitor') As Value                   24 Quit    
                   25 Connect    monitor@node4 on
                   25 Query    set autocommit=1
                   25 Query    SELECT CONNECTION_ID() AS Value                   25 Query    SET sql_log_bin=0
                   25 Query    SHOW PROCESSLIST                   25 Query    SELECT @@global.read_only As Value                   25 Query    SET GLOBAL read_only=1
                   25 Query    SELECT @@global.read_only As Value                   25 Query    SHOW PROCESSLIST                   25 Query    SET sql_log_bin=1
                   25 Quit    
                   26 Connect    monitor@node4 on
                   26 Query    set autocommit=1
                   26 Query    SELECT CONNECTION_ID() AS Value                   26 Query    SET wait_timeout=86400
                   26 Query    FLUSH TABLES WITH READ LOCK                   26 Query    SHOW MASTER STATUS170412 16:55:53    26 Query    UNLOCK TABLES                   26 Query    CHANGE MASTER TO MASTER_HOST = '192.168.244.20' MASTER_USER = 'repl' MASTER_PASSWORD = <secret> MASTE
R_PORT = 3306 MASTER_LOG_FILE = 'mysql-bin.000010' MASTER_LOG_POS = 120           26 Query    SET GLOBAL relay_log_purge=0
                   26 Query    START SLAVE                   27 Connect Out    repl@192.168.244.20:3306
                   26 Query    SHOW SLAVE STATUS                   26 Query    SELECT RELEASE_LOCK('MHA_Master_High_Availability_Failover') As Value                   26 Quit
MySQL高可用方案MHA在線切換的步驟及原理
  
新master 192.168.244.20
 
MySQL高可用方案MHA在線切換的步驟及原理
170412 16:52:38    23 Connect    monitor@node4 on
                   23 Query    set autocommit=1
                   23 Query    SELECT CONNECTION_ID() AS Value170412 16:52:39    24 Connect    monitor@node4 on
                   24 Query    set autocommit=1
                   24 Query    SELECT CONNECTION_ID() AS Value                   24 Query    SET wait_timeout=86400
                   24 Query    SELECT @@global.server_id As Value                   24 Query    SELECT VERSION() AS Value                   24 Query    SELECT @@global.gtid_mode As Value                   24 Query    SHOW GLOBAL VARIABLES LIKE 'log_bin'
                   24 Query    SHOW MASTER STATUS                   24 Query    SELECT @@global.datadir AS Value                   24 Query    SELECT @@global.slave_parallel_workers AS Value                   24 Query    SHOW SLAVE STATUS                   24 Query    SELECT @@global.read_only As Value                   24 Query    SELECT @@global.relay_log_purge As Value                   24 Query    SELECT @@global.relay_log_info_repository AS Value                   24 Query    SELECT @@global.datadir AS Value                   24 Query    SELECT @@global.relay_log_info_file AS Value                   24 Query    SHOW SLAVE STATUS                   24 Query    SELECT Repl_slave_priv AS Value FROM mysql.user WHERE user = 'repl'
           
           170412 16:54:06    24 Query    SELECT GET_LOCK('MHA_Master_High_Availability_Failover', '0') AS Value                   24 Query    SHOW SLAVE STATUS                   24 Query    SHOW SLAVE STATUS           
170412 16:55:52    24 Query    SHOW PROCESSLIST                   25 Connect    monitor@node4 on
                   25 Query    set autocommit=1
                   25 Query    SELECT CONNECTION_ID() AS Value                   25 Query    SELECT @@global.read_only As Value                   25 Query    SELECT @@global.read_only As Value                   25 Quit    
                   24 Query    SHOW SLAVE STATUS                   24 Query    SELECT MASTER_POS_WAIT('mysql-bin.000017','120',0) AS Result                   24 Query    STOP SLAVE SQL_THREAD                   24 Query    SHOW SLAVE STATUS        &n

(編輯:錯新網)

發表評論 共有條評論
用戶名: 密碼:
驗證碼: 匿名發表
金玫玫床戏