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

迷南。 2022-09-25 01:24 164阅读 0赞

环境搭建：

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;"   ！！！慎用慎用！！！