MySQL主库高可用 -- 双主单活故障自动切换方案

前言：（PS：前言是后来修改本文时加的）对于这篇文章，有博友提出了一些疑问和见解， 有了博友的关注，也促使我想把这套东西做的更实用、更安全。后来又经过思考，对脚本中一些条件和行为做了些改变。经过几次修改，现在终于敢说让小伙伴本使用这套东西了。

主要目的：

    以双主结构配合keepalived解决MySQL主从结构中主库的单点故障；同时通过具体的查询语句提供更细粒度、更为真实的关于主库可用性的判断。

基本思路：

    将DB1和DB2做成主动被动模式的双主结构：DB1主动、DB2被动，通过keepalived的VIP对外，将VIP设置成原DB1的IP，保证改造过程对代码透明

    三个前提：

            两台MySQL的配置文件里需要加上“log_slave_updates = 1”；

            并且“备用机”通过“read_only”参数实现除root用户之外的只读特性；

            分别在两个数据库创建test.test表，插入几条数据，供检测脚本使用。

    正常时，VIP在DB1，通过keepalived调用脚本定期检查mysql服务可用性（通过一个低权限用户连接mysql服务器并执行一个简单查询，根据返回结果来判定mysql是否可用）

若无法执行查询：

1. 第一次检测失败后,检查服务状态,：

1. 若服务异常，则执行切换：关闭DB1的keepalived，使VIP漂移至DB2，通过DB2上keepalived的notify_master机制，触发脚本将DB2的mysql从被动状态（只读）切换到主动状态（可读写），并发送通知邮件。

2. 若服务正常（则可能是一些临时性因素导致的监测失败），等待30s做第二次检查，这30s是对瞬时/短时因素造成检查失败的容忍时间，本着“能不切则不切”的原则。若第二次检查仍然失败

2.  开始执行系列切换动作

1. 将DB1的MySQL设置为read_only模式 （阻止写请继续求进入）

2. kill掉当前客户端的线程。原来担心kill掉线程会对数据执行造成影响，后来查看了官方文档“mysql shutdown process”，发现mysql正常关闭过程也有一步是如此操作，所以这里可以放心了。然后 sleep 2，给kill命令一些时间（关于kill命令的机制，参考官方解释）

3. 关闭DB1的keepalived，使DB2接管VIP。通过DB2上keepalived的notify_master机制，触发脚本将DB2的

   mysql从被动状态（只读）切换到主动状态（可读写），并发送通知邮件。

3.  管理员修复DB1后，通过脚本“change_to_backup.sh”将主库切换回DB1。脚本思路如下：

     注：涉及到切换主备，就会有中断时间，所以推荐此步骤在业务低谷期执行

1. 将DB2的read_only属性置为1

2. kill掉DB2上的client线程，并重启DB2的keepalived使VIP漂移至DB1

3. 确定DB1跟上了DB2的更新，并将DB1上的read_only属性移除
   

   
   

关于“数据一致性”和“切换时间”：

      连续两次失败以后，通过对主MySQL设置read_only属性，同时kill掉用户线程来保证在DB2接管服务之前，DB1上已经没有写操作，避免主从数据不一致。并且切换时间基本上是可确定的：

      30s（两次检测间隔）+2s（等待kill命令时间）+约1s（keepalived切换VIP），总时间不会超过35s。

以上是大致思路，具体实现看过下面的脚本，就会一目了然了。

DB1上keepalived 配置

! Configuration File for keepalivedvrrp_script chk_mysql {    script "/etc/keepalived/check_mysql.sh"    interval 30         #这里我的检查间隔设置的比较长，因为我们数据库前面有redis做缓存，数据库一两分钟级别的中断对整体可用性影响不大。这也是我没有采用成熟的方案而自己搞了这一套方案的“定心丸”}vrrp_instance VI_1 {    state BACKUP        #通过下面的priority来区分MASTER和BACKUP，也只有如此，底下的nopreempt才有效    interface em2    virtual_router_id 51    priority 100    advert_int 1    nopreempt           #防止切换到从库后，主keepalived恢复后自动切换回主库    authentication {        auth_type PASS        auth_pass 1111    }    track_script {        chk_mysql    }        virtual_ipaddress {        192.168.1.5/24    }}

/etc/keepalived/check_mysql.sh脚本内容如下（主要的判断逻辑都在这里）

#!/bin/sh###判断如果上次检查的脚本还没执行完，则退出此次执行if [ `ps -ef|grep -w "$0"|grep "/bin/sh*"|grep "?"|grep "?"|grep -v "grep"|wc -l` -gt 2 ];then  #理论上这里应该是1，但是实验的结果却是2    exit 0fialias mysql_con='mysql -uxxxx -pxxxx'###定义一个简单判断mysql是否可用的函数function excute_query {    mysql_con -e "select * from test.test;" 2>>/etc/keepalived/logs/check_mysql.err}###定义无法执行查询，且mysql服务异常时的处理函数function service_error {    echo -e "`date "+%F  %H:%M:%S"`    -----mysql service error，now stop keepalived-----" >> /etc/keepalived/logs/check_mysql.err    /sbin/service keepalived stop &>> /etc/keepalived/logs/check_mysql.err    echo -e "\n@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@\n" >> /etc/keepalived/logs/check_mysql.err}###定义无法执行查询,但mysql服务正常的处理函数function query_error {    echo -e "`date "+%F  %H:%M:%S"`    -----query error, but mysql service ok, retry after 30s-----" >> /etc/keepalived/logs/check_mysql.err    sleep 30    excute_query    if [ $? -ne 0 ];then        echo -e "`date "+%F  %H:%M:%S"`    -----still can't execute query-----" >> /etc/keepalived/logs/check_mysql.err        ###对DB1设置read_only属性        echo -e "`date "+%F  %H:%M:%S"`    -----set read_only = 1 on DB1-----" >> /etc/keepalived/logs/check_mysql.err        mysql_con -e "set global read_only = 1;" 2>> /etc/keepalived/logs/check_mysql.err        ###kill掉当前客户端连接        echo -e "`date "+%F  %H:%M:%S"`    -----kill current client thread-----" >> /etc/keepalived/logs/check_mysql.err        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"        sleep 2    ###给kill一个执行和缓冲时间        ###关闭本机keepalived               echo -e "`date "+%F  %H:%M:%S"`    -----stop keepalived-----" >> /etc/keepalived/logs/check_mysql.err        /sbin/service keepalived stop &>> /etc/keepalived/logs/check_mysql.err        echo -e "\n@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@\n" >> /etc/keepalived/logs/check_mysql.err    else        echo -e "`date "+%F  %H:%M:%S"`    -----query ok after 30s-----" >> /etc/keepalived/logs/check_mysql.err        echo -e "\n@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@\n" >> /etc/keepalived/logs/check_mysql.err    fi}###检查开始: 执行查询excute_queryif [ $? -ne 0 ];then    /sbin/service mysql status &>/dev/null    if [ $? -ne 0 ];then        service_error    else        query_error    fifi

DB2上keepalived配置：

! Configuration File for keepalivedvrrp_instance VI_1 {    state BACKUP    interface em2    virtual_router_id 51    priority 90    advert_int 1    authentication {        auth_type PASS        auth_pass 1111    }    notify_master /etc/keepalived/notify_master_mysql.sh    #此条指令告诉keepalived发现自己转为MASTER后执行的脚本    virtual_ipaddress {        192.168.1.5/24    }}

/etc/keepalived/notify_master_mysql.sh脚本内容：

#!/bin/bash###当keepalived监测到本机转为MASTER状态时，执行该脚本change_log=/etc/keepalived/logs/state_change.logalias 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_logslave_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"\    [e-mail] 2>> $change_log    echo -e "@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@\n" >> $change_log}slave_info slave_statusif [ $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_Log_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_Master_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_Pos=$Exec_Master_Log_Pos" >> $change_log    actionfi

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.logecho -e "`date "+%F  %H:%M:%S"`    -----set read_only = 1 on DB2-----" >> /etc/keepalived/logs/state_change.logmysql_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.logrm -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.logsleep 2    ###给kill一个执行和缓冲时间###确保DB1已经追上了,下面的repl为复制所用的账户，-h后跟DB1的内网IPpos=`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 1done###然后解除DB1的read_only属性echo -e "`date "+%F  %H:%M:%S"`    -----set read_only = 0 on DB1-----" >> /etc/keepalived/logs/state_change.logssh 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漂移到DB1echo -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.logecho "DB2 keepalived转为BACKUP状态，线上数据库切换至DB1"|/bin/mailx -s "DB2 keepalived change to BACKUP" [e-mail] 2>> /etc/keepalived/logs/state_change.logecho -e "@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@\n" >> /etc/keepalived/logs/state_change.log

日志截图：

DB1 mysql服务故障：

DB1 mysql服务正常，查询失败：

DB2 一次切换过程：

DB2 执行脚本手动切回DB1：

总结：此方相比MHA或者MMM之类技术，特点在于简单，降低实施和维护复杂度；同时也安全的解决了主从中master节点的单点问题；在此基础上，亦可以再增加从库实现读写分离等架构；不足之处是双主仍是单活，DB2只是作为热备。

本文出自 “奋进的K” 博客，请务必保留此出处http://kaifly.blog.51cto.com/3209616/1665729