MySQL高可用架构之Keepalived+主从架构部署

针对目前公司的数据库架构要做统计规划和调整，所以针对不同的业务环境要选择合适的架构就比较重要，同时作为运维人员又要考虑到维护的便捷性（不能做过多架构维护），最终停留在mha+vip/主从+vip/mycat集群/域名+主从这几种架构或组合，这里就分布对不同架构部署做下记录并对比起优劣，针对不同场景做出合适的选择。

本文主要针对主备自动切换，即VIP+主从方式，其中VIP通过keepalived控制。

一、环境描述

二、业务场景

1、优点

1)部署简单，管理方便。

2)可实现主备库VIP切换，对业务无感知。

2、缺点

1)存在脑裂风险

2)存在切换主备，状态无检测风险

3)跨VLAN不支持

4)看似部署简单，实则需要考虑的点非常多，如果考虑不完善建议慎用

3、业务场景

总而言之，此架构不推荐使用，使用时需谨慎。

1)双节点数据库服务器，仅仅为了实现主备VIP切换业务无感知

2)多个从节点，通过LVS，做负载均衡（现在不推荐这种架构实现负载均衡）

3)两节点的双向复制可以降低部分风险（来回切换VIP、脑裂等），单无法避免

三、数据库安装和主从部署

略

四、keepalived安装部署

1.keepalived下载

下载地址：http://www.keepalived.org/software/keepalived-1.4.4.tar.gz

2.keepalived安装

主库： [root@node1 tools]# tar -xzvf keepalived-1.4.4.tar.gz [root@node1 tools]# cd keepalived-1.4.4 [root@node1 keepalived-1.4.4]# ./configure  --prefix=/usr/local/keepalived [root@node1 keepalived-1.4.4]# make -j 4 [root@node1 keepalived-1.4.4]# make install [root@node1 keepalived-1.4.4]# cp -rp ./keepalived/etc/init.d/keepalived /etc/init.d/ [root@node1 keepalived-1.4.4]# chkconfig  --add keepalived [root@node1 keepalived-1.4.4]# mkdir /etc/keepalived [root@node2 keepalived-1.4.4]# cp -rp ./bin/keepalived  /usr/bin/ [root@node1 keepalived-1.4.4]# cp ./keepalived/etc/keepalived/keepalived.conf /etc/keepalived [root@node1 keepalived-1.4.4]# cp -rp /usr/local/keepalived/etc/sysconfig/keepalived  /etc/sysconfig/ [root@node1 keepalived-1.4.4]# service keepalived status keepalived is stopped 备库： 安装keepalived同上 ###备注 *** WARNING - this build will not support IPVS with IPv6. Please install libnl/libnl-3 dev libraries to support IPv6 with IPVS. ###

3.keepalived配置

主库配置：

[root@node1 keepalived-1.4.4]# cp -rp  /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf_bak [root@node1 keepalived-1.4.4]# >/etc/keepalived/keepalived.conf [root@node1 keepalived-1.4.4]# vi /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {   notification_email {     rangle@163.com   }   notification_email_from rangle@163.com   smtp_server 127.0.0.1   smtp_connect_timeout 30   router_id Node_Master }

vrrp_instance VI_1 {     state BACKUP          ##可配置master和backup模式，为了防止脑裂现象，主备均需要设置为backup模式，master模式会抢占VIP     interface eth1     virtual_router_id 43  ##VRRP组名，两个节点的设置必须一样，以指明各个节点属于同一VRRP组     priority 100     nopreempt            ##配合backup,防止切换后，主库服务恢复正常后，IP漂移过来     advert_int 1          ###组播信息发送间隔，两个节点设置必须一样     authentication {      ###设置验证信息，两个节点必须一致         auth_type PASS         auth_pass 1111     }     virtual_ipaddress {  ###指定虚拟IP, 两个节点设置必须一样         192.168.1.27 label eth1:1     } } virtual_server 192.168.1.27 3306 {     delay_loop 6     lb_algo wrr          #lvs调度算法rr|wrr|lc|wlc|lblc|sh|dh     lb_kind DR          #负载均衡转发规则NAT|DR|RUN     persistence_timeout 50     protocol TCP

    real_server 192.168.1.23 3306 {       weight 1       notify_down /etc/keepalived/mysql.sh       TCP_CHECK {       connect_timeout 10       nb_get_retry 3       delay_before_retry 3       connect_port 3306         }     } }

备库配置：

[root@node2 keepalived-1.4.4]# cp -rp  /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf_bak [root@node2 keepalived-1.4.4]# >/etc/keepalived/keepalived.conf [root@node2 keepalived-1.4.4]# vi /etc/keepalived/keepalived.conf

! Configuration File for keepalived   global_defs {       notification_email {       rangle@163.com       }       notification_email_from rangle@163.com       smtp_server 127.0.0.1       smtp_connect_timeout 30       router_id Node_backup     }  vrrp_instance VI_1 {       state BACKUP       interface eth1       virtual_router_id 43       priority 90                  ##主优先级高于备，主设置100，备可设置90,（1-254之间）       advert_int 1       authentication {       auth_type PASS       auth_pass 1111       }       virtual_ipaddress {       192.168.1.27 label eth1:1       }       }  virtual_server 192.168.1.27 3306 {       delay_loop 2                ##每隔2秒检查真实服务器状态       lb_algo wrr                  ##LVS算法，如果仅做高可用可不设       lb_kind DR                  ##LVS模式，如果仅做高可用可不设       persistence_timeout 60       protocol TCP

      real_server 192.168.1.24 3306{       weight 3       notify_down /etc/keepalived/mysql.sh    ##如果mysql服务宕掉，执行的脚本       echo '3' >  /etc/keepalived/t.log       TCP_CHECK {       connect_timeout 10       nb_get_retry 3          delay_before_retry 3       connect_port 3306       }       } }

mysql.sh配置内容：

#!/bin/bash

run_status=`service keepalived status|grep running|wc -l` pro_status=`ps -ef |grep keepalived|grep -v grep |wc -l`

service keepalived stop

if [ {run_status} != 0 ] || [ {pro_status} != 0 ]then  pkill keepalivedfi

五、keepalived优化

1、keepalived+mysql风险点

1）脑裂风险

现象：

如果由于主备内部网络通信（vrrp组播）故障（网络波动、防火墙等）导致VIP切换，但实际主备服务均正常，此时VIP会存在两个节点上，这个就会导致主备数据同时写入导致数据不一致。

优化方案：

****主库节点增加脑裂检查脚本，通过本机增加网关链路的检查，增加仲裁节点，判断是否本机对外的网络出现问题，此时在配合VRRP组播，如果网络存在问题则直接关闭keepalived和mysql服务，然后发送告警邮件、短信。

2）主从同步漂移风险

现象：

VIP漂移不会检查从库状态，即无论从库数据是否同步，VIP都将完成漂移，数据不一致风险较大。

优化方案：

****在从节点增加主从状态检查脚本，判断主从同步进程是否正常，同时判断是否有数据延迟，如果有延迟或同步错误则不能完成切换，直接关闭keepalived服务

2、优化后配置如下

具体优化配置和脚本如下：

主库配置：

keepalived.conf

! Configuration File for keepalived

global_defs {   notification_email {     abc@163.com   }   notification_email_from root@localhost   smtp_server root@localhost   smtp_connect_timeout 30   router_id node1 }

vrrp_script check_local {     script "/etc/keepalived/check_gateway.sh"     interval 5     fall    4     rise    4     weight 3 }

vrrp_instance VI_1 {     state BACKUP     interface eth1     virtual_router_id 43     priority  100     nopreempt     advert_int 1     authentication {         auth_type PASS         auth_pass 1111     }

    virtual_ipaddress {         192.168.1.27 label eth1:1     }     track_script {         check_local     }

} virtual_server 192.168.1.27 3306 {     delay_loop 6     lb_algo wrr          #lvs调度算法rr|wrr|lc|wlc|lblc|sh|dh     lb_kind DR          #负载均衡转发规则NAT|DR|RUN     persistence_timeout 50     protocol TCP

    real_server 192.168.1.23 3306 {       weight 3       notify_down /etc/keepalived/mysql.sh       TCP_CHECK {       connect_timeout 10       nb_get_retry 3       delay_before_retry 3       connect_port 3306         }     } }

备库配置：

keepalived.conf

! Configuration File for keepalived global_defs {       notification_email {       abc@163.com       }       notification_email_from root@localhost       smtp_server root@localhost       smtp_connect_timeout 30       router_id node2     }

vrrp_script check_local {     script "/etc/keepalived/check_gateway.sh "     interval 5     fall    4     rise    4     weight 3 }

vrrp_instance VI_1 {       state BACKUP       interface eth1       virtual_router_id 43       priority 90       advert_int 1       authentication {       auth_type PASS       auth_pass 1111       }

      virtual_ipaddress {       192.168.1.27 label eth1:1       }     track_script {         check_local     }

      }

 virtual_server 192.168.1.27 3306 {       delay_loop 2       lb_algo wrr       lb_kind DR       persistence_timeout 60       protocol TCP

      real_server 192.168.1.24 3306{       weight 3       notify_down /etc/keepalived/mysql.sh       echo '3' >  /etc/keepalived/t.log       TCP_CHECK {       connect_timeout 10       nb_get_retry 3          delay_before_retry 3       connect_port 3306       }       } }

脑裂检查脚本：

/etc/keepalived/check_gateway.sh

#!/bin/sh VIP=192.168.1.27 GATEWAY=192.168.1.254

/sbin/arping -I eth1 -c 5 -s $VIP $GATEWAY  &>/dev/null

gateway_status=$? keepalived_status=`ps -ef |grep keepalived|grep -v grep |wc -l`

if [ ${gateway_status} != 0 ] && [ ${keepalived_status} = 0 ] then   service keepalived start elif [ ${gateway_status} != 0 ] && [ ${keepalived_status} != 0 ]   service keepalived stop fi

 数据库服务宕机转移脚本

/etc/keepalived/mysql.sh

#!/bin/bash

run_status=`service keepalived status|grep running|wc -l` pro_status=`ps -ef |grep keepalived|grep -v grep |wc -l`

service keepalived stop

if [ ${run_status} != 0 ] || [ ${pro_status} != 0 ] then   pkill keepalived fi

 从库状态检查脚本（在备库做crontab定时任务*/1 * * * * sh /etc/keepalived/check_slave.sh >/dev/null 2>&1）

#!/bin/bash

VIP="192.168.1.27"

vip_status=`ip add |egrep "${VIP}"|wc -l` keepalived_status=`ps -ef |grep keepalived|grep -v grep|wc -l`

slave_status=`mysql -uroot -e "show slave status \G"|egrep  "Slave.*Running|Seconds_Behind_Master|Last_Errno"|grep -v "Slave_SQL_Running_State"|awk -F ':' '{printf("%s",$NF)}'` io_status=`echo "${slave_status}"|awk '{print $1}'|grep "Yes"|wc -l` sql_status=`echo "${slave_status}"|awk '{print $2}'|grep "Yes"|wc -l` sync_status=`echo "${slave_status}"|awk '{print $3}'` sync_time=`echo "${slave_status}"|awk '{print $4}'`

if [ ${io_status} -ne 1 ] || [ ${sql_status} -ne 1 ] || [ ${sync_status} -ne 0 ] then     if [ ${vip_status} -eq 0 ] && [ ${keepalived_status} -ne 0 ];then         service keepalived stop     fi elif [ ${io_status} -eq 1 ] && [ ${sql_status} -eq 1 ] && [ ${sync_status} -eq 0 ] then     if [ ${sync_time} -ge 120 ];then         if [ ${vip_status} -eq 0 ] && [ ${keepalived_status} -ne 0 ];then           service keepalived stop         fi     elif [ ${sync_time} -eq 0 ];then         if [ ${vip_status} -eq 0 ] && [ ${keepalived_status} -eq 0 ];then           service keepalived start         fi     fi fi

3、防火墙端口开放

VRRP是用IP多播的方式（默认多播地址224.0.0.18）实现高可用对之间通信的，如果开启防火墙记得开放这个vrrp协议对应的策略

iptables -A INPUT -p vrrp -d 224.0.0.18  -j ACCEPT

iptables -A INPUT -p vrrp  -j ACCEPT

六、keepalived故障转移测试

1、主数据库实例宕机

当前主数据库主机状态：

[root@node1 keepalived]# service mysqld status MySQL (Percona Server) running (22983)                    [  OK  ] [root@node1 keepalived]# service keepalived status keepalived (pid  14092) is running... [root@node1 keepalived]# ip add |grep inet     inet 127.0.0.1/8 scope host lo     inet6 ::1/128 scope host     inet 192.168.1.23/24 brd 192.168.1.255 scope global eth1     inet 192.168.1.27/32 scope global eth1:1     inet6 fe80::250:56ff:fe8d:496/64 scope link

当前从数据库主机状态：

[root@node2 keepalived]# service mysqld status MySQL (Percona Server) running (14146)                    [  OK  ] [root@node2 keepalived]# service keepalived status keepalived (pid  12207) is running... [root@node2 keepalived]# ip add |grep inet     inet 127.0.0.1/8 scope host lo     inet6 ::1/128 scope host     inet 192.168.1.24/24 brd 192.168.1.255 scope global eth1     inet6 fe80::250:56ff:fe8d:3d1e/64 scope link

主库执行关闭mysql实例命令，数据库关闭后发现VIP已漂移

[root@node1 keepalived]# service mysqld stop Shutting down MySQL (Percona Server)...............        [  OK  ] [root@node1 keepalived]# service mysqld status    MySQL (Percona Server) is not running                      [FAILED] [root@node1 keepalived]# service keepalived status keepalived is stopped [root@node1 keepalived]# ip add |grep inet     inet 127.0.0.1/8 scope host lo     inet6 ::1/128 scope host     inet 192.168.1.23/24 brd 192.168.1.255 scope global eth1     inet6 fe80::250:56ff:fe8d:496/64 scope link

此时检查从库相关状态

[root@node2 keepalived]# service mysqld status MySQL (Percona Server) running (14146)                    [  OK  ] [root@node2 keepalived]# service keepalived status keepalived (pid  12207) is running... [root@node2 keepalived]# ip add |grep inet     inet 127.0.0.1/8 scope host lo     inet6 ::1/128 scope host     inet 192.168.1.24/24 brd 192.168.1.255 scope global eth1     inet 192.168.1.27/32 scope global eth1:1     inet6 fe80::250:56ff:fe8d:3d1e/64 scope link [root@node2 keepalived]# mysql -uroot -e "show slave status \G"|egrep  "Slave.*Running|Seconds_Behind_Master|Last_Errno"|grep -v "Slave_SQL_Running_State"             Slave_IO_Running: Connecting             Slave_SQL_Running: Yes                   Last_Errno: 0         Seconds_Behind_Master: NULL

2、主数据库服务器宕机

同上，实现检查主备状态后，在主库执行shutdown -h now的命令后，检查从库状态

3、主keepalived服务停止

同上

4、主服务器网卡故障

此时keepalived服务正常，mysqld服务正常，VRRP通信无法完成的情况下，主库会执行关闭keepalived服务并完成切换

5、模拟VRRP通信故障（脑裂）

在备库开启防火墙，并不开通vrrp允许，此时VIP会存在于两边的服务器上

[root@node2 keepalived]# service iptables start iptables: Applying firewall rules:                        [  OK  ]

主库服务器状态检查

[root@node1 keepalived]# ip add|grep inet     inet 127.0.0.1/8 scope host lo     inet6 ::1/128 scope host     inet 192.168.1.23/24 brd 192.168.1.255 scope global eth1     inet 192.168.1.27/32 scope global eth1:1     inet6 fe80::250:56ff:fe8d:496/64 scope link

备库服务器状态检查：

[root@node2 keepalived]# ip add|grep inet     inet 127.0.0.1/8 scope host lo     inet6 ::1/128 scope host     inet 192.168.1.24/24 brd 192.168.1.255 scope global eth1     inet 192.168.1.27/32 scope global eth1:1     inet6 fe80::250:56ff:fe8d:3d1e/64 scope link

如果关掉从库的防火墙，那么VIP会选择高优先级的服务器上（priority）

此现象如何避免？我们可以在check_gateway.sh脚本里添加上组播状态检查的命令，我这里就不做了，仅做了网关检查的脑裂避免（网络问题导致网关暂时不可达而产生的脑裂）

6、模拟主切换到备后，主服务启动后是否会回切（优先级抢占测试）

（本次测试接着测试1进行，首先恢复主库的实例进程和keepalived进程）

主库执行命令：

[root@node1 keepalived]# service mysqld start Starting MySQL (Percona Server)......                      [  OK  ] [root@node1 keepalived]# service keepalived start Starting keepalived:                                      [  OK  ] [root@node1 keepalived]# service mysqld status    MySQL (Percona Server) running (1141)                      [  OK  ] [root@node1 keepalived]# service keepalived status keepalived (pid  1230) is running... [root@node1 keepalived]# ip add |grep inet            inet 127.0.0.1/8 scope host lo     inet6 ::1/128 scope host     inet 192.168.1.23/24 brd 192.168.1.255 scope global eth1     inet6 fe80::250:56ff:fe8d:496/64 scope link

备库执行命令：

[root@node2 keepalived]# service keepalived status keepalived (pid  12207) is running... [root@node2 keepalived]# service mysqld status    MySQL (Percona Server) running (14146)                    [  OK  ] [root@node2 keepalived]# ip add |grep inet     inet 127.0.0.1/8 scope host lo     inet6 ::1/128 scope host     inet 192.168.1.24/24 brd 192.168.1.255 scope global eth1     inet 192.168.1.27/32 scope global eth1:1     inet6 fe80::250:56ff:fe8d:3d1e/64 scope link

此时发现即使主库的服务恢复正常也不会主动抢占从库的VIP，因为两边设置的都是backup模式，但此时如果备库的服务、实例等原因导致需要切换VIP而此时主库由具备切换的条件，那么依旧可以完成切换（此时切记一旦完成主备切换后，需要手动恢复新的主备状态保障数据一致性后才能进行二次切换）

7、模拟备库数据延迟120S以上切换

从库先停止io_thread

(root:localhost:Sun May 20 10:24:45 2018)[(none)]>stop slave io_thread; Query OK, 0 rows affected (0.00 sec)

主库做大量的数据变更操作

然后监控从库keepalived状态即可

8、模拟备库数据同步错误切换

从库状态：

[root@node2 keepalived]# mysql -uroot -e "show slave status \G"|egrep  "Slave.*Running|Seconds_Behind_Master|Last_Errno"|grep -v "Slave_SQL_Running_State"             Slave_IO_Running: Yes             Slave_SQL_Running: No                   Last_Errno: 1050         Seconds_Behind_Master: NULL [root@node2 keepalived]# service keepalived status keepalived is stopped [root@node2 keepalived]# ip add |grep inet     inet 127.0.0.1/8 scope host lo     inet6 ::1/128 scope host     inet 192.168.1.24/24 brd 192.168.1.255 scope global eth1     inet6 fe80::250:56ff:fe8d:3d1e/64 scope link

主库状态：

[root@node1 keepalived]# service mysqld status MySQL (Percona Server) running (1141)                      [  OK  ] [root@node1 keepalived]# service keepalived  status      keepalived (pid  1230) is running... [root@node1 keepalived]# ip add |grep inet     inet 127.0.0.1/8 scope host lo     inet6 ::1/128 scope host     inet 192.168.1.23/24 brd 192.168.1.255 scope global eth1     inet 192.168.1.27/32 scope global eth1:1     inet6 fe80::250:56ff:fe8d:496/64 scope link

此时如果主库相关服务宕机，是无法完成VIP切换，通过关闭mysqld服务测试

主库已剔除VIP [root@node1 keepalived]# service mysqld stop Shutting down MySQL (Percona Server)..............        [  OK  ] [root@node1 keepalived]# ip addr |grep inet     inet 127.0.0.1/8 scope host lo     inet6 ::1/128 scope host     inet 192.168.1.23/24 brd 192.168.1.255 scope global eth1     inet6 fe80::250:56ff:fe8d:496/64 scope link 从库没有附加上VIP [root@node2 keepalived]# ip add |grep inet     inet 127.0.0.1/8 scope host lo     inet6 ::1/128 scope host     inet 192.168.1.24/24 brd 192.168.1.255 scope global eth1     inet6 fe80::250:56ff:fe8d:3d1e/64 scope link

附录： 导致裂脑发生的原因

高可用服务器之间心跳线链路故障，导致无法正常通信。 心跳线坏了（包括断了，老化）。 网卡即相关驱动坏了，IP配置及冲突问题（网卡直连） 心跳线间连接的设备故障（网卡及交换机） 仲裁的机器出问题（采用仲裁的方案）。 高可用服务器对上开启了iptables防火墙阻挡了心跳信息传输。 高可用服务器对上心跳网卡地址等信息配置不正确，导致发送心跳失败。 其他服务器配置不当等原因，如心跳方式不同，心跳广播冲突，软件BUG

在实际生产环境中，我们可以从以下几个方面来防止裂脑问题的发生。 (1)同时使用串行电缆和以太网电缆连接，同时用两条心跳线路，这样一条线路坏了，另一个还是好的，依然能传送心跳消息。 (2)当检测到裂脑时强行关闭一个心跳节点（这个功能需特殊设备支持，如fence,stonith）。相当于备节点接收不到心跳信息，发送关机命令通过单独的线路关闭主节点电源。 (3)做好对裂脑的监控报警（如邮件及手机短信等），在问题发生时人为的第一时间介入仲裁，降低损失。例如：百度的监控报警短信就有上行和下行的区别。报警信息到管理员手机上，就可以通过回复对应的字符串等操作就可以返回给服务器，让服务器根据指令自动执行处理相关。