千家信息网

请输入关键字词

热门搜索排行

最新搜索排行

导航： 首页 > 数据库 >

MySQL高可用之keepalived方案的示例分析

发表于：2025-02-21 作者：千家信息网编辑
千家信息网最后更新 2025年02月21日，这篇文章主要为大家展示了"MySQL高可用之keepalived方案的示例分析"，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下"MySQL高可用之keepal
千家信息网最后更新 2025年02月21日MySQL高可用之keepalived方案的示例分析

这篇文章主要为大家展示了"MySQL高可用之keepalived方案的示例分析"，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下"MySQL高可用之keepalived方案的示例分析"这篇文章吧。



实验环境
mysql master : 192.168.111.52
mysql slave : 192.168.111.53
keepalived vip : 192.168.111.60
、搭建过程
1. mysql双主的构建
① 互相 change master 即可，此处省略该过程，着重讲下keepalived

2. keepalived相关
① yum -y install keepalived 安装keepalived
② root@192.168.111.52:~# keepalived -v
Keepalived v1.2.7 (02/21,2013)

③ 编辑 /etc/keepalived/keepalived.conf

点击(此处)折叠或打开

  1. root@192.168.111.52:keepalived# cat keepalived.conf

  2. ! Configuration File for keepalived


  4. global_defs {##全局配置

  5. notification_email {

  6. horand_gc@163.com #####邮件接收者

  7. }

  8. notification_email_from dba@163.com #####邮件发送者

  9. smtp_server smtp.163.com #####SMTP服务器

  10. smtp_connect_timeout 30

  11. router_id haMySQL #####routerID 同一组keepalived设置为相同

  12. }


  14. vrrp_script chk_mysql {##健康检测相关配置

  15. script "/etc/keepalived/chk_mysql.sh" #####设置脚本 或者直命令 返回 0 则表示成功 >0 则表示失败， 详情可以后面的脚本内容

  16. interval 1 #####检测间隔

  17. weight 2 #####检测返回失败之后优先级会减少 2 (如果 master优先级100 ， slave 优先级 99 ,master检测失败则优先级为100-2 < 99 ，则slave会提升为主)

  18. }


  20. vrrp_instance VI_1 {

  21. state MASTER ##### 设为master

  22. interface eth2 ##### 网卡设定

  23. virtual_router_id 51 ##### 针对该 instance的虚拟id , 同一组 instance设置相同

  24. priority 100 ##### 优先级设定

  25. advert_int 1 ##### 检测时间间隔

  26. authentication {

  27. auth_type PASS ##### 同一组instance之间的认证方式为 PASS ,pass 为7777 ，必须相同(防止 有用户恶意伪造 vrrp)

  28. auth_pass 7777

  29. }

  30. virtual_ipaddress {

  31. 192.168.111.60 ##### 设置虚拟ip ,可以设置多个

  32. }


  34. track_script {

  35. chk_mysql ##### 表示该instance 使用chk_mysql进行相关检测

  36. }

  37. ##### 以下配置 在该实例 转换为 master,slave，或者出错的时候执行的脚本(可以设置邮件通知，或者处理一些其他问题)

  38. # notify_master "/etc/keepalived/change_master.sh"

  39. # notify_slave "/etc/keepalived/change_slave.sh"

  40. # notify_fault "/etc/keepalived/change_fault.sh"

  41. }


  43. root@192.168.111.53:keepalived# cat keepalived.conf

  44. ! Configuration File for keepalived


  46. global_defs {

  47. notification_email {

  48. horand_gc@163.com

  49. }

  50. notification_email_from dba@163.com

  51. smtp_server smtp.163.com

  52. smtp_connect_timeout 30

  53. router_id haMySQL

  54. }


  56. vrrp_script chk_mysql {

  57. script "/etc/keepalived/chk_mysql.sh"

  58. interval 1

  59. weight 2

  60. }


  62. vrrp_instance VI_1 {

  63. state BACKUP ##### 该主机作为备机 BACKUP

  64. interface eth2

  65. virtual_router_id 51

  66. priority 99 ##### 优先级设置 小于 master

  67. advert_int 1

  68. authentication {

  69. auth_type PASS

  70. auth_pass 7777

  71. }

  72. virtual_ipaddress {

  73. 192.168.111.60

  74. }


  76. track_script {

  77. chk_mysql

  78. }

  79. }


  81. root@192.168.111.52:keepalived# cat chk_mysql.sh

  82. #!/bin/bash


  84. num=`ps -ef |grep mysqld | grep -v grep | wc -l` ##### 查看mysqld进程数量 ， 正常情况有一个root起的mysqld_safe守护进程，还有一个属于mysql用户的mysqld进程

  85. [[ $num -eq 2 ]] && exit 0 || exit 1

3. 故障模拟
① 启动 keepalived

点击(此处)折叠或打开

  1. root@192.168.111.52:keepalived# tail /var/log/messages

  2. Apr 29 13:45:24 localhost Keepalived_healthcheckers[24184]: Registering Kernel netlink command channel

  3. Apr 29 13:45:24 localhost Keepalived_healthcheckers[24184]: Opening file '/etc/keepalived/keepalived.conf'.

  4. Apr 29 13:45:24 localhost Keepalived_healthcheckers[24184]: Configuration is using : 7417 Bytes

  5. Apr 29 13:45:24 localhost Keepalived_vrrp[24185]: Opening file '/etc/keepalived/keepalived.conf'.

  6. Apr 29 13:45:24 localhost Keepalived_vrrp[24185]: Configuration is using : 65552 Bytes

  7. Apr 29 13:45:24 localhost Keepalived_vrrp[24185]: Using LinkWatch kernel netlink reflector...

  8. Apr 29 13:45:24 localhost Keepalived_healthcheckers[24184]: Using LinkWatch kernel netlink reflector...

  9. Apr 29 13:45:25 localhost Keepalived_vrrp[24185]: VRRP_Script(chk_mysql) succeeded

  10. Apr 29 13:45:25 localhost Keepalived_vrrp[24185]: VRRP_Instance(VI_1) Transition to MASTER STATE

  11. Apr 29 13:45:26 localhost Keepalived_vrrp[24185]: VRRP_Instance(VI_1) Entering MASTER STATE


  13. root@192.168.111.53:keepalived# tailf /var/log/messages

  14. Apr 29 13:49:27 localhost Keepalived_healthcheckers[32569]: Registering Kernel netlink reflector

  15. Apr 29 13:49:27 localhost Keepalived_healthcheckers[32569]: Registering Kernel netlink command channel

  16. Apr 29 13:49:27 localhost Keepalived_vrrp[32570]: Opening file '/etc/keepalived/keepalived.conf'.

  17. Apr 29 13:49:27 localhost Keepalived_vrrp[32570]: Configuration is using : 65550 Bytes

  18. Apr 29 13:49:27 localhost Keepalived_vrrp[32570]: Using LinkWatch kernel netlink reflector...

  19. Apr 29 13:49:27 localhost Keepalived_healthcheckers[32569]: Opening file '/etc/keepalived/keepalived.conf'.

  20. Apr 29 13:49:27 localhost Keepalived_healthcheckers[32569]: Configuration is using : 7415 Bytes

  21. Apr 29 13:49:27 localhost Keepalived_vrrp[32570]: VRRP_Instance(VI_1) Entering BACKUP STATE

  22. Apr 29 13:49:27 localhost Keepalived_healthcheckers[32569]: Using LinkWatch kernel netlink reflector...

  23. Apr 29 13:49:27 localhost Keepalived_vrrp[32570]: VRRP_Script(chk_mysql) succeeded

ip a 可以查看到 vip 192.168.111.60 在 192.168.111.52(master)上

② 关闭 111.52上面的mysql

点击(此处)折叠或打开

  1. root@192.168.111.52:keepalived# mysqladmin shutdown

  2. root@192.168.111.52:keepalived# tailf /var/log/messages

  3. Apr 29 14:19:30 localhost Keepalived_vrrp[24862]: VRRP_Script(chk_mysql) failed

  4. Apr 29 14:19:32 localhost Keepalived_vrrp[24862]: VRRP_Instance(VI_1) Received higher prio advert

  5. Apr 29 14:19:32 localhost Keepalived_vrrp[24862]: VRRP_Instance(VI_1) Entering BACKUP STATE


  7. root@192.168.111.53:keepalived# tailf /var/log/messages

  8. Apr 29 14:19:55 localhost Keepalived_vrrp[566]: VRRP_Instance(VI_1) forcing a new MASTER election

  9. Apr 29 14:19:56 localhost Keepalived_vrrp[566]: VRRP_Instance(VI_1) Transition to MASTER STATE

  10. Apr 29 14:19:57 localhost Keepalived_vrrp[566]: VRRP_Instance(VI_1) Entering MASTER STATE

ip a 可以查看到 vip 192.168.111.60 在 192.168.111.53(master)上

③ 启动111.52上面的mysql

点击(此处)折叠或打开

  1. root@192.168.111.52:keepalived# mysqld_safe & ; tailf /var/log/messages

  2. Apr 29 14:24:21 localhost Keepalived_vrrp[24862]: VRRP_Script(chk_mysql) succeeded

  3. Apr 29 14:24:22 localhost Keepalived_vrrp[24862]: VRRP_Instance(VI_1) forcing a new MASTER election

  4. Apr 29 14:24:23 localhost Keepalived_vrrp[24862]: VRRP_Instance(VI_1) Transition to MASTER STATE

  5. Apr 29 14:24:24 localhost Keepalived_vrrp[24862]: VRRP_Instance(VI_1) Entering MASTER STATE


  7. root@192.168.111.53:keepalived# tailf /var/log/messages

  8. Apr 29 14:24:45 localhost Keepalived_vrrp[566]: VRRP_Instance(VI_1) Received higher prio advert

  9. Apr 29 14:24:45 localhost Keepalived_vrrp[566]: VRRP_Instance(VI_1) Entering BACKUP STATE

ip a 可以查看到 vip 192.168.111.60 在 192.168.111.52(master)上，也就是说111.52会持续通过track_script的脚本检查 ，若成功的话会恢复原来的优先级100 ，便把vip抢过来了(若不希望优先级高的直接上来直接抢占vip的话 需要再instance 里面配置 nopreempt ,backup无需设置)

虚拟server
以上是实验是通过 vrrp_script以及trace_script 实现优先级变换来实现故障转移的，现在看下 通过虚拟server怎么实现mysql的高可用
① 配置

点击(此处)折叠或打开

  1. root@192.168.111.52:keepalived# cat /etc/keepalived/keepalived.conf

  2. ! Configuration File for keepalived


  4. global_defs {

  5. notification_email {

  6. tab@taomee.com

  7. }

  8. notification_email_from dba@taomee.com

  9. smtp_server smtp.shidc.taomee.com

  10. smtp_connect_timeout 30

  11. router_id haMySQL

  12. }


  14. vrrp_script chk_mysql {

  15. script "/etc/keepalived/chk_mysql.sh"

  16. interval 1

  17. weight 2

  18. }


  20. vrrp_instance VI_1 {

  21. state MASTER

  22. interface eth2

  23. virtual_router_id 51

  24. priority 100

  25. nopreempt

  26. advert_int 1

  27. authentication {

  28. auth_type PASS

  29. auth_pass 7777

  30. }

  31. virtual_ipaddress {

  32. 192.168.111.60

  33. }


  35. # track_script {

  36. # chk_mysql

  37. # }

  38. }


  40. virtual_server 192.168.111.60 3306 {

  41. delay_loop 6

  42. persistence_timeout 300

  43. protocol TCP



  46. real_server 192.168.111.52 3306 {

  47. weight 1

  48. notify_down /etc/keepalived/kill_self.sh

  49. TCP_CHECK {

  50. tcp_port 3306

  51. connect_timeout 3

  52. }

  53. }


  55. }


  57. root@192.168.111.53:keepalived# cat /etc/keepalived/keepalived.conf

  58. ! Configuration File for keepalived


  60. global_defs {

  61. notification_email {

  62. tab@taomee.com

  63. }

  64. notification_email_from dba@taomee.com

  65. smtp_server smtp.shidc.taomee.com

  66. smtp_connect_timeout 30

  67. router_id haMySQL

  68. }


  70. vrrp_script chk_mysql {

  71. script "/etc/keepalived/chk_mysql.sh"

  72. interval 1

  73. weight 2

  74. }


  76. vrrp_instance VI_1 {

  77. state BACKUP

  78. interface eth2

  79. virtual_router_id 51

  80. priority 99

  81. advert_int 1

  82. authentication {

  83. auth_type PASS

  84. auth_pass 7777

  85. }

  86. virtual_ipaddress {

  87. 192.168.111.60

  88. }


  90. # track_script { #########这里先注释掉 通过追踪脚本的检查

  91. # chk_mysql

  92. # }

  93. }


  95. virtual_server 192.168.111.60 3306 {

  96. delay_loop 6

  97. persistence_timeout 300

  98. protocol TCP



  101. real_server 192.168.111.53 3306 {### 真实 服务

  102. weight 1 #### 权重，用来多真实服务 均衡使用

  103. notify_down /etc/keepalived/kill_self.sh ####在检查该服务不可用时执行该脚本(用来杀死 keepalived 实现 vip 飘逸)

  104. TCP_CHECK {

  105. tcp_port 3306 #### 检查端口 继承 real_server 192.168.111.53 3306 {### 真实 服务

  106. connect_timeout 3 #### tcp超时时间

  107. }

  108. }


  110. }


  112. root@192.168.111.53:keepalived# cat /etc/keepalived/kill_self.sh

  113. #!/bin/bash

  114. killall keepalived



②启动keepalived

点击(此处)折叠或打开

  1. root@192.168.111.52:keepalived# keepalived

  2. root@192.168.111.52:keepalived# tailf /var/log/messages

  3. Apr 29 14:48:22 localhost Keepalived_vrrp[20482]: Opening file '/etc/keepalived/keepalived.conf'.

  4. Apr 29 14:48:22 localhost Keepalived_vrrp[20482]: Configuration is using : 64590 Bytes

  5. Apr 29 14:48:22 localhost Keepalived_vrrp[20482]: Using LinkWatch kernel netlink reflector...

  6. Apr 29 14:48:22 localhost Keepalived_healthcheckers[20481]: IPVS: Scheduler not found

  7. Apr 29 14:48:22 localhost kernel: IPVS: Scheduler module ip_vs_ not found

  8. Apr 29 14:48:22 localhost Keepalived_healthcheckers[20481]: IPVS: Service not defined

  9. Apr 29 14:48:22 localhost Keepalived_healthcheckers[20481]: Using LinkWatch kernel netlink reflector...

  10. Apr 29 14:48:22 localhost Keepalived_healthcheckers[20481]: Activating healthchecker for service [192.168.111.52]:3306

  11. Apr 29 14:48:23 localhost Keepalived_vrrp[20482]: VRRP_Instance(VI_1) Transition to MASTER STATE

  12. Apr 29 14:48:24 localhost Keepalived_vrrp[20482]: VRRP_Instance(VI_1) Entering MASTER STATE


  14. root@192.168.111.53:keepalived# keepalived

  15. root@192.168.111.53:keepalived# tailf /var/log/messages

  16. Apr 29 14:48:51 localhost Keepalived_healthcheckers[25092]: Configuration is using : 11673 Bytes

  17. Apr 29 14:48:51 localhost Keepalived_vrrp[25093]: Opening file '/etc/keepalived/keepalived.conf'.

  18. Apr 29 14:48:51 localhost Keepalived_vrrp[25093]: Configuration is using : 64568 Bytes

  19. Apr 29 14:48:51 localhost Keepalived_vrrp[25093]: Using LinkWatch kernel netlink reflector...

  20. Apr 29 14:48:51 localhost Keepalived_vrrp[25093]: VRRP_Instance(VI_1) Entering BACKUP STATE

  21. Apr 29 14:48:51 localhost kernel: IPVS: Scheduler module ip_vs_ not found

  22. Apr 29 14:48:51 localhost Keepalived_healthcheckers[25092]: IPVS: Scheduler not found

  23. Apr 29 14:48:51 localhost Keepalived_healthcheckers[25092]: IPVS: Service not defined

  24. Apr 29 14:48:51 localhost Keepalived_healthcheckers[25092]: Using LinkWatch kernel netlink reflector...

  25. Apr 29 14:48:51 localhost Keepalived_healthcheckers[25092]: Activating healthchecker for service [192.168.111.53]:3306

此时 ip a 命令可以查看虚拟ip 111.60 在111.52(master) 上

③关闭 111.52上的mysql

点击(此处)折叠或打开

  1. root@192.168.111.52:keepalived# mysqladmin shutdown

  2. 2017-04-29T07:07:38.121123Z mysqld_safe mysqld from pid file /opt/mysql/mysqld.pid ended

  3. [1]+ Done mysqld_safe

  4. root@192.168.111.52:keepalived# tailf /var/log/messages

  5. Apr 29 15:07:31 localhost Keepalived_healthcheckers[23405]: TCP connection to [192.168.111.52]:3306 failed !!!

  6. Apr 29 15:07:31 localhost Keepalived_healthcheckers[23405]: Removing service [192.168.111.52]:3306 from VS [192.168.111.60]:3306

  7. Apr 29 15:07:31 localhost Keepalived_healthcheckers[23405]: IPVS: Service not defined

  8. Apr 29 15:07:31 localhost Keepalived_healthcheckers[23405]: Executing [/etc/keepalived/kill_self.sh] for service [192.168.111.52]:3306 in VS [192.168.111.60]:3306

  9. Apr 29 15:07:31 localhost Keepalived_healthcheckers[23405]: Lost quorum 1-0=1 > 0 for VS [192.168.111.60]:3306

  10. Apr 29 15:07:31 localhost Keepalived_healthcheckers[23405]: Remote SMTP server [0.0.0.0]:25 connected.

  11. Apr 29 15:07:31 localhost Keepalived[23404]: Stopping Keepalived v1.2.7 (02/21,2013)

  12. Apr 29 15:07:31 localhost Keepalived_healthcheckers[23405]: IPVS: No such service

  13. Apr 29 15:07:31 localhost Keepalived_vrrp[23406]: VRRP_Instance(VI_1) sending 0 priority


  15. root@192.168.111.53:keepalived# tailf /var/log/messages

  16. Apr 29 15:07:32 localhost Keepalived_vrrp[26815]: VRRP_Instance(VI_1) Transition to MASTER STATE

  17. Apr 29 15:07:33 localhost Keepalived_vrrp[26815]: VRRP_Instance(VI_1) Entering MASTER STATE

此时 ip a 命令可以看到虚拟ip 111.60在111.53(新master)上

以上是"MySQL高可用之keepalived方案的示例分析"这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注行业资讯频道！

很赞哦！
优先级 脚本 检测 服务 配置 内容 检查 方案 示例 分析 相同 命令 篇文章 进程 邮件 面的 成功 故障 时间 用户 数据库的安全要保护哪些东西 数据库安全各自的含义是什么 生产安全数据库录入 数据库的安全性及管理 数据库安全策略包含哪些 海淀数据库安全审计系统 建立农村房屋安全信息数据库 易用的数据库客户端支持安全管理 连接数据库失败ssl安全错误 数据库的锁怎样保障安全 黑龙江大兴服务器云空间 漳州鸿平网络技术 方舟国际服手游服务器管理 数据库的唯一键是哪个单词 百链数据库石河子大学 软件开发培训学时 软件开发三个人和两个人区别 源代码怎么安装到数据库 农行软件开发中心老总 地方网络技术培训流程 阿里云服务器换ip 区块链实体服务器 为确保全国两会网络安全稳定 戴尔R540服务器做raid 美国40岁网络技术员 英语作文新闻报道关于网络安全 胡一天网络安全大赛 有关网络安全的班会简讯 安宁综合软件开发报价表 golden数据库 宽带管理服务器怎么办 企业云服务器如何设置镜像 光猫dhcp服务器 合肥工业学校网络技术学习内容 网络安全试点项目 网络安全风险来源及防范对策 跑三维图怎么选择云服务器配置 软件开发高级架构师 加强网络安全管理杜绝管理盲区 根据几个字段查询重复数据库

相关文章

0