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. 第一次檢測失敗後,檢查服務狀态,：

若服務異常，則執行切換：關閉DB1的keepalived，使VIP漂移至DB2，通過DB2上keepalived的notify_master機制，觸發腳本将DB2的mysql從被動狀态（隻讀）切換到主動狀态（可讀寫），并發送通知郵件。

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

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

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

關閉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 配置

<code>! Configuration File </code><code>for</code> <code>keepalived</code>

<code>vrrp_script chk_mysql {</code>

<code> </code><code>script </code><code>"/etc/keepalived/check_mysql.sh"</code>

<code> </code><code>interval 30 </code><code>#這裡我的檢查間隔設定的比較長，因為我們資料庫前面有redis做緩存，資料庫一兩分鐘級别的中斷對整體可用性影響不大。這也是我沒有采用成熟的方案而自己搞了這一套方案的“定心丸”</code>

<code>vrrp_instance VI_1 {</code>

<code> </code><code>state BACKUP </code><code>#通過下面的priority來區分MASTER和BACKUP，也隻有如此，底下的nopreempt才有效</code>

<code> </code><code>interface em2</code>

<code> </code><code>virtual_router_id 51</code>

<code> </code><code>priority 100</code>

<code> </code><code>advert_int 1</code>

<code> </code><code>nopreempt </code><code>#防止切換到從庫後，主keepalived恢複後自動切換回主庫</code>

<code> </code><code>authentication {</code>

<code> </code><code>track_script {</code>

<code> </code><code>chk_mysql</code>

<code> </code><code>virtual_ipaddress {</code>

/etc/keepalived/check_mysql.sh腳本内容如下（主要的判斷邏輯都在這裡）

<code>###判斷如果上次檢查的腳本還沒執行完，則退出此次執行</code>

<code>if</code> <code>[ `</code><code>ps</code> <code>-ef|</code><code>grep</code> <code>-w </code><code>"$0"</code><code>|</code><code>grep</code> <code>"/bin/sh*"</code><code>|</code><code>grep</code> <code>"?"</code><code>|</code><code>grep</code> <code>"?"</code><code>|</code><code>grep</code> <code>-</code><code>v</code> <code>"grep"</code><code>|</code><code>wc</code> <code>-l` -gt 2 ];</code><code>then</code> <code>#理論上這裡應該是1，但是實驗的結果卻是2</code>

<code>alias</code> <code>mysql_con=</code><code>'mysql -uxxxx -pxxxx'</code>

<code>###定義一個簡單判斷mysql是否可用的函數</code>

<code>function</code> <code>excute_query {</code>

<code> </code><code>mysql_con -e </code><code>"select * from test.test;"</code> <code>2>></code><code>/etc/keepalived/logs/check_mysql</code><code>.err</code>

<code>###定義無法執行查詢，且mysql服務異常時的處理函數</code>

<code>function</code> <code>service_error {</code>

<code> </code><code>echo</code> <code>-e </code><code>"`date "</code><code>+%F %H:%M:%S</code><code>"` -----mysql service error，now stop keepalived-----"</code> <code>>> </code><code>/etc/keepalived/logs/check_mysql</code><code>.err</code>

<code> </code><code>/sbin/service</code> <code>keepalived stop &>> </code><code>/etc/keepalived/logs/check_mysql</code><code>.err</code>

<code> </code><code>echo</code> <code>-e </code><code>"\n@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@\n"</code> <code>>> </code><code>/etc/keepalived/logs/check_mysql</code><code>.err</code>

<code>###定義無法執行查詢,但mysql服務正常的處理函數</code>

<code>function</code> <code>query_error {</code>

<code> </code><code>echo</code> <code>-e </code><code>"`date "</code><code>+%F %H:%M:%S</code><code>"` -----query error, but mysql service ok, retry after 30s-----"</code> <code>>> </code><code>/etc/keepalived/logs/check_mysql</code><code>.err</code>

<code> </code><code>sleep</code> <code>30</code>

<code> </code><code>excute_query</code>

<code> </code><code>echo</code> <code>-e </code><code>"`date "</code><code>+%F %H:%M:%S</code><code>"` -----still can't execute query-----"</code> <code>>> </code><code>/etc/keepalived/logs/check_mysql</code><code>.err</code>

<code> </code><code>###對DB1設定read_only屬性</code>

<code> </code><code>echo</code> <code>-e </code><code>"`date "</code><code>+%F %H:%M:%S</code><code>"` -----set read_only = 1 on DB1-----"</code> <code>>> </code><code>/etc/keepalived/logs/check_mysql</code><code>.err</code>

<code> </code><code>mysql_con -e </code><code>"set global read_only = 1;"</code> <code>2>> </code><code>/etc/keepalived/logs/check_mysql</code><code>.err</code>

<code> </code><code>###kill掉目前用戶端連接配接</code>

<code> </code><code>echo</code> <code>-e </code><code>"`date "</code><code>+%F %H:%M:%S</code><code>"` -----kill current client thread-----"</code> <code>>> </code><code>/etc/keepalived/logs/check_mysql</code><code>.err</code>

<code> </code><code>###這裡其實是一個批量kill線程的小技巧</code>

<code> </code><code>mysql_con -e </code><code>'select concat("kill ",id,";") from information_schema.PROCESSLIST where command="Query" or command="Execute" into outfile "/tmp/kill.sql";'</code>

<code> </code><code>mysql_con -e </code><code>"source /tmp/kill.sql"</code>

<code> </code><code>sleep</code> <code>2 </code><code>###給kill一個執行和緩沖時間</code>

<code> </code><code>###關閉本機keepalived </code>

<code> </code><code>echo</code> <code>-e </code><code>"`date "</code><code>+%F %H:%M:%S</code><code>"` -----stop keepalived-----"</code> <code>>> </code><code>/etc/keepalived/logs/check_mysql</code><code>.err</code>

<code> </code><code>/sbin/service</code> <code>keepalived stop &>> </code><code>/etc/keepalived/logs/check_mysql</code><code>.err</code>

<code> </code><code>echo</code> <code>-e </code><code>"\n@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@\n"</code> <code>>> </code><code>/etc/keepalived/logs/check_mysql</code><code>.err</code>

<code> </code><code>echo</code> <code>-e </code><code>"`date "</code><code>+%F %H:%M:%S</code><code>"` -----query ok after 30s-----"</code> <code>>> </code><code>/etc/keepalived/logs/check_mysql</code><code>.err</code>

<code>excute_query</code>

<code> </code><code>/sbin/service</code> <code>mysql status &></code><code>/dev/null</code>

<code> </code><code>service_error</code>

<code> </code><code>query_error</code>

DB2上keepalived配置：

<code> </code><code>state BACKUP</code>

<code> </code><code>priority 90</code>

<code> </code><code>notify_master </code><code>/etc/keepalived/notify_master_mysql</code><code>.sh </code><code>#此條指令告訴keepalived發現自己轉為MASTER後執行的腳本</code>

/etc/keepalived/notify_master_mysql.sh腳本内容：

<code>###當keepalived監測到本機轉為MASTER狀态時，執行該腳本</code>

<code>change_log=</code><code>/etc/keepalived/logs/state_change</code><code>.log</code>

<code>alias</code> <code>mysql_con=</code><code>'mysql -uroot -pxxxx -e "show slave status\G;" 2>/dev/null'</code>

<code>echo</code> <code>-e </code><code>"`date "</code><code>+%F %H:%M:%S</code><code>"` -----keepalived change to MASTER-----"</code> <code>>> $change_log</code>

<code>slave_info() {</code>

<code> </code><code>###統一定義一個函數取得slave的position、running、和log_file等資訊</code>

<code> </code><code>###根據函數後面所跟參數來決定取得哪些資料</code>

<code> </code><code>if</code> <code>[ $1 = slave_status ];</code><code>then</code>

<code> </code><code>slave_stat=`mysql_con|</code><code>egrep</code> <code>-w </code><code>"Slave_IO_Running|Slave_SQL_Running"</code><code>`</code>

<code> </code><code>Slave_IO_Running=`</code><code>echo</code> <code>$slave_stat|</code><code>awk</code> <code>'{print $2}'</code><code>`</code>

<code> </code><code>Slave_SQL_Running=`</code><code>echo</code> <code>$slave_stat|</code><code>awk</code> <code>'{print $4}'</code><code>`</code>

<code> </code><code>log_file_pos=`mysql_con|</code><code>egrep</code> <code>-w </code><code>"Master_Log_File|Read_Master_Log_Pos|Exec_Master_Log_Pos"</code><code>`</code>

<code> </code><code>Master_Log_File=`</code><code>echo</code> <code>$log_file_pos|</code><code>awk</code> <code>'{print $2}'</code><code>`</code>

<code> </code><code>Read_Master_Log_Pos=`</code><code>echo</code> <code>$log_file_pos|</code><code>awk</code> <code>'{print $4}'</code><code>`</code>

<code> </code><code>Exec_Master_Log_Pos=`</code><code>echo</code> <code>$log_file_pos|</code><code>awk</code> <code>'{print $6}'</code><code>`</code>

<code>action() {</code>

<code> </code><code>###經判斷'應該&可以'切換時執行的動作</code>

<code> </code><code>echo</code> <code>-e </code><code>"`date "</code><code>+%F %H:%M:%S</code><code>"` -----set read_only = 0 on DB2-----"</code> <code>>> $change_log</code>

<code> </code><code>###解除read_only屬性</code>

<code> </code><code>mysql_con -e </code><code>"set global read_only = 0;"</code> <code>2>> $change_log</code>

<code> </code><code>echo</code> <code>"DB2 keepalived轉為MASTER狀态，線上資料庫切換至DB2"</code><code>|</code><code>/bin/mailx</code> <code>-s </code><code>"DB2 keepalived change to MASTER"</code><code>\</code>

<code> </code><code>[email protected] 2>> $change_log</code>

<code> </code><code>echo</code> <code>-e </code><code>"@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@\n"</code> <code>>> $change_log</code>

<code>slave_info slave_status</code>

<code>if</code> <code>[ $Slave_IO_Running = Yes -a $Slave_SQL_Running = Yes ];</code><code>then</code>

<code> </code><code>i=0 </code><code>#一個計數器</code>

<code> </code><code>slave_info log_file pos</code>

<code> </code><code>###判斷從master接收到的binlog是否全部在本地執行(這樣仍無法完全确定從庫已追上主庫，因為無法完全保證io_thread沒有延時(由網絡傳輸問題導緻的從庫落後的機率很小)</code>

<code> </code><code>until</code> <code>[ $Read_Master_Log_Pos = $Exec_Master_Log_Pos ]</code>

<code> </code><code>if</code> <code>[ $i -lt 10 ];</code><code>then</code> <code>#将等待exec_pos追上read_pos的時間限制為10s</code>

<code> </code><code>echo</code> <code>-e </code><code>"`date "</code><code>+%F %H:%M:%S"` -----Master_Log_File=$Master_Log_File. Exec_Master_Log_Pos($Exec_Master_Log_Pos) is behind Read_Master_Lo</code>

<code>g_Pos($Read_Master_Log_Pos), wait......" >> $change_log </code><code>#輸出消息到日志，等待exec_pos=read_pos</code>

<code> </code><code>sleep</code> <code>1</code>

<code> </code><code>slave_info log_file pos</code>

<code> </code><code>echo</code> <code>-e "The waits </code><code>time</code> <code>is </code><code>more</code> <code>than 10s,now force change. Master_Log_File=$Master_Log_File Read_Master_Log_Pos=$Read_Master_Log_Pos Exec_Ma</code>

<code>ster_Log_Pos=$Exec_Master_Log_Pos" >> $change_log</code>

<code> </code><code>action</code>

<code> </code><code>action </code>

<code> </code><code>echo</code> <code>-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</code>

<code>s=$Exec_Master_Log_Pos" >> $change_log</code>

<code> </code><code>action</code>

DB2上手動切換回DB1的腳本change_to_backup.sh：

<code>###手動執行将主庫切換回DB1的操作</code>

<code>echo</code> <code>-e </code><code>"`date "</code><code>+%F %H:%M:%S</code><code>"` -----change to BACKUP manually-----"</code> <code>>> </code><code>/etc/keepalived/logs/state_change</code><code>.log</code>

<code>echo</code> <code>-e </code><code>"`date "</code><code>+%F %H:%M:%S</code><code>"` -----set read_only = 1 on DB2-----"</code> <code>>> </code><code>/etc/keepalived/logs/state_change</code><code>.log</code>

<code>mysql_con -e </code><code>"set global read_only = 1;"</code> <code>2>> </code><code>/etc/keepalived/logs/state_change</code><code>.log</code>

<code>###kill掉目前用戶端連接配接</code>

<code>echo</code> <code>-e </code><code>"`date "</code><code>+%F %H:%M:%S</code><code>"` -----kill current client thread-----"</code> <code>>> </code><code>/etc/keepalived/logs/state_change</code><code>.log</code>

<code>###這裡其實是一個批量kill線程的小技巧</code>

<code>mysql_con -e </code><code>'select concat("kill ",id,";") from information_schema.PROCESSLIST where command="Query" or command="Execute" into outfile "/tmp/kill.sql";'</code>

<code>mysql_con -e </code><code>"source /tmp/kill.sql"</code> <code>2>> </code><code>/etc/keepalived/logs/state_change</code><code>.log</code>

<code>sleep</code> <code>2 </code><code>###給kill一個執行和緩沖時間</code>

<code>###確定DB1已經追上了,下面的repl為複制所用的賬戶，-h後跟DB1的内網IP</code>

<code>pos=`mysql -urepl -pxxxx -h192.168.1.x -e </code><code>"show slave status\G;"</code><code>|</code><code>grep</code> <code>"Master_Log_Pos"</code><code>|</code><code>awk</code> <code>'{printf ("%s",$NF "\t")}'</code><code>`</code>

<code>read_pos=`</code><code>echo</code> <code>$pos|</code><code>awk</code> <code>'{print $1}'</code><code>`</code>

<code>exec_pos=`</code><code>echo</code> <code>$pos|</code><code>awk</code> <code>'{print $2}'</code><code>`</code>

<code>until</code> <code>[ $read_pos = $exec_pos ]</code>

<code> </code><code>echo</code> <code>-e </code><code>"`date "</code><code>+%F %H:%M:%S</code><code>"` -----DB1 Exec_Master_Log_Pos($exec_pos) is behind Read_Master_Log_Pos($read_pos), wait......"</code> <code>>> </code><code>/etc/keepalived/logs/state_change</code><code>.log</code>

<code> </code><code>sleep</code> <code>1</code>

<code>###然後解除DB1的read_only屬性</code>

<code>echo</code> <code>-e </code><code>"`date "</code><code>+%F %H:%M:%S</code><code>"` -----set read_only = 0 on DB1-----"</code> <code>>> </code><code>/etc/keepalived/logs/state_change</code><code>.log</code>

<code>ssh</code> <code>192.168.1.x </code><code>'mysql -uxxxx -pxxxx -e "set global read_only = 0;" && /etc/init.d/keepalived start'</code> <code>2>> </code><code>/etc/keepalived/logs/state_change</code><code>.log</code>

<code>###重新開機DB2的keepalived使VIP漂移到DB1</code>

<code>echo</code> <code>-e </code><code>"`date "</code><code>+%F %H:%M:%S</code><code>"` -----make VIP move to DB1-----"</code> <code>>> </code><code>/etc/keepalived/logs/state_change</code><code>.log</code>

<code>/sbin/service</code> <code>keepalived restart &>> </code><code>/etc/keepalived/logs/state_change</code><code>.log</code>

<code>echo</code> <code>"DB2 keepalived轉為BACKUP狀态，線上資料庫切換至DB1"</code><code>|</code><code>/bin/mailx</code> <code>-s </code><code>"DB2 keepalived change to BACKUP"</code> <code>[email protected] 2>> </code><code>/etc/keepalived/logs/state_change</code><code>.log</code>

<code>echo</code> <code>-e </code><code>"@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@\n"</code> <code>>> </code><code>/etc/keepalived/logs/state_change</code><code>.log</code>

日志截圖：

DB1 mysql服務故障：

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

DB2 一次切換過程：

DB2 執行腳本手動切回DB1：

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

本文轉自kai404 51CTO部落格，原文連結：http://blog.51cto.com/kaifly/1665729，如需轉載請自行聯系原作者

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

繼續閱讀

Shell程式設計——sort排序、uniq忽略重複、tr替換壓縮删除、cut指定删除字段、正規表達式元字元sort 指令uniq 指令tr 指令cut 指令正規表達式

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

ACS基本配置-權限等級管理

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

登入plsql 報錯 the account is locked --使用者被鎖

SequoiaDB巨杉資料庫C++驅動概述