针对目前公司的数据库架构要做统计规划和调整，所以针对不同的业务环境要选择合适的架构就比较重要，同时作为运维人员又要考虑到维护的便捷性（不能做过多架构维护），最终停留在mha vip/主从 vip/mycat集群/域名 主从这几种架构或组合，这里就分布对不同架构部署做下记录并对比起优劣，针对不同场景做出合适的选择。
本文主要针对主备自动切换，即VIP 主从方式，其中VIP通过keepalived控制。
一、环境描述
服务器IP | 操作系统 | 数据库 | 硬件配置 | 备注 |
|---|---|---|---|---|
192.168.1.23 | RHEL6.6_x86_64 | MySQL 5.7.21 | 内存：16GB CPU：4cores 硬盘:100GB | VIP：192.168.1.27 Keepalived:1.4.4 |
192.168.1.24 | RHEL6.6_x86_64 | MySQL 5.7.21 | 内存：16GB CPU：4cores 硬盘:100GB |
二、业务场景
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)做好对裂脑的监控报警（如邮件及手机短信等），在问题发生时人为的第一时间介入仲裁，降低损失。例如：百度的监控报警短信就有上行和下行的区别。报警信息到管理员手机上，就可以通过回复对应的字符串等操作就可以返回给服务器，让服务器根据指令自动执行处理相关。
