Keepalived+MySQL主主数据同步实现数据库高可用（HA）

迷南。 2022-07-16 00:26 304阅读 0赞

原文链接：[http://blog.csdn.net/xyang81/article/details/52659123][http_blog.csdn.net_xyang81_article_details_52659123]

# 环境 #

操作系统版本：CentOS7 64位  
MySQL版本：5.6.33  
Keepalived版本：v1.2.23  
节点1IP：192.168.1.205 主机名：edu-mysql-01  
节点2IP：192.168.1.206 主机名：edu-mysql-02  
VIP（虚拟IP）：192.168.1.207

下文中说到和vip即指192.168.1.207，节点1或205即指192.168.1.205，节点2或206即指192.168.1.206

MySQL高可用主要是通过Keepalived这款软件来实现多个节点的服务可用性检测和故障转移，对外提供VIP地址供客户端连接，由keepalived根据服务的状态，负责将vip漂移到真实可用的机器上，从而实现服务的高可用。阅读本文前，需要先安装MySQL、Keepalived以及MySQL主主同步配置，可参考我之前写的文章自行安装与配置：  
1>、[《 MySQL5.7安装与配置（YUM）》][MySQL5.7_YUM]  
2>、[《MySQL主主数据同步》][MySQL]  
3>、[《Keepalived安装与配置》][Keepalived]

本文配置keepalived为非抢占模式。

# 节点1配置 #

shell> vi /etc/keepalived/keepalived.conf
    ! Configuration File for keepalived
    
    global_defs {
       router_id db-01
    }
    
    vrrp_instance VI_1 {
        state BACKUP # 两个节点都为BACKUP状态，根据优先级大小判断谁为MASTER
        interface enp0s3
        virtual_router_id 51
        priority 100
        advert_int 1
        nopreempt # 非抢占模式
        authentication {
            auth_type PASS
            auth_pass 1111
        }
    
        # 虚拟IP池
        virtual_ipaddress {
            192.168.1.207
        }
    }
    
    virtual_server 192.168.1.207 3306 {
         delay_loop 2
         lb_algo wrr
         lb_kind DR
         persistence_timeout 60
         protocol TCP
         real_server 192.168.1.205 3306 {
             weight 3
             notify_down /etc/keepalived/mysql.sh  # 当mysql服务down了之后，执行的脚本
             TCP_CHECK {
                 connect_timeout 10   # mysql连接超时时长（秒）
                 nb_get_retry 3       # mysql服务连接失败，重试次数
                 delay_before_retry 3 #每隔3秒检测一次mysql服务是否可用
                 connect_port 3306
             }
         }
    }

`/etc/keepalived/mysql.sh`配置：

#!/bin/sh
    pkill keepalived

当mysql服务停止时，会执行`/etc/keepalived/mysql.sh`脚本，将当前节点的`keepalived`服务停止，这样vip就会切换到另外一个节点上，从而实现了服务的高可用。

# 节点2配置 #

shell> vi /etc/keepalived/keepalived.conf
    ! Configuration File for keepalived
    
    global_defs { router_id db-02 }
    
    vrrp_instance VI_1 { state BACKUP interface enp0s3 virtual_router_id 51 priority 90 advert_int 1 nopreempt authentication { auth_type PASS auth_pass 1111 }
    
        virtual_ipaddress { 192.168.1.207 }
    }
    
    virtual_server 192.168.1.207 3306 { delay_loop 2 lb_algo wrr lb_kind DR persistence_timeout 60 protocol TCP real_server 192.168.1.206 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 }
         }
    }

`/etc/keepalived/mysql.sh`配置同节点1

# 启动MySQL和Keepalived服务 #

shell> service mysql start
    shell> service keepalived start

注意：先启动mysql，再启动keepalived。因为keepalived启动之后会去连接mysql，检测服务是否可用，如果3次都没连接成功，则会将keepalived进程杀死。

# 连接测试 #

注意：连接到VIP地址，而非直接连接到真实的MySQL服务器。

先看看vip漂在哪台服务器上：

![vip查看][vip]

从上图得知，此时VIP漂移在节点1（192.168.1.205）上。

shell> mysql -u root -proot -h 192.168.1.207

![通过vip连接到mysql][vip_mysql]

> 注：在指定ip连接到mysql时，需要授权配置远程连接的帐号、密码和ip。如：`grant all privileges on *.* to 'root'@'%' identified by 'root' with grant option;`

# 故障转移测试 #

从上面连接测试得知，此时vip漂移在192.168.1.205节点上，所以通过vip（192.168.1.207）连接到的真实服务器是192.168.1.205。将205的mysql服务停止，观察vip是否会切换到206上。

> 通过/var/log/message文件可以看到keepalived故障的日志

## 节点1的日志： ##

Sep 25 16:00:32 edu-mysql-01 Keepalived_healthcheckers[3517]: TCP connection to [192.168.1.205]:3306 failed.
    Sep 25 16:00:35 edu-mysql-01 Keepalived_healthcheckers[3517]: TCP connection to [192.168.1.205]:3306 failed.
    Sep 25 16:00:35 edu-mysql-01 Keepalived_healthcheckers[3517]: Check on service [192.168.1.205]:3306 failed after 1 retry.
    Sep 25 16:00:35 edu-mysql-01 Keepalived_healthcheckers[3517]: Removing service [192.168.1.205]:3306 from VS [192.168.1.207]:3306
    Sep 25 16:00:35 edu-mysql-01 Keepalived_healthcheckers[3517]: Executing [/etc/keepalived/mysql.sh] for service [192.168.1.205]:3306 in VS [192.168.1.207]:3306
    Sep 25 16:00:35 edu-mysql-01 Keepalived_healthcheckers[3517]: Lost quorum 1-0=1 > 0 for VS [192.168.1.207]:3306
    Sep 25 16:00:35 edu-mysql-01 kernel: IPVS: __ip_vs_del_service: enter
    Sep 25 16:00:35 edu-mysql-01 Keepalived[3515]: Stopping
    Sep 25 16:00:35 edu-mysql-01 Keepalived_healthcheckers[3517]: Stopped
    Sep 25 16:00:35 edu-mysql-01 Keepalived_vrrp[3518]: VRRP_Instance(VI_1) sent 0 priority
    Sep 25 16:00:35 edu-mysql-01 Keepalived_vrrp[3518]: VRRP_Instance(VI_1) removing protocol VIPs.
    Sep 25 16:00:36 edu-mysql-01 Keepalived_vrrp[3518]: Stopped
    Sep 25 16:00:36 edu-mysql-01 Keepalived[3515]: Stopped Keepalived v1.2.23 (09/12,2016)

前三行是keepalived心跳检测，每秒检测一次mysql服务是否可用，当第3次连接失败时，将该服务从lvs真实服务器列表中移除（第4行），并执行`/etc/keepalived/mysql.sh`脚本（第5行）停止keepalived服务，转让vip使用权。

## 节点2的日志： ##

Sep 25 16:00:36 edu-mysql-02 Keepalived_vrrp[3457]: VRRP_Instance(VI_1) Transition to MASTER STATE
    Sep 25 16:00:37 edu-mysql-02 Keepalived_vrrp[3457]: VRRP_Instance(VI_1) Entering MASTER STATE
    Sep 25 16:00:37 edu-mysql-02 Keepalived_vrrp[3457]: VRRP_Instance(VI_1) setting protocol VIPs.
    Sep 25 16:00:37 edu-mysql-02 Keepalived_vrrp[3457]: Sending gratuitous ARP on enp0s3 for 192.168.1.207
    Sep 25 16:00:37 edu-mysql-02 Keepalived_vrrp[3457]: VRRP_Instance(VI_1) Sending/queueing gratuitous ARPs on enp0s3 for 192.168.1.207
    Sep 25 16:00:37 edu-mysql-02 Keepalived_vrrp[3457]: Sending gratuitous ARP on enp0s3 for 192.168.1.207
    Sep 25 16:00:37 edu-mysql-02 Keepalived_vrrp[3457]: Sending gratuitous ARP on enp0s3 for 192.168.1.207
    Sep 25 16:00:37 edu-mysql-02 Keepalived_vrrp[3457]: Sending gratuitous ARP on enp0s3 for 192.168.1.207
    Sep 25 16:00:37 edu-mysql-02 Keepalived_vrrp[3457]: Sending gratuitous ARP on enp0s3 for 192.168.1.207
    Sep 25 16:00:37 edu-mysql-02 Keepalived_healthcheckers[3456]: Netlink reflector reports IP 192.168.1.207 added
    Sep 25 16:00:42 edu-mysql-02 Keepalived_vrrp[3457]: Sending gratuitous ARP on enp0s3 for 192.168.1.207
    Sep 25 16:00:42 edu-mysql-02 Keepalived_vrrp[3457]: VRRP_Instance(VI_1) Sending/queueing gratuitous ARPs on enp0s3 for 192.168.1.207

节点2当前是BACKUP状态，当收到节点1挂掉的通知后，将自己的状态转换为master状态（第1行），接着将vip（192.168.1.207）绑定到enp0s3网卡上（第3到第5行）。

## 查看故障转移后的vip ##

![vip故障转移][vip 1]

从上图可知，vip成功从205转移到了206上。

此时在之前已经和vip建立了连接的mysql客户端当中，再执行sql时，第一次会执行失败，因为之前的连接已经断开，第二次执行时，会尝试重新连接到vip对应的新的mysql真实服务器上。如下图所示：  
![vip断线重连][vip 2]

[http_blog.csdn.net_xyang81_article_details_52659123]: http://blog.csdn.net/xyang81/article/details/52659123
[MySQL5.7_YUM]: http://blog.csdn.net/xyang81/article/details/51759200
[MySQL]: http://blog.csdn.net/xyang81/article/details/52562571
[Keepalived]: http://blog.csdn.net/xyang81/article/details/52554398
[vip]: /images/20220716/db1d1d13e19e42f99147b22904aa57d6.png
[vip_mysql]: /images/20220716/2768fb7f2be24690aa926ffc9c9fd932.png
[vip 1]: /images/20220716/0b51565b58df45d98ade01657c810786.png
[vip 2]: /images/20220716/ca8ab9cf88724a77b0703060075d4343.png