官方文档----ProxySQL 1.4.2 现在支持原生集群！！！
官方文档地址：https://proxysql.com/blog/proxysql-cluster/
前言
ProxySQL 是一个去中心化的代理，建议靠近应用部署。这种方法甚至可以很好地扩展到数百个节点，因为它被设计为在运行时很容易重新配置。
这允许使用 Ansible/Chef/Puppet/Salt 等软件（按字母顺序）或 Etcd/Consul/ZooKeeper 等服务发现工具简单地配置 ProxySQL 实例群。
此外，ProxySQL 是高度可定制的，可以在任何使用这些技术的设置中采用，甚至可以在自制工具中采用。
然而，该解决方案有其缺点：
- 其配置需要外部软件（即配置管理软件）
- 多个实例无法本地同步其配置
- 收敛时间不可预测
- 没有针对脑裂（网络分区）的保护
为了解决上述问题，ProxySQL 1.4.2 中引入了新功能以支持原生集群。
正如我们已经指出的那样，这些功能是实验性的，可能会发生变化，尤其是因为路线图中的所有功能尚未实现。
设置 3 节点 ProxySQL 集群
我们将首先建立一个只有 3 个节点的集群。
我们将使用以下引导配置文件/etc/proxysql.cnf。
管理变量将主要设置为默认值，我们在任何情况下都指定它们以枚举新的集群变量。需要注意的重要一点是cluster_username和变量应指定也在中列出的cluster_password凭据。实际上，可以包含多组凭据。admin_credentialsadmin_credentials
datadir="/var/lib/proxysql"
admin_variables =
{
admin_credentials="admin:admin;cluster1:secret1pass"
mysql_ifaces="0.0.0.0:6032"
cluster_username="cluster1"
cluster_password="secret1pass"
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="172.16.3.130"
port=6032
comment="proxysql130"
},
{
hostname="172.16.3.131"
port=6032
comment="proxysql131"
},
{
hostname="172.16.3.132"
port=6032
comment="proxysql132"
}
)
在这个阶段，我们可以在所有 3 个节点上启动 proxysql。正如文档中所指出的，当集群处于活动状态时，proxysql 会为每个活动模块的配置生成校验和。
让我们连接到任何 proxysql 管理界面，并检查当前的配置校验和：
Admin130> SELECT * FROM runtime_checksums_values ORDER BY name;
+-------------------+---------+------------+--------------------+
| name | version | epoch | checksum |
+-------------------+---------+------------+--------------------+
| admin_variables | 0 | 0 | |
| mysql_query_rules | 1 | 1504615779 | 0x0000000000000000 |
| mysql_servers | 1 | 1504615779 | 0x0000000000000000 |
| mysql_users | 1 | 1504615779 | 0x0000000000000000 |
| mysql_variables | 0 | 0 | |
| proxysql_servers | 1 | 1504615779 | 0x474020F334F98128 |
+-------------------+---------+------------+--------------------+
6 rows in set (0.00 sec)
请注意，目前只有 4 个模块处于活动状态：
- mysql_query_rules
- mysql_servers
- mysql_users
- proxysql_servers
未激活的模块具有version=0.
具有空配置的模块具有零值校验和。
Epoch 是一个 unix 时间戳。
Admin130> SELECT name,FROM_UNIXTIME(epoch), checksum FROM runtime_checksums_values WHERE version ORDER BY name;
+-------------------+----------------------+--------------------+
| name | FROM_UNIXTIME(epoch) | checksum |
+-------------------+----------------------+--------------------+
| mysql_query_rules | 2017-09-05 12:49:39 | 0x0000000000000000 |
| mysql_servers | 2017-09-05 12:49:39 | 0x0000000000000000 |
| mysql_users | 2017-09-05 12:49:39 | 0x0000000000000000 |
| proxysql_servers | 2017-09-05 12:49:39 | 0x474020F334F98128 |
+-------------------+----------------------+--------------------+
4 rows in set (0.00 sec)
要查看整个集群的状态，我们需要检查表stats_proxysql_servers_checksums：
Admin130> SELECT * FROM stats_proxysql_servers_checksums;
+--------------+------+-------------------+---------+------------+--------------------+------------+------------+------------+
| hostname | port | name | version | epoch | checksum | changed_at | updated_at | diff_check |
+--------------+------+-------------------+---------+------------+--------------------+------------+------------+------------+
| 172.16.3.132 | 6032 | admin_variables | 0 | 0 | | 0 | 1504618520 | 0 |
| 172.16.3.132 | 6032 | mysql_query_rules | 1 | 1504615782 | 0x0000000000000000 | 1504615782 | 1504618520 | 0 |
| 172.16.3.132 | 6032 | mysql_servers | 1 | 1504615782 | 0x0000000000000000 | 1504615782 | 1504618520 | 0 |
| 172.16.3.132 | 6032 | mysql_users | 1 | 1504615782 | 0x0000000000000000 | 1504615782 | 1504618520 | 0 |
| 172.16.3.132 | 6032 | mysql_variables | 0 | 0 | | 0 | 1504618520 | 0 |
| 172.16.3.132 | 6032 | proxysql_servers | 1 | 1504615782 | 0x474020F334F98128 | 1504615782 | 1504618520 | 0 |
| 172.16.3.131 | 6032 | admin_variables | 0 | 0 | | 0 | 1504618520 | 0 |
| 172.16.3.131 | 6032 | mysql_query_rules | 1 | 1504615780 | 0x0000000000000000 | 1504615781 | 1504618520 | 0 |
| 172.16.3.131 | 6032 | mysql_servers | 1 | 1504615780 | 0x0000000000000000 | 1504615781 | 1504618520 | 0 |
| 172.16.3.131 | 6032 | mysql_users | 1 | 1504615780 | 0x0000000000000000 | 1504615781 | 1504618520 | 0 |
| 172.16.3.131 | 6032 | mysql_variables | 0 | 0 | | 0 | 1504618520 | 0 |
| 172.16.3.131 | 6032 | proxysql_servers | 1 | 1504615780 | 0x474020F334F98128 | 1504615781 | 1504618520 | 0 |
| 172.16.3.130 | 6032 | admin_variables | 0 | 0 | | 0 | 1504618520 | 0 |
| 172.16.3.130 | 6032 | mysql_query_rules | 1 | 1504615779 | 0x0000000000000000 | 1504615780 | 1504618520 | 0 |
| 172.16.3.130 | 6032 | mysql_servers | 1 | 1504615779 | 0x0000000000000000 | 1504615780 | 1504618520 | 0 |
| 172.16.3.130 | 6032 | mysql_users | 1 | 1504615779 | 0x0000000000000000 | 1504615780 | 1504618520 | 0 |
| 172.16.3.130 | 6032 | mysql_variables | 0 | 0 | | 0 | 1504618520 | 0 |
| 172.16.3.130 | 6032 | proxysql_servers | 1 | 1504615779 | 0x474020F334F98128 | 1504615780 | 1504618520 | 0 |
+--------------+------+-------------------+---------+------------+--------------------+------------+------------+------------+
18 rows in set (0.00 sec)
不活动的模块有version=0，空配置也有零值，时间戳是“unix时间戳”：
Admin130> SELECT hostname, port, name, version, FROM_UNIXTIME(epoch) epoch, checksum, FROM_UNIXTIME(changed_at) changed_at, FROM_UNIXTIME(updated_at) updated_at, diff_check, DATETIME('NOW') FROM stats_proxysql_servers_checksums WHERE version > 0 ORDER BY hostname, name;
+--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| hostname | port | name | version | epoch | checksum | changed_at | updated_at | diff_check | DATETIME('NOW') |
+--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| 172.16.3.130 | 6032 | mysql_query_rules | 1 | 2017-09-05 12:49:39 | 0x0000000000000000 | 2017-09-05 12:49:40 | 2017-09-05 13:44:48 | 0 | 2017-09-05 13:44:48 |
| 172.16.3.130 | 6032 | mysql_servers | 1 | 2017-09-05 12:49:39 | 0x0000000000000000 | 2017-09-05 12:49:40 | 2017-09-05 13:44:48 | 0 | 2017-09-05 13:44:48 |
| 172.16.3.130 | 6032 | mysql_users | 1 | 2017-09-05 12:49:39 | 0x0000000000000000 | 2017-09-05 12:49:40 | 2017-09-05 13:44:48 | 0 | 2017-09-05 13:44:48 |
| 172.16.3.130 | 6032 | proxysql_servers | 1 | 2017-09-05 12:49:39 | 0x474020F334F98128 | 2017-09-05 12:49:40 | 2017-09-05 13:44:48 | 0 | 2017-09-05 13:44:48 |
| 172.16.3.131 | 6032 | mysql_query_rules | 1 | 2017-09-05 12:49:40 | 0x0000000000000000 | 2017-09-05 12:49:41 | 2017-09-05 13:44:48 | 0 | 2017-09-05 13:44:48 |
| 172.16.3.131 | 6032 | mysql_servers | 1 | 2017-09-05 12:49:40 | 0x0000000000000000 | 2017-09-05 12:49:41 | 2017-09-05 13:44:48 | 0 | 2017-09-05 13:44:48 |
| 172.16.3.131 | 6032 | mysql_users | 1 | 2017-09-05 12:49:40 | 0x0000000000000000 | 2017-09-05 12:49:41 | 2017-09-05 13:44:48 | 0 | 2017-09-05 13:44:48 |
| 172.16.3.131 | 6032 | proxysql_servers | 1 | 2017-09-05 12:49:40 | 0x474020F334F98128 | 2017-09-05 12:49:41 | 2017-09-05 13:44:48 | 0 | 2017-09-05 13:44:48 |
| 172.16.3.132 | 6032 | mysql_query_rules | 1 | 2017-09-05 12:49:42 | 0x0000000000000000 | 2017-09-05 12:49:42 | 2017-09-05 13:44:48 | 0 | 2017-09-05 13:44:48 |
| 172.16.3.132 | 6032 | mysql_servers | 1 | 2017-09-05 12:49:42 | 0x0000000000000000 | 2017-09-05 12:49:42 | 2017-09-05 13:44:48 | 0 | 2017-09-05 13:44:48 |
| 172.16.3.132 | 6032 | mysql_users | 1 | 2017-09-05 12:49:42 | 0x0000000000000000 | 2017-09-05 12:49:42 | 2017-09-05 13:44:48 | 0 | 2017-09-05 13:44:48 |
| 172.16.3.132 | 6032 | proxysql_servers | 1 | 2017-09-05 12:49:42 | 0x474020F334F98128 | 2017-09-05 12:49:42 | 2017-09-05 13:44:48 | 0 | 2017-09-05 13:44:48 |
+--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
12 rows in set (0.00 sec)
澄清一些领域：
epoch是远程代理执行时产生的时间戳LOAD ... TO RUNTIME
changed_at是本地代理检测到远程代理发生变化时记录的时间戳
updated_at是本地代理从远程代理检索校验和的最后一个时间戳
添加新用户
现在我们已经启动并运行了 ProxySQL 集群，让我们看看配置是如何从单个节点传播到所有其他节点的。
让我们首先在其中一个节点上添加一个新用户。
Admin130> SELECT * FROM mysql_users;
Empty set (0.00 sec)
Admin130> INSERT INTO mysql_users(username,password) VALUES ('sbtest','sbtest');
Query OK, 1 row affected (0.00 sec)
Admin130> SELECT * FROM mysql_users;
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| sbtest | sbtest | 1 | 0 | 0 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
1 row in set (0.00 sec)
Admin130> SELECT * FROM runtime_mysql_users;
Empty set (0.00 sec)
从上面的例子我们可以看到，运行时没有加载用户，我们只是在内存中创建了一个新用户。
是时候将它加载到运行时，看看接下来会发生什么：
Admin130> LOAD MYSQL USERS TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
Admin> SELECT hostname, port, name, version, FROM_UNIXTIME(epoch) epoch, checksum, FROM_UNIXTIME(changed_at) changed_at, FROM_UNIXTIME(updated_at) updated_at, diff_check, DATETIME('NOW') FROM stats_proxysql_servers_checksums WHERE version > 0 ORDER BY hostname, name;
+--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| hostname | port | name | version | epoch | checksum | changed_at | updated_at | diff_check | DATETIME('NOW') |
+--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| 172.16.3.130 | 6032 | mysql_query_rules | 1 | 2017-09-05 12:49:39 | 0x0000000000000000 | 2017-09-05 12:49:40 | 2017-09-05 13:47:52 | 0 | 2017-09-05 13:47:53 |
| 172.16.3.130 | 6032 | mysql_servers | 1 | 2017-09-05 12:49:39 | 0x0000000000000000 | 2017-09-05 12:49:40 | 2017-09-05 13:47:52 | 0 | 2017-09-05 13:47:53 |
| 172.16.3.130 | 6032 | mysql_users | 2 | 2017-09-05 13:47:48 | 0x7917CD487C11478F | 2017-09-05 13:47:48 | 2017-09-05 13:47:52 | 0 | 2017-09-05 13:47:53 |
| 172.16.3.130 | 6032 | proxysql_servers | 1 | 2017-09-05 12:49:39 | 0x474020F334F98128 | 2017-09-05 12:49:40 | 2017-09-05 13:47:52 | 0 | 2017-09-05 13:47:53 |
| 172.16.3.131 | 6032 | mysql_query_rules | 1 | 2017-09-05 12:49:40 | 0x0000000000000000 | 2017-09-05 12:49:41 | 2017-09-05 13:47:53 | 0 | 2017-09-05 13:47:53 |
| 172.16.3.131 | 6032 | mysql_servers | 1 | 2017-09-05 12:49:40 | 0x0000000000000000 | 2017-09-05 12:49:41 | 2017-09-05 13:47:53 | 0 | 2017-09-05 13:47:53 |
| 172.16.3.131 | 6032 | mysql_users | 2 | 2017-09-05 13:47:48 | 0x7917CD487C11478F | 2017-09-05 13:47:49 | 2017-09-05 13:47:53 | 0 | 2017-09-05 13:47:53 |
| 172.16.3.131 | 6032 | proxysql_servers | 1 | 2017-09-05 12:49:40 | 0x474020F334F98128 | 2017-09-05 12:49:41 | 2017-09-05 13:47:53 | 0 | 2017-09-05 13:47:53 |
| 172.16.3.132 | 6032 | mysql_query_rules | 1 | 2017-09-05 12:49:42 | 0x0000000000000000 | 2017-09-05 12:49:42 | 2017-09-05 13:47:53 | 0 | 2017-09-05 13:47:53 |
| 172.16.3.132 | 6032 | mysql_servers | 1 | 2017-09-05 12:49:42 | 0x0000000000000000 | 2017-09-05 12:49:42 | 2017-09-05 13:47:53 | 0 | 2017-09-05 13:47:53 |
| 172.16.3.132 | 6032 | mysql_users | 2 | 2017-09-05 13:47:48 | 0x7917CD487C11478F | 2017-09-05 13:47:49 | 2017-09-05 13:47:53 | 0 | 2017-09-05 13:47:53 |
| 172.16.3.132 | 6032 | proxysql_servers | 1 | 2017-09-05 12:49:42 | 0x474020F334F98128 | 2017-09-05 12:49:42 | 2017-09-05 13:47:53 | 0 | 2017-09-05 13:47:53 |
+--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
12 rows in set (0.00 sec)
Admin130> SELECT hostname, port, name, version, FROM_UNIXTIME(epoch) epoch, checksum, FROM_UNIXTIME(changed_at) changed_at, FROM_UNIXTIME(updated_at) updated_at, diff_check, DATETIME('NOW') FROM stats_proxysql_servers_checksums WHERE name='mysql_users' ORDER BY hostname, name;
+--------------+------+-------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| hostname | port | name | version | epoch | checksum | changed_at | updated_at | diff_check | DATETIME('NOW') |
+--------------+------+-------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| 172.16.3.130 | 6032 | mysql_users | 2 | 2017-09-05 13:47:48 | 0x7917CD487C11478F | 2017-09-05 13:47:48 | 2017-09-05 13:48:21 | 0 | 2017-09-05 13:48:21 |
| 172.16.3.131 | 6032 | mysql_users | 2 | 2017-09-05 13:47:48 | 0x7917CD487C11478F | 2017-09-05 13:47:49 | 2017-09-05 13:48:21 | 0 | 2017-09-05 13:48:21 |
| 172.16.3.132 | 6032 | mysql_users | 2 | 2017-09-05 13:47:48 | 0x7917CD487C11478F | 2017-09-05 13:47:49 | 2017-09-05 13:48:21 | 0 | 2017-09-05 13:48:21 |
+--------------+------+-------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
3 rows in set (0.00 sec)
我们在 172.16.3.130 上应用了更改，并且更改几乎立即传播到其他任何地方。
检查 172.16.3.131 上的日志，我们可以看到同步期间生成的日志条目：
2017-09-05 14:47:48 [INFO] Cluster: detected a new checksum for mysql_users from peer 172.16.3.130:6032, version 2, epoch 1504619268, checksum 0x7917CD487C11478F . Not syncing yet ...
2017-09-05 14:47:48 [INFO] Cluster: detected a peer 172.16.3.130:6032 with mysql_users version 2, epoch 1504619268, diff_check 3\. Own version: 1, epoch: 1504615780\. Proceeding with remote sync
2017-09-05 14:47:48 [INFO] Cluster: detected a peer 172.16.3.130:6032 with mysql_users version 2, epoch 1504619268, diff_check 4\. Own version: 1, epoch: 1504615780\. Proceeding with remote sync
2017-09-05 14:47:48 [INFO] Cluster: detected peer 172.16.3.130:6032 with mysql_users version 2, epoch 1504619268
2017-09-05 14:47:48 [INFO] Cluster: Fetching MySQL Users from peer 172.16.3.130:6032 started
2017-09-05 14:47:48 [INFO] Cluster: Fetching MySQL Users from peer 172.16.3.130:6032 completed
2017-09-05 14:47:48 [INFO] Cluster: Loading to runtime MySQL Users from peer 172.16.3.130:6032
2017-09-05 14:47:48 [INFO] Cluster: Saving to disk MySQL Query Rules from peer 172.16.3.130:6032
2017-09-05 14:47:48 [INFO] Cluster: detected a new checksum for mysql_users from peer 172.16.3.132:6032, version 2, epoch 1504619268, checksum 0x7917CD487C11478F . Not syncing yet ...
2017-09-05 14:47:48 [INFO] Cluster: checksum for mysql_users from peer 172.16.3.132:6032 matches with local checksum 0x7917CD487C11478F , we won't sync.
2017-09-05 14:47:49 [INFO] Cluster: detected a new checksum for mysql_users from peer 172.16.3.131:6032, version 2, epoch 1504619268, checksum 0x7917CD487C11478F . Not syncing yet ...
2017-09-05 14:47:49 [INFO] Cluster: checksum for mysql_users from peer 172.16.3.131:6032 matches with local checksum 0x7917CD487C11478F , we won't sync.
发生了什么？
ProxySQL 检测到表的校验和发生变化mysql_users
当diff_check达到阈值时，同步过程被初始化
MySQL 用户从 172.16.3.130:6032 获取
MySQL 用户加载到运行时
MySQL 用户也被保存到磁盘，因为cluster_mysql_users_save_to_disk=true
还从 172.16.3.132:6032 检测到新的校验和，但它与本地校验和匹配，因此无需同步
还从 172.16.3.131:6032 （即同一个 proxysql 实例）检测到一个新的校验和，但它与本地校验和匹配，因此无需再次同步
现在让我们直接在 172.16.3.131:6032（一个不同的节点）上添加一个新用户。
我们首先验证存在哪些用户，然后添加一个新用户：
Admin131> SELECT hostname, port, name, version, FROM_UNIXTIME(epoch) epoch, checksum, FROM_UNIXTIME(changed_at) changed_at, FROM_UNIXTIME(updated_at) updated_at, diff_check, DATETIME('NOW') FROM stats_proxysql_servers_checksums WHERE name='mysql_users' ORDER BY hostname, name;
+--------------+------+-------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| hostname | port | name | version | epoch | checksum | changed_at | updated_at | diff_check | DATETIME('NOW') |
+--------------+------+-------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| 172.16.3.130 | 6032 | mysql_users | 2 | 2017-09-05 13:47:48 | 0x7917CD487C11478F | 2017-09-05 13:47:48 | 2017-09-05 14:52:32 | 0 | 2017-09-05 14:52:32 |
| 172.16.3.131 | 6032 | mysql_users | 2 | 2017-09-05 13:47:48 | 0x7917CD487C11478F | 2017-09-05 13:47:49 | 2017-09-05 14:52:32 | 0 | 2017-09-05 14:52:32 |
| 172.16.3.132 | 6032 | mysql_users | 2 | 2017-09-05 13:47:48 | 0x7917CD487C11478F | 2017-09-05 13:47:48 | 2017-09-05 14:52:32 | 0 | 2017-09-05 14:52:32 |
+--------------+------+-------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
3 rows in set (0.00 sec)
Admin131> SELECT * FROM mysql_users;
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| sbtest | *2AFD99E79E4AA23DE141540F4179F64FFB3AC521 | 1 | 0 | 0 | | 0 | 1 | 0 | 0 | 1 | 10000 |
| sbtest | *2AFD99E79E4AA23DE141540F4179F64FFB3AC521 | 1 | 0 | 0 | | 0 | 1 | 0 | 1 | 0 | 10000 |
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
2 rows in set (0.00 sec)
Admin131> INSERT INTO mysql_users (username,password) VALUES ('user1','password1');
Query OK, 1 row affected (0.00 sec)
Admin131> LOAD MYSQL USERS TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
Admin131> SELECT hostname, port, name, version, FROM_UNIXTIME(epoch) epoch, checksum, FROM_UNIXTIME(changed_at) changed_at, FROM_UNIXTIME(updated_at) updated_at, diff_check, DATETIME('NOW') FROM stats_proxysql_servers_checksums WHERE name='mysql_users' ORDER BY hostname, name;
+--------------+------+-------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| hostname | port | name | version | epoch | checksum | changed_at | updated_at | diff_check | DATETIME('NOW') |
+--------------+------+-------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| 172.16.3.130 | 6032 | mysql_users | 3 | 2017-09-05 14:53:45 | 0x7C60129E5360AC28 | 2017-09-05 14:53:45 | 2017-09-05 14:53:48 | 0 | 2017-09-05 14:53:48 |
| 172.16.3.131 | 6032 | mysql_users | 3 | 2017-09-05 14:53:44 | 0x7C60129E5360AC28 | 2017-09-05 14:53:45 | 2017-09-05 14:53:48 | 0 | 2017-09-05 14:53:48 |
| 172.16.3.132 | 6032 | mysql_users | 3 | 2017-09-05 14:53:45 | 0x7C60129E5360AC28 | 2017-09-05 14:53:45 | 2017-09-05 14:53:47 | 0 | 2017-09-05 14:53:48 |
+--------------+------+-------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
3 rows in set (0.00 sec)
同样在这种情况下，配置会立即传播到所有节点。
有趣的是，在LOAD TO RUNTIME不执行任何更改的情况下运行时会发生什么。
Admin131> LOAD MYSQL USERS TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
Admin131> SELECT hostname, port, name, version, FROM_UNIXTIME(epoch) epoch, checksum, FROM_UNIXTIME(changed_at) changed_at, FROM_UNIXTIME(updated_at) updated_at, diff_check, DATETIME('NOW') FROM stats_proxysql_servers_checksums WHERE name='mysql_users' ORDER BY hostname, name;
+--------------+------+-------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| hostname | port | name | version | epoch | checksum | changed_at | updated_at | diff_check | DATETIME('NOW') |
+--------------+------+-------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| 172.16.3.130 | 6032 | mysql_users | 3 | 2017-09-05 14:53:45 | 0x7C60129E5360AC28 | 2017-09-05 14:53:45 | 2017-09-05 14:55:45 | 0 | 2017-09-05 14:55:45 |
| 172.16.3.131 | 6032 | mysql_users | 4 | 2017-09-05 14:55:44 | 0x7C60129E5360AC28 | 2017-09-05 14:53:45 | 2017-09-05 14:55:45 | 0 | 2017-09-05 14:55:45 |
| 172.16.3.132 | 6032 | mysql_users | 3 | 2017-09-05 14:53:45 | 0x7C60129E5360AC28 | 2017-09-05 14:53:45 | 2017-09-05 14:55:45 | 0 | 2017-09-05 14:55:45 |
+--------------+------+-------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
3 rows in set (0.00 sec)
在 proxysql131 上，version增加 1，但校验和不会改变。这意味着不会触发同步，因为不需要它。
同样，version其他节点上的 不会改变，因为它们不会重新执行LOAD ... TO RUNTIME。
创建新的查询规则
我们刚刚看到了如何在一个 ProxySQL 节点中创建新用户，并立即被其他节点检测到。
现在是展示同步 MySQL 查询规则的时候了。
让我们首先在没有任何特定行为的节点中创建新的查询规则。
Admin130> SELECT * FROM mysql_query_rules;
Empty set (0.00 sec)
Admin130> INSERT INTO mysql_query_rules (schemaname, destination_hostgroup) VALUES ('shard001',1);
Query OK, 1 row affected (0.00 sec)
Admin130> INSERT INTO mysql_query_rules (schemaname, destination_hostgroup) VALUES ('shard002',1);
Query OK, 1 row affected (0.00 sec)
Admin130> INSERT INTO mysql_query_rules (schemaname, destination_hostgroup) VALUES ('shard003',1);
Query OK, 1 row affected (0.00 sec)
Admin130> INSERT INTO mysql_query_rules (schemaname, destination_hostgroup) VALUES ('shard004',1);
Query OK, 1 row affected (0.00 sec)
Admin130> INSERT INTO mysql_query_rules (schemaname, destination_hostgroup) VALUES ('shard005',2), ('shard006',2), ('shard007',2), ('shard008',2);
Query OK, 4 rows affected (0.00 sec)
Admin130> UPDATE mysql_query_rules SET active=1, apply=1 WHERE destination_hostgroup IN (1,2);
Query OK, 8 rows affected (0.00 sec)
让我们检查一下当前的查询规则：
Admin130> SELECT rule_id,schemaname,destination_hostgroup,apply FROM mysql_query_rules WHERE active=1;
+---------+------------+-----------------------+-------+
| rule_id | schemaname | destination_hostgroup | apply |
+---------+------------+-----------------------+-------+
| 1 | shard001 | 1 | 1 |
| 2 | shard002 | 1 | 1 |
| 3 | shard003 | 1 | 1 |
| 4 | shard004 | 1 | 1 |
| 5 | shard005 | 2 | 1 |
| 6 | shard006 | 2 | 1 |
| 7 | shard007 | 2 | 1 |
| 8 | shard008 | 2 | 1 |
+---------+------------+-----------------------+-------+
8 rows in set (0.00 sec)
在另一个节点上，比如 proxysql131，没有规则：
Admin131> SELECT rule_id,schemaname,destination_hostgroup,apply FROM mysql_query_rules WHERE active=1;
Empty set (0.00 sec)
Admin131> SELECT rule_id,schemaname,destination_hostgroup,apply FROM runtime_mysql_query_rules WHERE active=1;
Empty set (0.00 sec)
现在是时候将规则加载到 proxysql130 上的运行时：
Admin130> SELECT name,FROM_UNIXTIME(epoch), checksum FROM runtime_checksums_values WHERE name='mysql_query_rules';
+-------------------+----------------------+--------------------+
| name | FROM_UNIXTIME(epoch) | checksum |
+-------------------+----------------------+--------------------+
| mysql_query_rules | 2017-09-05 23:03:53 | 0x0000000000000000 |
+-------------------+----------------------+--------------------+
1 row in set (0.00 sec)
Admin130> SELECT hostname, port, name, version, FROM_UNIXTIME(epoch) epoch, checksum, FROM_UNIXTIME(changed_at) changed_at, FROM_UNIXTIME(updated_at) updated_at, diff_check, DATETIME('NOW') FROM stats_proxysql_servers_checksums WHERE name='mysql_query_rules' ORDER BY hostname, name;
+--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| hostname | port | name | version | epoch | checksum | changed_at | updated_at | diff_check | DATETIME('NOW') |
+--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| 172.16.3.130 | 6032 | mysql_query_rules | 1 | 2017-09-05 23:03:53 | 0x0000000000000000 | 2017-09-05 23:03:53 | 2017-09-05 23:04:47 | 0 | 2017-09-05 23:04:47 |
| 172.16.3.131 | 6032 | mysql_query_rules | 1 | 2017-09-05 23:03:53 | 0x0000000000000000 | 2017-09-05 23:03:54 | 2017-09-05 23:04:47 | 0 | 2017-09-05 23:04:47 |
| 172.16.3.132 | 6032 | mysql_query_rules | 1 | 2017-09-05 23:03:54 | 0x0000000000000000 | 2017-09-05 23:03:55 | 2017-09-05 23:04:47 | 0 | 2017-09-05 23:04:47 |
+--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
3 rows in set (0.00 sec)
Admin130> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
Admin130> SELECT name,FROM_UNIXTIME(epoch), checksum FROM runtime_checksums_values WHERE name='mysql_query_rules';
+-------------------+----------------------+--------------------+
| name | FROM_UNIXTIME(epoch) | checksum |
+-------------------+----------------------+--------------------+
| mysql_query_rules | 2017-09-05 23:04:51 | 0xE2F5A21142C799C0 |
+-------------------+----------------------+--------------------+
1 row in set (0.00 sec)
Admin130> SELECT hostname, port, name, version, FROM_UNIXTIME(epoch) epoch, checksum, FROM_UNIXTIME(changed_at) changed_at, FROM_UNIXTIME(updated_at) updated_at, diff_check, DATETIME('NOW') FROM stats_proxysql_servers_checksums WHERE name='mysql_query_rules' ORDER BY hostname, name;
+--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| hostname | port | name | version | epoch | checksum | changed_at | updated_at | diff_check | DATETIME('NOW') |
+--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| 172.16.3.130 | 6032 | mysql_query_rules | 2 | 2017-09-05 23:04:51 | 0xE2F5A21142C799C0 | 2017-09-05 23:04:51 | 2017-09-05 23:06:22 | 0 | 2017-09-05 23:06:23 |
| 172.16.3.131 | 6032 | mysql_query_rules | 2 | 2017-09-05 23:04:52 | 0xE2F5A21142C799C0 | 2017-09-05 23:04:52 | 2017-09-05 23:06:23 | 0 | 2017-09-05 23:06:23 |
| 172.16.3.132 | 6032 | mysql_query_rules | 2 | 2017-09-05 23:04:52 | 0xE2F5A21142C799C0 | 2017-09-05 23:04:52 | 2017-09-05 23:06:23 | 0 | 2017-09-05 23:06:23 |
+--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
3 rows in set (0.00 sec)
从上面我们可以看出，其他节点立即检测到信息，并立即与具有最新更改的节点同步。
让我们检查一下proxysql131上的日志：
2017-09-06 00:04:51 ProxySQL_Cluster.cpp:325:set_checksums(): [INFO] Cluster: detected a new checksum for mysql_query_rules from peer 172.16.3.130:6032, version 2, epoch 1504652691, checksum 0xE2F5A21142C799C0 . Not syncing yet ...
2017-09-06 00:04:52 ProxySQL_Cluster.cpp:473:set_checksums(): [INFO] Cluster: detected a peer 172.16.3.130:6032 with mysql_query_rules version 2, epoch 1504652691, diff_check 3\. Own version: 1, epoch: 1504652633\. Proceeding with remote sync
2017-09-06 00:04:52 ProxySQL_Cluster.cpp:473:set_checksums(): [INFO] Cluster: detected a peer 172.16.3.130:6032 with mysql_query_rules version 2, epoch 1504652691, diff_check 4\. Own version: 1, epoch: 1504652633\. Proceeding with remote sync
2017-09-06 00:04:52 ProxySQL_Cluster.cpp:1106:get_peer_to_sync_mysql_query_rules(): [INFO] Cluster: detected peer 172.16.3.130:6032 with mysql_query_rules version 2, epoch 1504652691
2017-09-06 00:04:52 ProxySQL_Cluster.cpp:572:pull_mysql_query_rules_from_peer(): [INFO] Cluster: Fetching MySQL Query Rules from peer 172.16.3.130:6032 started
2017-09-06 00:04:52 ProxySQL_Cluster.cpp:622:pull_mysql_query_rules_from_peer(): [INFO] Cluster: Fetching MySQL Query Rules from peer 172.16.3.130:6032 completed
2017-09-06 00:04:52 ProxySQL_Cluster.cpp:623:pull_mysql_query_rules_from_peer(): [INFO] Cluster: Loading to runtime MySQL Servers from peer 172.16.3.130:6032
2017-09-06 00:04:52 ProxySQL_Cluster.cpp:626:pull_mysql_query_rules_from_peer(): [INFO] Cluster: Saving to disk MySQL Query Rules from peer 172.16.3.130:6032
2017-09-06 00:04:52 ProxySQL_Cluster.cpp:325:set_checksums(): [INFO] Cluster: detected a new checksum for mysql_query_rules from peer 172.16.3.132:6032, version 2, epoch 1504652692, checksum 0xE2F5A21142C799C0 . Not syncing yet ...
2017-09-06 00:04:52 ProxySQL_Cluster.cpp:327:set_checksums(): [INFO] Cluster: checksum for mysql_query_rules from peer 172.16.3.132:6032 matches with local checksum 0xE2F5A21142C799C0 , we won't sync.
2017-09-06 00:04:52 ProxySQL_Cluster.cpp:325:set_checksums(): [INFO] Cluster: detected a new checksum for mysql_query_rules from peer 172.16.3.131:6032, version 2, epoch 1504652692, checksum 0xE2F5A21142C799C0 . Not syncing yet ...
2017-09-06 00:04:52 ProxySQL_Cluster.cpp:327:set_checksums(): [INFO] Cluster: checksum for mysql_query_rules from peer 172.16.3.131:6032 matches with local checksum 0xE2F5A21142C799C0 , we won't sync.
同样在这种情况下，我们可以看到检测到更改，并且在短时间内所有节点都同步。
结论
在第一篇关于 ProxySQL Cluster 的博客文章中，我们展示了多个 ProxySQL 实例如何相互了解，以及我们如何为任何 proxysql 实例创建新用户和新查询规则，以及如何将这些信息立即传播到所有其他节点。