本文是使用《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
暂无评论内容