/etc/keepalived/notify_master_mysql.sh脚本内容：
#!/bin/bash
###当keepalived监测到本机转为MASTER状态时，执行该脚本
change_log=/etc/keepalived/logs/state_change.log
alias mysql_con='mysql -uroot -pxxxx -e "show slave status\G;" 2>/dev/null'
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|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|egrep -w "Master_Log_File|Read_Master_Log_Pos|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}'`
Exec_Master_Log_Pos=`echo $log_file_pos|awk '{print $6}'`
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"|/bin/mailx -s "DB2 keepalived change to MASTER"\
lijiankai@dm.com 2>> $change_log
echo -e "@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@\n" >> $change_log
}
slave_info slave_status
if [ $Slave_IO_Running = Yes -a $Slave_SQL_Running = Yes ];then
i=0 #一个计数器
slave_info log_file pos
###判断从master接收到的binlog是否全部在本地执行(这样仍无法完全确定从库已追上主库，因为无法完全保证io_thread没有延时(由网络传输问题导致的从库落后的概率很小)
until [ $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"` -----Master_Log_File=$Master_Log_File. Exec_Master_Log_Pos($Exec_Master_Log_Pos) is behind Read_Master_Lo
g_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 Exec_Ma
ster_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 Exec_Master_Log_Po
s=$Exec_Master_Log_Pos" >> $change_log
action
fi
DB2上手动切换回DB1的脚本change_to_backup.sh：
#!/bin/sh
###手动执行将主库切换回DB1的操作
alias mysql_con='mysql -uxxxx -pxxxx'
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
pos=`mysql -urepl -pxxxx -h192.168.1.x -e "show slave status\G;"|grep "Master_Log_Pos"|awk '{printf ("%s",$NF "\t")}'`
read_pos=`echo $pos|awk '{print $1}'`
exec_pos=`echo $pos|awk '{print $2}'`
until [ $read_pos = $exec_pos ]
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.1.x 'mysql -uxxxx -pxxxx -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"|/bin/mailx -s "DB2 keepalived change to BACKUP" xxx@xxxx.com 2>> /etc/keepalived/logs/state_change.log
echo -e "@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@\n" >> /etc/keepalived/logs/state_change.log
日志截图：
DB1 mysql服务故障：
DB1 mysql服务正常，查询失败：
DB2 一次切换过程：
DB2 执行脚本手动切回DB1：
