ProxySQL--基础--7.1--实现一个简单的读写分离(不推荐)

ProxySQL–基础–7.1–实现一个简单的读写分离(不推荐)

---

1、介绍

1.1、准备环境

mysql1主2从

ProxySQL--基础--2.3--部署--准备测试环境--mysql1主2从

1.1.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.1.2、架构图

1.2、操作步骤

1. ProxySQL 添加MySQL节点
   1. 具体细节：ProxySQL–基础–2.4–部署–管理MSYQL节点
2. 监控后端MySQL节点
   1. 具体细节：ProxySQL–基础–2.4–部署–管理MSYQL节点
3. 对ProxySQL中的节点分组
   1. 具体细节：ProxySQL–基础–2.4–部署–管理MSYQL节点
4. 添加 普通用户
   1. 具体细节：ProxySQL–基础–2.4–部署–管理MSYQL节点
5. 配置读写分离 路由规则

1.2.1、添加MySQL节点

# 连接到ProxySQL的管理接口
mysql -uadmin -padmin -P6032 -h127.0.0.1;


insert into mysql_servers(hostgroup_id,hostname,port)values(10,'192.168.187.88',3307);
insert into mysql_servers(hostgroup_id,hostname,port)values(10,'192.168.187.88',3308);
insert into mysql_servers(hostgroup_id,hostname,port)values(10,'192.168.187.88',3309);
 

# 将配置加载到RUNTIME，使其可以立马生效，并保存到disk。
load mysql servers to runtime;
save mysql servers to disk;


# 查看下各节点是否都是 ONLINE
select * from mysql_servers\G

1.2.2、监控后端MySQL节点

# 进入容器
docker exec -it M1 bash ;
# 进入mysql
mysql -uroot -proot ;


# 在mysql的master 节点上 创建一个监控用户 
create user monitor@'192.168.187.%' identified by '123456'; 
grant replication client on *.* to monitor@'192.168.187.%';



# 在ProxySQL中配置监控用户
# 连接到ProxySQL的管理接口
mysql -uadmin -padmin -P6032 -h127.0.0.1; 

set mysql-monitor_username='monitor';
set mysql-monitor_password='123456';
 
#  配置需要加载到RUNTIME，并保存到disk。
load mysql variables to runtime;
save mysql variables to disk;

# 可以通过下面语句查询
select * from global_variables WHERE variable_name='mysql-monitor_username'\G

# 查看下connect和ping的监控是否正常
select * from mysql_server_connect_log order by time_start_us desc limit 6;
select * from mysql_server_ping_log order by time_start_us desc limit 6;

 

1.2.3、对ProxySQL中的节点分组

 

# 往mysql_replication_hostgroups表插入分组数据
insert into mysql_replication_hostgroups values(10,20,"xx项目--读写分离");


# 将配置加载到RUNTIME，使其可以立马生效，并保存到disk。
load mysql servers to runtime;
save mysql servers to disk;

1.2.4、添加 普通用户

# msyql 添加用户(只需master执行即可，会复制给两个slave)
# 创建用户
create user sqlsender@'192.168.187.%' identified by '123456';  

# 授权
grant all on *.* to root@'192.168.187.%' identified by '123456';
grant all on *.* to sqlsender@'192.168.187.%' identified by '123456';



# ProxySQL 添加用户

insert into mysql_users(username,password,default_hostgroup)values('root','123456',10);
insert into mysql_users(username,password,default_hostgroup)values('sqlsender','123456',10);


# 将配置加载到RUNTIME，使其可以立马生效，并保存到disk。
load mysql users to runtime;
save mysql users to disk;


  
# 保证transaction_persistent=1
update mysql_users set transaction_persistent=1 where username='root';
# 将配置加载到RUNTIME，使其可以立马生效，并保存到disk。
load mysql users to runtime;
save mysql users to disk;

2、配置读写分离 路由规则

实现一个最简单的语句级路由规则，从而实现读写分离。必须注意，这只是实验，实际的路由规则绝不应该仅根据所谓的读、写操作进行分离，而是从各项指标中找出压力大、执行频繁的语句单独写规则、做缓存等等。

和查询规则有关的表有两个：

• mysql_query_rules：路由规则表，本文只介绍这个表。
• mysql_query_rules_fast_routing：是mysql_query_rules的扩展表

2.1、配置规则

插入两个规则，目的是将select语句分离到hostgroup_id=20的读组，但由于select语句中有一个特殊语句SELECT...FOR UPDATE它会申请写锁，所以应该路由到hostgroup_id=10的写组。

# 插入规则
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)
VALUES
(1,1,'^SELECT.*FOR UPDATE$',10,1),
(2,1,'^SELECT',20,1);


  
# 将配置加载到RUNTIME，使其可以立马生效，并保存到disk。
load mysql query rules to runtime;
save mysql query rules to disk;

select ... for update规则的rule_id必须要小于普通的select规则的rule_id，因为ProxySQL是根据rule_id的顺序进行规则匹配的。

2.2、测试读写分离

2.2.1、测试：读操作是否路由给了hostgroup_id的读组。

mysql -uroot -p123456 -P6033 -h127.0.0.1 -e 'select @@server_id'

2.2.2、测试：写操作是否路由给了hostgroup_id的写组。

这里以事务持久化进行测试

mysql -uroot -p123456 -P6033 -h127.0.0.1 -e '\
start transaction;\
select @@server_id;\
commit;\
select @@server_id;'

显然，一切都按照预期进行。

2.3、查看路由的信息

可查询stats库中的stats_mysql_query_digest表

SELECT hostgroup hg,
sum_time,
count_star,
digest_text
FROM stats_mysql_query_digest
ORDER BY sum_time DESC;