mysql高可用之keepalived

wongchaofan發表於2024-08-05

轉載:https://www.cnblogs.com/ivictor/p/5522383.html

MySQL的高可用方案一般有如下幾種:

keepalived+雙主,MHA,MMM,Heartbeat+DRBD,PXC,Galera Cluster

比較常用的是keepalived+雙主,MHA和PXC。

對於小公司,一般推薦使用keepalived+雙主,簡單。

下面來部署一下

配置環境:

角色 主機IP 主機名 作業系統版本 軟體版本

VIP 192.168.244.10

master1 192.168.244.145 master1 CentOS7.1 MySQL 5.6.26,Keepalived v1.2.13

master2 192.168.244.146 master2 CentOS7.1 MySQL 5.6.26,Keepalived v1.2.13

一、 配置MySQL雙主複製環境

1. 修改配置檔案

master1中有關複製的配置如下:

[mysqld]
log-bin=mysql-bin
server-id=1
log_slave_updates=1

master2

[mysqld]
log-bin=mysql-bin
server-id=2
log_slave_updates=1
read_only=1

2. 建立複製使用者

master1中建立:

CREATE USER 'repl'@'192.168.244.146' IDENTIFIED BY 'mysql';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.244.146';

master2中建立:

CREATE USER 'repl'@'192.168.244.145' IDENTIFIED BY 'mysql';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.244.145';

3. 執行CHANGE MASTER TO語句

因是從頭搭建MySQL主從複製叢集,所以不需要獲取全域性讀鎖來得到二進位制日誌檔案的位置,直接根據show master status的輸出來確認。

master1上執行:

CHANGE MASTER TO
  MASTER_HOST='192.168.244.146',
  MASTER_USER='repl',
  MASTER_PASSWORD='mysql',
  MASTER_LOG_FILE='mysql-bin.000004',
  MASTER_LOG_POS=64729;

master2上執行:

CHANGE MASTER TO
  MASTER_HOST='192.168.244.145',
  MASTER_USER='repl',
  MASTER_PASSWORD='mysql',
  MASTER_LOG_FILE='mysql-bin.000003',
  MASTER_LOG_POS=68479;

4. 分別在兩個節點上執行start slave語句並透過show slave status\G檢視複製是否搭建成功。

成功標準:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

二、 配置Keepalived

1. 安裝Keepalived

# yum install -y keepalived

當然,也可直接編譯官方的原始碼包。

2. 修改Keepalived的配置檔案

master1

[root@master1 ~]# vim /etc/keepalived/keepalived.conf

vrrp_script chk_mysql {
    script "/etc/keepalived/check_mysql.sh"
    interval 30         #設定檢查間隔時長,可根據自己的需求自行設定
}
vrrp_instance VI_1 {
    state BACKUP        #透過下面的priority來區分MASTER和BACKUP,也只有如此,底下的nopreempt才有效
    interface eno16777736
    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.244.10/24
    }
}

關於keepalived的引數的詳細介紹,可參考:LVS+Keepalived搭建MyCAT高可用負載均衡叢集

其中,/etc/keepalived/check_mysql.sh內容如下:

#!/bin/bash
 
###判斷如果上次檢查的指令碼還沒執行完,則退出此次執行
if [ `ps -ef|grep -w "$0"|grep -v "grep"|wc -l` -gt 2 ];then
    exit 0
fi 
mysql_con='mysql -uroot -p123456'
error_log="/etc/keepalived/logs/check_mysql.err"
 
###定義一個簡單判斷mysql是否可用的函式
function excute_query {
    ${mysql_con} -e "select 1;" 2>> ${error_log}
}
 
###定義無法執行查詢,且mysql服務異常時的處理函式
function service_error {
    echo -e "`date "+%F  %H:%M:%S"`    -----mysql service error,now stop keepalived-----" >> ${error_log}
    service keepalived stop &>> ${error_log}
    echo "DB1 keepalived 已停止"|mail -s "DB1 keepalived 已停止,請及時處理!" slowtech@126.com 2>> ${error_log}
    echo -e "\n---------------------------------------------------------\n" >> ${error_log}
}
 
###定義無法執行查詢,但mysql服務正常的處理函式
function query_error {
    echo -e "`date "+%F  %H:%M:%S"`    -----query error, but mysql service ok, retry after 30s-----" >> ${error_log}
    sleep 30
    excute_query
    if [ $? -ne 0 ];then
        echo -e "`date "+%F  %H:%M:%S"`    -----still can't execute query-----" >> ${error_log}
 
        ###對DB1設定read_only屬性
        echo -e "`date "+%F  %H:%M:%S"`    -----set read_only = 1 on DB1-----" >> ${error_log}
        mysql_con -e "set global read_only = 1;" 2>> ${error_log}
 
        ###kill掉當前客戶端連線
        echo -e "`date "+%F  %H:%M:%S"`    -----kill current client thread-----" >> ${error_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"
        sleep 2    ###給kill一個執行和緩衝時間
        ###關閉本機keepalived       
        echo -e "`date "+%F  %H:%M:%S"`    -----stop keepalived-----" >> ${error_log}
        service keepalived stop &>> ${error_log}
        echo "DB1 keepalived 已停止"|mail -s "DB1 keepalived 已停止,請及時處理!" slowtech@126.com 2>> ${error_log}
        echo -e "\n---------------------------------------------------------\n" >> ${error_log}
    else
        echo -e "`date "+%F  %H:%M:%S"`    -----query ok after 30s-----" >> ${error_log}
        echo -e "\n---------------------------------------------------------\n" >> ${error_log}
    fi
}
 
###檢查開始: 執行查詢
excute_query
if [ $? -ne 0 ];then
    service mysqld status &>/dev/null
    if [ $? -ne 0 ];then
        service_error
    else
        query_error
    fi
fi

透過具體的查詢語句來判斷資料庫服務的可用性,如果查詢失敗,則判斷mysqld程序本身的狀態,如果不正常,則直接停止當前節點的keepalived,將VIP轉移到另外一個節點,如果正常,則等待30s,再次執行查詢語句,還是失敗,則將當前的master節點設定為read_only,並kill掉當前的客戶端連線,然後停止當前的keepalived。

master2

[root@master2 ~]# vim /etc/keepalived/keepalived.conf

! Configuration File for keepalived
 
vrrp_instance VI_1 {
    state BACKUP
    interface eno16777736
    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.244.10/24
    }
}

其中,/etc/keepalived/notify_master_mysql.sh的內容如下:

#!/bin/bash
###當keepalived監測到本機轉為MASTER狀態時,執行該指令碼
 
change_log=/etc/keepalived/logs/state_change.log
mysql_con='mysql -uroot -p123456'
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} -e "show slave status\G;"|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} -e "show slave status\G;"|egrep -w "Master_Log_File|Read_Master_Log_Pos|Relay_Master_Log_File|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}'`
        Relay_Master_Log_File=`echo $log_file_pos|awk '{print $6}'`
        Exec_Master_Log_Pos=`echo $log_file_pos|awk '{print $8}'`
    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"|mail -s "DB2 keepalived change to MASTER"\
    slowtech@126.com 2>> $change_log
 
    echo -e "---------------------------------------------------------\n" >> $change_log
}
 
slave_info slave_status
if [ $Slave_SQL_Running = Yes ];then
    i=0    #一個計數器
    slave_info log_file pos
        ###判斷從master接收到的binlog是否全部在本地執行(這樣仍無法完全確定從庫已追上主庫,因為無法完全保證io_thread沒有延時(由網路傳輸問題導致的從庫落後的機率很小)
    until [ $Master_Log_File = $Relay_Master_Log_File -a $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"`    -----Relay_Master_Log_File=$Relay_Master_Log_File,Exec_Master_Log_Pos=$Exec_Master_Log_Pos is behind Master_Log_File=$Master_Log_File,Read_Master_Log_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 Relay_Master_Log_File=$Relay_Master_Log_File Exec_Master_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 Relay_Master_Log_File=$Relay_Master_Log_File Exec_Master_Log_Pos=$Exec_Master_Log_Pos" >> $change_log
    action
fi

整個指令碼的邏輯是讓從的Exec_Master_Log_Pos儘可能的追上Read_Master_Log_Pos,它給了10s的限制,如果還是沒有追上,則直接將master2設定為主(透過解除read_only屬性),其實這裡面還是有待商榷的,譬如10s的限制是否合理,還是一定需要Exec_Master_Log_Pos=Read_Master_Log_Pos才切換。

當原主恢復正常後,如何將VIP從master2切回到master1中呢?

#!/bin/bash
###手動執行將主庫切換回DB1的操作
 
mysql_con='mysql -uroot -p123456'
 
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
log_file_pos=`mysql -urepl -pmysql -h192.168.244.145 -e "show slave status\G;"|egrep -w "Master_Log_File|Read_Master_Log_Pos|Relay_Master_Log_File|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}'`
Relay_Master_Log_File=`echo $log_file_pos|awk '{print $6}'`
Exec_Master_Log_Pos=`echo $log_file_pos|awk '{print $8}'`
until [ $Read_Master_Log_Pos = $Exec_Master_Log_Pos -a $Master_Log_File = $Relay_Master_Log_File ]
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.244.145 'mysql -uroot -p123456 -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"|mail -s "DB2 keepalived change to BACKUP" slowtech@126.com 2>> /etc/keepalived/logs/state_change.log
 
echo -e "--------------------------------------------------\n" >> /etc/keepalived/logs/state_change.log

總結:

1. /etc/keepalived/check_mysql.sh和/etc/keepalived/notify_master_mysql.sh必須加可執行許可權。

如果前者沒有加可執行許可權,則master1上將不會繫結VIP,日誌直接提示如下資訊:

May 25 14:37:09 master1 Keepalived_vrrp[3165]: VRRP_Instance(VI_1) Entering BACKUP STATE
May 25 14:37:09 master1 Keepalived_vrrp[3165]: VRRP sockpool: [ifindex(2), proto(112), unicast(0), fd(10,11)]
May 25 14:37:50 master1 Keepalived_vrrp[3165]: VRRP_Instance(VI_1) Now in FAULT state

2. 在Keepalived中有兩種模式,分別是master->backup模式和backup->backup模式,這兩種模式有什麼區別呢?

在master->backup模式下,一旦主庫宕掉,虛擬IP會自動漂移到從庫,當主庫修復後,keepalived啟動後,還會把虛擬IP搶過來,即使你設定nopreempt(不搶佔)的方式搶佔IP的動作也會發生。在backup->backup模式下,當主庫宕掉後虛擬IP會自動漂移到從庫上,當原主恢復之後重啟keepalived服務,並不會搶佔新主的虛擬IP,即使是優先順序高於從庫的優先順序別,也不會搶佔IP。為了減少IP的漂移次數,生產中我們通常是把修復好的主庫當做新主庫的備庫。

3. 本文是在MySQL主庫高可用 -- 雙主單活故障自動切換方案 基礎上,結合自己對於MySQL的理解整理的。原文的指令碼直接執行有點問題,思路有有點瑕疵,於是結合自己的實際環境,重新修改了一把。

4. 在測試的過程中,有以下幾點需要注意:

1> master1檢測指令碼的邏輯是如果MySQL的服務不可用,則透過service keepalived stop命令來關閉keepalived,但在實際測試的過程中,卻出現了即便執行了service keepalived stop命令,keepalived程序依然沒有停止,導致MySQL的服務雖然不可用了,但VIP並不沒有漂移到master2上。

最佳化方案:在執行service keepalived stop後,等待5s,再次檢測keepalived的狀態,如果keepalived沒有關閉,則直接kill掉。

2> keepalived的日誌預設是輸出到/var/log/messages中,這樣不便於檢視。如何自定義keepalived的日誌輸出檔案呢?

如果是用service啟動的,修改/etc/sysconfig/keepalived檔案

KEEPALIVED_OPTIONS="-D -d -S 0" 

如果不是,則啟動的時候指定以上引數,如:

/usr/local/keepalived/sbin/keepalived -d -D -S 0 

修改/etc/syslog.conf

# keepalived -S 0 
local0.*                                                /var/log/keepalived.log

重啟syslog

RHEL 5&6:service syslog restart

RHEL 7:service rsyslog restart

相關文章