当前位置:Gxlcms > mysql > Keepalive之MySQL故障自动切换脚本

Keepalive之MySQL故障自动切换脚本

时间:2021-07-01 10:21:17 帮助过:25人阅读

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 status\G" | grep -w Master_Log_File | awk -F": " '{print $2}')
Relay_Master_Log_File=$(mysql -e "show slave status\G" | grep -w Relay_Master_Log_File | awk -F": " '{print $2}')
Read_Master_Log_Pos=$(mysql -e "show slave status\G" | grep -w Read_Master_Log_Pos | awk -F": " '{print $2}')
Exec_Master_Log_Pos=$(mysql -e "show slave status\G" | 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 status\G" | awk -F': ' '/File/{print $2}')
M_Position1=$(mysql -e "show master status\G" | awk -F': ' '/Position/{print $2}')
sleep 1
M_File2=$(mysql -e "show master status\G" | awk -F': ' '/File/{print $2}')
M_Position2=$(mysql -e "show master status\G" | 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
有兴趣的朋友可以测试下,有问题及时交流。

人气教程排行