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. 第一次检测失败后,检查服务状态,：

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

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

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

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

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

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

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

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

将DB2的read_only属性置为1

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

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

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

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

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

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

DB1上keepalived 配置

<code>! Configuration File </code><code>for</code> <code>keepalived</code>

<code>vrrp_script chk_mysql {</code>

<code> </code><code>script </code><code>"/etc/keepalived/check_mysql.sh"</code>

<code> </code><code>interval 30 </code><code>#这里我的检查间隔设置的比较长，因为我们数据库前面有redis做缓存，数据库一两分钟级别的中断对整体可用性影响不大。这也是我没有采用成熟的方案而自己搞了这一套方案的“定心丸”</code>

<code>vrrp_instance VI_1 {</code>

<code> </code><code>state BACKUP </code><code>#通过下面的priority来区分MASTER和BACKUP，也只有如此，底下的nopreempt才有效</code>

<code> </code><code>interface em2</code>

<code> </code><code>virtual_router_id 51</code>

<code> </code><code>priority 100</code>

<code> </code><code>advert_int 1</code>

<code> </code><code>nopreempt </code><code>#防止切换到从库后，主keepalived恢复后自动切换回主库</code>

<code> </code><code>authentication {</code>

<code> </code><code>track_script {</code>

<code> </code><code>chk_mysql</code>

<code> </code><code>virtual_ipaddress {</code>

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

<code>###判断如果上次检查的脚本还没执行完，则退出此次执行</code>

<code>if</code> <code>[ `</code><code>ps</code> <code>-ef|</code><code>grep</code> <code>-w </code><code>"$0"</code><code>|</code><code>grep</code> <code>"/bin/sh*"</code><code>|</code><code>grep</code> <code>"?"</code><code>|</code><code>grep</code> <code>"?"</code><code>|</code><code>grep</code> <code>-</code><code>v</code> <code>"grep"</code><code>|</code><code>wc</code> <code>-l` -gt 2 ];</code><code>then</code> <code>#理论上这里应该是1，但是实验的结果却是2</code>

<code>alias</code> <code>mysql_con=</code><code>'mysql -uxxxx -pxxxx'</code>

<code>###定义一个简单判断mysql是否可用的函数</code>

<code>function</code> <code>excute_query {</code>

<code> </code><code>mysql_con -e </code><code>"select * from test.test;"</code> <code>2>></code><code>/etc/keepalived/logs/check_mysql</code><code>.err</code>

<code>###定义无法执行查询，且mysql服务异常时的处理函数</code>

<code>function</code> <code>service_error {</code>

<code> </code><code>echo</code> <code>-e </code><code>"`date "</code><code>+%F %H:%M:%S</code><code>"` -----mysql service error，now stop keepalived-----"</code> <code>>> </code><code>/etc/keepalived/logs/check_mysql</code><code>.err</code>

<code> </code><code>/sbin/service</code> <code>keepalived stop &>> </code><code>/etc/keepalived/logs/check_mysql</code><code>.err</code>

<code> </code><code>echo</code> <code>-e </code><code>"\n@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@\n"</code> <code>>> </code><code>/etc/keepalived/logs/check_mysql</code><code>.err</code>

<code>###定义无法执行查询,但mysql服务正常的处理函数</code>

<code>function</code> <code>query_error {</code>

<code> </code><code>echo</code> <code>-e </code><code>"`date "</code><code>+%F %H:%M:%S</code><code>"` -----query error, but mysql service ok, retry after 30s-----"</code> <code>>> </code><code>/etc/keepalived/logs/check_mysql</code><code>.err</code>

<code> </code><code>sleep</code> <code>30</code>

<code> </code><code>excute_query</code>

<code> </code><code>echo</code> <code>-e </code><code>"`date "</code><code>+%F %H:%M:%S</code><code>"` -----still can't execute query-----"</code> <code>>> </code><code>/etc/keepalived/logs/check_mysql</code><code>.err</code>

<code> </code><code>###对DB1设置read_only属性</code>

<code> </code><code>echo</code> <code>-e </code><code>"`date "</code><code>+%F %H:%M:%S</code><code>"` -----set read_only = 1 on DB1-----"</code> <code>>> </code><code>/etc/keepalived/logs/check_mysql</code><code>.err</code>

<code> </code><code>mysql_con -e </code><code>"set global read_only = 1;"</code> <code>2>> </code><code>/etc/keepalived/logs/check_mysql</code><code>.err</code>

<code> </code><code>###kill掉当前客户端连接</code>

<code> </code><code>echo</code> <code>-e </code><code>"`date "</code><code>+%F %H:%M:%S</code><code>"` -----kill current client thread-----"</code> <code>>> </code><code>/etc/keepalived/logs/check_mysql</code><code>.err</code>

<code> </code><code>###这里其实是一个批量kill线程的小技巧</code>

<code> </code><code>mysql_con -e </code><code>'select concat("kill ",id,";") from information_schema.PROCESSLIST where command="Query" or command="Execute" into outfile "/tmp/kill.sql";'</code>

<code> </code><code>mysql_con -e </code><code>"source /tmp/kill.sql"</code>

<code> </code><code>sleep</code> <code>2 </code><code>###给kill一个执行和缓冲时间</code>

<code> </code><code>###关闭本机keepalived </code>

<code> </code><code>echo</code> <code>-e </code><code>"`date "</code><code>+%F %H:%M:%S</code><code>"` -----stop keepalived-----"</code> <code>>> </code><code>/etc/keepalived/logs/check_mysql</code><code>.err</code>

<code> </code><code>/sbin/service</code> <code>keepalived stop &>> </code><code>/etc/keepalived/logs/check_mysql</code><code>.err</code>

<code> </code><code>echo</code> <code>-e </code><code>"\n@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@\n"</code> <code>>> </code><code>/etc/keepalived/logs/check_mysql</code><code>.err</code>

<code> </code><code>echo</code> <code>-e </code><code>"`date "</code><code>+%F %H:%M:%S</code><code>"` -----query ok after 30s-----"</code> <code>>> </code><code>/etc/keepalived/logs/check_mysql</code><code>.err</code>

<code>excute_query</code>

<code> </code><code>/sbin/service</code> <code>mysql status &></code><code>/dev/null</code>

<code> </code><code>service_error</code>

<code> </code><code>query_error</code>

DB2上keepalived配置：

<code> </code><code>state BACKUP</code>

<code> </code><code>priority 90</code>

<code> </code><code>notify_master </code><code>/etc/keepalived/notify_master_mysql</code><code>.sh </code><code>#此条指令告诉keepalived发现自己转为MASTER后执行的脚本</code>

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

<code>###当keepalived监测到本机转为MASTER状态时，执行该脚本</code>

<code>change_log=</code><code>/etc/keepalived/logs/state_change</code><code>.log</code>

<code>alias</code> <code>mysql_con=</code><code>'mysql -uroot -pxxxx -e "show slave status\G;" 2>/dev/null'</code>

<code>echo</code> <code>-e </code><code>"`date "</code><code>+%F %H:%M:%S</code><code>"` -----keepalived change to MASTER-----"</code> <code>>> $change_log</code>

<code>slave_info() {</code>

<code> </code><code>###统一定义一个函数取得slave的position、running、和log_file等信息</code>

<code> </code><code>###根据函数后面所跟参数来决定取得哪些数据</code>

<code> </code><code>if</code> <code>[ $1 = slave_status ];</code><code>then</code>

<code> </code><code>slave_stat=`mysql_con|</code><code>egrep</code> <code>-w </code><code>"Slave_IO_Running|Slave_SQL_Running"</code><code>`</code>

<code> </code><code>Slave_IO_Running=`</code><code>echo</code> <code>$slave_stat|</code><code>awk</code> <code>'{print $2}'</code><code>`</code>

<code> </code><code>Slave_SQL_Running=`</code><code>echo</code> <code>$slave_stat|</code><code>awk</code> <code>'{print $4}'</code><code>`</code>

<code> </code><code>log_file_pos=`mysql_con|</code><code>egrep</code> <code>-w </code><code>"Master_Log_File|Read_Master_Log_Pos|Exec_Master_Log_Pos"</code><code>`</code>

<code> </code><code>Master_Log_File=`</code><code>echo</code> <code>$log_file_pos|</code><code>awk</code> <code>'{print $2}'</code><code>`</code>

<code> </code><code>Read_Master_Log_Pos=`</code><code>echo</code> <code>$log_file_pos|</code><code>awk</code> <code>'{print $4}'</code><code>`</code>

<code> </code><code>Exec_Master_Log_Pos=`</code><code>echo</code> <code>$log_file_pos|</code><code>awk</code> <code>'{print $6}'</code><code>`</code>

<code>action() {</code>

<code> </code><code>###经判断'应该&可以'切换时执行的动作</code>

<code> </code><code>echo</code> <code>-e </code><code>"`date "</code><code>+%F %H:%M:%S</code><code>"` -----set read_only = 0 on DB2-----"</code> <code>>> $change_log</code>

<code> </code><code>###解除read_only属性</code>

<code> </code><code>mysql_con -e </code><code>"set global read_only = 0;"</code> <code>2>> $change_log</code>

<code> </code><code>echo</code> <code>"DB2 keepalived转为MASTER状态，线上数据库切换至DB2"</code><code>|</code><code>/bin/mailx</code> <code>-s </code><code>"DB2 keepalived change to MASTER"</code><code>\</code>

<code> </code><code>[email protected] 2>> $change_log</code>

<code> </code><code>echo</code> <code>-e </code><code>"@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@\n"</code> <code>>> $change_log</code>

<code>slave_info slave_status</code>

<code>if</code> <code>[ $Slave_IO_Running = Yes -a $Slave_SQL_Running = Yes ];</code><code>then</code>

<code> </code><code>i=0 </code><code>#一个计数器</code>

<code> </code><code>slave_info log_file pos</code>

<code> </code><code>###判断从master接收到的binlog是否全部在本地执行(这样仍无法完全确定从库已追上主库，因为无法完全保证io_thread没有延时(由网络传输问题导致的从库落后的概率很小)</code>

<code> </code><code>until</code> <code>[ $Read_Master_Log_Pos = $Exec_Master_Log_Pos ]</code>

<code> </code><code>if</code> <code>[ $i -lt 10 ];</code><code>then</code> <code>#将等待exec_pos追上read_pos的时间限制为10s</code>

<code> </code><code>echo</code> <code>-e </code><code>"`date "</code><code>+%F %H:%M:%S"` -----Master_Log_File=$Master_Log_File. Exec_Master_Log_Pos($Exec_Master_Log_Pos) is behind Read_Master_Lo</code>

<code>g_Pos($Read_Master_Log_Pos), wait......" >> $change_log </code><code>#输出消息到日志，等待exec_pos=read_pos</code>

<code> </code><code>sleep</code> <code>1</code>

<code> </code><code>slave_info log_file pos</code>

<code> </code><code>echo</code> <code>-e "The waits </code><code>time</code> <code>is </code><code>more</code> <code>than 10s,now force change. Master_Log_File=$Master_Log_File Read_Master_Log_Pos=$Read_Master_Log_Pos Exec_Ma</code>

<code>ster_Log_Pos=$Exec_Master_Log_Pos" >> $change_log</code>

<code> </code><code>action</code>

<code> </code><code>action </code>

<code> </code><code>echo</code> <code>-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_Po</code>

<code>s=$Exec_Master_Log_Pos" >> $change_log</code>

<code> </code><code>action</code>

DB2上手动切换回DB1的脚本change_to_backup.sh：

<code>###手动执行将主库切换回DB1的操作</code>

<code>echo</code> <code>-e </code><code>"`date "</code><code>+%F %H:%M:%S</code><code>"` -----change to BACKUP manually-----"</code> <code>>> </code><code>/etc/keepalived/logs/state_change</code><code>.log</code>

<code>echo</code> <code>-e </code><code>"`date "</code><code>+%F %H:%M:%S</code><code>"` -----set read_only = 1 on DB2-----"</code> <code>>> </code><code>/etc/keepalived/logs/state_change</code><code>.log</code>

<code>mysql_con -e </code><code>"set global read_only = 1;"</code> <code>2>> </code><code>/etc/keepalived/logs/state_change</code><code>.log</code>

<code>###kill掉当前客户端连接</code>

<code>echo</code> <code>-e </code><code>"`date "</code><code>+%F %H:%M:%S</code><code>"` -----kill current client thread-----"</code> <code>>> </code><code>/etc/keepalived/logs/state_change</code><code>.log</code>

<code>###这里其实是一个批量kill线程的小技巧</code>

<code>mysql_con -e </code><code>'select concat("kill ",id,";") from information_schema.PROCESSLIST where command="Query" or command="Execute" into outfile "/tmp/kill.sql";'</code>

<code>mysql_con -e </code><code>"source /tmp/kill.sql"</code> <code>2>> </code><code>/etc/keepalived/logs/state_change</code><code>.log</code>

<code>sleep</code> <code>2 </code><code>###给kill一个执行和缓冲时间</code>

<code>###确保DB1已经追上了,下面的repl为复制所用的账户，-h后跟DB1的内网IP</code>

<code>pos=`mysql -urepl -pxxxx -h192.168.1.x -e </code><code>"show slave status\G;"</code><code>|</code><code>grep</code> <code>"Master_Log_Pos"</code><code>|</code><code>awk</code> <code>'{printf ("%s",$NF "\t")}'</code><code>`</code>

<code>read_pos=`</code><code>echo</code> <code>$pos|</code><code>awk</code> <code>'{print $1}'</code><code>`</code>

<code>exec_pos=`</code><code>echo</code> <code>$pos|</code><code>awk</code> <code>'{print $2}'</code><code>`</code>

<code>until</code> <code>[ $read_pos = $exec_pos ]</code>

<code> </code><code>echo</code> <code>-e </code><code>"`date "</code><code>+%F %H:%M:%S</code><code>"` -----DB1 Exec_Master_Log_Pos($exec_pos) is behind Read_Master_Log_Pos($read_pos), wait......"</code> <code>>> </code><code>/etc/keepalived/logs/state_change</code><code>.log</code>

<code> </code><code>sleep</code> <code>1</code>

<code>###然后解除DB1的read_only属性</code>

<code>echo</code> <code>-e </code><code>"`date "</code><code>+%F %H:%M:%S</code><code>"` -----set read_only = 0 on DB1-----"</code> <code>>> </code><code>/etc/keepalived/logs/state_change</code><code>.log</code>

<code>ssh</code> <code>192.168.1.x </code><code>'mysql -uxxxx -pxxxx -e "set global read_only = 0;" && /etc/init.d/keepalived start'</code> <code>2>> </code><code>/etc/keepalived/logs/state_change</code><code>.log</code>

<code>###重启DB2的keepalived使VIP漂移到DB1</code>

<code>echo</code> <code>-e </code><code>"`date "</code><code>+%F %H:%M:%S</code><code>"` -----make VIP move to DB1-----"</code> <code>>> </code><code>/etc/keepalived/logs/state_change</code><code>.log</code>

<code>/sbin/service</code> <code>keepalived restart &>> </code><code>/etc/keepalived/logs/state_change</code><code>.log</code>

<code>echo</code> <code>"DB2 keepalived转为BACKUP状态，线上数据库切换至DB1"</code><code>|</code><code>/bin/mailx</code> <code>-s </code><code>"DB2 keepalived change to BACKUP"</code> <code>[email protected] 2>> </code><code>/etc/keepalived/logs/state_change</code><code>.log</code>

<code>echo</code> <code>-e </code><code>"@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@\n"</code> <code>>> </code><code>/etc/keepalived/logs/state_change</code><code>.log</code>

日志截图：

DB1 mysql服务故障：

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

DB2 一次切换过程：

DB2 执行脚本手动切回DB1：

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

本文转自kai404 51CTO博客，原文链接：http://blog.51cto.com/kaifly/1665729，如需转载请自行联系原作者

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

继续阅读

Shell编程——sort排序、uniq忽略重复、tr替换压缩删除、cut指定删除字段、正则表达式元字符sort 命令uniq 命令tr 命令cut 命令正则表达式

Linxu常用命令技巧汇总

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艰难安装LDAP,SSL认证

《Linux命令行与Shell脚本编程大全第2版.布卢姆》pdf

MySQL的4种隔离级别？出现问题

ACS基本配置-权限等级管理

XX系统实施过程问题总结

无组件上传图片到数据库中，最完整解决方案

【MySQL数据库】数据库索引事务1.索引2.事务

neo4j之cypher使用文档

NOSQL安全攻击

mybatis_入门程序Mybatis入门

mysql使用source命令导入.sql文件

登录plsql 报错 the account is locked --用户被锁

SequoiaDB巨杉数据库C++驱动概述