mysql vip切换未重连问题_MySQL高可用方案设计与测试（下篇）

Mysql切换检测脚本

keepalived_to_master.sh

升级为master时的检测脚本(主要检查备机复制状态是否可用，并发送报警)。

#!/bin/bash

#检查从mysql复制状态，确认是否可升级为主

VIP=10.130.10.43

MYSQL=/home/ddb/mysql/xtradb-5.1.47_netease/bin/mysql

MYSQL_HOST=127.0.0.1

MYSQL_PORT=5331

MYSQL_USER=root

MYSQL_PASSWORD=123

Seconds_Behind=5

PDIR=(dirname0)

isOK=1

if [ -f PDIR/repl ] && grep

"fail"PDIR/repl ; then

isOK=0

fi

msg="[`date +\"%Y-%m-%d %T \"`] change master to `hostname -i`:$MYSQL_PORT "

if [ $isOK -eq 1 ] ; then

MYSQL−hMYSQL_HOST -PMYSQLPORT−uMYSQL_USER -pMYSQLPASSWORD−e"SETGLOBALreadonly=OFF;">>PDIR/keepalived.log 2>&1

msg="$msg succeed"

else

sudo /etc/init.d/keepalived stop >>$PDIR/keepalived.log 2>&1

msg="$msg failed"

fi

echo msg>>PDIR/keepalived.log

cd PDIR && python sendmail.py

"msg"

keepalived_to_backup.sh

降级为backup时的检测脚本(设置备机mysql为readonly)。

#!/bin/bash

#备机状态打开read_only

MYSQL=/home/ddb/mysql/xtradb-5.1.47_netease/bin/mysql

MYSQL_HOST=127.0.0.1

MYSQL_PORT=5331

MYSQL_USER=root

MYSQL_PASSWORD=123

PDIR=(dirname0)

MYSQL−hMYSQL_HOST -PMYSQLPORT−uMYSQL_USER -pMYSQLPASSWORD−e"SETGLOBALreadonly=ON;">>PDIR/keepalived.log

启动keepalived

在主备两台服务器上执行service keepalived start，启动keepalived。即可实现对mysql的监测与切换。

五、方案测试

测试项a-c虚拟机环境，测试项d-f物理机环境

a)按上述配置启动keepalived后，先测试vip是否绑定成功。

node-1网卡配置

node-2网卡配置