ProxySQL–基础–8.1–路由规则–介绍、基于端口(用户,schema)的路由
1、介绍
1.1、路由过程
- 应用app 发送 SQL语句 到ProxySQL
- ProxySQL 将这个SQL语句(或者重写后的SQL语句)发送给后端的MySQL节点
- MySQL节点执行查询,并将查询结果返回给ProxySQL
- ProxySQL将结果返回给 应用app(如果设置了查询缓存,则先缓存查询结果)
1.2、ProxySQL路由方式
- 基于 ip/port
- 基于 username
- 基于 schema
- 基于 SQL语句
- 基于SQL语句的路由是按照规则进行匹配的
- 匹配方式
- hash高效匹配
- 正则匹配
- 支持更复杂的链式规则匹配
1.3、准备环境
mysql1主2从
ProxySQL--基础--2.3--部署--准备测试环境--mysql1主2从
1.3.1、机器信息
名称 | Ip | Port | server_id |
---|---|---|---|
M1 | 192.168.187.88 | 3307 | 110 |
M1S1 | 192.168.187.88 | 3308 | 120 |
M1S2 | 192.168.187.88 | 3309 | 130 |
Proxysql | 192.168.187.88 | 6032,6033 | null |
1.3.2、架构图
2、基于端口的路由(不推荐)
2.1、介绍
- 优点
- 读写分离配置简单
- 缺点
- 必须在应用APP的代码中指定端口号
- 修改端口号时,应用APP的代码也必须做出相应的修改
2.2、配置
2.2.1、修改ProxySQL监听SQL流量的端口号,让其监听在不同端口上
# 登录ProxySQL
mysql -uadmin -padmin -P6032 -h127.0.0.1
mysql> set mysql-interfaces='0.0.0.0:6033;0.0.0.0:6034';
mysql> save mysql variables to disk;
2.2.2、重启ProxySQL
service proxysql restart
# 查看端口
[root@zhoufei ~]# netstat -tnlp | grep proxysql
tcp 0 0 0.0.0.0:6032 0.0.0.0:* LISTEN 127452/proxysql
tcp 0 0 0.0.0.0:6033 0.0.0.0:* LISTEN 127452/proxysql
tcp 0 0 0.0.0.0:6034 0.0.0.0:* LISTEN 127452/proxysql
上面已经看到,已经监听到6033,6034端口了
2.2.3、修改mysql_query_rules表
插入两条规则,分别监听在6033端口和6034端口
- 6033端口:对应hostgroup_id=10,是负责写的组
- 6034端口:对应hostgroup_id=20,是负责读的组。
# 为了测试,先清空已有规则
delete from mysql_query_rules;
delete from mysql_users;
insert into mysql_query_rules(rule_id,active,proxy_port,destination_hostgroup,apply) values(1,1,6033,10,1);
insert into mysql_query_rules(rule_id,active,proxy_port,destination_hostgroup,apply) values(2,1,6034,20,1);
# 将配置加载到RUNTIME,使其可以立马生效,并保存到disk。
load mysql query rules to runtime;
save mysql query rules to disk;
到这里配置结束了
2.3、扩展
除了基于端口进行分离,还有以下方式
- 基于监听地址(修改字段proxy_addr即可)
- 基于客户端地址(修改字段client_addr字段即可,该用法可用于采集数据、数据分析等)
无论哪种路由方式,其实都是在修改mysql_query_rules表
2.3.1、mysql_query_rules表的介绍
MYSQL--架构--ProxySQL--03--库和表
2、mysql_query_rules表
3、基于用户的路由(不推荐)
3.1、介绍
- 和基于端口的配置是类似的。
- 注意:在插入mysql user到
mysql_users
表中时,就已经指定了默认的路由目标组,这是一个默认路由规则了。当成功匹配到mysql_query_rules中的规则时,这个默认目标就不再生效。所以,通过默认路由目标,也能简单地实现读写分离。
3.2、配置
3.2.1、在后端MySQL Server上先创建好用于读、写分离的用户
- root用户:用于写操作
- reader用户:用于读操作。
- 在master节点上执行
# 进入容器
docker exec -it M1 bash ;
# 进入mysql
mysql -uroot -proot ;
# 创建用户
create user reader@'192.168.187.%' identified by '123456';
grant all on *.* to root@'192.168.187.%' identified by '123456';
grant select,show databases,show view on *.* to reader@'192.168.187.%' identified by '123456';
3.2.2、将这两个用户添加到ProxySQL的mysql_users表中,并创建两条规则,分别这两个用户进行匹配。
# 为了测试,先清空已有规则
delete from mysql_query_rules;
delete from mysql_users;
# 插入用户
insert into mysql_users(username,password,default_hostgroup)values('root','123456',10);
insert into mysql_users(username,password,default_hostgroup)values('reader','123456',20);
# 将配置加载到RUNTIME,使其可以立马生效,并保存到disk。
load mysql users to runtime;
save mysql users to disk;
# 插入规则
insert into mysql_query_rules(rule_id,active,username,destination_hostgroup,apply)values(1,1,'root',10,1);
insert into mysql_query_rules(rule_id,active,username,destination_hostgroup,apply)values(2,1,'reader',20,1);
# 将配置加载到RUNTIME,使其可以立马生效,并保存到disk。
load mysql query rules to runtime;
save mysql query rules to disk;
在上面演示的示例中,mysql_query_rules
中基于username的规则和mysql_users
中这两个用户的默认规则是重复的。
当成功匹配到mysql_query_rules中的规则时,mysql_users中这两个用户的默认规则就不再生效
3.3、测试
# 使用reader用户去查数据
select * FROM test1.t1;
-- ProxySQL路由出去的各类查询相关统计数据
select * from stats_mysql_query_digest;
4、基于数据库名称(schema)的路由
4.1、介绍
基于数据库名称(schemaname)的路由,在一定程度上实现了简单的sharding功能。例如,将后端MySQL集群中的节点A和节点B定义在不同主机组中,ProxySQL将所有对于DB1库的查询路由到节点A所在的主机组,将所有对DB2库的查询路由到节点B所在的主机组。
ProxySQL的schemaname字段只是个鸡肋,要实现分库sharding,只能通过正则匹配、查询重写的方式来实现。
4.2、举例
原语句如下,用于找出浙江省的211大学。
select * from zhongguo.university where prov='Zhejiang' and high=211;
按省份分库后,通过ProxySQL的正则替换,将语句改写为如下SQL语句
select * from Zhejiang.university where 1=1 high=211;
然后还可以将改写后的SQL语句路由到指定的主机组中,实现真正的分库。