签到成功

知道了

CNDBA社区CNDBA社区

MySQL 高可用架构：双主+ haproxy + keepalived

2022-05-26 11:29 1608 0 原创 MySQL
作者： dave
本文链接：http://www.cndba.cn/cndba/dave/article/108024

在之前博客，我们介绍了mysql双主 + keepalived实现高可用，这种方式在一台出现问题的时候可以切换，但是正常情况下只有一台服务使用，无法实现负载均衡，本文引入haproxy实现正常情况下也能有负载均衡的效果.

MySQL 高可用架构：双主 + keepalived 搭建手册
https://www.cndba.cn/dave/article/108023

双主 + keepalived 的搭建我们这里不再重复描述。

1 安装配置haproxy

下载haproxy：

https://www.haproxy.org/download/2.5/src/haproxy-2.5.7.tar.gz

安装依赖包：

yum -y install pcre-devel bzip2-devel gcc*

安装haproxy：

[root@www.cndba.cn_1 ~]# tar xzvf haproxy-2.5.7.tar.gz
[root@www.cndba.cn_1 ~]# cd haproxy-2.5.7/
[root@www.cndba.cn_1 haproxy-2.5.7]# make -j $(nproc) TARGET=linux-glibc USE_NS=
[root@www.cndba.cn_1 haproxy-2.5.7]# make install PREFIX=/usr/local/haproxy

创建配置文件：

[root@www.cndba.cn_2 haproxy-2.5.7]# cat /etc/haproxy/haproxy.cfg
global
    log 127.0.0.1 local0
    log 127.0.0.1 local1 notice
    maxconn 4096
    uid 99
    gid 99
    daemon
defaults
    mode                    http
    log                     global
    option                  tcplog
    option                  dontlognull
    option                  redispatch
    retries                 3
    timeout queue           1m
    timeout connect         10s
    timeout client          1m
    timeout server          1m
    timeout check           10s
    maxconn                 4096
listen mysql_proxy
    bind 0.0.0.0:3307
    mode tcp
    balance  source
    option mysql-check user haproxy #在mysql中创建无任何权限用户haproxy，且无密码
    server mysqldb1 172.31.185.120:3306 weight 1  check  inter 1s rise 2 fall 2        #master mysql
    server mysqldb2 172.31.185.165:3306 weight 2  check  inter 1s rise 2 fall 2 backup         #slave mysql，如果是双主，这里可以删掉backup
listen stats   # 这里是web 访问haproxy 的入口：http://127.0.0.1:8888/dbs, 用户名和密码都是admin
   mode http
   bind 0.0.0.0:8888
   stats enable
   stats uri /dbs
   stats realm Global/ statistics
   stats auth admin:admin

在mysql 中创建haproxy 用户：http://www.cndba.cn/cndba/dave/article/108024

mysql> CREATE USER 'haproxy'@'%' IDENTIFIED BY ''; 
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

探活脚本：http://www.cndba.cn/cndba/dave/article/108024

[root@www.cndba.cn_1 keepalived]# cat keepalived_mysql.sh
#!/bin/bash
source /etc/profile
mysql_process=$(ps -C mysqld --no-header | wc -l)
haproxy_process=$(ps -C haproxy --no-header | wc -l)
if [ $mysql_process -eq 0 ]; then
    systemctl restart keepalived.service
fi

if [ $haproxy_process -eq 0 ];then
   systemctl stop keepalived.service
fi

[root@www.cndba.cn_1 keepalived]#chmod a+x keepalived_mysql.sh

主服务器keepalived 脚本：http://www.cndba.cn/cndba/dave/article/108024

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

vrrp_script chk_servers {
    script "/etc/keepalived/keepalived_mysql.sh"
    interval 5                                  #5秒执行一次脚本
    weight 2
}

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 {
        chk_servers
    }
   # notify_master /scripts/keepalived_master.sh
   notify_backup "/usr/local/mysql/bin/mysqld restart"
   # notify_stop /scripts/keepalived_stop.sh
}
[root@www.cndba.cn_1 keepalived]#

被服务器keepalived 脚本：

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

[root@www.cndba.cn_2 keepalived]# cat keepalived.conf
global_defs {
        router_id LVS_LEVEL2    #备份服务器名称
}

vrrp_script chk_servers {
    script "/etc/keepalived/keepalived_mysql.sh"
    interval 5                                  #5秒执行一次脚本
    weight 2
}


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 {
       chk_servers
    }

    notify_backup "/usr/local/mysql/bin/mysqld restart"
}

[root@www.cndba.cn_2 keepalived]#

keepalived实时检测 /scripts/keepalived_mysql.sh脚本，当发现mysql挂掉后，就会重启keeplived服务，这时vip切换到另外一台mysql机器，此时mysql成为backup，同时执行notify_backup中的命令，重启mysql数据库，这样做的好处就是不会导致双主挂掉，导致丢数据的情况（这里担心的是如果mysql挂掉而长时间没有恢复的话，就会造成部分数据库丢失），如果是haproxy挂掉，则直接停掉keepalived，vip切换到另外一台机器，主从依然正常，影响不大，后续自己手动重启就可以了。http://www.cndba.cn/cndba/dave/article/108024http://www.cndba.cn/cndba/dave/article/108024

重启KeepAlived 并不会导致VIP 飘走。

2 将Haproxy 添加到系统服务

复制haproxy文件到/usr/sbin下，因为下面的haproxy.init启动脚本默认会去/usr/sbin下找。http://www.cndba.cn/cndba/dave/article/108024

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

添加到系统服务并开机自启动

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

[root@www.cndba.cn_1 examples]# pwd
/root/haproxy-2.5.7/examples
[root@www.cndba.cn_1 examples]# ll
total 40
-rw-rw-r-- 1 root root 3740 May 13 22:11 acl-content-sw.cfg
-rw-rw-r-- 1 root root 4714 May 13 22:11 basic-config-edge.cfg
-rw-rw-r-- 1 root root 2499 May 13 22:11 content-sw-sample.cfg
drwxrwxr-x 2 root root  132 May 13 22:11 errorfiles
-rw-rw-r-- 1 root root 2380 May 13 22:11 haproxy.init
-rw-rw-r-- 1 root root 1301 May 13 22:11 option-http_proxy.cfg
-rw-rw-r-- 1 root root  845 May 13 22:11 quick-test.cfg
-rw-rw-r-- 1 root root 1300 May 13 22:11 socks4.cfg
-rw-rw-r-- 1 root root 2274 May 13 22:11 transparent_proxy.cfg
-rw-rw-r-- 1 root root 1238 May 13 22:11 wurfl-example.cfg
[root@www.cndba.cn_1 examples]# cp haproxy.init /etc/init.d/haproxy
[root@www.cndba.cn_1 examples]# chmod 755 /etc/init.d/haproxy
[root@www.cndba.cn_1 examples]# chkconfig --add haproxy
[root@www.cndba.cn_1 examples]# chkconfig haproxy on
[root@www.cndba.cn_1 examples]# chkconfig --list|grep haproxy

Note: This output shows SysV services only and does not include native
      systemd services. SysV configuration data might be overridden by native
      systemd configuration.

      If you want to list systemd services use 'systemctl list-unit-files'.
      To see services enabled on particular target use
      'systemctl list-dependencies [target]'.

haproxy         0:off   1:off   2:on    3:on    4:on    5:on    6:off

服务管理：

启动：service haproxy start
停止：service haproxy stop
重载：service haproxy restart
状态：service haproxy status
检查：service haproxy test


[root@www.cndba.cn_1 examples]# service haproxy start
Reloading systemd:                                         [  OK  ]
Starting haproxy (via systemctl):                          [  OK  ]
[root@www.cndba.cn_1 examples]# ps -ef|grep haproxy
nobody   14383     1  0 10:12 ?        00:00:00 /usr/sbin/haproxy -D -f /etc/haproxy/haproxy.cfg -p /var/run/haproxy.pid
root     14579  7848  0 10:13 pts/1    00:00:00 grep --color=auto haproxy
[root@www.cndba.cn_1 examples]#

3 验证

3.1 HAProxy 界面

http://172.31.185.120:8888/dbs

3.2 HAProxy 端口访问

[mysql@www.cndba.cn_3 ~]$ mysql -h172.31.185.80 -urepl -prepl --port 3307
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 2233
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.165 |        2 |
+----------------+----------+
1 row in set (0.01 sec)

mysql>

3.3 重启KeepAlived，VIP 不会飘走

[root@www.cndba.cn_1 examples]# 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 29894507sec preferred_lft 29894507sec
    inet6 fe80::f816:3eff:fe70:35c7/64 scope link
       valid_lft forever preferred_lft forever
[root@www.cndba.cn_1 examples]#


[root@www.cndba.cn_2 examples]# 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 29658157sec preferred_lft 29658157sec
    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 examples]#

重启172.31.185.80 所在节点的KeepAlived， 重启VIP 不会飘走，只是中断一会又重新起来：

[root@www.cndba.cn_2 examples]# systemctl restart keepalived.service
[root@www.cndba.cn_2 examples]# 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 29658056sec preferred_lft 29658056sec
    inet6 fe80::f816:3eff:fe29:97dc/64 scope link
       valid_lft forever preferred_lft forever
[root@www.cndba.cn_2 examples]# 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 29658032sec preferred_lft 29658032sec
    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 examples]#

3.4 停用HAProxy， VIP 会飘走

[root@www.cndba.cn_1 keepalived]# service haproxy stop
Stopping haproxy (via systemctl):                          [  OK  ]
[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 29892664sec preferred_lft 29892664sec
    inet6 fe80::f816:3eff:fe70:35c7/64 scope link
       valid_lft forever preferred_lft forever
[root@www.cndba.cn_1 keepalived]#

3.5 关闭VIP 节点的数据库，VIP 会飘走

关闭VIP 172.31.185.80 所在节点的数据库：

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

[root@www.cndba.cn_1 keepalived]#  service mysqld stop
Shutting down MySQL............ SUCCESS!
[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 29892439sec preferred_lft 29892439sec
    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
[root@www.cndba.cn_1 keepalived]# ps -ef|grep keep
root      1877     1  0 11:22 ?        00:00:00 /usr/local/keepalived/sbin/keepalived -D
root      1878  1877  0 11:22 ?        00:00:00 /usr/local/keepalived/sbin/keepalived -D
root      1924  7848  0 11:22 pts/1    00:00:00 grep --color=auto keep
[root@www.cndba.cn_1 keepalived]# ps -ef|grep keep
root      1877     1  0 11:22 ?        00:00:00 /usr/local/keepalived/sbin/keepalived -D
root      1878  1877  0 11:22 ?        00:00:00 /usr/local/keepalived/sbin/keepalived -D
root      1989  7848  0 11:22 pts/1    00:00:00 grep --color=auto keep
[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 29892431sec preferred_lft 29892431sec
    inet6 fe80::f816:3eff:fe70:35c7/64 scope link
       valid_lft forever preferred_lft forever
[root@www.cndba.cn_1 keepalived]#

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

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

dave

关注

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

  • 2283
    原创
  • 3
    翻译
  • 579
    转载
  • 196
    评论
  • 访问：8170489次
  • 积分：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