备案控制台
探索云世界
开发者社区 数据库 文章 正文

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

2017-11-02 2837
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL Serverless，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
简介：

前言：（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 配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
! 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脚本内容如下（主要的判断逻辑都在这里）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
#!/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配置：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
! 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脚本内容：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
#!/bin/bash
###当keepalived监测到本机转为MASTER状态时，执行该脚本
 
change_log= /etc/keepalived/logs/state_change .log
alias  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_log
 
slave_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" \
     lijiankai@dm.com 2>> $change_log
 
     echo  -e  "@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@\n"  >> $change_log
}
 
slave_info slave_status
if  [ $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_Lo
g_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_Ma
ster_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_Po
s=$Exec_Master_Log_Pos" >> $change_log
     action
fi

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
#!/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一个执行和缓冲时间
 
###确保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  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漂移到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
 
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


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



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




文章标签：
云数据库 RDS MySQL 版
关系型数据库
MySQL
数据库
Shell
关键词：
云数据库 RDS MySQL 版库
云数据库 RDS MySQL 版方案
云数据库 RDS MySQL 版高可用
云数据库 RDS MySQL 版高可用方案
云数据库 RDS MySQL 版双主
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境，其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
技术小胖子
目录
相关文章
你鞋带开了~
|
1月前
|
关系型数据库 MySQL API
Flink CDC产品常见问题之mysql整库同步到starrock时任务挂掉如何解决
Flink CDC（Change Data Capture）是一个基于Apache Flink的实时数据变更捕获库，用于实现数据库的实时同步和变更流的处理；在本汇总中，我们组织了关于Flink CDC产品在实践中用户经常提出的问题及其解答，目的是辅助用户更好地理解和应用这一技术，优化实时数据处理流程。
你鞋带开了~
59 1
vohelon
|
1月前
|
分布式计算 DataWorks 关系型数据库
DataWorks支持将ODPS表拆分并回流到MySQL的多个库和表中
【2月更文挑战第14天】DataWorks支持将ODPS表拆分并回流到MySQL的多个库和表中
vohelon
59 8
vohelon
|
2月前
|
分布式计算 DataWorks 关系型数据库
DataWorks支持将ODPS表拆分并回流到MySQL的多个库和表中
DataWorks支持将ODPS表拆分并回流到MySQL的多个库和表中
vohelon
30 4
雪碧有白泡泡
|
2月前
|
存储 关系型数据库 MySQL
Mysql高可用｜索引｜事务 | 调优
Mysql高可用｜索引｜事务 | 调优
雪碧有白泡泡
61 1
醉鱼Java
|
2月前
|
SQL 存储 关系型数据库
MySQL索引（二）索引优化方案有哪些
MySQL索引（二）索引优化方案有哪些
醉鱼Java
47 0
sunr0ng
|
7天前
|
SQL 关系型数据库 MySQL
用MySQL创建公司资料库表格
创建了员工、分支、客户及工作关系的数据库表格。员工与分支间有works_with表记录销售数据，外键关联并处理删除操作（set null或cascade）。插入数据后，通过SQL查询获取员工、客户信息，使用聚合函数、通配符、联合查询和JOIN操作。子查询用于复杂条件筛选。数据库设计确保了数据完整性和参照完整性。
sunr0ng
13 0
熬夜不加班
|
8天前
|
关系型数据库 MySQL
MySQL全局库表查询准确定位字段
information_schema.COLUMNS 详细信息查询
熬夜不加班
193 4
洛神灬殇
|
25天前
|
canal 消息中间件 关系型数据库
【分布式技术专题】「分布式技术架构」MySQL数据同步到Elasticsearch之N种方案解析，实现高效数据同步
【分布式技术专题】「分布式技术架构」MySQL数据同步到Elasticsearch之N种方案解析，实现高效数据同步
洛神灬殇
73 0
洛神灬殇
|
25天前
|
SQL 关系型数据库 MySQL
【MySQL技术之旅】（7）总结和盘点优化方案系列之常用SQL的优化
【MySQL技术之旅】（7）总结和盘点优化方案系列之常用SQL的优化
洛神灬殇
39 1
游客rbtl3kntj7oro
|
1月前
|
关系型数据库 MySQL API
Flink CDC产品常见问题之mysql整库同步到starrock时任务挂掉如何解决
Flink CDC产品常见问题之mysql整库同步到starrock时任务挂掉如何解决
游客rbtl3kntj7oro
35 1

热门文章

最新文章

  • 1
    超1/3中国500强企业都在用的「汇联易」，为什么选用阿里云RDS？
  • 2
    MySQL全局库表查询准确定位字段
  • 3
    MySQL学习（三）
  • 4
    对线面试官 - 如何理解MySQL的索引覆盖和索引下推
  • 5
    在Python Web开发过程中：数据库与缓存，MySQL和NoSQL数据库的主要差异是什么？
  • 6
    安装Docker&镜像容器操作&使用Docker安装部署MySQL，Redis，RabbitMQ，Nacos，Seata，Minio
  • 7
    轻松入门MySQL：深入探究MySQL的ER模型，数据库设计的利器与挑战（22）
  • 8
    【MySQL技术专题】「问题实战系列」深入探索和分析MySQL数据库的数据备份和恢复实战开发指南（8.0版本升级篇）
  • 9
    MySQL数据库，可以使用二进制日志（binary log）进行时间点恢复
  • 10
    MySQL学习（五）
  • 1
    【MySQL实战笔记】07 | 行锁功过：怎么减少行锁对性能的影响？-02 死锁和死锁检测
    13
  • 2
    数据库开发之mysql前言以及详细解析
    9
  • 3
    Python与MySQL数据库交互：面试实战
    25
  • 4
    【MySQL面试题pro版-12】
    13
  • 5
    【MySQL面试题pro版-10】
    13
  • 6
    【MySQL面试题pro版-9】
    13
  • 7
    【MySQL面试题pro版-8】
    11
  • 8
    【MySQL面试题pro版-7】
    17
  • 9
    【MySQL面试题pro版-6】
    16
  • 10
    MySQL面试题系列-16
    16

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • 搭建电商项目架构连接MySQL
  • 搭建4层电商项目架构，实战连接MySQL
  • PolarDB MySQL引擎重磅功能及产品能力盛大发布

    • 相关实验场景

    更多
  • 测试场景（RDS无优惠）
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • 使用Python完成RDS数据开发及分析
  • Excel文件转存到RDS数据库
  • 使用CloudLens采集RDS日志并进行审计分析

    • 推荐镜像

    更多
  • mysql
  • mariadb
  • postgresql
    • 下一篇
    部署LAMP环境（Alibaba Cloud Linux 3）