当前位置:Gxlcms > mysql > MySQL高可用-双主故障自动切换方案

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

时间:2021-07-01 10:21:17 帮助过:28人阅读

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

前言:(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 keepalived

vrrp_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 0
fi

alias 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_query
if [ $? -ne 0 ];then
    /sbin/service mysql status &>/dev/null
    if [ $? -ne 0 ];then
        service_error
    else
        query_error
    fi
fi


DB2上keepalived配置:

! Configuration File for keepalived

vrrp_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状态时,执行该脚本
alias mysql_con='mysql -uxxxx -pxxxx'
echo -e "`date "+%F  %H:%M:%S"`   -----keepalived change to MASTER-----" >> /etc/keepalived/logs/state_change.log

###判断是否已经将从master接收到的binlog全部在本地执行(这么做仍然无法完全确定从库就已经追上了主库,因为虽然说从库延时一般情况都是慢在sql_thread上,但是也无法完全保证io_thread完全就没有延时。但至少能保证已经读取到的binlog在本地执行完毕)
pos=`mysql_con -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"`    -----Exec_Master_Log_Pos is behind Read_Master_Log_Pos, wait......" >> /etc/keepalived/logs/state_ch
ange.log
    sleep 1
done

###然后解除read_only属性
echo -e "`date "+%F  %H:%M:%S"`    -----set read_only = 0 on DB2-----" >> /etc/keepalived/logs/state_change.log
mysql_con -e "set global read_only = 0;" 2>> /etc/keepalived/logs/state_change.log

echo "DB2 keepalived changed to MASTER,online DB server changed to DB2"|/bin/mailx -s "DB2 keepalived change to MASTER" xxxx@xxxx.com 2>> /etc/keepalived/logs/state_change.log
echo -e "@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@\n" >> /etc/keepalived/logs/state_change.log

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一个执行和缓冲时间

###重启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

###确保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 -pxxxx 192.168.1.x 'mysql -uxxxx -pxxxx -e "set global read_only = 0;"' 2>> /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服务故障

wKiom1Yfb07x-EW5AAD6D94EuSs047.jpg

DB1 mysql服务正常,查询失败

wKiom1YYad6QI607AAFDXJ38Zpk793.jpg

DB2 一次切换过程

wKioL1YYaOXjTQEDAAB9x-ob4rE048.jpg

DB2 执行脚本手动切回DB1:

wKiom1YYa97Rq_5_AAFRVWUeFVI819.jpg


总结:此方案适用于中小型企业,解决了主从中master节点的单点问题;同时,在此基础上,可以再增加从库实现读写分离等架构。

人气教程排行