MySQL主庫高可用 -- 雙主單活故障自動切換方案

MySQL主庫高可用 -- 雙主單活故障自動切換方案

前言：（PS：前言是後來修改本文時加的）對於這篇文章，有博友提出了一些疑問和見解， 有了博友的關注，也促使我想把這套東西做的更實用、更安全。後來又經過思考，對指令碼中一些條件和行為做了些改變。經過幾次修改，現在終於敢說讓小夥伴本使用這套東西了。

主要目的：

以雙主結構配合keepalived解決MySQL主從結構中主庫的單點故障；同時通過具體的查詢語句提供更細粒度、更為真實的關於主庫可用性的判斷。

基本思路：
    將DB1和DB2做成主動被動模式的雙主結構：DB1主動、DB2被動，通過keepalived的VIP對外，將VIP設定成原DB1的IP，保證改造過程對代碼透明
    三個前提：

            兩台MySQL的設定檔裡需要加上“log_slave_updates = 1”；
            並且“備用機”通過“read_only”參數實現除root使用者之外的唯讀特性；
            分別在兩個資料庫建立test.test表，插入幾條資料，供檢測指令碼使用。
    正常時，VIP在DB1，通過keepalived呼叫指令碼定期檢查mysql服務可用性（通過一個低許可權使用者串連mysql伺服器並執行一個簡單查詢，根據返回結果來判定mysql是否可用）

若無法執行查詢：

1. 第一次檢測失敗後,檢查服務狀態,：

1. 若服務異常，則執行切換：關閉DB1的keepalived，使VIP漂移至DB2，通過DB2上keepalived的notify_master機制，觸發指令碼將DB2的mysql從被動狀態（唯讀）切換到主動狀態（可讀寫），並發送通知訊息。

2. 若服務正常（則可能是一些臨時性因素導致的監測失敗），等待30s做第二次檢查，這30s是對瞬時/短時因素造成檢查失敗的容忍時間，本著“能不切則不切”的原則。若第二次檢查仍然失敗

2.  開始執行系列切換動作

1. 將DB1的MySQL設定為 read_only模式 （阻止寫請繼續求進入）

2. kill掉當前用戶端的線程。原來擔心kill掉線程會對資料執行造成影響，後來查看了官方文檔“mysql shutdown process”，發現mysql正常關閉過程也有一步是如此操作，所以這裡可以放心了。然後 sleep 2，給kill命令一些時間（關於kill命令的機制，參考官方解釋）

3. 關閉DB1的keepalived，使DB2接管VIP。通過DB2上keepalived的notify_master機制，觸發指令碼將DB2的

   mysql從被動狀態（唯讀）切換到主動狀態（可讀寫），並發送通知訊息。

3.  管理員修複DB1後，通過指令碼“change_to_backup.sh”將主庫切換回DB1。指令碼思路如下：

    註：涉及到切換主備，就會有停機時間，所以推薦此步驟在業務低穀期執行

1. 將DB2的read_only屬性置為1

2. kill掉DB2上的client線程，並重啟DB2的keepalived使VIP漂移至DB1

3. 確定DB1跟上了DB2的更新，並將DB1上的read_only屬性移除

關於“資料一致性”和“切換時間”：

      連續兩次失敗以後，通過對主MySQL設定read_only屬性，同時kill掉使用者線程來保證在DB2接管服務之前，DB1上已經沒有寫操作，避免主從資料不一致。並且切換時間基本上是可確定的：

      30s（兩次活動訊號間隔時間）+2s（等待kill命令時間）+約1s（keepalived 切換VIP），總時間不會超過35s。

以上是大致思路，具體實現看過下面的指令碼，就會一目瞭然了。

DB1上keepalived 配置

! Configuration File for keepalived
 
vrrp_script chk_mysql {
    script "/etc/keepalived/check_mysql.sh"
    interval 30        #這裡我的檢查間隔設定的比較長，因為我們資料庫前面有redis做緩衝，資料庫一兩分鐘層級的中斷對整體可用性影響不大。這也是我沒有採用成熟的方案而自己搞了這一套方案的“定心丸”
}
vrrp_instance VI_1 {
    state BACKUP        #通過下面的priority來區分MASTER和BACKUP，也只有如此，底下的nopreempt才有效
    interface em2
    virtual_router_id 51
    priority 100
    advert_int 1
    nopreempt          #防止切換到從庫後，主keepalived恢複後自動切換回主庫
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    track_script {
        chk_mysql
    }
     
    virtual_ipaddress {
        192.168.1.5/24
    }
}

/etc/keepalived/check_mysql.sh指令碼內容如下（主要的判斷邏輯都在這裡）
#!/bin/sh
 
###判斷如果上次檢查的指令碼還沒執行完，則退出此次執行
if [ `ps -ef|grep -w "$0"|grep "/bin/sh*"|grep "?"|grep "?"|grep -v "grep"|wc -l` -gt 2 ];then  #理論上這裡應該是1，但是實驗的結果卻是2
    exit 0
fi
 
alias mysql_con='mysql -uxxxx -pxxxx'
 
###定義一個簡單判斷mysql是否可用的函數
function excute_query {
    mysql_con -e "select * from test.test;" 2>>/etc/keepalived/logs/check_mysql.err
}
 
###定義無法執行查詢，且mysql服務異常時的處理函數
function service_error {
    echo -e "`date "+%F  %H:%M:%S"`    -----mysql service error，now stop keepalived-----" >> /etc/keepalived/logs/check_mysql.err
    /sbin/service keepalived stop &>> /etc/keepalived/logs/check_mysql.err
    echo -e "\n@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@\n" >> /etc/keepalived/logs/check_mysql.err
}
 
###定義無法執行查詢,但mysql服務正常的處理函數
function query_error {
    echo -e "`date "+%F  %H:%M:%S"`    -----query error, but mysql service ok, retry after 30s-----" >> /etc/keepalived/logs/check_mysql.err
    sleep 30
    excute_query
    if [ $? -ne 0 ];then
        echo -e "`date "+%F  %H:%M:%S"`    -----still can't execute query-----" >> /etc/keepalived/logs/check_mysql.err
 
        ###對DB1設定read_only屬性
        echo -e "`date "+%F  %H:%M:%S"`    -----set read_only = 1 on DB1-----" >> /etc/keepalived/logs/check_mysql.err
        mysql_con -e "set global read_only = 1;" 2>> /etc/keepalived/logs/check_mysql.err
 
        ###kill掉當前用戶端串連
        echo -e "`date "+%F  %H:%M:%S"`    -----kill current client thread-----" >> /etc/keepalived/logs/check_mysql.err
        rm -f /tmp/kill.sql &>/dev/null
        ###這裡其實是一個批量kill線程的小技巧
        mysql_con -e 'select concat("kill ",id,";") from  information_schema.PROCESSLIST where command="Query" or command="Execute" into outfile "/tmp/kill.sql";'
        mysql_con -e "source /tmp/kill.sql"
        sleep 2    ###給kill一個執行和緩衝時間
        ###關閉本機keepalived       
        echo -e "`date "+%F  %H:%M:%S"`    -----stop keepalived-----" >> /etc/keepalived/logs/check_mysql.err
        /sbin/service keepalived stop &>> /etc/keepalived/logs/check_mysql.err
        echo -e "\n@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@\n" >> /etc/keepalived/logs/check_mysql.err
    else
        echo -e "`date "+%F  %H:%M:%S"`    -----query ok after 30s-----" >> /etc/keepalived/logs/check_mysql.err
        echo -e "\n@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@\n" >> /etc/keepalived/logs/check_mysql.err
    fi
}
 
###檢查開始: 執行查詢
excute_query
if [ $? -ne 0 ];then
    /sbin/service mysql status &>/dev/null
    if [ $? -ne 0 ];then
        service_error
    else
        query_error
    fi
fi

DB2上keepalived配置：
! Configuration File for keepalived
 
vrrp_instance VI_1 {
    state BACKUP
    interface em2
    virtual_router_id 51
    priority 90
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    notify_master /etc/keepalived/notify_master_mysql.sh    #此條指令告訴keepalived發現自己轉為MASTER後執行的指令碼
    virtual_ipaddress {
        192.168.1.5/24
    }
}

/etc/keepalived/notify_master_mysql.sh指令碼內容：
#!/bin/bash
###當keepalived監測到本機轉為MASTER狀態時，執行該指令碼
 
change_log=/etc/keepalived/logs/state_change.log
alias mysql_con='mysql -uroot -pxxxx -e "show slave status\G;" 2>/dev/null'
 
echo -e "`date "+%F  %H:%M:%S"`  -----keepalived change to MASTER-----" >> $change_log
 
slave_info() {
    ###統一定義一個函數取得slave的position、running、和log_file等資訊
    ###根據函數後面所跟參數來決定取得哪些資料
    if [ $1 = slave_status ];then
        slave_stat=`mysql_con|egrep -w "Slave_IO_Running|Slave_SQL_Running"`
        Slave_IO_Running=`echo $slave_stat|awk '{print $2}'`
        Slave_SQL_Running=`echo $slave_stat|awk '{print $4}'`
    elif [ $1 = log_file -a $2 = pos ];then
        log_file_pos=`mysql_con|egrep -w "Master_Log_File|Read_Master_Log_Pos|Exec_Master_Log_Pos"`
        Master_Log_File=`echo $log_file_pos|awk '{print $2}'`
        Read_Master_Log_Pos=`echo $log_file_pos|awk '{print $4}'`
        Exec_Master_Log_Pos=`echo $log_file_pos|awk '{print $6}'`
    fi
}
 
action() {
    ###經判斷'應該&可以'切換時執行的動作
    echo -e "`date "+%F  %H:%M:%S"`    -----set read_only = 0 on DB2-----" >> $change_log
 
    ###解除read_only屬性
    mysql_con -e "set global read_only = 0;" 2>> $change_log
 
    echo "DB2 keepalived轉為MASTER狀態，線上資料庫切換至DB2"|/bin/mailx -s "DB2 keepalived change to MASTER"\
    lijiankai@dm.com 2>> $change_log
 
    echo -e "@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@\n" >> $change_log
}
 
slave_info slave_status
if [ $Slave_IO_Running = Yes -a $Slave_SQL_Running = Yes ];then
    i=0    #一個計數器
    slave_info log_file pos
        ###判斷從master接收到的binlog是否全部在本地執行(這樣仍無法完全確定從庫已追上主庫，因為無法完全保證io_thread沒有延時(由網路傳輸問題導致的從庫落後的機率很小)
    until [ $Read_Master_Log_Pos = $Exec_Master_Log_Pos ]
    do
        if [ $i -lt 10 ];then    #將等待exec_pos追上read_pos的時間限制為10s
            echo -e "`date "+%F  %H:%M:%S"`    -----Master_Log_File=$Master_Log_File. Exec_Master_Log_Pos($Exec_Master_Log_Pos) is behind Read_Master_Lo
g_Pos($Read_Master_Log_Pos), wait......" >> $change_log    #輸出訊息到日誌，等待exec_pos=read_pos
            i=$(($i+1))
            sleep 1
            slave_info log_file pos
        else
            echo -e "The waits time is more than 10s,now force change. Master_Log_File=$Master_Log_File Read_Master_Log_Pos=$Read_Master_Log_Pos Exec_Ma
ster_Log_Pos=$Exec_Master_Log_Pos" >> $change_log
            action
            exit 0
        fi
    done
    action 
 
else
    slave_info log_file pos
    echo -e "DB2's slave status is wrong,now force change. Master_Log_File=$Master_Log_File Read_Master_Log_Pos=$Read_Master_Log_Pos  Exec_Master_Log_Po
s=$Exec_Master_Log_Pos" >> $change_log
    action
fi

DB2上手動切換回DB1的指令碼change_to_backup.sh：
#!/bin/sh
###手動執行將主庫切換回DB1的操作
 
alias mysql_con='mysql -uxxxx -pxxxx'
 
echo -e "`date "+%F  %H:%M:%S"`    -----change to BACKUP manually-----" >> /etc/keepalived/logs/state_change.log
echo -e "`date "+%F  %H:%M:%S"`    -----set read_only = 1 on DB2-----" >> /etc/keepalived/logs/state_change.log
mysql_con -e "set global read_only = 1;" 2>> /etc/keepalived/logs/state_change.log
 
###kill掉當前用戶端串連
echo -e "`date "+%F  %H:%M:%S"`    -----kill current client thread-----" >> /etc/keepalived/logs/state_change.log
rm -f /tmp/kill.sql &>/dev/null
###這裡其實是一個批量kill線程的小技巧
mysql_con -e 'select concat("kill ",id,";") from  information_schema.PROCESSLIST where command="Query" or command="Execute" into outfile "/tmp/kill.sql";'
mysql_con -e "source /tmp/kill.sql" 2>> /etc/keepalived/logs/state_change.log
sleep 2    ###給kill一個執行和緩衝時間
 
###確保DB1已經追上了,下面的repl為複製所用的賬戶，-h後跟DB1的內網IP
pos=`mysql -urepl -pxxxx -h192.168.1.x -e "show slave status\G;"|grep "Master_Log_Pos"|awk '{printf ("%s",$NF "\t")}'`
read_pos=`echo $pos|awk '{print $1}'`
exec_pos=`echo $pos|awk '{print $2}'`
until [ $read_pos = $exec_pos ]
do
    echo -e "`date "+%F  %H:%M:%S"`    -----DB1 Exec_Master_Log_Pos($exec_pos) is behind Read_Master_Log_Pos($read_pos), wait......" >> /etc/keepalived/logs/state_change.log
    sleep 1
done
 
###然後解除DB1的read_only屬性
echo -e "`date "+%F  %H:%M:%S"`    -----set read_only = 0 on DB1-----" >> /etc/keepalived/logs/state_change.log
ssh 192.168.1.x 'mysql -uxxxx -pxxxx -e "set global read_only = 0;" && /etc/init.d/keepalived start' 2>> /etc/keepalived/logs/state_change.log
 
###重啟DB2的keepalived使VIP漂移到DB1
echo -e "`date "+%F  %H:%M:%S"`    -----make VIP move to DB1-----" >> /etc/keepalived/logs/state_change.log
/sbin/service keepalived restart &>> /etc/keepalived/logs/state_change.log
 
echo "DB2 keepalived轉為BACKUP狀態，線上資料庫切換至DB1"|/bin/mailx -s "DB2 keepalived change to BACKUP" xxx@xxxx.com 2>> /etc/keepalived/logs/state_change.log
 
echo -e "@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@\n" >> /etc/keepalived/logs/state_change.log

日誌：
DB1 mysql服務故障：

DB1 mysql服務正常，查詢失敗：

DB2 一次切換過程：

DB2 執行指令碼手動切回DB1：

總結：此方相比MHA或者MMM之類技術，特點在於簡單，降低實施和維護複雜度；同時也安全的解決了主從中master節點的單點問題；在此基礎上，亦可以再增加從庫實現讀寫分離等架構；不足之處是雙主仍是單活，DB2隻是作為熱備。

本文永久更新連結地址：