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

來源：懂視網責編：小采時間：2020-11-09 16:14:21

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

MySQL高可用-雙主故障自動切換方案:前言：（PS：前言是第二次修改本文時加的）對于這篇文章，有博友提出了一些疑問和見解，有了博友的關注，也促使我想把這套東西做的更實用、更安全。后來又經過思考，對腳本中一些條件和行為做了些改變。經過修改，現在終于敢說讓小伙伴本使用這套東西了。

推薦度：

點擊下載本文 文檔為doc格式

導讀MySQL高可用-雙主故障自動切換方案:前言：（PS：前言是第二次修改本文時加的）對于這篇文章，有博友提出了一些疑問和見解，有了博友的關注，也促使我想把這套東西做的更實用、更安全。后來又經過思考，對腳本中一些條件和行為做了些改變。經過修改，現在終于敢說讓小伙伴本使用這套東西了。

前言：（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. 第一次檢測失敗后,檢查服務狀態,：

若服務異常，則執行切換：關閉DB1的keepalived，使VIP漂移至DB2，通過DB2上keepalived的notify_master機制，觸發腳本將DB2的mysql從被動狀態（只讀）切換到主動狀態（可讀寫），并發送通知郵件。
若服務正常（則可能是一些臨時性因素導致的監測失敗），等待30s做第二次檢查，這30s是對瞬時/短時因素造成檢查失敗的容忍時間，本著“能不切則不切”的原則。若第二次檢查仍然失敗

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

將DB1的MySQL設置為 read_only模式（阻止寫請繼續求進入）
kill掉當前客戶端的線程。原來擔心kill掉線程會對數據執行造成影響，后來查看了官方文檔“mysql shutdown process”，發現mysql正常關閉過程也有一步是如此操作，所以這里可以放心了。然后 sleep 2，給kill命令一些時間（關于kill命令的機制，參考官方解釋）
關閉DB1的keepalived，使DB2接管VIP。通過DB2上keepalived的notify_master機制，觸發腳本將DB2的
mysql從被動狀態（只讀）切換到主動狀態（可讀寫），并發送通知郵件。

3. 管理員修復DB1后，通過腳本“change_to_backup.sh”將主庫切換回DB1。腳本思路如下：

注：涉及到切換主備，就會有中斷時間，所以推薦此步驟在業務低谷期執行

將DB2的read_only屬性置為1
kill掉DB2上的client線程，并重啟DB2的keepalived使VIP漂移至DB1
確定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狀態時，執行該腳本
alias mysql_con='mysql -uxxxx -pxxxx'
echo -e "`date "+%F %H:%M:%S"` -----keepalived change to MASTER-----" >> /etc/keepalived/logs/state_change.log

###判斷是否已經將從master接收到的binlog全部在本地執行（這么做仍然無法完全確定從庫就已經追上了主庫，因為雖然說從庫延時一般情況都是慢在sql_thread上，但是也無法完全保證io_thread完全就沒有延時。但至少能保證已經讀取到的binlog在本地執行完畢）
pos=`mysql_con -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"` -----Exec_Master_Log_Pos is behind Read_Master_Log_Pos, wait......" >> /etc/keepalived/logs/state_ch
ange.log
 sleep 1
done

###然后解除read_only屬性
echo -e "`date "+%F %H:%M:%S"` -----set read_only = 0 on DB2-----" >> /etc/keepalived/logs/state_change.log
mysql_con -e "set global read_only = 0;" 2>> /etc/keepalived/logs/state_change.log

echo "DB2 keepalived changed to MASTER，online DB server changed to DB2"|/bin/mailx -s "DB2 keepalived change to MASTER" xxxx@xxxx.com 2>> /etc/keepalived/logs/state_change.log
echo -e "@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@\n" >> /etc/keepalived/logs/state_change.log

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一個執行和緩沖時間

###重啟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

###確保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 -pxxxx 192.168.1.x 'mysql -uxxxx -pxxxx -e "set global read_only = 0;"' 2>> /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服務故障