07proxysql读写分离

1. 1. proxysql读写分离意义
2. 2. 相关表
3. 3. 使用不同的端口的读写分离策略
   1. 3.1. 使用admin登陆交互界面，并修改配置
   2. 3.2. 增加正对Port的访问规则
   3. 3.3. 尝试访问
4. 4. 使用账号的读写分离
   1. 4.1. 增加用户的访问规则
5. 5. 使用区别sql的读写分离
   1. 5.1. 关键表：
   2. 5.2. 查询相关日志记录的例子
   3. 5.3. 增加访问规则

proxysql 读写分离配置

1. proxysql读写分离意义

使用中间间进行读写分离有点像是一个妥协产物，因为前期架构规划未到位，导致数据库压力日益剧增，需要进行数据库的压力负载转移，进行读写分离。

proxysql作为中间间，可以进行读写分离的操作，在不改动代码的前提下进行sql的分发实现读写分离。

优点：

• 可以降低数据库的负载
• 对应用侵入性较小

缺点

• 增加了出故障的概率
• 增加了维护的难度

2. 相关表

关键表: mysql_query_rules
字段解释

show create table mysql_query_rules;


• rule_id ：自增主键，用于唯一标识每个查询规则。
• active ：整型字段，用于控制规则的启用/禁用状态。0 表示禁用，1 表示启用。
• username : 字符串字段，用于指定要应用规则的用户名。可以是具体的用户名，也可以是通配符或正则表达式。
• schemaname : 字符串字段，用于指定要应用规则的数据库模式名。可以是具体的模式名，也可以是通配符或正则表达式。
• flagIN : 整型字段，用于设置输入标志。该字段用于确定如何处理查询的输入部分。默认为 0。
• client_addr : 字符串字段，用于指定要应用规则的客户端地址。可以是具体的 IP 地址，也可以是 IP 地址范围或通配符。
• proxy_addr : 字符串字段，用于指定代理服务器的地址。
• proxy_port : 整型字段，用于指定代理服务器的端口。该字段应在 0 到 65535 之间。
• digest : 字符串字段，用于存储查询的摘要信息。
• match_digest : 字符串字段，用于存储与查询摘要匹配的模式。
• match_pattern : 字符串字段，用于存储与查询文本匹配的模式。
• negate_match_pattern : 整型字段，用于控制是否对匹配模式进行取反操作。0 表示不进行取反，1 表示进行取反。
• re_modifiers : 字符串字段，用于指定正则表达式的修改器。默认值为CASELESS，表示忽略大小写。
• flagOUT : 整型字段，用于设置输出标志。该字段用于确定如何处理查询的输出部分。
• replace_pattern : 字符串字段，用于指定要替换查询结果的模式。
• destination_hostgroup : 整型字段，用于指定要将查询转发到的目标主机组。默认为 NULL。
• cache_ttl : 整型字段，用于设置查询结果的缓存时间。该字段应大于 0。
• cache_empty_result : 整型字段，用于控制是否缓存空结果。默认为 NULL。
• cache_timeout : 整型字段，用于设置查询结果的缓存超时时间。该字段应大于等于 0。
• reconnect : 整型字段，用于控制是否重新连接到目标主机组。0 表示不重新连接，1 表示重新连接。
• timeout : 无符号整型字段，用于设置查询的超时时间。
• retries : 整型字段，用于设置查询的重试次数。该字段应在 0 到 1000 之间。
• delay : 无符号整型字段，用于设置查询的延迟时间。
• next_query_flagIN : 无符号整型字段，用于设置下一个查询的输入标志。
• mirror_flagOUT : 无符号整型字段，用于设置镜像查询的输出标志。
• mirror_hostgroup : 无符号整型字段，用于指定镜像查询的目标主机组。
• error_msg : 字符串字段，用于存储错误消息。
• OK_msg : 字符串字段，用于存储成功消息。
• sticky_conn : 整型字段，用于控制是否启用粘性连接。0 表示不启用，1 表示启用。
• multiplex : 整型字段，用于控制是否启用复用连接。0 表示不启用，1 表示启用，2 表示仅在第一个连接上启用复用。
• gtid_from_hostgroup : 无符号整型字段，用于指定从中获取全局事务标识符（gtid）的主机组。
• log : 整型字段，用于控制是否记录查询日志。0 表示不记录，1 表示记录。
• apply : 整型字段，用于控制是否应用规则。该字段应设置为 1。

其中 rule_id 因为是自增的，所以可以不用指定，
active 的层级 > apply 层级，只有当 active 字段为 1 且 apply 字段为 1 时，规则才会被应用于匹配的查询
active 和 apply 因为涉及到策略是否开启，必须指定

注意: 读写分离策略，从mysql_query_rules第一条记录开始匹配，匹配成功后返回对应的策略

3. 策略1： 使用不同的端口的读写分离策略

方案： 将不同的proxy_port绑定到不同的destination_hostgroup组上面

比如

proxy_port 6033 --> 绑定到destination_hostgroup为读写的组上面
proxy_port 6043 --> 绑定到destination_hostgroup为只读的组上面

3.1. 使用admin登陆交互界面，并修改配置

需要在admin界面修改 mysql-interfaces = “0.0.0.0:6033;0.0.0.0:6043”;
监听两个端口，一个端口作为读写主端口，一个端口作为只读端口
mysql-interfaces 修改后，需要重启proxysql生效

set mysql-interfaces = "0.0.0.0:6033;0.0.0.0:6043";
SAVE MYSQL VARIABLES TO DISK;
PROXYSQL RESTART;


3.2. 增加正对Port的访问规则

insert into mysql_query_rules (rule_id,active,proxy_port,destination_hostgroup,apply) VALUES (1,1,6033,1,1), (2,1,6043,2,1);

3.3. 尝试访问

# 尝试访问6033端口
[root@test ~]# mysql -u monitor -p -P6033 -h 127.0.0.1 -e "select @@server_id"
Logging to file '/data/mysql/mysql3306/logs/query.log'
Enter password:
+-------------+
| @@server_id |
+-------------+
| 1721602 |
+-------------+
# 访问的是第一个节点172.16.0.1

4. 策略1： 使用账号的读写分离

方案： 将不同的username绑定到不同的destination_hostgroup

比如

user1 --> 绑定到destination_hostgroup为读写的组上面
user2 --> 绑定到destination_hostgroup为只读的组上面

4.1. 增加用户的访问规则

INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('monitor','password',1);
delete from mysql_query_rules;
insert into mysql_query_rules (active, apply, username, destination_hostgroup) VALUES (1,1,'monitor',2);
insert into mysql_query_rules (active,proxy_port,destination_hostgroup,apply) VALUES (1,6035,2,1);

SAVE MYSQL QUERY RULES TO DISK;
LOAD MYSQL QUERY RULES TO RUNTIME;


5. 策略1： 使用区别sql的读写分离

方案： 将不同的 digest/match_digest/match_pattern 绑定到不同的 destination_hostgroup组 上面

比如

sql1 (select xxxx from tb1) --> 绑定到destination_hostgroup为读写的组上面
sql2 (select count(*) from tb1) --> 绑定到destination_hostgroup为只读的组上面

5.1. 关键表：

stats_mysql_query_digest

mysql> select hostgroup,schemaname,username,digest,digest_text,count_star,from_unixtime(first_seen),from_unixtime(last_seen),sum_time/1000,min_time/1000,max_time/1000,sum_rows_affected,sum_rows_sent from stats_mysql_query_digest limit 3;


CREATE TABLE stats_mysql_query_digest (
hostgroup INT,
schemaname VARCHAR NOT NULL,
username VARCHAR NOT NULL,
client_address VARCHAR NOT NULL,
digest VARCHAR NOT NULL,
digest_text VARCHAR NOT NULL,
count_star INTEGER NOT NULL,
first_seen INTEGER NOT NULL,
last_seen INTEGER NOT NULL,
sum_time INTEGER NOT NULL,
min_time INTEGER NOT NULL,
max_time INTEGER NOT NULL,
sum_rows_affected INTEGER NOT NULL,
sum_rows_sent INTEGER NOT NULL,
PRIMARY KEY(hostgroup, schemaname, username, client_address, digest))


字段解释

• hostgroup : 记录的hostgroup组
• schemaname : 记录的schema
• username : 访问的username
• client_address : 访问客户端
• digest : sql摘要id
• digest_text : sql摘要的语句
• count_star : 执行次数
• first_seen : 首次执行时间,最后执行时间，timestamp
• last_seen : 最后执行时间，timestamp
• sum_time : 总共执行时间，单位毫秒，平均时间可以用 sum_time/count_star 计算出来
• min_time : 最小执行时间，单位毫秒
• max_time : 最大执行时间，单位毫秒
• sum_rows_affected : 影响行数
• sum_rows_sent : 发送行数

5.2. 查询相关日志记录的例子

目的：先搂出符合需求的语句出来，再进行合理的读写分离策略制定

• 如何根据总执行时间查找前 5 个查询：

SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' ORDER BY sum_time DESC LIMIT 2;


• 如何根据计数查找前 5 个查询：

SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' ORDER BY count_star DESC LIMIT 2;


• 如何根据最大执行时间查找前 5 个查询：

SELECT digest,SUBSTR(digest_text,0,40),count_star,sum_time,sum_time/count_star avg_time, min_time, max_time FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' ORDER BY max_time DESC LIMIT 2;


• 如何查找按总执行时间排序的前 5 个查询，并且最小执行时间至少为 1 毫秒：

SELECT digest,SUBSTR(digest_text,0,20),count_star,sum_time,sum_time/count_star avg_time, min_time, max_time FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' AND min_time > 1000 ORDER BY sum_time DESC LIMIT 2;


• 如何找到按总执行时间排序的前 5 个查询，平均执行时间至少为 1 秒。同时显示总执行时间的百分比：

Admin> SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time,sum_time/count_star avg_time, ROUND(sum_time*100.00/(SELECT SUM(sum_time) FROM stats_mysql_query_digest),3) pct FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' AND sum_time/count_star > 1000000 ORDER BY sum_time DESC LIMIT 2;


|--------------------|--------------------------|------------|--------------|----------|-------|

• 如何查找按总执行时间排序的前 5 个查询，平均执行时间至少为 15 毫秒，并显示占总执行时间的百分比：

Admin> SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time,sum_time/count_star avg_time, ROUND(sum_time*100.00/(SELECT SUM(sum_time) FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%'),3) pct FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' AND sum_time/count_star > 15000 ORDER BY sum_time DESC LIMIT 2;


5.3. 增加访问规则

需求： 查询到两个sql，测试需要将这两个sql调整到备库查询

digest 为 0xDA65260DF35B8D13， digest_text为 select @@server_id
digest 为 0xCA0825C1C5C3C490， digest_text为 select sleep(?)

1. 根据digest的id去做读写分离

delete from mysql_query_rules;
INSERT INTO mysql_query_rules (apply,active,digest,destination_hostgroup)
VALUES (1,1,'0xDA65260DF35B8D13',1);
INSERT INTO mysql_query_rules (apply,active,digest,destination_hostgroup)
VALUES (1,1,'0xCA0825C1C5C3C490',2);

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;


2. 根据match_digest的正则

delete from mysql_query_rules;
INSERT INTO mysql_query_rules (apply,active,username,match_digest,destination_hostgroup)
VALUES (1,1,'monitor','select @@server_id',2);

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;


以上情形都可以尝试使用

select @@server_id ;
-- or
select sleep(5);


该语句去查proxysql的暴露的端口，发现查询的均可以分离到destination_hostgroup为2的库也就是备库中

以上都是使用例子，如需生产使用，需根据实际业务，做更多开发测试才行