世界上只有一种英雄主义,就是看清生活的真相之后依然热爱生活!

用 keepalived 实现 proxysql cluster 高可用

2019-07-30
158次查阅
2019/7/30

本文是使用《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

1条评论

想说点什么?