ProxySQL 集群中节点间配置如何快速同步,减少繁琐的切换节点敲击命令?一种方法是使用其自带的Cluster模块,该模块经过测试基本可用,即使不同步也不会带来大的问题,只需通过监控发现后,手工处理一下即可;另一种方法是直接传输proxysql.db数据库文件到另一个节点,然后执行load… to mem;load … to run即可。这种方式似乎更便捷!实际上我们还可以写成脚本,使这一操作更加自动化。

使用proxysql01节点为主配置节点。其连接其它的节点都配置成免秘钥ssh连接,然后写一个简单的sync.sh文件。

1
2
3
4
5
6
7
8
9
10
11
12
13
#!/bin/bash

#the remote proxysql host,if you have many, try to use a loop.
proxysql_host=10.0.1.111

#the table to load
tbl=$1

echo "Copy proxysql.db to remote ..."
scp /dbfiles/proxysql/data/proxysql.db root@${proxysql_host}:/dbfiles/proxysql/data/

echo "flush configdb"
ssh root@${proxysql_host} "/dbfiles/proxysql/flush_configdb.sh $tbl"

该脚本调用了proxysql02节点上的一个flush_configdb.sh脚本。这个脚本的内容如下:

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
#!/bin/bash
tbl=$1

admin='mysql -uadmin -padmin -h127.0.0.1 -P6032'

$admin -e "PROXYSQL FLUSH CONFIGDB;"

if [ ! "$tbl" ];then
echo "load all tables to memory and run..."
$admin -e "LOAD MYSQL USERS TO MEM; LOAD MYSQL USERS TO RUN;"
$admin -e "LOAD MYSQL SERVERS TO MEM; LOAD MYSQL SERVERS TO RUNTIME;"
$admin -e "LOAD MYSQL QUERY RULES TO MEM; LOAD MYSQL QUERY RULES TO RUNTIME;"
$admin -e "LOAD MYSQL VARIABLES TO MEM; LOAD MYSQL VARIABLES TO RUNTIME;"
$admin -e "LOAD ADMIN VARIABLES TO MEM; LOAD ADMIN VARIABLES TO RUNTIME;"
echo "done"
elif [ "$tbl" = "mysql_users" ];then
echo "load mysql_users to run..."
$admin -e "LOAD MYSQL USERS TO MEM; LOAD MYSQL USERS TO RUN;"
echo "done"
elif [ "$tbl" = "mysql_servers" ];then
echo "load mysql_servers to run"
$admin -e "LOAD MYSQL SERVERS TO MEM; LOAD MYSQL SERVERS TO RUNTIME;"
echo "done"
elif [ "$tbl" = "mysql_query_rules" ];then
echo "load mysql query rules to run..."
$admin -e "LOAD MYSQL QUERY RULES TO MEM; LOAD MYSQL QUERY RULES TO RUNTIME;"
echo "done"
elif [ "$tbl" = "mysql_variables" ];then
echo "load mysql variables to run..."
$admin -e "LOAD MYSQL VARIABLES TO MEM; LOAD MYSQL VARIABLES TO RUNTIME;"
echo "done"
elif [ "$tbl" = "admin_variables" ];then
echo "load admin variables to run..."
$admin -e "LOAD ADMIN VARIABLES TO MEM; LOAD ADMIN VARIABLES TO RUNTIME;"
echo "done"
else
echo "Para:$tbl not accepted!"
fi

sync.sh把proxysql.db传输给proxysql02节点的datadir,替换原来的proxysql.db. flush_configdb.sh则执行一些load 命令。它首先执行PROXYSQL FLUSH CONFIGDB;使proxysql进程与该数据文件关联。然后根据传入的参数,执行load命令使配置生效。
如果不想执行这些load命令,还可以执行命令proxysql restart,重启proxysql. proxysql的重启非常快,只需几毫秒,监控都来不及告警。