MySQL:基于双主复制的keepalived的HA方案

时间：2022-09-15 07:51:21

环境搭建：

A-B双主，A主B备主(最好开启版同步复制确保数据基本完整性)

keepalived 已安装


keepalived 配置文件

A(keepalived.conf)   B只修改priority 80即可

vrrp_script vs_mysql_161 {
    script "/data/scripts/keepalived/check_mysql.sh"
    interval 15 
    timeout 10
    fall 3
    rise 2
}
vrrp_instance VI_161 {
    state BACKUP
    nopreempt
    interface eth0
    virtual_router_id 161
    priority 90
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    track_script {
        vs_mysql_161
    }
    virtual_ipaddress {
        192.168.56.161
    }
    notify_master "/data/scripts/keepalived/notify.sh master"
    notify_backup  "/data/scripts/keepalived/notify.sh backup"
    notify_fault  "/data/scripts/keepalived/notify.sh fault"
}

MySQL检查脚本

check_mysql.sh 

#!/bin/bash
mysql_conn="/usr/local/mysql/bin/mysql -uroot -proot -h127.0.0.1 -P3306"

errFile=/tmp/keepalived.err
ip=`/sbin/ifconfig | grep "192.168.56.255" | awk -F "[: ]+" '{print $4}'`

call(){
for phone in xxx
do
    #curl "http:/sendSMS.json?phone={$phone}&msg={$ip}+{$MYSQL_PORT}+{keepalived_is_change}"
    echo "[ `date +"%Y-%m-%d %H:%M:%S"` ] {$ip}+{$MYSQL_PORT}+{keepalived_is_change}"| tee -a $errFile
done
}

##检查逻辑i:查询成功 j:进程在----查询成功，进程在OK！查询失败，进程在，5s后再次查询，成功则OK，不成功则监本检测失败！进程不存在，脚本检查失败！
while true
do
    $mysql_conn -e "SELECT SYSDATE();"  >/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
            echo "@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@"| tee -a $errFile  
            echo "[ `date +"%Y-%m-%d %H:%M:%S"` ] MySQL query error,sleep 5s,try again!"| tee -a $errFile
            sleep 5
            $mysql_conn -e "SELECT SYSDATE();"  >/dev/null 2>&1
            i1=$?
                if [ $i1 = 0 ]
                then 
                    echo "[ `date +"%Y-%m-%d %H:%M:%S"` ] MySQL again query OK!"| tee -a $errFile
                    exit 0
                else
                    echo "[ `date +"%Y-%m-%d %H:%M:%S"` ] MySQL again query error!"| tee -a $errFile
                    break
                fi
        else
            break
        fi
    fi
done
#此处发邮件发短信报警
call
exit 1

keepalived 状态切换脚本

 notify.sh 

#!/bin/bash
mysql_conn="/usr/local/mysql/bin/mysql -uroot -proot -h127.0.0.1 -P3306"
ip=`/sbin/ifconfig | grep "192.168.56.255" | awk -F "[: ]+" '{print $4}'`
errFile=/tmp/keepalived.change
stat=$1

call(){
for phone in xxx
do
    #curl "http://msg/sendSMS.json?phone={$phone}&msg={$ip}+{keepalived_is_change}+{$stat}"
    echo "[ `date +"%Y-%m-%d %H:%M:%S"` ] {$ip}+{keepalived_is_change}+{$stat}"| tee -a $errFile
done
}

###keepalived进入master状态，设置数据库只读，检查现在备主是否执行完所有relay log，执行完，取消只读，如30s还未执行完，reset slave all；强制切换成主库！
###keepalived进入backup状态，设置数据库只读！
###keepalived进入fault状态,脚本检测失败，停库，停keepalived！
if [ $stat = 'master' ];then
    echo  "@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@" | tee -a $errFile
    i=1
    $mysql_conn -e "set global read_only=1;"
    echo  "[ `date +"%Y-%m-%d %H:%M:%S"` ] MySQL change master,set db read_only" | tee -a $errFile
    while [ $i -lt 10 ] 
    do
        pos=`$mysql_conn -e "show slave status\G;"|egrep "Master_Log" |awk '{printf ("%s",$NF "\t")}'`
        read_file=`echo $pos|awk '{print $1}'`
        read_pos=`echo $pos|awk '{print $2}'`
        exec_file=`echo $pos|awk '{print $3}'`
        exec_pos=`echo $pos|awk '{print $4}'`
        echo $read_file $exec_file $read_pos $exec_pos
        sleep 3
        let i++
        if [ $read_pos = $exec_pos ] && [ $read_pos = $exec_pos ];then 
            $mysql_conn -e "set global read_only=0;"
            echo "[ `date +"%Y-%m-%d %H:%M:%S"` ] MySQL slave has relay all relay log,cancel db read_only!"| tee -a $errFile
            break
        fi
    done
    if [ $i -eq 10 ];then
        $mysql_conn -e "stop slave;reset slave all;set global read_only=0;"
        echo "[ `date +"%Y-%m-%d %H:%M:%S"` ][Waring]MySQL slave not relay all relay log,cancel db read_only!"| tee -a $errFile
    fi
elif [ $stat = 'backup' ];then
    $mysql_conn -e "set global read_only=1;"
    echo "[ `date +"%Y-%m-%d %H:%M:%S"` ] MySQL slave set db read_only!"| tee -a $errFile
elif [ $stat = 'fault' ];then
    echo "[ `date +"%Y-%m-%d %H:%M:%S"` ] keepalived Entering FAULT STATE,stop MySQL and keepalived!"| tee -a $errFile
    /etc/init.d/mysql3306 stop
    /etc/init.d/keepalived stop
fi

call




测试1

主检测脚本2次脚本失败，在第三次5s后检测成功(fall 3起作用)
A：
cat /tmp/keepalived.err 
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
[ 2016-08-04 17:22:58 ] MySQL query error,sleep 5s,try again!
[ 2016-08-04 17:23:03 ] MySQL again query error!
[ 2016-08-04 17:23:03 ] {192.168.56.159}+{}+{keepalived_is_change}
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
[ 2016-08-04 17:23:13 ] MySQL query error,sleep 5s,try again!
[ 2016-08-04 17:23:18 ] MySQL again query error!
[ 2016-08-04 17:23:18 ] {192.168.56.159}+{}+{keepalived_is_change}
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
[ 2016-08-04 17:23:28 ] MySQL query error,sleep 5s,try again!
[ 2016-08-04 17:23:33 ] MySQL again query OK!

cat /var/log/messages
Aug  4 17:23:03 Zabbix Keepalived_vrrp[15588]: pid 17479 exited with status 1
Aug  4 17:23:18 Zabbix Keepalived_vrrp[15588]: pid 17512 exited with status 1

测试2 

设置max_user_connections=2，登录2个客户端，模拟检查脚本执行错误
A：
[root@192.168.56.159 keepalived]cat /tmp/keepalived.err 
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
[ 2016-08-04 17:40:59 ] MySQL query error,sleep 5s,try again!
[ 2016-08-04 17:41:04 ] MySQL again query error!
[ 2016-08-04 17:41:04 ] {192.168.56.159}+{}+{keepalived_is_change}
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
[ 2016-08-04 17:41:14 ] MySQL query error,sleep 5s,try again!
[ 2016-08-04 17:41:19 ] MySQL again query error!
[ 2016-08-04 17:41:19 ] {192.168.56.159}+{}+{keepalived_is_change}
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
[ 2016-08-04 17:41:29 ] MySQL query error,sleep 5s,try again!
[ 2016-08-04 17:41:34 ] MySQL again query error!
[ 2016-08-04 17:41:34 ] {192.168.56.159}+{}+{keepalived_is_change}
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
[ 2016-08-04 17:41:44 ] MySQL query error,sleep 5s,try again!
[ 2016-08-04 17:41:49 ] MySQL again query error!
[ 2016-08-04 17:41:49 ] {192.168.56.159}+{}+{keepalived_is_change}

cat /var/log/messages
Aug  4 17:41:04 Zabbix Keepalived_vrrp[18675]: pid 18766 exited with status 1
Aug  4 17:41:19 Zabbix Keepalived_vrrp[18675]: pid 18795 exited with status 1
Aug  4 17:41:34 Zabbix Keepalived_vrrp[18675]: pid 18818 exited with status 1
Aug  4 17:41:34 Zabbix Keepalived_vrrp[18675]: VRRP_Script(vs_mysql_161) failed
Aug  4 17:41:34 Zabbix Keepalived_vrrp[18675]: VRRP_Instance(VI_161) Entering FAULT STATE
Aug  4 17:41:34 Zabbix Keepalived_vrrp[18675]: VRRP_Instance(VI_161) removing protocol VIPs.
Aug  4 17:41:34 Zabbix Keepalived_vrrp[18675]: VRRP_Instance(VI_161) Now in FAULT state
Aug  4 17:41:34 Zabbix Keepalived_healthcheckers[18674]: Netlink reflector reports IP 192.168.56.161 removed
Aug  4 17:41:36 Zabbix ntpd[1591]: Deleting interface #17 eth0, 192.168.56.161#123, interface stats: received=0, sent=0, dropped=0, active_time=72 secs
Aug  4 17:41:46 Zabbix Keepalived[18672]: Stopping
Aug  4 17:41:46 Zabbix Keepalived_healthcheckers[18674]: Stopped
Aug  4 17:41:47 Zabbix Keepalived_vrrp[18675]: Stopped
Aug  4 17:41:47 Zabbix Keepalived[18672]: Stopped Keepalived v1.2.21 (08/03,2016)

B：
[root@192.168.56.160 keepalived]cat /tmp/keepalived.change 
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
[ 2016-08-04 17:41:37 ] MySQL change master,set db read_only
[ 2016-08-04 17:41:40 ] MySQL slave has relay all relay log,cancel db read_only!
[ 2016-08-04 17:41:40 ] {192.168.56.160}+{keepalived_is_change}+{master}

cat /var/log/messages
Aug  4 17:41:35 redis Keepalived_vrrp[11028]: VRRP_Instance(VI_161) Transition to MASTER STATE
Aug  4 17:41:37 redis Keepalived_vrrp[11028]: VRRP_Instance(VI_161) Entering MASTER STATE
Aug  4 17:41:37 redis Keepalived_vrrp[11028]: VRRP_Instance(VI_161) setting protocol VIPs.
Aug  4 17:41:37 redis Keepalived_vrrp[11028]: Sending gratuitous ARP on eth0 for 192.168.56.161
Aug  4 17:41:37 redis Keepalived_vrrp[11028]: VRRP_Instance(VI_161) Sending/queueing gratuitous ARPs on eth0 for 192.168.56.161
Aug  4 17:41:37 redis Keepalived_vrrp[11028]: Sending gratuitous ARP on eth0 for 192.168.56.161
Aug  4 17:41:37 redis Keepalived_vrrp[11028]: Sending gratuitous ARP on eth0 for 192.168.56.161
Aug  4 17:41:37 redis Keepalived_vrrp[11028]: Sending gratuitous ARP on eth0 for 192.168.56.161
Aug  4 17:41:37 redis Keepalived_vrrp[11028]: Sending gratuitous ARP on eth0 for 192.168.56.161
Aug  4 17:41:37 redis Keepalived_healthcheckers[11027]: Netlink reflector reports IP 192.168.56.161 added
Aug  4 17:41:39 redis ntpd[1534]: Listening on interface #14 eth0, 192.168.56.161#123 Enabled
Aug  4 17:41:42 redis Keepalived_vrrp[11028]: Sending gratuitous ARP on eth0 for 192.168.56.161
Aug  4 17:41:42 redis Keepalived_vrrp[11028]: VRRP_Instance(VI_161) Sending/queueing gratuitous ARPs on eth0 for 192.168.56.161
Aug  4 17:41:42 redis Keepalived_vrrp[11028]: Sending gratuitous ARP on eth0 for 192.168.56.161
Aug  4 17:41:42 redis Keepalived_vrrp[11028]: Sending gratuitous ARP on eth0 for 192.168.56.161
Aug  4 17:41:42 redis Keepalived_vrrp[11028]: Sending gratuitous ARP on eth0 for 192.168.56.161
Aug  4 17:41:42 redis Keepalived_vrrp[11028]: Sending gratuitous ARP on eth0 for 192.168.56.161

测试3 

设置max_user_connections=2，登录2个客户端，模拟检查脚本执行错误
此时B可以执行 FLUSH TABLES WITH READ LOCK; 模拟从库延时
Seconds_Behind_Master: 53

B：
cat /tmp/keepalived.change 
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
[ 2016-08-04 17:51:00 ] MySQL change master,set db read_only
[ 2016-08-04 17:51:27 ][Waring]MySQL slave not relay all relay log,cancel db read_only!
[ 2016-08-04 17:51:27 ] {192.168.56.160}+{keepalived_is_change}+{master}

notify.sh脚本中，30s如备主还延迟则 "stop slave;reset slave all;set global read_only=0;"   ！！！慎用慎用！！！







标签：

相关文章