用 keepalived 实现 proxysql cluster 高可用

本文是使用《ProxySQL 代理 MariaDB 实现读写分离和负载均衡》的补充，主要记录 proxysql 集群和 keepalived 的配置。配置中的参数参考：https://www.cnblogs.com/kevingrace/p/10411457.html

实验环境：

• proxysql-node-1（192.168.0.6）
• proxysql-node-2（192.168.0.9）

proxysql cluster 配置

编辑/etc/proxysql.cnf配置文件，添加修改以下信息：

admin_variables=
{
    admin_credentials="admin:admin;cluster:123456"
#   mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"
    mysql_ifaces="0.0.0.0:6032"
#   refresh_interval=2000
#   debug=true
    cluster_username="cluster"
    cluster_password="123456"
    cluster_check_interval_ms=200
    cluster_check_status_frequency=100
    cluster_mysql_query_rules_save_to_disk=true
    cluster_mysql_servers_save_to_disk=true
    cluster_mysql_users_save_to_disk=true
    cluster_proxysql_servers_save_to_disk=true
    cluster_mysql_query_rules_diffs_before_sync=3
    cluster_mysql_servers_diffs_before_sync=3
    cluster_mysql_users_diffs_before_sync=3
    cluster_proxysql_servers_diffs_before_sync=3
}
proxysql_servers=
(
    {
        hostname="192.168.0.6"
        port=6032
        weight=1
        comment="ProxySQL-node-1"
    },
    {
        hostname="192.168.0.9"
        port=6032
        weight=1
        comment="ProxySQL-node-2"
    }
)

查看配置：

mysql> select * from proxysql_servers;
+-------------+------+--------+-----------------+
| hostname    | port | weight | comment         |
+-------------+------+--------+-----------------+
| 192.168.0.6 | 6032 | 1      | ProxySQL-node-1 |
| 192.168.0.9 | 6032 | 1      | ProxySQL-node-2 |
+-------------+------+--------+-----------------+

select * from stats_proxysql_servers_checksums;

mysql> select * from stats_proxysql_servers_metrics;
+-------------+------+--------+-----------------+------------------+----------+---------------+---------+------------------------------+----------------------------+
| hostname    | port | weight | comment         | response_time_ms | Uptime_s | last_check_ms | Queries | Client_Connections_connected | Client_Connections_created |
+-------------+------+--------+-----------------+------------------+----------+---------------+---------+------------------------------+----------------------------+
| 192.168.0.9 | 6032 | 1      | ProxySQL-node-2 | 4                | 161      | 9861          | 0       | 0                            | 0                          |
| 192.168.0.6 | 6032 | 1      | ProxySQL-node-1 | 3                | 161      | 14986         | 0       | 0                            | 0                          |
+-------------+------+--------+-----------------+------------------+----------+---------------+---------+------------------------------+----------------------------+
2 rows in set (0.00 sec)

配置 keepalived

安装keepalived

yum -y install curl gcc openssl-devel libnl3-devel net-snmp-devel
cd /usr/local/src/
wget https://www.keepalived.org/software/keepalived-2.0.18.tar.gz
tar zxvf keepalived-2.0.18.tar.gz
cd keepalived-2.0.18/
./configure --prefix=/usr/local/keepalived
make && make install
#复制启动和配置文件到相应的目录
cp keepalived/etc/init.d/keepalived /etc/rc.d/init.d/
cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
mkdir /etc/keepalived/
ln -s /usr/local/keepalived/sbin/keepalived /usr/sbin/

创建一个检测proxysql的检测脚本：

vim /etc/keepalived/check_proxysql.sh
#!/bin/bash
/usr/bin/netstat -na | grep -e '0.0.0.0:6033' -e '0.0.0.0:6032' &>/dev/null

proxysql-node-1（192.168.0.6）配置

vim /etc/keepalived/keepalived.conf

# Keepalive Config for ProxySQL Master
global_defs {
    # 运行Keepalived机器的一个标识
    router_id PROXYSQL_HA
    script_user root
    enable_script_security
}
vrrp_script chk_proxysql_port {
    # 检测MySQL是否存活的脚本
    script "/etc/keepalived/check_proxysql.sh"

    # 脚本执行间隔，每1s检测一次
    interval 1

    # 脚本结果导致的优先级变更，检测失败（脚本返回非0）则优先级减50
    weight -50

    #检测连续2次失败才算确定是真失败。会用weight减少优先级（1-255之间）
    fall 2

    #检测2次成功就算成功。但不修改优先级
    rise 2
}
vrrp_instance VI_1 {

    # 设置一个节点标识，并无实际作用。
    state MASTER

    # 指定虚拟ip的网卡接口
    interface ens33

    # 发送多播包的地址，设为本机网卡地址
    mcast_src_ip 192.168.0.6

    # VRRP组名，两个节点必须一致，以指明各节点同属一VRRP组
    virtual_router_id 50

    # 数字越大，优先级越高，在同一个vrrp_instance下，MASTER优先级必须大于BACKUP优先级。
    # 这样MASTER故障恢复后，就可以将VIP资源再次抢回来。
    priority 100

    # 播信息发送间隔，两个节点需一致
    advert_int 1

    # 认证方式和密码
    authentication {
        auth_type PASS
        auth_pass 1qaz2wsx
    }

    # 设置虚拟IP，两个节点需设置一样
    virtual_ipaddress {
        192.168.0.20
    }
    track_script {
        chk_proxysql_port
    }
}

proxysql-node-2（192.168.0.9）配置

vim /etc/keepalived/keepalived.conf
# Keepalive Config for ProxySQL Backup
global_defs {
    router_id PROXYSQL_HA
    script_user root
    enable_script_security
}
vrrp_script chk_proxysql_port {
    script "/etc/keepalived/check_proxysql.sh"
    interval 1
    weight -50
    fall 2
    rise 2
}
vrrp_instance VI_1 {
    state BACKUP
    interface ens33
    mcast_src_ip 192.168.0.9
    virtual_router_id 50
    priority 90
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1qaz2wsx
    }
    virtual_ipaddress {
        192.168.0.20
    }
    track_script {
        chk_proxysql_port
    }
}

启动：

/etc/init.d/keepalived start