MySQL高可用之keepalived方案

一、keepalived 介绍
    
     浅析keepalived vip漂移原理与VRRP协议

二、实验环境
    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
   
3. 
   
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. }
    
13. 
    
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. }
    
19. 
    
20. vrrp_instance VI_1 {
    
21.     state MASTER                          ##### 设为master
    
22.     interface eth1                        ##### 网卡设定
    
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.     }
    
33.     
    
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. }
    
42. 
    
43. root@192.168.111.53:keepalived# cat keepalived.conf
    
44. ! Configuration File for keepalived
    
45. 
    
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. }
    
55. 
    
56. vrrp_script chk_mysql {
    
57.         script "/etc/keepalived/chk_mysql.sh"
    
58.         interval 1
    
59.         weight 2
    
60. }
    
61. 
    
62. vrrp_instance VI_1 {
    
63.     state BACKUP                            ##### 该主机作为备机 BACKUP 
    
64.     interface eth1
    
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.     }
    
75.     
    
76.     track_script {
    
77.         chk_mysql
    
78.     }
    
79. }
    
80. 
    
81. root@192.168.111.52:keepalived# cat chk_mysql.sh
    
82. #!/bin/bash
    
83. 
    
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
    
12. 
    
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
   
6. 
   
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
   
6. 
   
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 里面配置 nopreem pt ,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
   
3. 
   
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. }
    
13. 
    
14. vrrp_script chk_mysql {
    
15.     script "/etc/keepalived/chk_mysql.sh"
    
16.     interval 1
    
17.     weight 2
    
18. }
    
19. 
    
20. vrrp_instance VI_1 {
    
21.     state MASTER
    
22.     interface eth1
    
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.     }
    
34.     
    
35. # track_script {
    
36. #    chk_mysql
    
37. # }
    
38. }
    
39. 
    
40. virtual_server 192.168.111.60 3306 {
    
41.     delay_loop 6
    
42.     persistence_timeout 300
    
43.     protocol TCP
    
44. 
    
45. 
    
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.     }
    
54. 
    
55. }
    
56. 
    
57. root@192.168.111.53:keepalived# cat /etc/keepalived/keepalived.conf
    
58. ! Configuration File for keepalived
    
59. 
    
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. }
    
69. 
    
70. vrrp_script chk_mysql {
    
71.         script "/etc/keepalived/chk_mysql.sh"
    
72.         interval 1
    
73.         weight 2
    
74. }
    
75. 
    
76. vrrp_instance VI_1 {
    
77.     state BACKUP
    
78.     interface eth1
    
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.     }
    
89.     
    
90. # track_script {    #########这里先注释掉 通过追踪脚本的检查
    
91. # chk_mysql
    
92. # }
    
93. }
    
94. 
    
95. virtual_server 192.168.111.60 3306 {
    
96.     delay_loop 6
    
97.     persistence_timeout 300
    
98.     protocol TCP
    
99. 
    
100. 
     
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.     }
     
109. 
     
110. }
111. 
     
112. root@192.168.111.53:keepalived# cat /etc/keepalived/kill_self.sh
113. #!/bin/bash
114. killall keepalived
115. 
     

    ②启动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
    
13. 
    
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
    
14. 
    
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)上

以上

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31385648/viewspace-2138202/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31385648/viewspace-2138202/