官方文档----ProxySQL 1.4.2 现在支持原生集群！！！

本文主要是介绍官方文档----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 实例创建新用户和新查询规则，以及如何将这些信息立即传播到所有其他节点。

这篇关于官方文档----ProxySQL 1.4.2 现在支持原生集群！！！的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！

Java教程

官方文档----ProxySQL 1.4.2 现在支持原生集群！！！

前言

设置 3 节点 ProxySQL 集群

添加新用户

创建新的查询规则

结论

前端开发

后端开发

移动端开发

数据库

服务器运维

人工智能

区块链

游戏开发

网站运营

大数据/云计算

软件工程

软件/开发工具使用

资讯