签到成功

知道了

CNDBA社区CNDBA社区

MySQL 高可用架构：双主 + keepalived 搭建手册

2022-05-25 13:16 1815 0 原创 MySQL
作者： dave
本文链接：http://www.cndba.cn/cndba/dave/article/108023

在之前的博客，我们了解了MySQL 双主集群的搭建， 如下：

MySQL 5.7 主主 双向复制 环境搭建手册
https://www.cndba.cn/dave/article/108022

本篇我们在介绍下keepalived的搭建。

1 下载并安装软件

下载keepalived：http://www.cndba.cn/cndba/dave/article/108023http://www.cndba.cn/cndba/dave/article/108023

[mysql@www.cndba.cn_1 ~]$ wget https://www.keepalived.org/software/keepalived-2.2.7.tar.gz —no-check-certificate

安装依赖包：

[root@www.cndba.cn_1 ~]# yum install -y gcc openssl-devel libnl libnl-devel libnfnetlink-devel

解压keepalived压缩包放到/usr/local/并创建软链接：

[root@www.cndba.cn_1 mysql]# tar zxvf keepalived-2.2.7.tar.gz -C /usr/local/
[root@www.cndba.cn_1 mysql]# cd /usr/local/
[root@www.cndba.cn_1 local]# ln -s keepalived-2.2.7 keepalived
[root@www.cndba.cn_1 local]# ll |grep keepalived
lrwxrwxrwx   1 root  root         16 May 25 09:28 keepalived -> keepalived-2.2.7
drwxrwxr-x  10  1000 mysql      4096 Jan 17 03:10 keepalived-2.2.7
[root@www.cndba.cn_1 local]#

安装keepalivedhttp://www.cndba.cn/cndba/dave/article/108023

http://www.cndba.cn/cndba/dave/article/108023

  1. —prefix：keepalived安装目录
  2. —sysconf：keepalived的核心配置文件，必须要在/etc目录下面，改为其他位置会导致启动不了，不配置在该目录下的话，启动keepalived时日志文件里面会报错，显示找不到/etc这个文件夹
[root@www.cndba.cn_1 local]#cd /usr/local/keepalived
[root@www.cndba.cn_1 keepalived]./configure --prefix=/usr/local/keepalived/ --sysconf /etc

编译和安装keepalived

make && make install

创建keepalived软链接 /usr/sbin/如果存在keepalived就先删除

[root@www.cndba.cn_1 sbin]# ln -s /usr/local/keepalived/sbin/keepalived /usr/sbin/

2 配置开机自启动

安装置换会自动创建配置文件，这里只需要检查一下：

[root@www.cndba.cn_1 keepalived]# cat /lib/systemd/system/keepalived.service
[Unit]
Description=LVS and VRRP High Availability Monitor
After=network-online.target syslog.target
Wants=network-online.target
Documentation=man:keepalived(8)
Documentation=man:keepalived.conf(5)
Documentation=man:genhash(1)
Documentation=https://keepalived.org

[Service]
Type=forking
PIDFile=/run/keepalived.pid
KillMode=process
EnvironmentFile=-/etc/sysconfig/keepalived
ExecStart=/usr/local/keepalived/sbin/keepalived  $KEEPALIVED_OPTIONS
ExecReload=/bin/kill -HUP $MAINPID

[Install]
WantedBy=multi-user.target
[root@www.cndba.cn_1 keepalived]#

相关维护命令：

http://www.cndba.cn/cndba/dave/article/108023
http://www.cndba.cn/cndba/dave/article/108023

  1. systemctl daemon-reload 重新加载
  2. systemctl enable keepalived.service 设置开机自动启动
  3. systemctl disable keepalived.service 取消开机自动启动
  4. systemctl start keepalived.service 启动
  5. systemctl stop keepalived.service停止

3 配置keepalived 文件

现在启动keepalived 会失败。 因为配置文件还没有修改。

/etc 目录下会有一个示例配置文件：

/etc/keepalived/keepalived.conf.sample

主服务器配置文件：

[root@www.cndba.cn_1 ~]# cat /etc/keepalived/keepalived.conf
global_defs {
        router_id LVS_LEVEL1    #主服务器名称
}

vrrp_script check_run {
   script "/etc/keepalived/mysql_check.sh"
   interval 5                           #5秒执行一次脚本
}

vrrp_instance VI_1 {
    state MASTER                        #主服务器
    interface eth0                      #承载VIP地址到物理接口
    virtual_router_id 51        #虚拟路由器ID号，每个热播组保持一致
    priority 100                        #优先级，数值越大优先级越高
    advert_int 1                        #检查间隔，默认为1s
    authentication {            #认证信息，每个热播组保持一致
        auth_type PASS      #认证类型
        auth_pass 1111          #密码字串
    }
    virtual_ipaddress {
        172.31.185.80           #VIP地址（内网地址）
    }
    track_script {
        check_run
    }
}

备用服务器配置文件：

[root@www.cndba.cn_2 ~]# cat /etc/keepalived/keepalived.conf
global_defs {
        router_id LVS_LEVEL2    #备份服务器名称
}
vrrp_script check_run {
        script "/etc/keepalived/mysql_check.sh"
        interval 5                              #5秒执行一次脚本
}
vrrp_instance VI_1 {
    state BACKUP                        #备份服务器
    interface eth0                      #承载VIP地址到物理接口
    virtual_router_id 51        #虚拟路由器ID号，每个热播组保持一致
    priority 50                         #优先级，数值越大优先级越高
    advert_int 1                        #检查间隔，默认为1s
    authentication {            #认证信息，每个热播组保持一致
        auth_type PASS      #认证类型
        auth_pass 1111          #密码字串
    }
    virtual_ipaddress {
        172.31.185.80       #VIP地址（和主服务器设置一样）
    }
    track_script {
        check_run
    }
}

[root@www.cndba.cn_2 ~]#

Mysql 检测脚本：mysql_check.sh

#!/bin/bash
/usr/local/mysql/bin/mysql -uroot -p'root' -e "show status" &>/dev/null 
if [ $? -ne 0 ] ;then
    systemctl stop keepalived
fi


chmod +x /etc/keepalived/mysql_check.sh

4 启动keepalived

主服务器：

[root@www.cndba.cn_1 keepalived]# systemctl start keepalived.service
[root@www.cndba.cn_1 keepalived]# systemctl status keepalived.service
● keepalived.service - LVS and VRRP High Availability Monitor
   Loaded: loaded (/usr/lib/systemd/system/keepalived.service; enabled; vendor preset: disabled)
   Active: active (running) since Wed 2022-05-25 12:44:48 CST; 9s ago
     Docs: man:keepalived(8)
           man:keepalived.conf(5)
           man:genhash(1)
           https://keepalived.org
  Process: 15467 ExecStart=/usr/local/keepalived/sbin/keepalived $KEEPALIVED_OPTIONS (code=exited, status=0/SUCCESS)
 Main PID: 15468 (keepalived)
   CGroup: /system.slice/keepalived.service
           ├─15468 /usr/local/keepalived/sbin/keepalived -D
           └─15469 /usr/local/keepalived/sbin/keepalived -D

May 25 12:44:54 mongodb1.novalocal Keepalived_vrrp[15469]: (VI_1) received a 0 auth, expecting 1!
May 25 12:44:55 mongodb1.novalocal Keepalived_vrrp[15469]: (VI_1) received a 0 auth, expecting 1!
May 25 12:44:56 mongodb1.novalocal Keepalived_vrrp[15469]: (VI_1) received a 0 auth, expecting 1!
May 25 12:44:56 mongodb1.novalocal Keepalived_vrrp[15469]: (VI_1) Sending/queueing gratuitous ARPs on eth0 for 172.31.185.80
May 25 12:44:56 mongodb1.novalocal Keepalived_vrrp[15469]: Sending gratuitous ARP on eth0 for 172.31.185.80
May 25 12:44:56 mongodb1.novalocal Keepalived_vrrp[15469]: Sending gratuitous ARP on eth0 for 172.31.185.80
May 25 12:44:56 mongodb1.novalocal Keepalived_vrrp[15469]: Sending gratuitous ARP on eth0 for 172.31.185.80
May 25 12:44:56 mongodb1.novalocal Keepalived_vrrp[15469]: Sending gratuitous ARP on eth0 for 172.31.185.80
May 25 12:44:56 mongodb1.novalocal Keepalived_vrrp[15469]: Sending gratuitous ARP on eth0 for 172.31.185.80
May 25 12:44:57 mongodb1.novalocal Keepalived_vrrp[15469]: (VI_1) received a 0 auth, expecting 1!
[root@www.cndba.cn_1 keepalived]#

从服务器：

[root@www.cndba.cn_2 keepalived]# systemctl start keepalived.service
[root@www.cndba.cn_2 keepalived]# systemctl status keepalived.service
● keepalived.service - LVS and VRRP High Availability Monitor
   Loaded: loaded (/usr/lib/systemd/system/keepalived.service; disabled; vendor preset: disabled)
   Active: active (running) since Wed 2022-05-25 12:44:51 CST; 34s ago
     Docs: man:keepalived(8)
           man:keepalived.conf(5)
           man:genhash(1)
           https://keepalived.org
  Process: 30562 ExecStart=/usr/local/keepalived/sbin/keepalived $KEEPALIVED_OPTIONS (code=exited, status=0/SUCCESS)
 Main PID: 30563 (keepalived)
   CGroup: /system.slice/keepalived.service
           ├─30563 /usr/local/keepalived/sbin/keepalived -D
           └─30564 /usr/local/keepalived/sbin/keepalived -D

May 25 12:45:24 mongodb2 Keepalived_vrrp[30564]: Sending gratuitous ARP on eth0 for 172.31.185.80
May 25 12:45:24 mongodb2 Keepalived_vrrp[30564]: Sending gratuitous ARP on eth0 for 172.31.185.80
May 25 12:45:24 mongodb2 Keepalived_vrrp[30564]: Sending gratuitous ARP on eth0 for 172.31.185.80
May 25 12:45:24 mongodb2 Keepalived_vrrp[30564]: Sending gratuitous ARP on eth0 for 172.31.185.80
May 25 12:45:24 mongodb2 Keepalived_vrrp[30564]: (VI_1) Master received advert from 172.31.185.120 with higher priority 100, ours 50
May 25 12:45:24 mongodb2 Keepalived_vrrp[30564]: (VI_1) Entering BACKUP STATE
May 25 12:45:24 mongodb2 Keepalived_vrrp[30564]: (VI_1) removing VIPs.
May 25 12:45:24 mongodb2 Keepalived_vrrp[30564]: (VI_1) received a 0 auth, expecting 1!
May 25 12:45:25 mongodb2 Keepalived_vrrp[30564]: (VI_1) received a 0 auth, expecting 1!
May 25 12:45:26 mongodb2 Keepalived_vrrp[30564]: (VI_1) received a 0 auth, expecting 1!
[root@www.cndba.cn_2 keepalived]#

5 测试

[mysql@www.cndba.cn_3 ~]$ mysql -h172.31.185.80 -urepl -prepl
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or /g.
Your MySQL connection id is 88
Server version: 5.7.33-log MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.

mysql>

主服务器：

[root@www.cndba.cn_1 keepalived]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether fa:16:3e:70:35:c7 brd ff:ff:ff:ff:ff:ff
    inet 172.31.185.120/22 brd 172.31.187.255 scope global noprefixroute dynamic eth0
       valid_lft 29973482sec preferred_lft 29973482sec
    inet 172.31.185.80/32 scope global eth0
       valid_lft forever preferred_lft forever
    inet6 fe80::f816:3eff:fe70:35c7/64 scope link
       valid_lft forever preferred_lft forever

备用服务器：http://www.cndba.cn/cndba/dave/article/108023

[root@www.cndba.cn_2 keepalived]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether fa:16:3e:29:97:dc brd ff:ff:ff:ff:ff:ff
    inet 172.31.185.165/22 brd 172.31.187.255 scope global noprefixroute dynamic eth0
       valid_lft 29737145sec preferred_lft 29737145sec
    inet6 fe80::f816:3eff:fe29:97dc/64 scope link
       valid_lft forever preferred_lft forever
[root@www.cndba.cn_2 keepalived]#

关闭主库上的DB 实例：

http://www.cndba.cn/cndba/dave/article/108023 

[root@www.cndba.cn_1 keepalived]# service mysqld stop
Shutting down MySQL............ SUCCESS!
[root@www.cndba.cn_1 keepalived]#

查看IP信息：

主服务器：

[root@www.cndba.cn_1 keepalived]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether fa:16:3e:70:35:c7 brd ff:ff:ff:ff:ff:ff
    inet 172.31.185.120/22 brd 172.31.187.255 scope global noprefixroute dynamic eth0
       valid_lft 29973171sec preferred_lft 29973171sec
    inet6 fe80::f816:3eff:fe70:35c7/64 scope link
       valid_lft forever preferred_lft forever
[root@www.cndba.cn_1 keepalived]#

备服务器：http://www.cndba.cn/cndba/dave/article/108023

[root@www.cndba.cn_2 keepalived]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether fa:16:3e:29:97:dc brd ff:ff:ff:ff:ff:ff
    inet 172.31.185.165/22 brd 172.31.187.255 scope global noprefixroute dynamic eth0
       valid_lft 29736804sec preferred_lft 29736804sec
    inet 172.31.185.80/32 scope global eth0
       valid_lft forever preferred_lft forever
    inet6 fe80::f816:3eff:fe29:97dc/64 scope link
       valid_lft forever preferred_lft forever
[root@www.cndba.cn_2 keepalived]#

已经成功切到备服务器，并且连接正常：

[mysql@www.cndba.cn_3 ~]$ mysql -h172.31.185.80 -urepl -prepl
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or /g.
Your MySQL connection id is 179
Server version: 5.7.33-log MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.

mysql> select SUBSTRING_INDEX(host,':',1) as ip , count(*) from information_schema.processlist group by ip;
+----------------+----------+
| ip             | count(*) |
+----------------+----------+
| 172.31.185.131 |        1 |
+----------------+----------+
1 row in set (0.01 sec)

mysql>

重启主服务器：http://www.cndba.cn/cndba/dave/article/108023

[root@www.cndba.cn_1 keepalived]# service mysqld start
Starting MySQL.. SUCCESS!
[root@www.cndba.cn_1 keepalived]#

同步正常，但VIP 没有切换过来，依旧在备用服务器上。

版权声明：本文为博主原创文章，未经博主允许不得转载。

用户评论
* 以下用户言论只代表其个人观点，不代表CNDBA社区的观点或立场
dave

dave

关注

人的一生应该是这样度过的：当他回首往事的时候，他不会因为虚度年华而悔恨，也不会因为碌碌无为而羞耻；这样，在临死的时候，他就能够说：“我的整个生命和全部精力，都已经献给世界上最壮丽的事业....."

  • 2283
    原创
  • 3
    翻译
  • 579
    转载
  • 196
    评论
  • 访问：8169071次
  • 积分：4428
  • 等级：核心会员
  • 排名：第1名
精华文章

    达梦数据库运维实战

    Oracle 18c 必须掌握的新特性：管理与实战

    Oracle数据库问题解决方案和故障排除手册
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        友情链接： CNDBA社区 | openGauss | openEuler | openAnolis | 广告联系: database@ustc.edu
        Copyright © 2016 All Rights Reserved. Powered by CNDBA · 皖ICP备2022006297号-1·

        QQ交流群

        注册联系QQ