读写分离
**
实测
**
ProxySQL
官方
demo
演示了三种读写分离的方式：使用不同的端口进行读写分离、使用正则表达式进行通用的读写分离、使用正则和
digest
进行更智能的读写分离。最后一种是针对特定业务进行的优化调整，也可将其归结为第二种方式，下边分别进行测试。
基于端口的读写分离
环境准备
1. MySQL
里创建访问用户，监控用户
SQL
#创建监控账号
create user monitor@'192.168.31.%' identified with mysql_native_password by '******';
#授权
grant replication client on *.* to monitor@'192.168.31.%' identified by '******' ;
#创建访问用户账号
create user proxyadmin@'192.168.31.%' identified with mysql_native_password by '******';
#开放权限
grant all privileges on *.* to proxyadmin@'192.168.31.%';
flush privileges;
2. ProxySQL
配置
mysql
节点
SQL
##hostgroup_id, hostname, port 组成一个主键
mysql> select * from mysql_servers;
Empty set (0.00 sec)
mysql> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.31.128',5310);
Query OK, 1 row affected (0.01 sec)
mysql> insert into mysql_servers(hostgroup_id,hostname,port) values(20,'192.168.31.130',5310);
Query OK, 1 row affected (0.01 sec)
mysql> select * from mysql_servers\G
*************************** 1. row ***************************
hostgroup_id: 10
hostname: 192.168.31.128
port: 5310
gtid_port: 0
status: ONLINE
weight: 1
compression: 0
max_connections: 1000
max_replication_lag: 0
use_ssl: 0
max_latency_ms: 0
comment:
*************************** 2. row ***************************
hostgroup_id: 20
hostname: 192.168.31.130
port: 5310
gtid_port: 0
status: ONLINE
weight: 1
compression: 0
max_connections: 1000
max_replication_lag: 0
use_ssl: 0
max_latency_ms: 0
comment:
2 rows in set (0.00 sec)
##加载到内存
mysql> load mysql servers to runtime;
Query OK, 0 rows affected (0.04 sec)
##持久化到磁盘
mysql> save mysql servers to disk;
Query OK, 0 rows affected (0.08 sec)
使用用户配置，配置
mysql_users
表
SQL
mysql> insert into mysql_users(username,password,default_hostgroup) values('proxyadmin','123qwe',10);
Query OK, 1 row affected (0.00 sec)
mysql> load mysql users to runtime;
Query OK, 0 rows affected (0.01 sec)
mysql> save mysql users to disk;
Query OK, 0 rows affected (0.02 sec)
mysql> select * from mysql_users\G
*************************** 1. row ***************************
username: proxyadmin
password: 123qwe
active: 1
use_ssl: 0
default_hostgroup: 10
default_schema: NULL
schema_locked: 0
transaction_persistent: 1
fast_forward: 0
backend: 1
frontend: 1
max_connections: 10000
attributes:
comment:
1 row in set (0.01 sec)
监控用户配置
Monitor
模块就会开始监控后端的
read_only
值，当监控到
read_only
值后，就会按照
read_only
的值将某些节点自动移动到读
/
写组
SQL
mysql> set mysql-monitor_username='monitor';
Query OK, 1 row affected (0.00 sec)
mysql> set mysql-monitor_password='123qwe';
Query OK, 1 row affected (0.00 sec)
mysql> load mysql variables to runtime;
Query OK, 0 rows affected (0.02 sec)
mysql> save mysql variables to disk;
Query OK, 158 rows affected (0.02 sec)
配置基于端口的路由规则
写端口通过
6401
端口，读端口通过
6402
端口，从而通过不同端口来实现读写分离功能。
SQL
INSERT INTO mysql_query_rules (rule_id,active,proxy_port,destination_hostgroup,apply)
VALUES (1,1,6401,10,1), (2,1,6402,20,1);
mysql> load mysql query rules to runtime;
Query OK, 0 rows affected (0.01 sec)
mysql> save mysql query rules to disk;
Query OK, 0 rows affected (0.03 sec)
设置读写分离端口
因端口的修改不能动态加载
,
需重启
ProxySQL
实例
SQL
mysql> SET mysql-interfaces='0.0.0.0:6401;0.0.0.0:6402';
Query OK, 1 row affected (0.00 sec)
mysql> load mysql variables to runtime;
Query OK, 0 rows affected (0.00 sec)
mysql> save mysql variables to disk;
Query OK, 158 rows affected (0.01 sec)
验证
SQL
[user@server1 proxysql]$ mysql -uproxyadmin -p***** -P6401 -h127.0.0.1
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.5.30 (ProxySQL)
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| server1 |
+------------+
1 row in set (0.01 sec)
[user@server1 proxysql]$ mysql -uproxyadmin -p***** -P6402 -h127.0.0.1
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.5.30 (ProxySQL)
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| server2 |
+------------+
1 row in set (0.01 sec)
测试验证
•
所有来自于
6401
端口连接的查询都将被发送到
hostgroup
为
10
的组中；
•
所有来自于
6402
端口连接的查询都将被发送到
hostgroup
为
20
的组中
;
配置缺点
•
该配置需要应用有内置的读写分离功能，以便区分读和写；
•
通常应用只配置一个单独的入口来连接
ProxySQL
，这对很多业务来说是不可接受的。
信息查询
#read_only
日志此时也为空
(
正常来说，新环境配置时，这个只读日志是为空的
)
Plaintext
MySQL [(none)]> select * from mysql_server_read_only_log;
#replication_lag
的监控日志为空
Plaintext
MySQL [(none)]> select * from mysql_server_replication_lag_log;
注意：可能会有很多
connect_error
，这是因为没有配置监控信息时的错误，配置后如果
connect_error
的结果为
NULL
则表示正常。
Plaintext
MySQL [(none)]> select * from mysql_server_connect_log;
以下是对心跳信息的监控
(
对
ping
指标的监控
)
Plaintext
MySQL [(none)]> select * from mysql_server_ping_log;
查看路由的信息，可查询
stats
库中的
stats_mysql_query_digest
表。
Plaintext
mysql> select* from stats_mysql_query_digest;
mysql>SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY
开启
web
统计功能
查看路由的信息，可查询
stats
库中的
stats_mysql_query_digest
表。
SQL
mysql> update global_variables set variable_value='true' where variable_name='admin-web_enabled';
Query OK, 1 row affected (0.01 sec)
mysql> LOAD ADMIN VARIABLES TO RUNTIME;
Query OK, 0 rows affected (0.02 sec)
mysql> SAVE ADMIN VARIABLES TO DISK;
Query OK, 49 rows affected (0.01 sec)
mysql> select * from global_variables where variable_name LIKE 'admin-web%' or variable_name LIKE 'admin-stats%';
+----------------------------------------+----------------+
| variable_name | variable_value |
+----------------------------------------+----------------+
| admin-stats_credentials | stats:***** |
| admin-stats_mysql_connections | 60 |
| admin-stats_mysql_connection_pool | 60 |
| admin-stats_mysql_query_cache | 60 |
| admin-stats_mysql_query_digest_to_disk | 0 |
| admin-stats_system_cpu | 60 |
| admin-stats_system_memory | 60 |
| admin-web_enabled | true |
| admin-web_port | 6080 |
| admin-web_verbosity | 0 |
+----------------------------------------+----------------+
10 rows in set (0.00 sec)
访问
并使用
stats:****
登录即可查看一些统计信息。
基于正则规则的读写分离
开启
eventlog
eventslog
可以记录用户在通过
ProxySQL
访问时的所有
SQL
语句，可以用其观察具体的路由规则，以下为开启方法：
SQL
## 设置eventslog的文件名称
mysql> SET mysql-eventslog_filename='queries.log';
Query OK, 1 row affected (0.00 sec)
## 设置eventslog的格式为json
mysql> SET mysql-eventslog_format=2;
Query OK, 1 row affected (0.00 sec)
## 加载到runtime层并保存到持久层
mysql> load mysql variables to runtime;
Query OK, 0 rows affected (0.00 sec)
mysql> save mysql variables to disk;
Query OK, 158 rows affected (0.01 sec)
清除之前创建的查询规则：
SQL
mysql> delete from mysql_query_rules;
Query OK, 2 rows affected (0.00 sec)
mysql> SET mysql-interfaces='0.0.0.0:6033';;
Query OK, 1 row affected (0.00 sec)
mysql> load mysql variables to runtime;
Query OK, 0 rows affected (0.00 sec)
mysql> save mysql variables to disk;
Query OK, 158 rows affected (0.01 sec)
## 延续上面配置,添加查询规则记录eventslog,如下表示对所有的操作都记录
INSERT INTO mysql_query_rules(active, match_digest, log,apply)
VALUES(1,'.',1,0);
## 添加基于正则的路由规则
INSERT INTO mysql_query_rules(active,match_digest,destination_hostgroup,apply)
VALUES(1,'^SELECT.*FOR UPDATE$',10,1),(1,'^SELECT',20,1);
## 加载配置到运行层并保存配置到持久层
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
测试预测
•
所有的
SELECT FOR UPDATE
语句将被路由到
hostgroup
为
10
的组中；
•
其他的
SELECT
语句将被路由到
hostgroup
为
20
的组中；
•
除以上外的其他语句，将被路由到连接用户默认的
hostgroup
组中；
测试验证
SQL
## 小写查询（select请求忽略大小写，查看eventslog，该SQL路由到读组20）
mysql> select * from sbtest1 limit 1;
## 大写查询（select请求忽略大小写，查看eventslog，该SQL路由到读组20）
mysql> SELECT * from sbtest1 limit 1;
## 大小写混合查询（select请求忽略大小写，查看eventslog，该SQL路由到读组20）
mysql> SelECT * from sbtest1 limit 1;
## 包含空格的查询（ProxySQL会对包含空格的SQL进行处理，查看eventslog，该SQL路由到读组20）
mysql> SelECT * from sbtest1 limit 1;
## 小写查询（select请求忽略大小写，查看eventslog，该SQL路由到写组10）
mysql> select * from sbtest1 limit 1 for update;
ProxySQL
集群
proxysql-1:192.168.31.128
proxysql-2:192.168.31.130
更改所有
ProxySQL
节点的配置文件
SQL
# vim /etc/proxysql.cnf
#修改
admin_variables=
{
admin_credentials="admin:admin;;cluster1:******"
# mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"
mysql_ifaces="0.0.0.0:6032"
# refresh_interval=2000
# debug=true
#集群用户名称，与最上面的相同
cluster_username="cluster1"
#集群用户密码，与最上面的相同
cluster_password="*******"
cluster_check_interval_ms=200
cluster_check_status_frequency=100
cluster_mysql_query_rules_save_to_disk=true
cluster_mysql_servers_save_to_disk=true
cluster_mysql_users_save_to_disk=true
cluster_proxysql_servers_save_to_disk=true
cluster_mysql_query_rules_diffs_before_sync=3
cluster_mysql_servers_diffs_before_sync=3
cluster_mysql_users_diffs_before_sync=3
cluster_proxysql_servers_diffs_before_sync=3
}
#新增
proxysql_servers=
(
{
hostname="192.168.31.128"
port=6032
weight=1
comment="ProxySQL-node1"
},
{
hostname="192.168.31.130"
port=6032
weight=1
comment="ProxySQL-node2"
}
)
启动两个节点的
ProxySQL
服务
注意：
如果存在如果存在
"proxysql.db"
文件
(
在
/var/lib/proxysql
目录下
)
，则
ProxySQL
服务只有在第一次启动时才会去读取
proxysql.cnf
文件并解析；后面启动会就不会读取
proxysql.cnf
文件了！如果想要让
proxysql.cnf
文件里的配置在重启
proxysql
服务后生效
(
即想要让
proxysql
重启时读取并解析
proxysql.cnf
配置文件
)
，则需要先删除
/var/lib/proxysql/proxysql.db
数据库文件，然后再重启
proxysql
服务。这样就相当于初始化启动
proxysql
服务了，会再次生产一个纯净的
proxysql.db
数据库文件
(
如果之前配置了
proxysql
相关路由规则等，则就会被抹掉
)
。
观察集群状况
（两个节点上都可以查看，无特殊说明均在
192.168.31.128
上操作
)
）
SQL
mysql> select * from proxysql_servers;
+----------------+------+--------+----------------+
| hostname | port | weight | comment |
+----------------+------+--------+----------------+
| 192.168.31.128 | 6032 | 1 | ProxySQL-node1 |
| 192.168.31.130 | 6032 | 1 | ProxySQL-node2 |
+----------------+------+--------+----------------+
2 rows in set (0.00 sec)
mysql> select * from stats_proxysql_servers_metrics;
+----------------+------+--------+----------------+------------------+----------+---------------+---------+------------------------------+----------------------------+
| hostname | port | weight | comment | response_time_ms | Uptime_s | last_check_ms | Queries | Client_Connections_connected | Client_Connections_created |
+----------------+------+--------+----------------+------------------+----------+---------------+---------+------------------------------+----------------------------+
| 192.168.31.130 | 6032 | 1 | ProxySQL-node2 | 0 | 0 | 46226673 | 0 | 0 | 0 |
| 192.168.31.128 | 6032 | 1 | ProxySQL-node1 | 9 | 141 | 18414 | 0 | 0 | 0 |
+----------------+------+--------+----------------+------------------+----------+---------------+---------+------------------------------+----------------------------+
2 rows in set (0.00 sec)
mysql> select hostname,port,comment,Uptime_s,last_check_ms from stats_proxysql_servers_metrics;
+----------------+------+----------------+----------+---------------+
| hostname | port | comment | Uptime_s | last_check_ms |
+----------------+------+----------------+----------+---------------+
| 192.168.31.130 | 6032 | ProxySQL-node2 | 0 | 46285888 |
| 192.168.31.128 | 6032 | ProxySQL-node1 | 201 | 17332 |
+----------------+------+----------------+----------+---------------+
2 rows in set (0.00 sec)
mysql> select hostname,name,checksum,updated_at from stats_proxysql_servers_checksums;
+----------------+-------------------+--------------------+------------+
| hostname | name | checksum | updated_at |
+----------------+-------------------+--------------------+------------+
| 192.168.31.130 | admin_variables | | 0 |
| 192.168.31.130 | mysql_query_rules | | 0 |
| 192.168.31.130 | mysql_servers | | 0 |
| 192.168.31.130 | mysql_users | | 0 |
| 192.168.31.130 | mysql_variables | | 0 |
| 192.168.31.130 | proxysql_servers | | 0 |
| 192.168.31.128 | admin_variables | 0x6A7FE5E0EFF3CC92 | 1693908720 |
| 192.168.31.128 | mysql_query_rules | 0x0000000000000000 | 1693908720 |
| 192.168.31.128 | mysql_servers | 0x0000000000000000 | 1693908720 |
| 192.168.31.128 | mysql_users | 0x0000000000000000 | 1693908720 |
| 192.168.31.128 | mysql_variables | 0x3F6C52D7C3EE3ADD | 1693908720 |
| 192.168.31.128 | proxysql_servers | 0x7F9BB0FB6C104089 | 1693908720 |
+----------------+-------------------+--------------------+------------+
12 rows in set (0.00 sec)
观察
ProxySQL
集群中实例之间的数据同步，新增
mysql
节点
SQL
#原有数据
mysql> select * from mysql_servers;
Empty set (0.01 sec)
# 新增一个后端MySQL主机信息
mysql> insert into mysql_servers(hostgroup_id,hostname,port,comment) values (10,'192.168.31.128',5310,'master_mysql');
Query OK, 1 row affected (0.00 sec)
#当前主机查看mysql_servers信息
mysql> select * from mysql_servers\G
*************************** 1. row ***************************
hostgroup_id: 10
hostname: 192.168.31.128
port: 5310
gtid_port: 0
status: ONLINE
weight: 1
compression: 0
max_connections: 1000
max_replication_lag: 0
use_ssl: 0
max_latency_ms: 0
comment: master_mysql
1 row in set (0.01 sec)
#另一台主机查看为空
# 持久化，并加载到运行环境中
mysql> save mysql servers to disk;
mysql> load mysql servers to runtime;
# 再次到另一台proxysql主机上查看，可以看到新插入的数据，发现有这个后端MySQL主机信息，已经被更新到192.168.31.130实例中的memory和runtime环境中。
mysql> select * from mysql_servers\G
*************************** 1. row ***************************
hostgroup_id: 10
hostname: 192.168.31.128
port: 5310
gtid_port: 0
status: ONLINE
weight: 1
compression: 0
max_connections: 1000
max_replication_lag: 0
use_ssl: 0
max_latency_ms: 0
comment: master_mysql
1 row in set (0.01 sec)
# 查看另一台proxysql主机日志，会看到同步的具体信息
2023-09-05 20:20:22 [INFO] Cluster: Loading to runtime MySQL Servers from peer 192.168.31.128:6032
2023-09-05 20:20:22 [INFO] Dumping mysql_servers_incoming
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+--------------+
| hostgroup_id | hostname | port | gtid_port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+--------------+
| 10 | 192.168.31.128 | 5310 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | master_mysql |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+--------------+
此时，两节点的
proxysql cluster
集群搭建完毕
ProxySQL
其他功能参考
作者
**
简介
**
丁鹏
，中国移动云能力中心数据库产品部
分布式
数据库
高级
开发工程师
，
负责云原生数据库海山
（
He3DB
）
分布式
版架构设计
，
拥有
丰富的
数据库内核开发经验
和
实践知识。
