ProxySQL+Keepalived高可用方案

发表于 | 分类于 | | 阅读次数:

基础信息

ProxySQL

版本：1.4.8-32-g669c149
ProxySQL 1：proxysql1 192.168.1.15 监听端口6032
ProxySQL 2：proxysql2 192.168.1.16 监听端口6032

Keepalived

版本：keepalived-1.4.4
同时在ProxySQL 1和ProxySQL 2上面安装

VIP

192.168.1.88

机器信息

1
2
3
4
# cat /etc/redhat-release
CentOS Linux release 7.4.1708 (Core) 
# uname -a
Linux 192-168-1-16 3.10.0-693.5.2.el7.x86_64 #1 SMP Fri Oct 20 20:32:50 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux

安装Keepalived

1
2
3
4
5
6
7
8
# 依赖安装
yum install curl gcc openssl-devel libnl3-devel net-snmp-devel

# 下载
wget "https://rpmfind.net/linux/centos/7.5.1804/os/x86_64/Packages/keepalived-1.3.5-6.el7.x86_64.rpm"

# 安装Keepalived
rpm -ivh keepalived-1.3.5-6.el7.x86_64.rpm

配置

两个keepalived配置

1
2
ProxySQL 1: state MASTER;priority 100
ProxySQL 2: state BACKUP;priority 90
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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
vim /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {
   notification_email {
     acassen@firewall.loc
     failover@firewall.loc
     sysadmin@firewall.loc
   }
   notification_email_from Alexandre.Cassen@firewall.loc
   smtp_server 192.168.200.1
   smtp_connect_timeout 30
   router_id PROXY_HA
   vrrp_skip_check_adv_addr
   vrrp_strict
   vrrp_garp_interval 0
   vrrp_gna_interval 0
}

# Script used to check if Proxy is running
vrrp_script check_proxy {  #检测proxysql服务是否在运行。有很多方式，比如进程，用脚本检测等等
  script "killall -0 proxysql" #这里通过命令监测
  interval 2  #检查执行间隔，每2s检测一次
  weight -30  #结果导致的优先级变更，检测失败（返回非0）则优先级 -30
  fall 2     #检测连续2次失败才算确定是真失败。会用weight减少优先级（1-255之间）
  rise 1     #检测1次成功就算成功。但不修改优先级
}

# Virtual interface
# The priority specifies the order in which the assigned interface to take over in a failover
vrrp_instance VI_1 {
    state MASTER    #实例状态，只有MASTER 和 BACKUP两种状态，并且需要全部大写。抢占模式下，其中MASTER为工作状态，BACKUP为备用状态。当MASTER所在的服务器失效时，BACKUP所在的服务会自动把它的状态由BACKUP切换到MASTER状态。当失效的MASTER所在的服务恢复时，BACKUP从MASTER恢复到BACKUP状态。
    interface eth0  #指定虚拟ip的网卡接口
    virtual_router_id 91    #路由器标识，MASTER和BACKUP必须是一致的，可选择IP最后一段使用，相同的 VRID 为一个组，他将决定多播的 MAC 地址。 
    priority 100 #定义优先级，数字越大，优先级越高，在同一个vrrp_instance下，MASTER的优先级必须大于BACKUP的优先级。这样MASTER故障恢复后，就可以将VIP资源再次抢回来
    #nopreempt   #禁止抢占服务。默认情况，当MASTER服务挂掉之后，BACKUP自动升级为MASTER并接替它的任务，当MASTER服务恢复后，升级为MASTER的BACKUP服务又自动降为BACKUP，把工作权交给原MASTER。当配置了nopreempt，MASTER从挂掉到恢复，不再将服务抢占过来。注意不要开，会影响多次主备切换
    advert_int 1    #MASTER与BACKUP节点间同步检查的时间间隔，单位为秒
    virtual_ipaddress {
        172.16.200.88 #VIP
    }
    track_script {
      check_proxy   #状态检测Script
    }
}

配置iptables防火墙规则

1
2
3
4
5
6
vim /etc/sysconfig/iptables

-A INPUT -i eth0 -d 224.0.0.18 -j ACCEPT   #允许组播地址通信  
-A INPUT -i eth0 -p vrrp -j ACCEPT         #允许VRRP（虚拟路由器冗余协）通信 
-A INPUT -p tcp -m state --state NEW -m tcp --dport 6033 -j ACCEPT    #开放proxysql的6033端口 
-A INPUT -p tcp -m state --state NEW -m tcp --dport 6032 -j ACCEPT    #开放proxysql的6032管理端口

重启防火墙

1
systemctl restart iptables

测试

启动keepalived

1
systemctl start keepalived

查看vip切换情况

1
2
3
4
5
6
7
8
9
10
11
ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN qlen 1
    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
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether fa:3e:6e:ff:b7:00 brd ff:ff:ff:ff:ff:ff
    inet 192.168.1.15/16 brd 192.168.255.255 scope global eth0
       valid_lft forever preferred_lft forever
    inet 191.168.1.88/32 scope global eth0
       valid_lft forever preferred_lft forever

通过VIP连接

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql -u proxysql -p -h vip -P6033
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 26
Server version: 5.5.30 (ProxySQL)

Copyright (c) 2009-2017 Percona LLC and/or its affiliates
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

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.

proxysql:(none)>

停止proxysql1的proxysql进程

1
2
# 停止proxysql
systemctl stop proxysql
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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
# 查看两台机器keepalived的日志
# proxysql1
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 一 2018-05-28 15:56:07 CST; 49s ago
  Process: 23027 ExecStart=/usr/sbin/keepalived $KEEPALIVED_OPTIONS (code=exited, status=0/SUCCESS)
 Main PID: 23028 (keepalived)
   CGroup: /system.slice/keepalived.service
           ├─23028 /usr/sbin/keepalived -D
           ├─23029 /usr/sbin/keepalived -D
           └─23030 /usr/sbin/keepalived -D

5月 28 15:56:49 172-16-200-15 Keepalived_vrrp[23030]: /usr/bin/killall -0 proxysql exited with status 1
5月 28 15:56:51 172-16-200-15 Keepalived_vrrp[23030]: /usr/bin/killall -0 proxysql exited with status 1
5月 28 15:56:51 172-16-200-15 Keepalived_vrrp[23030]: VRRP_Script(check_proxy) failed
5月 28 15:56:52 172-16-200-15 Keepalived_vrrp[23030]: VRRP_Instance(VI_1) Changing effective priority from 100 to 70
5月 28 15:56:52 172-16-200-15 Keepalived_vrrp[23030]: VRRP_Instance(VI_1) Received advert with higher priority 90, ours 70
5月 28 15:56:52 172-16-200-15 Keepalived_vrrp[23030]: VRRP_Instance(VI_1) Entering BACKUP STATE
5月 28 15:56:52 172-16-200-15 Keepalived_vrrp[23030]: VRRP_Instance(VI_1) removing protocol VIPs.
5月 28 15:56:52 172-16-200-15 Keepalived_vrrp[23030]: VRRP_Instance(VI_1) removing protocol iptable drop rule
5月 28 15:56:53 172-16-200-15 Keepalived_vrrp[23030]: /usr/bin/killall -0 proxysql exited with status 1
5月 28 15:56:55 172-16-200-15 Keepalived_vrrp[23030]: /usr/bin/killall -0 proxysql exited with status 1


# proxysql2
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 一 2018-05-28 16:00:29 CST; 29s ago
  Process: 28006 ExecStart=/usr/sbin/keepalived $KEEPALIVED_OPTIONS (code=exited, status=0/SUCCESS)
 Main PID: 28007 (keepalived)
   CGroup: /system.slice/keepalived.service
           ├─28007 /usr/sbin/keepalived -D
           ├─28008 /usr/sbin/keepalived -D
           └─28009 /usr/sbin/keepalived -D

5月 28 16:00:29 172-16-200-16 Keepalived_vrrp[28009]: WARNING - script `killall` resolved by path search to `/usr/bin/killall`. Please specify full path.
5月 28 16:00:29 172-16-200-16 Keepalived_vrrp[28009]: SECURITY VIOLATION - scripts are being executed but script_security not enabled.
5月 28 16:00:29 172-16-200-16 Keepalived_vrrp[28009]: VRRP_Instance(VI_1) removing protocol VIPs.
5月 28 16:00:29 172-16-200-16 Keepalived_vrrp[28009]: VRRP_Instance(VI_1) removing protocol iptable drop rule
5月 28 16:00:29 172-16-200-16 Keepalived_vrrp[28009]: Using LinkWatch kernel netlink reflector...
5月 28 16:00:30 172-16-200-16 Keepalived_vrrp[28009]: VRRP_Instance(VI_1) Entering BACKUP STATE
5月 28 16:00:30 172-16-200-16 Keepalived_vrrp[28009]: VRRP sockpool: [ifindex(2), proto(112), unicast(0), fd(10,11)]
5月 28 16:00:30 172-16-200-16 Keepalived_vrrp[28009]: VRRP_Script(check_proxy) succeeded
5月 28 16:00:57 172-16-200-16 Keepalived_vrrp[28009]: VRRP_Instance(VI_1) forcing a new MASTER election
5月 28 16:00:58 172-16-200-16 Keepalived_vrrp[28009]: VRRP_Instance(VI_1) Transition to MASTER STATE
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
# 查看两台机器的vip绑定情况
# proxysql1
ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN qlen 1
    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
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether fa:3e:6e:ff:b7:00 brd ff:ff:ff:ff:ff:ff
    inet 172.16.200.15/16 brd 172.16.255.255 scope global eth0
       valid_lft forever preferred_lft forever
       
# proxysql2
ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN qlen 1
    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
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether fa:e2:15:64:5c:00 brd ff:ff:ff:ff:ff:ff
    inet 172.16.200.16/16 brd 172.16.255.255 scope global eth0
       valid_lft forever preferred_lft forever
    inet 172.16.200.88/32 scope global eth0
       valid_lft forever preferred_lft forever

可见已经实现vip的漂移，通过命令行连接

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 mysql -u proxysql -p -h vip -P6033
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 3
Server version: 5.5.30 (ProxySQL)

Copyright (c) 2009-2017 Percona LLC and/or its affiliates
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

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.

proxysql:(none)>

问题排查

keepalived主备同时都有VIP

原因：主备vvrp直接无法通信
解决：检查防火墙，开启组播

主备切换后，新主再故障，主备无法再次切换

原因：主开启了nopreempt，不抢占
解决：去除主的该配置即可，可参考Keepalived中Master和Backup角色选举策略

参考
  1. Mysql+Keepalived双主热备高可用操作记录
  2. 解决ProxySQL的单点问题
  3. Keepalived中Master和Backup角色选举策略