Keepalive之MySQL故障自動切換腳本

mysql架構為master-slave(主從),master故障自動切換到slave上。當然也可以設置為雙master,但這里有個弊端:就是當主的壓力很大時,從上延時很大,比如落后2000秒,此時主掛了,從接管(VIP漂移到從),用戶剛才發表的文章,此時因為同步延時大,還沒復制

MySQL架構為master-slave(主從),master故障自動切換到slave上。當然也可以設置為雙master,但這里有個弊端:就是當主的壓力很大時,從上延時很大,比如落后2000秒,此時主掛了,從接管(VIP漂移到從),用戶剛才發表的文章,此時因為同步延時大,還沒復制過來,于是用戶又發表了一篇文章,當原來的master修好后,因從的IO和SQL線程還在開啟狀態,還會繼續同步剛才沒有同步復制完的數據,這時有可能把用戶新發表的文章更改掉,造成用戶數據丟失。

考慮到這種情況,我這里還是用的master-slave(主從)架構。

keepalive安裝很簡單,這里不再啰嗦。主要看下配置文件和腳本:

# more /etc/keepalived/keepalived.conf?
global_defs {
?? router_id KeepAlive_Mysql
}?

vrrp_script check_run {
script “/root/sh/mysql_check.sh”
interval 300
}

vrrp_sync_group VG1 {
group {
VI_1
}
}

vrrp_instance VI_1 {
??? state BACKUP
??? interface eth0??
??? virtual_router_id 51
??? priority 100??
??? advert_int 1
??? nopreempt
??? authentication {
??????? auth_type PASS
??????? auth_pass 1111
??? }
??? track_script {
??? check_run
??? }

??? notify_master /root/sh/master.sh
??? notify_backup /root/sh/backup.sh
??? notify_stop /root/sh/stop.sh

??? virtual_ipaddress {
??????? 192.168.8.150
??? }
}

notify_master |??? # 狀態改變為MASTER后執行的腳本
notify_backup |??? # 狀態改變為BACKUP后執行的腳本
notify_fault |??? # 狀態改變為FAULT后執行的腳本
notify_stop |??? # VRRP停止后后執行的腳本
notify |??????? # (1)任意狀態改變后執行的腳本
下面解釋下這4個腳本的用法:

mysql_check.sh(健康檢查腳本,當發現mysql連接不上,會把keepalive進程關閉,并切換。)
# more mysql_check.sh?
#!/bin/bash

. /root/.bash_profile

count=1

while true
do

mysql -e “show status;” > /dev/null 2>&1
i=$?
ps aux | grep mysqld | grep -v grep > /dev/null 2>&1
j=$?
if [ $i = 0 ] && [ $j = 0 ]
then
?? exit 0
else
?? if [ $i = 1 ] && [ $j = 0 ]
?? then
?????? exit 0
?? else
??????? if [ $count -gt 5 ]
??????? then
????????????? break
??????? fi
?? let count++
?? continue
?? fi
fi

done

/etc/init.d/keepalived stop
master.sh(狀態改變為MASTER后執行的腳本)? (首先判斷同步復制是否執行完畢,如果未執行完畢,等1分鐘后,不論是否執行完畢,都跳過,并停止同步復制進程。) (其次,更改前端程序連接的業務賬號admin的權限和密碼,并記錄當前切換以后的日志和POS點。)
# more master.sh
#!/bin/bash

. /root/.bash_profile

Master_Log_File=$(mysql -e “show slave statusG” | grep -w Master_Log_File | awk -F”: ” ‘{print $2}’)
Relay_Master_Log_File=$(mysql -e “show slave statusG” | grep -w Relay_Master_Log_File | awk -F”: ” ‘{print $2}’)
Read_Master_Log_Pos=$(mysql -e “show slave statusG” | grep -w Read_Master_Log_Pos | awk -F”: ” ‘{print $2}’)
Exec_Master_Log_Pos=$(mysql -e “show slave statusG” | grep -w Exec_Master_Log_Pos | awk -F”: ” ‘{print $2}’)

i=1

while true
do

if [ $Master_Log_File = $Relay_Master_Log_File ] && [ $Read_Master_Log_Pos -eq $Exec_Master_Log_Pos ]
then
?? echo “ok”
?? break
else
?? sleep 1

?? if [ $i -gt 60 ]
?? then
????? break
?? fi
?? continue
?? let i++
fi
done

mysql -e “stop slave;”
mysql -e “flush logs;GRANT ALL PRIVILEGES ON *.* TO ‘admin’@’%’ IDENTIFIED BY ‘admin’;flush privileges;”
mysql -e “show master status;” > /tmp/master_status_$(date “+%y%m%d-%H%M”).txt
backup.sh(狀態改變為BACKUP后執行的腳本)
# more backup.sh?
#!/bin/bash

. /root/.bash_profile

mysql -e “GRANT ALL PRIVILEGES ON *.* TO ‘admin’@’%’ IDENTIFIED BY ‘1q2w3e4r’;flush privileges;”
stop.sh(keepalived停止后后執行的腳本) (首先把admin密碼更改掉) (其次,設置參數,保證不丟失數據) (最后,查看是否還有寫操作,不論是否執行完畢,1分鐘后都退出。)
# more stop.sh?
#!/bin/bash

. /root/.bash_profile

mysql -e “GRANT ALL PRIVILEGES ON *.* TO ‘admin’@’%’ IDENTIFIED BY ‘1q2w3e4r’;flush privileges;”
mysql -e “set global innodb_support_xa=1;”
mysql -e “set global sync_binlog=1;”
mysql -e “set global innodb_flush_log_at_trx_commit=1;”

M_File1=$(mysql -e “show master statusG” | awk -F’: ‘ ‘/File/{print $2}’)
M_Position1=$(mysql -e “show master statusG” | awk -F’: ‘ ‘/Position/{print $2}’)
sleep 1
M_File2=$(mysql -e “show master statusG” | awk -F’: ‘ ‘/File/{print $2}’)
M_Position2=$(mysql -e “show master statusG” | awk -F’: ‘ ‘/Position/{print $2}’)

i=1

while true
do

if [ $M_File1 = $M_File2 ] && [ $M_Position1 -eq $M_Position2 ]
then
?? echo “ok”
?? break
else
?? sleep 1

?? if [ $i -gt 60 ]
?? then
????? break
?? fi
?? continue
?? let i++
fi
done
有興趣的朋友可以測試下,有問題及時交流。

? 版權聲明
THE END
喜歡就支持一下吧
點贊10 分享