Keepalived+Mysql实现主从复制及宕机自动切换_mysql数据库卡死 主从自动切换

前提:本节课程前提条件是node1(master)和node2(slave)的主从架构已经搭建完毕，如果你不知道如何搭建主从，那么可以参考这篇文章

https://blog.csdn.net/weixin_65607252/article/details/128827571?spm=1001.2014.3001.5502
1

1.实战环境(centos7)

2.编写mysql存活检查脚本

在主从服务器均需要此脚本

在/usr/local/scripts/新建 chk_mysql_alived.sh ，此脚本用来监测mysql状态是否是运行状态

#!bin/bash
if ss -tnl | grep -q 3306; then
    echo "mysql is alive"
    exit 0
else
    echo "mysql is dead"
    systemctl stop keepalived
    exit 2
fi
1
2
3
4
5
6
7
8
9

授权执行权限

chmod u+x /usr/local/scripts/chk_mysql_alived.sh
1

测试脚本是否正常

 sh chk_mysql_alived.sh
1

3.安装及配置Keepalived

1>主从服务器上都安装Keepalived

yum install -y ipset
yum install -y keepalived
1
2

注意:如果安装过程中出现以下错误(显示缺少libmysqlclient)，按照以下命令执行

错误：软件包：1:net-snmp-agent-libs-5.7.2-48.el7_8.1.x86_64 (updates)
          需要：libmysqlclient.so.18(libmysqlclient_18)(64bit)
错误：软件包：1:net-snmp-agent-libs-5.7.2-48.el7_8.1.x86_64 (updates)
          需要：libmysqlclient.so.18()(64bit)
 您可以尝试添加 --skip-broken 选项来解决该问题
 您可以尝试执行：rpm -Va --nofiles --nodigest
1
2
3
4
5
6

执行下载：

#安装wget
yum install wget

#下载mysql-community-libs-compat
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-libs-compat-5.7.38-1.el7.x86_64.rpm

#安装
rpm -ivh mysql-community-libs-compat-5.7.38-1.el7.x86_64.rpm

#重新安装keepalived 即可
yum install keepalived -y
1
2
3
4
5
6
7
8
9
10
11

2>主库服务器上部署Keepalived

进入Keepalived配置文件，替换以下内容

vim /etc/keepalived/keepalived.conf
1

vrrp_script chk_mysql_alived {
  script "/usr/local/scripts/chk_mysql_alived.sh"
  interval 2 
  weight -10
}

vrrp_instance VI_1 {
    state MASTER
    interface ens33  # 请根据您的实际网络接口进行调整
    virtual_router_id 50 #与从库的要一致
    priority 101     # 较高的优先级，确保主服务器始终是 MASTER
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.228.112  # 虚拟 IP 地址
    }
    track_script {
        chk_mysql_alived
    }
    vrrp_log {
        file /var/log/keepalived.log  # 日志文件的位置
        syslog yes  # 启用系统日志输出
        debug yes  # 详细调试日志（根据需要调整）
        daemon_notice yes  # 记录守护进程通知
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

Keekpalived相关命令

systemctl disable keepalived  #不会在启动时自动启动
systemctl start keepalived    #启动
systemctl status keepalived   #查看状态
systemctl stop keepalived     #停止
systemctl restart keepalived  #重启
1
2
3
4
5

启动完之后查看keepalived的状态

[root@localhost scripts]# systemctl status keepalived
● keepalived.service - LVS and VRRP High Availability Monitor
   Loaded: loaded (/usr/lib/systemd/system/keepalived.service; disabled; vendor preset: disabled)
   Active: active (running) since 五 2023-08-11 09:57:44 CST; 5s ago
  Process: 32737 ExecStart=/usr/sbin/keepalived $KEEPALIVED_OPTIONS (code=exited, status=0/SUCCESS)
 Main PID: 32755 (keepalived)
   CGroup: /system.slice/keepalived.service
           ├─32755 /usr/sbin/keepalived -D
           ├─32756 /usr/sbin/keepalived -D
           └─32757 /usr/sbin/keepalived -D

8月 11 09:57:44 localhost.localdomain Keepalived_healthcheckers[32756]: Opening file '/etc/keepalived/keepalived.conf'.
8月 11 09:57:45 localhost.localdomain Keepalived_vrrp[32757]: VRRP_Instance(VI_1) Transition to MASTER STATE
8月 11 09:57:46 localhost.localdomain Keepalived_vrrp[32757]: VRRP_Instance(VI_1) Entering MASTER STATE
8月 11 09:57:46 localhost.localdomain Keepalived_vrrp[32757]: VRRP_Instance(VI_1) setting protocol VIPs.
8月 11 09:57:46 localhost.localdomain Keepalived_vrrp[32757]: Sending gratuitous ARP on ens33 for 192.168.228.112
8月 11 09:57:46 localhost.localdomain Keepalived_vrrp[32757]: VRRP_Instance(VI_1) Sending/queueing gratuitous ARPs on ens33 for 192.168.228.112
8月 11 09:57:46 localhost.localdomain Keepalived_vrrp[32757]: Sending gratuitous ARP on ens33 for 192.168.228.112
8月 11 09:57:46 localhost.localdomain Keepalived_vrrp[32757]: Sending gratuitous ARP on ens33 for 192.168.228.112
8月 11 09:57:46 localhost.localdomain Keepalived_vrrp[32757]: Sending gratuitous ARP on ens33 for 192.168.228.112
8月 11 09:57:46 localhost.localdomain Keepalived_vrrp[32757]: Sending gratuitous ARP on ens33 for 192.168.228.112
[root@localhost scripts]# 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

执行命令，查询虚拟ip是否挂启,如图所示,出现以下说明配置没有问题

ip a
1

3>从库服务器上部署Keepalived

与主库同理，唯一有区别的就是state 为BACKUP为备机
进入Keepalived配置文件，替换以下内容

vim /etc/keepalived/keepalived.conf
1

vrrp_script chk_mysql_alived {
  script "/usr/local/scripts/chk_mysql_alived.sh"
  interval 2 
  weight -10
}

vrrp_instance VI_1 {
    state BACKUP
    interface ens33  # 请根据您的实际网络接口进行调整
    virtual_router_id 50 #与主库的要一致
    priority 100     # 较低的优先级，确保从服务器始终是 BACKUP
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.228.112  # 虚拟 IP 地址
    }
    track_script {
        chk_mysql_alived
    }
    vrrp_log {
        file /var/log/keepalived.log  # 日志文件的位置
        syslog yes  # 启用系统日志输出
        debug yes  # 详细调试日志（根据需要调整）
        daemon_notice yes  # 记录守护进程通知
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

4.测试是否真的实现了mysql主机宕机后，自动切换到重机

确认主从mysql以及keepalived都是启动状态下执行如下操作进行测试

注意:keepalived服务不要设置成自启动，可能会影响mysql存活脚本的执行，无法正常关闭keepalived

1>任意找一个SpringBoot项目将mysql的配置改为主库的ip进行测试

2>切换到主库，停止主库的mysql服务

启动：systemctl start mysqld
关闭：systemctl stop mysqld
重启：systemctl restart mysqld
查看状态：systemctl status mysqld
1
2
3
4

3>执行完之后检查主库mysql，以及keepalived状态是否为关闭状态，如果均为关闭状态说明没问题,然后去检查项目是否能够正常运行，如果正常运行，即代表成功。