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

已注销 2022-05-26 数据库 180 0 百度已收录

阿里云新用户专享
领取限量2000元代金券

限量爆款选购
2核8G内存5M带宽3000元/3年

学生用户专属
18-24岁用户直享￥9.5/月

全民上云优选
新老用户均可购买低至17元/月

企业级应用一折起购
稳定，可靠企业级独享实例

多产品一键采购
购物车采购可减5000元

ECS 云服务器 SWAS 轻量应用服务器 RDS 云数据库 Redis 云数据库 CDN 内容分发 OSS 对象存储 SLB 负载均衡 NAT 网关 DNS 云解析 MAIL 企业邮箱 WAF 应用防火墙 DDoS 高防 SMS 短信包 MK 云市场 XIN 心选 IM 商标注册 JZ 自营建站

官方文档地址：https://proxysql.com/blog/proxysql-cluster/

前言

ProxySQL 是一个去中心化的代理，建议靠近应用部署。这种方法甚至可以很好地扩展到数百个节点，因为它被设计为在运行时很容易重新配置。

这允许使用 Ansible/Chef/Puppet/Salt 等软件（按数据库软件字母顺序）或 Etcd/Consul/ZooKeeper 等服务发现工具简单地配置 ProxySQL 实例群。

此外，ProxySQL 是高度可定制的，可以在任何使用这些技术的设置中采用，甚至unix是系统软件么可以在自制工具中采用。

然而，该解决方案有其缺点：

其配unix怎么读置需要外部软件（即配置管帅气撩人高冷动漫男头理软件）
多个实例无法本地同步其配置
收敛时间不可预测
没有针对脑裂（网络分区）的保护

为了解mysql密码忘记了怎么办决上述问题，ProxySQL 1.4.2 中引入了新功能以支持原生集群。

正如我们已经指出的那样，这些功能是实验性的，可能会发生变化，尤其是因为路线图中的所有功能尚未实现。

设置 3 节点 ProxySQL 集群

我们将首先建立一个只有 3 个节点的集群。

我们将使用以下引mysql基础命令导配置文件/etc/proxysql.cnf。

管理变量将主要设置为默认值，我们在任何情况下都指定它们以枚举新的集群变量。需要注意的重要一点是cluster_username和变量应指定也在中mysql面试题列出的cluster_passwor其他业务收入d凭据。实际上，可以包含多组凭据。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_quer生麒麟贵子的女人特征y_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，空配置也有零sql数据库值，unix系统时间戳是“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

chang数据库系统概论第五版课后答案ed_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)

从上面的例子我们可以看到，运行时没有加载用户，我SQL们只是在内存中创建了一个新用户。

是时候将它加载到运行时，看看接下来会发生什么：

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 上应用了更改，并且更改几乎立即传播到其他任何地方。

检unix时间查 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.

发生了什么？

Proxsql数据库ySQL 检测到表的校验和发生变化my数据库查询语句sql_users

当diff_check达到阈值时，同步过程被其他应付款初始化

MySQL 用户从 172.16.3.130:6032 获取

MySQL 用户加载到运行unix和linux的区别时

MySQL 用户也被保存到磁盘，因为cluster_mysql_users_s数据库系统概论第五版课后答案ave_to_其他货币资金disk=t宋庆玲rue

还从数据库查询语句 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)

同样在这种情况下，配置mysql索引会立即传播到所有节点。

有趣的是，在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 上，versmysql面试题ion增加 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)

在另一个节点上，比如 pr其他和其它的区别oxysql131，没有规则：

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.

同样在这种情况下，我们可以看到检测到更改，并且在苏卿陆容渊短时间内所有节点都同步。

结论

在第一篇关于 Proxymysql数据库基础知识SQL Clus数据库软件ter 的博客文章中，我们展示了多个 ProxySQL 实例如何相互了解，以及我们其他应付款如何为任何 proxysql 实例创建新用户和新查询规则，以及如何将这些信息立即传播到所有其他舍曲林节点。

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

前言

设置 3 节点 ProxySQL 集群

添加新用户数据库系统工程师

创建新的查询规则

结论

发表评论

发表评论取消回复

前言

设置 3 节点 ProxySQL 集群

添加新用户数据库系统工程师

创建新的查询规则

结论

相关文章

发表评论

发表评论 取消回复

发表评论取消回复