proxysql的花式路由规则

ProxySQL可以实现多种方式的路由:基于ip/port、username、schema、SQL语句。其中基于SQL语句的路由是按照规则进行匹配的,匹配方式有hash高效匹配、正则匹配,还支持更复杂的链式规则匹配。

角色主机IPserver_id数据状态
Proxysql 10.2.83.140 null
Master 10.2.83.140 140 刚安装的全新MySQL实例,mysql 8.0.19

Slave1

10.2.83.141 141 刚安装的全新MySQL实例,mysql 8.0.19

1.基于port的路由

虽然基于端口实现读写分离配置起来非常简单,但是缺点也很明显:必须在前端app的代码中指定端口号码。这意味着MySQL的一部分流量权限被开发人员掌控了,换句话说,DBA无法全局控制MySQL的流量。此外,修改端口号时,app的代码也必须做出相应的修改。

首先修改ProxySQL监听SQL流量的端口号,让其监听在不同端口上。

admin> set mysql-interfaces='0.0.0.0:6033;0.0.0.0:6034';

admin> save mysql variables to disk;

然后重启ProxySQL。

service proxysql stop
service proxysql start

[root@node2 ~]# netstat -luntp|grep proxy
tcp 0 0 0.0.0.0:6032 0.0.0.0:* LISTEN 11543/proxysql
tcp 0 0 0.0.0.0:6033 0.0.0.0:* LISTEN 11543/proxysql
tcp 0 0 0.0.0.0:6034 0.0.0.0:* LISTEN 11543/proxysql

 

监听到不同端口,再去修改mysql_query_rules

insert into mysql_query_rules(rule_id,active,proxy_port,destination_hostgroup,apply) values(1,1,6033,10,1), (2,1,6034,20,1);

load mysql query rules to runtime;

save mysql query rules to disk;

1.1 mysql_query_rules表的介绍

  • rule_id:规则的id。规则是按照rule_id的顺序进行处理的
  • active:只有该字段值为1的规则才会加载到runtime数据结构,所以只有这些规则才会被查询处理模块处理。
  • username:用户名筛选,当设置为非NULL值时,只有匹配的用户建立的连接发出的查询才会被匹配。
  • schemaname:schema筛选,当设置为非NULL值时,只有当连接使用schemaname作为默认schema时,该连接发出的查询才会被匹配。(在MariaDB/MySQL中,schemaname等价于databasename)。
  • flagIN,flagOUT:这些字段允许我们创建"链式规则"(chains of rules),一个规则接一个规则。
  • apply:当匹配到该规则时,立即应用该规则。
  • client_addr:通过源地址进行匹配。
  • proxy_addr:当流入的查询是在本地某地址上时,将匹配。
  • proxy_port:当流入的查询是在本地某端口上时,将匹配。
  • digest:通过digest进行匹配,digest的值在stats_mysql_query_digest.digest中。
  • match_digest:通过正则表达式匹配digest。
  • match_pattern:通过正则表达式匹配查询语句的文本内容。
  • negate_match_pattern:设置为1时,表示未被match_digestmatch_pattern匹配的才算被成功匹配。也就是说,相当于在这两个匹配动作前加了NOT操作符进行取反。
  • re_modifiers:RE正则引擎的修饰符列表,多个修饰符使用逗号分隔。指定了CASELESS后,将忽略大小写。指定了GLOBAL后,将替换全局(而不是第一个被匹配到的内容)。为了向后兼容,默认只启用了CASELESS修饰符。
  • replace_pattern:将匹配到的内容替换为此字段值。它使用的是RE2正则引擎的Replace。注意,这是可选的,当未设置该字段,查询处理器将不会重写语句,只会缓存、路由以及设置其它参数。
  • destination_hostgroup:将匹配到的查询路由到该主机组。但注意,如果用户的transaction_persistent=1(见mysql_users表),且该用户建立的连接开启了一个事务,则这个事务内的所有语句都将路由到同一主机组,无视匹配规则。
  • cache_ttl:查询结果缓存的时间长度(单位毫秒)。注意,在ProxySQL 1.1中,cache_ttl的单位是秒。
  • reconnect:目前不使用该功能。
  • timeout:被匹配或被重写的查询执行的最大超时时长(单位毫秒)。如果一个查询执行的时间太久(超过了这个值),该查询将自动被杀掉。如果未设置该值,将使用全局变量mysql-default_query_timeout的值。
  • retries:当在执行查询时探测到故障后,重新执行查询的最大次数。如果未指定,则使用全局变量mysql-query_retries_on_failure的值。
  • delay:延迟执行该查询的毫秒数。本质上是一个限流机制和QoS,使得可以将优先级让位于其它查询。这个值会写入到mysql-default_query_delay全局变量中,所以它会应用于所有的查询。将来的版本中将会提供一个更高级的限流机制。
  • mirror_flagOUT和mirror_hostgroupmirroring相关的设置,目前mirroring正处于实验阶段,所以不解释。
  • error_msg:查询将被阻塞,然后向客户端返回error_msg指定的信息。
  • sticky_conn:当前还未实现该功能。
  • multiplex:如果设置为0,将禁用multiplexing。如果设置为1,则启用或重新启用multiplexing,除非有其它条件(如用户变量或事务)阻止启用。如果设置为2,则只对当前查询不禁用multiplexing。默认值为NULL,表示不会修改multiplexing的策略。
  • log:查询将记录日志。
  • apply:当设置为1后,当匹配到该规则后,将立即应用该规则,不会再评估其它的规则(注意:应用之后,将不会评估mysql_query_rules_fast_routing中的规则)。
  • comment:注释说明字段,例如描述规则的意义。

 

 

2.基于mysql username进行路由

基于mysql user的配置方式和基于端口的配置是类似的。

需要注意,在插入mysql user到mysql_users表中时,就已经指定了默认的路由目标组,这已经算是一个路由规则了(只不过是默认路由目标)。当成功匹配到mysql_query_rules中的规则时,这个默认目标就不再生效。所以,通过默认路由目标,也能简单地实现读写分离。

例如,在后端MySQL Server上先创建好用于读、写分离的用户。例如,root用户用于写操作,reader用户用于读操作。

# 在master节点上执行: grant all on *.* to root@'192.168.100.%' identified by 'P@ssword1!'; grant select,show databases,show view on *.* to reader@'192.168.100.%' identified by 'P@ssword1!';

然后将这两个用户添加到ProxySQL的mysql_users表中,并创建两条规则分别就有这两个用户进行匹配。

 

insert into mysql_users(username,password,default_hostgroup)
values('root','P@ssword1!',10),('reader','P@ssword1!',20);

 

load mysql users to runtime;
save mysql users to disk;

 

delete from mysql_query_rules; # 为了测试,先清空已有规则

 

insert into mysql_query_rules(rule_id,active,username,destination_hostgroup,apply)
values(1,1,'root',10,1),(2,1,'reader',20,1);
load mysql query rules to runtime;
save mysql query rules to disk;

 

3.SQL重写

在mysql_query_rules表中有match_pattern字段和replace_pattern字段,前者是匹配SQL语句的正则表达式,后者是匹配成功后(命中规则),将原SQL语句改写,改写后再路由给后端。

需要注意几点:

1.如果不设置replace_pattern字段,则不会重写。
2.要重写SQL语句,必须使用match_pattern的方式做正则匹配,不能使用match_digest。因为match_digest是对参数化后的语句进行匹配。
3.ProxySQL支持两种正则引擎:RE2和PCRE,默认使用的引擎是PCRE。这两个引擎默认都设置了caseless修饰符(re_modifiers字段),表示匹配时忽略大小写。还可以设置其它修饰符,如global修饰符,global修饰符主要用于SQL语句重写,表示全局替换,而非首次替换。
4.因为SQL语句千变万化,在写正则语句的时候,一定要注意"贪婪匹配"和"非贪婪匹配"的问题。
5.stats_mysql_query_digest表中的digest_text字段显示了替换后的语句。也就是真正路由出去的语句。

例如:前端程序通过Proxysql查询的语句为:select * from mirror.mirror ,到后端数据库转为:select * from mirror.t1

mysql> select * from mirror;
+------+--------+
| id | name |
+------+--------+
| 1 | xiuxiu |
| 2 | test |
| 2 | test |
| 2 | test |
| 2 | test |
| 2 | test |
+------+--------+


mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | dddd |
| 1 | dddd |
| 1 | dddd |
| 1 | dddd |
| 1 | dddd |
| 1 | dddd |
| 1 | dddd |
| 1 | dddd |
+------+------+

 

插入如下规则:

delete from mysql_query_rules;

select * from stats_mysql_query_digest_reset where 1=0;

insert into mysql_query_rules(rule_id,active,match_pattern,replace_pattern,destination_hostgroup,apply) values (1,1,"mirror.mirror","mirror.t1",20,1);

load mysql query rules to runtime;

save mysql query rules to disk;

admin> select rule_id,destination_hostgroup,match_pattern,replace_pattern from mysql_query_rules;
+---------+-----------------------+---------------+-----------------+
| rule_id | destination_hostgroup | match_pattern | replace_pattern |
+---------+-----------------------+---------------+-----------------+
| 1 | 20 | mirror.mirror | mirror.t1 |
+---------+-----------------------+---------------+-----------------+

 

测试:

[root@node2 ~]# mysql -uproxysql -p123 -P 6033 -h 127.0.0.1 -e 'select * from mirror.mirror'
Warning: Using a password on the command line interface can be insecure.
+------+------+
| id | name |
+------+------+
| 1 | dddd |
| 1 | dddd |
| 1 | dddd |
| 1 | dddd |
| 1 | dddd |
| 1 | dddd |
| 1 | dddd |
| 1 | dddd |
+------+------+

 

发现已经生效,查询mirror表已经转到查询t1表

 

参考文章链接:

https://www.cnblogs.com/f-ck-need-u/p/9309760.html 

 3.1 SQL重写

演示下sharding时的分库语句怎么改写

假如存在库:student ,表stu

调整前,表数据:

mysql> select * from stu;
+------+-------+---------+
| uid | name | zhuanye |
+------+-------+---------+
| 1 | zhang | Linux |
| 2 | zhang | JAVA |
| 3 | wang | Python |
| 4 | wang | Mysql |
+------+-------+---------+
4 rows in set (0.00 sec)

 

每次查询:

select  count(*) from stu where zhaunye="Linux" 即可以统计学习linux的学生有多少,但是在实际生产中可能一张表会特别大,那查询效率可能很低,

所以我们可以针对后端数据进行拆分到不同的表,即Linux,JAVA,Python,Mysql不同学科不同的库。

mysql> use Python
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select * from stu;
+------+------+---------+
| uid | name | zhuanye |
+------+------+---------+
| 1 | wang | Python |
+------+------+---------+

 

mysql> use Mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from stu;
+------+------+---------+
| uid | name | zhuanye |
+------+------+---------+
| 1 | wang | Mysql |
+------+------+---------+
1 row in set (0.00 sec)

 

mysql> use Linux
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from stu;
+------+------+---------+
| uid | name | zhuanye |
+------+------+---------+
| 1 | wang | Linux |
+------+------+---------+
1 row in set (0.00 sec)

 

mysql> use JAVA
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from stu;
+------+------+---------+
| uid | name | zhuanye |
+------+------+---------+
| 1 | wang | JAVA |
+------+------+---------+

 

于是,原来查询MySQL专业学生的SQL语句:

select * from student.stu where zhuanye='MySQL' and uid=1;

分库后,该SQL语句需要重写为:

select * from Mysql.stu where  1=1 and uid=1 ;

 

匹配规则写法如下:

admin> delete from mysql_query_rules;
Query OK, 1 row affected (0.00 sec)

admin> select rule_id,destination_hostgroup dest_hg,match_pattern,replace_pattern from mysql_query_rules;
Empty set (0.00 sec)

admin> insert into mysql_query_rules(rule_id,active,apply,destination_hostgroup,match_pattern,replace_pattern) values (1,1,1,20,"^(select.*?from) student\.(.*?) where zhuanye=['""](.*?)['""] (.*)$","\1 \3.\2 where 1=1 \4");
Query OK, 1 row affected (0.00 sec)

admin> load mysql query rules to runtime;
Query OK, 0 rows affected (0.00 sec)

admin> save mysql query rules to disk;
Query OK, 0 rows affected (0.02 sec)

admin> select rule_id,destination_hostgroup dest_hg,match_pattern,replace_pattern from mysql_query_rules;
+---------+---------+------------------------------------------------------------------+-----------------------+
| rule_id | dest_hg | match_pattern | replace_pattern |
+---------+---------+------------------------------------------------------------------+-----------------------+
| 1 | 20 | ^(select.*?from) student\.(.*?) where zhuanye=['"](.*?)['"] (.*)$ | \1 \3.\2 where 1=1 \4 |
+---------+---------+------------------------------------------------------------------+-----------------------+
1 row in set (0.00 sec)

查询测试:

模拟前端查询命令

[root@node2 ~]# mysql -uproxysql -p123 -P 6033 -h 127.0.0.1 -e 'select * from mirror.stu where zhuanye="Mysql" and 1=1'
Warning: Using a password on the command line interface can be insecure.
+------+------+---------+
| uid | name | zhuanye |
+------+------+---------+
| 1 | wang | Mysql |
+------+------+---------+

解释下前面的规则:

match_pattern:
"^(select.*?from) it_db\.(.*?) where zhuanye=['""](.*?)['""] (.*)$"
replace_pattern:
"\1 \3.\2 where 1=1 \4"

^(select.*?from):表示不贪婪匹配到from字符。之所以不贪婪匹配,是为了避免子查询或join子句出现多个from的情况。
it_db\.(.*?):这里的it_db是稍后要替换掉为"MySQL"字符的部分,而it_db后面的表稍后要附加在"MySQL"字符后,所以对其分组捕获。
zhuanye=['""](.*?)['""]
- 这里的zhuanye字段稍后是要删除的,但后面的字段值"MySQL"需要保留作为稍后的分库,因此对字段值分组捕获。同时,字段值前后的引号可能是单引号、双引号,所以两种情况都要考虑到。
['""]:要把引号保留下来,需要对额外的引号进行转义:双引号转义后成单个双引号。所以,真正插入到表中的结果是['"]
- 这里的语句并不健壮,因为如果是zhuanye='MySQL"这样单双引号混用也能被匹配。如果要避免这种问题,需要使用PCRE的反向引用。例如,改写为:zhuanye=(['""])(.*?)\g[N],这里的[N]要替换为(['""])对应的分组号码,例如\g3
(.*)$:匹配到结束。因为这里的测试语句简单,没有join和子查询什么的,所以直接匹配。
"\1 \3.\2 where 1=1 \4":这里加了1=1,是为了防止出现and/or等运算符时前面缺少表达式。例如(.*)$捕获到的内容为and xxx=1,不加上1=1的话,将替换为where and xxx=1,这是错误的语句,所以1=1是个占位表达式。

可见,要想实现一些复杂的匹配目标,正则表达式是非常繁琐的。所以,很有必要去掌握PCRE正则表达式。

 

4. 读写分离 

这种模式的读写分离,严格区分后端的master和slave节点,且slave节点必须设置选项read_only=1。在ProxySQL上,分两个组,一个写组HG=10,一个读组HG=20。同时在ProxySQL上开启monitor模块的read_only监控功能,让ProxySQL根据监控到的read_only值来自动调整节点放在HG=10(master会放进这个组)还是HG=20(slave会放进这个组)。

这种模式的读写分离是最简单的,只需在mysql_users表中设置用户的默认路由组为写组HG=10,并在mysql_query_rules中加上两条简单的规则(一个select for update,一个select)即可。

这种模式的读写分离,严格区分后端的master和slave节点,且slave节点必须设置选项read_only=1。在ProxySQL上,分两个组,一个写组HG=10,一个读组HG=20。同时在ProxySQL上开启monitor模块的read_only监控功能,让ProxySQL根据监控到的read_only值来自动调整节点放在HG=10(master会放进这个组)还是HG=20(slave会放进这个组)。

这种模式的读写分离是最简单的,只需在mysql_users表中设置用户的默认路由组为写组HG=10,并在mysql_query_rules中加上两条简单的规则(一个select for update,一个select)即可。

 

#1 . 添加主机到ProxySQL 

insert into mysql_servers(hostgroup_id,hostname,port) values (10,'10.2.83.140',3306);  //主节点写

insert into mysql_servers(hostgroup_id,hostname,port) values (20,'10.2.83.141',3306);  //从节点读           

load mysql servers to runtime;

save mysql servers to disk;

#2.  创建监控用户,并开启监控 

主库(写库)创建监控用户否则无法实现路由

create user monitor@'%' identified with mysql_native_password  by '123';

grant replication client on *.* to monitor@'%';

#3.  proxySQL修改variables表           

set mysql-monitor_username='monitor';

set mysql-monitor_password='123';
或者 :

UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';

UPDATE global_variables SET variable_value='123'  WHERE variable_name='mysql-monitor_password';

load mysql variables to runtime;

save mysql variables to disk

#4. 配置应用用户 (主库配置就好,会自动同步到从库)

create user proxysql@'%' identified with mysql_native_password  by '123';

grant all on *.* to proxysql@'%';

#5.  proxysql 创建应用用户

insert into mysql_users(username,password,default_hostgroup) values('proxysql','123',10);

load mysql users to runtime;

save mysql users to disk;

#6. 简单的读写路由规则(读写分离)

分配原则:所有的select 语句走20组,除了^select.*for update写入20,默认其他的都写入10组,

注意要把^select.*for update写在第一条,因为路由规则会根据rule_id从小到大进行匹配,会优先匹配。                    

insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values (1,1,'^select.*for update$',10,1);
insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values (2,1,'^select',20,1);

load mysql query rules to runtime;

save mysql query rules to disk;

参考链接

https://www.cnblogs.com/f-ck-need-u/p/9318558.html

 

 

posted @ 2021-06-29 17:09  学的都会  阅读(728)  评论(0编辑  收藏  举报