用 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配置文件,添加修改以下信息:

<span class="hljs-attr">admin_variables</span>=
<span class="hljs-attr">{</span>
<span class="hljs-attr">admin_credentials</span>=<span class="hljs-string">"admin:admin;cluster:123456"</span>
<span class="hljs-comment"># mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"</span>
<span class="hljs-attr">mysql_ifaces</span>=<span class="hljs-string">"0.0.0.0:6032"</span>
<span class="hljs-comment"># refresh_interval=2000</span>
<span class="hljs-comment"># debug=true</span>
<span class="hljs-attr">cluster_username</span>=<span class="hljs-string">"cluster"</span>
<span class="hljs-attr">cluster_password</span>=<span class="hljs-string">"123456"</span>
<span class="hljs-attr">cluster_check_interval_ms</span>=<span class="hljs-string">200</span>
<span class="hljs-attr">cluster_check_status_frequency</span>=<span class="hljs-string">100</span>
<span class="hljs-attr">cluster_mysql_query_rules_save_to_disk</span>=<span class="hljs-string">true</span>
<span class="hljs-attr">cluster_mysql_servers_save_to_disk</span>=<span class="hljs-string">true</span>
<span class="hljs-attr">cluster_mysql_users_save_to_disk</span>=<span class="hljs-string">true</span>
<span class="hljs-attr">cluster_proxysql_servers_save_to_disk</span>=<span class="hljs-string">true</span>
<span class="hljs-attr">cluster_mysql_query_rules_diffs_before_sync</span>=<span class="hljs-string">3</span>
<span class="hljs-attr">cluster_mysql_servers_diffs_before_sync</span>=<span class="hljs-string">3</span>
<span class="hljs-attr">cluster_mysql_users_diffs_before_sync</span>=<span class="hljs-string">3</span>
<span class="hljs-attr">cluster_proxysql_servers_diffs_before_sync</span>=<span class="hljs-string">3</span>
<span class="hljs-attr">}</span>
<span class="hljs-attr">proxysql_servers</span>=
<span class="hljs-attr">(</span>
<span class="hljs-attr">{</span>
<span class="hljs-attr">hostname</span>=<span class="hljs-string">"192.168.0.6"</span>
<span class="hljs-attr">port</span>=<span class="hljs-string">6032</span>
<span class="hljs-attr">weight</span>=<span class="hljs-string">1</span>
<span class="hljs-attr">comment</span>=<span class="hljs-string">"ProxySQL-node-1"</span>
<span class="hljs-attr">},</span>
<span class="hljs-attr">{</span>
<span class="hljs-attr">hostname</span>=<span class="hljs-string">"192.168.0.9"</span>
<span class="hljs-attr">port</span>=<span class="hljs-string">6032</span>
<span class="hljs-attr">weight</span>=<span class="hljs-string">1</span>
<span class="hljs-attr">comment</span>=<span class="hljs-string">"ProxySQL-node-2"</span>
<span class="hljs-attr">}</span>
<span class="hljs-attr">)</span>
<span class="hljs-attr">admin_variables</span>=
<span class="hljs-attr">{</span>
    <span class="hljs-attr">admin_credentials</span>=<span class="hljs-string">"admin:admin;cluster:123456"</span>
<span class="hljs-comment">#   mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"</span>
    <span class="hljs-attr">mysql_ifaces</span>=<span class="hljs-string">"0.0.0.0:6032"</span>
<span class="hljs-comment">#   refresh_interval=2000</span>
<span class="hljs-comment">#   debug=true</span>
    <span class="hljs-attr">cluster_username</span>=<span class="hljs-string">"cluster"</span>
    <span class="hljs-attr">cluster_password</span>=<span class="hljs-string">"123456"</span>
    <span class="hljs-attr">cluster_check_interval_ms</span>=<span class="hljs-string">200</span>
    <span class="hljs-attr">cluster_check_status_frequency</span>=<span class="hljs-string">100</span>
    <span class="hljs-attr">cluster_mysql_query_rules_save_to_disk</span>=<span class="hljs-string">true</span>
    <span class="hljs-attr">cluster_mysql_servers_save_to_disk</span>=<span class="hljs-string">true</span>
    <span class="hljs-attr">cluster_mysql_users_save_to_disk</span>=<span class="hljs-string">true</span>
    <span class="hljs-attr">cluster_proxysql_servers_save_to_disk</span>=<span class="hljs-string">true</span>
    <span class="hljs-attr">cluster_mysql_query_rules_diffs_before_sync</span>=<span class="hljs-string">3</span>
    <span class="hljs-attr">cluster_mysql_servers_diffs_before_sync</span>=<span class="hljs-string">3</span>
    <span class="hljs-attr">cluster_mysql_users_diffs_before_sync</span>=<span class="hljs-string">3</span>
    <span class="hljs-attr">cluster_proxysql_servers_diffs_before_sync</span>=<span class="hljs-string">3</span>
<span class="hljs-attr">}</span>
<span class="hljs-attr">proxysql_servers</span>=
<span class="hljs-attr">(</span>
    <span class="hljs-attr">{</span>
        <span class="hljs-attr">hostname</span>=<span class="hljs-string">"192.168.0.6"</span>
        <span class="hljs-attr">port</span>=<span class="hljs-string">6032</span>
        <span class="hljs-attr">weight</span>=<span class="hljs-string">1</span>
        <span class="hljs-attr">comment</span>=<span class="hljs-string">"ProxySQL-node-1"</span>
    <span class="hljs-attr">},</span>
    <span class="hljs-attr">{</span>
        <span class="hljs-attr">hostname</span>=<span class="hljs-string">"192.168.0.9"</span>
        <span class="hljs-attr">port</span>=<span class="hljs-string">6032</span>
        <span class="hljs-attr">weight</span>=<span class="hljs-string">1</span>
        <span class="hljs-attr">comment</span>=<span class="hljs-string">"ProxySQL-node-2"</span>
    <span class="hljs-attr">}</span>
<span class="hljs-attr">)</span>
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
<span class="hljs-comment"># Keepalive Config for ProxySQL Master</span>
<span class="hljs-attr">global_defs</span> <span class="hljs-string">{</span>
<span class="hljs-comment"> # 运行Keepalived机器的一个标识</span>
<span class="hljs-attr">router_id</span> <span class="hljs-string">PROXYSQL_HA</span>
<span class="hljs-attr">script_user</span> <span class="hljs-string">root</span>
<span class="hljs-attr">enable_script_security</span>
<span class="hljs-attr">}</span>
<span class="hljs-attr">vrrp_script</span> <span class="hljs-string">chk_proxysql_port {</span>
<span class="hljs-comment"> # 检测MySQL是否存活的脚本</span>
<span class="hljs-attr">script</span> <span class="hljs-string">"/etc/keepalived/check_proxysql.sh"</span>
<span class="hljs-comment">
# 脚本执行间隔,每1s检测一次</span>
<span class="hljs-attr">interval</span> <span class="hljs-string">1</span>
<span class="hljs-comment">
# 脚本结果导致的优先级变更,检测失败(脚本返回非0)则优先级减50</span>
<span class="hljs-attr">weight</span> <span class="hljs-string">-50</span>
<span class="hljs-comment">
#检测连续2次失败才算确定是真失败。会用weight减少优先级(1-255之间)</span>
<span class="hljs-attr">fall</span> <span class="hljs-string">2</span>
<span class="hljs-comment">
#检测2次成功就算成功。但不修改优先级</span>
<span class="hljs-attr">rise</span> <span class="hljs-string">2</span>
<span class="hljs-attr">}</span>
<span class="hljs-attr">vrrp_instance</span> <span class="hljs-string">VI_1 {</span>
<span class="hljs-comment">
# 设置一个节点标识,并无实际作用。</span>
<span class="hljs-attr">state</span> <span class="hljs-string">MASTER</span>
<span class="hljs-comment">
# 指定虚拟ip的网卡接口</span>
<span class="hljs-attr">interface</span> <span class="hljs-string">ens33</span>
<span class="hljs-comment">
# 发送多播包的地址,设为本机网卡地址</span>
<span class="hljs-attr">mcast_src_ip</span> <span class="hljs-string">192.168.0.6</span>
<span class="hljs-comment">
# VRRP组名,两个节点必须一致,以指明各节点同属一VRRP组</span>
<span class="hljs-attr">virtual_router_id</span> <span class="hljs-string">50</span>
<span class="hljs-comment">
# 数字越大,优先级越高,在同一个vrrp_instance下,MASTER优先级必须大于BACKUP优先级。</span>
<span class="hljs-comment"> # 这样MASTER故障恢复后,就可以将VIP资源再次抢回来。</span>
<span class="hljs-attr">priority</span> <span class="hljs-string">100</span>
<span class="hljs-comment">
# 播信息发送间隔,两个节点需一致</span>
<span class="hljs-attr">advert_int</span> <span class="hljs-string">1</span>
<span class="hljs-comment">
# 认证方式和密码</span>
<span class="hljs-attr">authentication</span> <span class="hljs-string">{</span>
<span class="hljs-attr">auth_type</span> <span class="hljs-string">PASS</span>
<span class="hljs-attr">auth_pass</span> <span class="hljs-string">1qaz2wsx</span>
<span class="hljs-attr">}</span>
<span class="hljs-comment">
# 设置虚拟IP,两个节点需设置一样</span>
<span class="hljs-attr">virtual_ipaddress</span> <span class="hljs-string">{</span>
<span class="hljs-attr">192.168.0.20</span>
<span class="hljs-attr">}</span>
<span class="hljs-attr">track_script</span> <span class="hljs-string">{</span>
<span class="hljs-attr">chk_proxysql_port</span>
<span class="hljs-attr">}</span>
<span class="hljs-attr">}</span>
<span class="hljs-comment"># Keepalive Config for ProxySQL Master</span>
<span class="hljs-attr">global_defs</span> <span class="hljs-string">{</span>
<span class="hljs-comment">    # 运行Keepalived机器的一个标识</span>
    <span class="hljs-attr">router_id</span> <span class="hljs-string">PROXYSQL_HA</span>
    <span class="hljs-attr">script_user</span> <span class="hljs-string">root</span>
    <span class="hljs-attr">enable_script_security</span>
<span class="hljs-attr">}</span>
<span class="hljs-attr">vrrp_script</span> <span class="hljs-string">chk_proxysql_port {</span>
<span class="hljs-comment">    # 检测MySQL是否存活的脚本</span>
    <span class="hljs-attr">script</span> <span class="hljs-string">"/etc/keepalived/check_proxysql.sh"</span>
<span class="hljs-comment">
    # 脚本执行间隔,每1s检测一次</span>
    <span class="hljs-attr">interval</span> <span class="hljs-string">1</span>
<span class="hljs-comment">
    # 脚本结果导致的优先级变更,检测失败(脚本返回非0)则优先级减50</span>
    <span class="hljs-attr">weight</span> <span class="hljs-string">-50</span>
<span class="hljs-comment">
    #检测连续2次失败才算确定是真失败。会用weight减少优先级(1-255之间)</span>
    <span class="hljs-attr">fall</span> <span class="hljs-string">2</span>
<span class="hljs-comment">
    #检测2次成功就算成功。但不修改优先级</span>
    <span class="hljs-attr">rise</span> <span class="hljs-string">2</span>
<span class="hljs-attr">}</span>
<span class="hljs-attr">vrrp_instance</span> <span class="hljs-string">VI_1 {</span>
<span class="hljs-comment">
    # 设置一个节点标识,并无实际作用。</span>
    <span class="hljs-attr">state</span> <span class="hljs-string">MASTER</span>
<span class="hljs-comment">
    # 指定虚拟ip的网卡接口</span>
    <span class="hljs-attr">interface</span> <span class="hljs-string">ens33</span>
<span class="hljs-comment">
    # 发送多播包的地址,设为本机网卡地址</span>
    <span class="hljs-attr">mcast_src_ip</span> <span class="hljs-string">192.168.0.6</span>
<span class="hljs-comment">
    # VRRP组名,两个节点必须一致,以指明各节点同属一VRRP组</span>
    <span class="hljs-attr">virtual_router_id</span> <span class="hljs-string">50</span>
<span class="hljs-comment">
    # 数字越大,优先级越高,在同一个vrrp_instance下,MASTER优先级必须大于BACKUP优先级。</span>
<span class="hljs-comment">    # 这样MASTER故障恢复后,就可以将VIP资源再次抢回来。</span>
    <span class="hljs-attr">priority</span> <span class="hljs-string">100</span>
<span class="hljs-comment">
    # 播信息发送间隔,两个节点需一致</span>
    <span class="hljs-attr">advert_int</span> <span class="hljs-string">1</span>
<span class="hljs-comment">
    # 认证方式和密码</span>
    <span class="hljs-attr">authentication</span> <span class="hljs-string">{</span>
        <span class="hljs-attr">auth_type</span> <span class="hljs-string">PASS</span>
        <span class="hljs-attr">auth_pass</span> <span class="hljs-string">1qaz2wsx</span>
    <span class="hljs-attr">}</span>
<span class="hljs-comment">
    # 设置虚拟IP,两个节点需设置一样</span>
    <span class="hljs-attr">virtual_ipaddress</span> <span class="hljs-string">{</span>
        <span class="hljs-attr">192.168.0.20</span>
    <span class="hljs-attr">}</span>
    <span class="hljs-attr">track_script</span> <span class="hljs-string">{</span>
        <span class="hljs-attr">chk_proxysql_port</span>
    <span class="hljs-attr">}</span>
<span class="hljs-attr">}</span>
# 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)配置

<span class="hljs-attr">vim</span> <span class="hljs-string">/etc/keepalived/keepalived.conf</span>
<span class="hljs-comment"># Keepalive Config for ProxySQL Backup</span>
<span class="hljs-attr">global_defs</span> <span class="hljs-string">{</span>
<span class="hljs-attr">router_id</span> <span class="hljs-string">PROXYSQL_HA</span>
<span class="hljs-attr">script_user</span> <span class="hljs-string">root</span>
<span class="hljs-attr">enable_script_security</span>
<span class="hljs-attr">}</span>
<span class="hljs-attr">vrrp_script</span> <span class="hljs-string">chk_proxysql_port {</span>
<span class="hljs-attr">script</span> <span class="hljs-string">"/etc/keepalived/check_proxysql.sh"</span>
<span class="hljs-attr">interval</span> <span class="hljs-string">1</span>
<span class="hljs-attr">weight</span> <span class="hljs-string">-50</span>
<span class="hljs-attr">fall</span> <span class="hljs-string">2</span>
<span class="hljs-attr">rise</span> <span class="hljs-string">2</span>
<span class="hljs-attr">}</span>
<span class="hljs-attr">vrrp_instance</span> <span class="hljs-string">VI_1 {</span>
<span class="hljs-attr">state</span> <span class="hljs-string">BACKUP</span>
<span class="hljs-attr">interface</span> <span class="hljs-string">ens33</span>
<span class="hljs-attr">mcast_src_ip</span> <span class="hljs-string">192.168.0.9</span>
<span class="hljs-attr">virtual_router_id</span> <span class="hljs-string">50</span>
<span class="hljs-attr">priority</span> <span class="hljs-string">90</span>
<span class="hljs-attr">advert_int</span> <span class="hljs-string">1</span>
<span class="hljs-attr">authentication</span> <span class="hljs-string">{</span>
<span class="hljs-attr">auth_type</span> <span class="hljs-string">PASS</span>
<span class="hljs-attr">auth_pass</span> <span class="hljs-string">1qaz2wsx</span>
<span class="hljs-attr">}</span>
<span class="hljs-attr">virtual_ipaddress</span> <span class="hljs-string">{</span>
<span class="hljs-attr">192.168.0.20</span>
<span class="hljs-attr">}</span>
<span class="hljs-attr">track_script</span> <span class="hljs-string">{</span>
<span class="hljs-attr">chk_proxysql_port</span>
<span class="hljs-attr">}</span>
<span class="hljs-attr">}</span>
<span class="hljs-attr">vim</span> <span class="hljs-string">/etc/keepalived/keepalived.conf</span>
<span class="hljs-comment"># Keepalive Config for ProxySQL Backup</span>
<span class="hljs-attr">global_defs</span> <span class="hljs-string">{</span>
    <span class="hljs-attr">router_id</span> <span class="hljs-string">PROXYSQL_HA</span>
    <span class="hljs-attr">script_user</span> <span class="hljs-string">root</span>
    <span class="hljs-attr">enable_script_security</span>
<span class="hljs-attr">}</span>
<span class="hljs-attr">vrrp_script</span> <span class="hljs-string">chk_proxysql_port {</span>
    <span class="hljs-attr">script</span> <span class="hljs-string">"/etc/keepalived/check_proxysql.sh"</span>
    <span class="hljs-attr">interval</span> <span class="hljs-string">1</span>
    <span class="hljs-attr">weight</span> <span class="hljs-string">-50</span>
    <span class="hljs-attr">fall</span> <span class="hljs-string">2</span>
    <span class="hljs-attr">rise</span> <span class="hljs-string">2</span>
<span class="hljs-attr">}</span>
<span class="hljs-attr">vrrp_instance</span> <span class="hljs-string">VI_1 {</span>
    <span class="hljs-attr">state</span> <span class="hljs-string">BACKUP</span>
    <span class="hljs-attr">interface</span> <span class="hljs-string">ens33</span>
    <span class="hljs-attr">mcast_src_ip</span> <span class="hljs-string">192.168.0.9</span>
    <span class="hljs-attr">virtual_router_id</span> <span class="hljs-string">50</span>
    <span class="hljs-attr">priority</span> <span class="hljs-string">90</span>
    <span class="hljs-attr">advert_int</span> <span class="hljs-string">1</span>
    <span class="hljs-attr">authentication</span> <span class="hljs-string">{</span>
        <span class="hljs-attr">auth_type</span> <span class="hljs-string">PASS</span>
        <span class="hljs-attr">auth_pass</span> <span class="hljs-string">1qaz2wsx</span>
    <span class="hljs-attr">}</span>
    <span class="hljs-attr">virtual_ipaddress</span> <span class="hljs-string">{</span>
        <span class="hljs-attr">192.168.0.20</span>
    <span class="hljs-attr">}</span>
    <span class="hljs-attr">track_script</span> <span class="hljs-string">{</span>
        <span class="hljs-attr">chk_proxysql_port</span>
    <span class="hljs-attr">}</span>
<span class="hljs-attr">}</span>
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
© 版权声明
THE END
喜欢就支持一下吧
点赞0 分享
评论 抢沙发

请登录后发表评论

    暂无评论内容