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

环境搭建：

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/bashmysql_conn="/usr/local/mysql/bin/mysql -uroot -proot -h127.0.0.1 -P3306"errFile=/tmp/keepalived.errip=`/sbin/ifconfig | grep "192.168.56.255" | awk -F "[: ]+" '{print $4}'`call(){for phone in xxxdo    #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 $errFiledone}##检查逻辑i:查询成功 j:进程在----查询成功，进程在OK！查询失败，进程在，5s后再次查询，成功则OK，不成功则监本检测失败！进程不存在，脚本检查失败！while truedo    $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    fidone#此处发邮件发短信报警callexit 1

keepalived 状态切换脚本

 notify.sh 

#!/bin/bashmysql_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.changestat=$1call(){for phone in xxxdo    #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 $errFiledone}###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    fielif [ $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 $errFileelif [ $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 stopficall

测试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/messagesAug  4 17:23:03 Zabbix Keepalived_vrrp[15588]: pid 17479 exited with status 1Aug  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/messagesAug  4 17:41:04 Zabbix Keepalived_vrrp[18675]: pid 18766 exited with status 1Aug  4 17:41:19 Zabbix Keepalived_vrrp[18675]: pid 18795 exited with status 1Aug  4 17:41:34 Zabbix Keepalived_vrrp[18675]: pid 18818 exited with status 1Aug  4 17:41:34 Zabbix Keepalived_vrrp[18675]: VRRP_Script(vs_mysql_161) failedAug  4 17:41:34 Zabbix Keepalived_vrrp[18675]: VRRP_Instance(VI_161) Entering FAULT STATEAug  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 stateAug  4 17:41:34 Zabbix Keepalived_healthcheckers[18674]: Netlink reflector reports IP 192.168.56.161 removedAug  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 secsAug  4 17:41:46 Zabbix Keepalived[18672]: StoppingAug  4 17:41:46 Zabbix Keepalived_healthcheckers[18674]: StoppedAug  4 17:41:47 Zabbix Keepalived_vrrp[18675]: StoppedAug  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/messagesAug  4 17:41:35 redis Keepalived_vrrp[11028]: VRRP_Instance(VI_161) Transition to MASTER STATEAug  4 17:41:37 redis Keepalived_vrrp[11028]: VRRP_Instance(VI_161) Entering MASTER STATEAug  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.161Aug  4 17:41:37 redis Keepalived_vrrp[11028]: VRRP_Instance(VI_161) Sending/queueing gratuitous ARPs on eth0 for 192.168.56.161Aug  4 17:41:37 redis Keepalived_vrrp[11028]: Sending gratuitous ARP on eth0 for 192.168.56.161Aug  4 17:41:37 redis Keepalived_vrrp[11028]: Sending gratuitous ARP on eth0 for 192.168.56.161Aug  4 17:41:37 redis Keepalived_vrrp[11028]: Sending gratuitous ARP on eth0 for 192.168.56.161Aug  4 17:41:37 redis Keepalived_vrrp[11028]: Sending gratuitous ARP on eth0 for 192.168.56.161Aug  4 17:41:37 redis Keepalived_healthcheckers[11027]: Netlink reflector reports IP 192.168.56.161 addedAug  4 17:41:39 redis ntpd[1534]: Listening on interface #14 eth0, 192.168.56.161#123 EnabledAug  4 17:41:42 redis Keepalived_vrrp[11028]: Sending gratuitous ARP on eth0 for 192.168.56.161Aug  4 17:41:42 redis Keepalived_vrrp[11028]: VRRP_Instance(VI_161) Sending/queueing gratuitous ARPs on eth0 for 192.168.56.161Aug  4 17:41:42 redis Keepalived_vrrp[11028]: Sending gratuitous ARP on eth0 for 192.168.56.161Aug  4 17:41:42 redis Keepalived_vrrp[11028]: Sending gratuitous ARP on eth0 for 192.168.56.161Aug  4 17:41:42 redis Keepalived_vrrp[11028]: Sending gratuitous ARP on eth0 for 192.168.56.161Aug  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: 53B：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;"   ！！！慎用慎用！！！