MySQL读写分离中间件

港南四大炮亡

已于 2023-03-29 23:00:53 修改

阅读量1.5k

点赞数

文章标签： mysql 中间件数据库

于 2023-03-29 22:48:32 首次发布

本文链接：https://blog.csdn.net/lpx1249115962/article/details/129847367

版权

1.什么是读写分离中间件？
就是实现当[写]的时候转发到主库，当[读]的时候转发到从库的工具。
很类似学习过的proxy,比如nginx proxy做动静分离.
2.为什么要实现读写分离？
1）让主库专注于写，因为读可以有很多从库可以干。
2）让多个从库接收并发读请求。
好处，增加读和写的并发,防止锁竞争，减轻主数据库的压力。

生产场景:读写比基本都比较大,超过10/1.

3.读写分离中间件有哪些？
1）程序实现（JAVA,PHP,PYTHON,SHELL）
需要读写分离的SQL语句DML语句关键字:insert,delete,update,select
   读：select开头找从库，否则就找主库。
优点：少配置一个服务，降低了连接延迟以及服务宕机问题。
   程序修改mysql操作，直接和数据库通信，简单快捷的读写分离和随机的方式实现的负载均衡，权限独立分配，需要开发人员协助。

2）中间件插件(对应用层透明,ip1 读,ip2写)
amoeba:   直接实现读写分离和负载均衡，不用修改代码，有很灵活的数据解决方案，自己分配账户，和后端数据库权限管理独立，权限处理不够灵活。
   mysql-proxy:
   直接实现读写分离和负载均衡，不用修改代码，master和slave用一样的帐号，效率低
   proxySQL
   Atlas
   maxscale
   cobar
   mycat
   ====================
   proxySQL（推荐使用）类似学习过的proxy NGINX（nginx 反向代理，代理web服务请求）

4. ProxySQL介绍
ProxySQL是一个高性能的MySQL代理软件，可以实现数据库读写分离，支持 Query路由功能，支持动态指定某个SQL进行缓存，支持动态加载配置信息（无需重启ProxySQL ），支持故障切换和SQL过滤功能。
Scale 100K+ connections
across thousands of servers
ProxySQL网站：
https://www.proxysql.com/
https://github.com/sysown/proxysql/wiki

5. 基本架构逻辑原理
见架构图。

6.检查修改主从复制环境
GTID环境:
51:Master mha node/mha management
52/53:Slave mha node

7. 安装ProxySQL插件
# 下载proxySQL
https://proxysql.com/
https://github.com/sysown/proxysql/releases

Scale 100K+ connections
across thousands of servers

# 安装proxySQL
# 理论选择哪个机器都可以，本次选择51安装。
[root@db03 ~]# rpm -ivh proxysql-2.0.10-1-centos7.x86_64.rpm
[root@db03 bin]# systemctl start proxysql
[root@db03 bin]# netstat -tulnp|grep 603
tcp 0 0 0.0.0.0:6033 0.0.0.0:* LISTEN 15595/proxysql
tcp 0 0 0.0.0.0:6032 0.0.0.0:* LISTEN 15595/proxysql

注意:
#6033 对外提供服务端口,用户连接
#6032 管理员管理端口

# 登录proxySQL
[root@db03 bin]# mysql -uadmin -padmin -h127.0.0.1 -P6032

8. ProxySQL中管理结构自带系统库

2)mysql_servers: 后端可以连接MySQL服务器的列表 ###nginx upstream池子
db03 [main]>select * from mysql_servers;
+--------------+-----------+------+-----------+--------+--------+----------------+
| hostgroup_id | hostname | port | gtid_port | status | weight |max_connections |
+--------------+-----------+------+-----------+--------+--------+----------------+
| 20 | 10.0.0.52 | 3306 | 0 | ONLINE | 1 |1000 |
| 10 | 10.0.0.51 | 3306 | 0 | ONLINE | 1 |1000 |
| 20 | 10.0.0.53 | 3306 | 0 | ONLINE | 1 |1000 |
| 20 | 10.0.0.51 | 3306 | 0 | ONLINE | 1 |1000 |
+--------------+-----------+------+-----------+--------+--------+----------------+

3)mysql_users: 配置后端数据库的账号和监控的账号。
db03 [main]>select username,password,default_hostgroup from mysql_users;
+----------+----------+-------------------+
| username | password | default_hostgroup |
+----------+----------+-------------------+
| root | 123 | 10 |
+----------+----------+-------------------+

4)mysql_query_rules: 指定Query路由到后端不同服务器的规则列表。
db03 [main]>select rule_id,active,match_pattern,destination_hostgroup from mysql_query_rules;
+---------+--------+----------------------+-----------------------+
| rule_id | active | match_pattern | destination_hostgroup |
+---------+--------+----------------------+-----------------------+
| 1 | 1 | ^select.*for update$ | 10 |
| 2 | 1 | ^select | 20 |
+---------+--------+----------------------+-----------------------+

注：表名以 runtime_开头的表示ProxySQL当前运行的配置内容，不能直接修改。
不带runtime_是下文图中Mem相关的配置。

disk ：
   持久化的磁盘的配置
stats：
   统计信息的汇总
monitor：
   监控的收集信息，比如数据库的健康状态等
stats_history:
   ProxySQL收集的有关其内部功能的历史指标

9. ProxySQL管理接口的多层配置关系
见架构图。

整套配置系统分为三层：
顶层 RUNTIME
中间层 MEMORY（主要修改的配置表）
持久层 DISK和CFG FILE

RUNTIME ：
代表ProxySQL当前正在使用的配置，无法直接修改此配置，必须要从下一层（MEM层）load进来。

MEMORY：
MEMORY层上面连接RUNTIME层，下面连接disk持久层。这层可以在线操作ProxySQL 配置，随便修改，不会影响生产环境。确认正常之后在加载到RUNTIME和持久化磁盘上。
修改方法：使用SQL DML语句：insert、update、delete、select。

DISK和CONFIG FILE：
持久化配置信息。重启时，可以从磁盘快速加载回来。

10. 在不同层次间移动配置

为了将配置持久化到磁盘或者应用到 runtime，在管理接口下有一系列管理命令来实现它们。

1. user相关配置
## MEM加载到runtime
LOAD MYSQL USERS TO RUNTIME;

## runtime保存至MEM
SAVE MYSQL USERS TO MEMORY;

## disk加载到MEM
LOAD MYSQL USERS FROM DISK;

## MEM到disk
SAVE MYSQL USERS TO DISK;

## CFG到MEM
LOAD MYSQL USERS FROM CONFIG
=============================
2. server相关配置
## MEM加载到runtime
LOAD MYSQL SERVERS TO RUNTIME;

## runtime保存至MEM
SAVE MYSQL SERVERS TO MEMORY;

## disk加载到 MEM
LOAD MYSQL SERVERS FROM DISK;

## MEM 到disk
SAVE MYSQL SERVERS TO DISK;

## CFG 到 MEM
LOAD MYSQL SERVERS FROM CONFIG
===============================
3. mysql query rules配置
## MEM 加载到runtime
LOAD MYSQL QUERY RULES TO RUNTIME;

## runtime 保存至 MEM
SAVE MYSQL QUERY RULES TO MEMORY;

## disk 加载到 MEM
LOAD MYSQL QUERY RULES FROM DISK;

## MEM 到 disk
SAVE MYSQL QUERY RULES TO DISK;

## CFG 到 MEM
LOAD MYSQL QUERY RULES FROM CONFIG

=================================
4. MySQL variables配置
## MEM 加载到runtime
LOAD MYSQL VARIABLES TO RUNTIME;

## runtime 保存至 MEM
SAVE MYSQL VARIABLES TO MEMORY;

## disk 加载到 MEM
LOAD MYSQL VARIABLES FROM DISK;

## MEM 到 disk
SAVE MYSQL VARIABLES TO DISK;

## CFG 到 MEM
LOAD MYSQL VARIABLES FROM CONFIG

总结：
日常配置其实大部分时间在MEM层配置，
然后load到RUNTIME，然后SAVE到Disk。cfg很少使用。

常见命令：
使用SQL DML语句修改：insert、update、delete、select。

使用LOAD,SAVE生效和保存。
load xxx to runtime;
save xxx to disk;
XXX就是上面配置里关键字部分

注意：
所有配置保MEM或disk层时，都不会发生任何警告或错误。
只有load到runtime状态时才会验证配置。当load到 runtime时，如果出现错误，将恢复为之前保存的状态，这时可以去检查错误日志。　　

11. ProxySQL应用————基于SQL实现读写分离

11.1 MySQL从库要设定read_only参数

1)主从复制的从库应该禁止用户写入。

2)ProxySQL如何判断主从（根据是否有read-only=1）
read-only=1就是从，否则就是主。

将52,53设置为read-only=1,修改配置步骤略.

11.2 配置读写节点分组信息

在mysql_replication_hostgroup表中，配置读写组编号
10编号对应就是写库的组，20编号对应的就是读库的组。

[root@db01 ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032
db03 [(none)]>use main

insert into mysql_replication_hostgroups(writer_hostgroup, reader_hostgroup, comment)
values (10,20,'proxy');

db01 [main]>load mysql servers to runtime;
db01 [main]>save mysql servers to disk;

db01 [main]>select * from mysql_replication_hostgroups\G
*************************** 1. row ***************************
writer_hostgroup: 10
reader_hostgroup: 20
check_type: read_only
comment: proxy
说明：
   ProxySQL会根据server的read_only的取值将服务器进行分组。
   read_only=0的server，master被分到编号为10的写组，
   read_only=1的server，slave则被分到编号20的读组。
   所以需要将从库设置：set global read_only=1;

11.3. 添加主机到ProxySQL
insert into mysql_servers(hostgroup_id,hostname,port) values (10,'10.0.0.51',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values (20,'10.0.0.52',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values (20,'10.0.0.53',3306);

db01 [main]>select * from mysql_servers;
+--------------+-----------+------+-----------+--------+--------+-----------------+
| hostgroup_id | hostname | port | gtid_port | status | weight | max_connections |
+--------------+-----------+------+-----------+--------+--------+-----------------+
| 10 | 10.0.0.51 | 3306 | 0 | ONLINE | 1 | 1000 |
| 20 | 10.0.0.52 | 3306 | 0 | ONLINE | 1 | 1000 |
| 20 | 10.0.0.53 | 3306 | 0 | ONLINE | 1 | 1000 |
+--------------+-----------+------+-----------+--------+--------+-----------------+

load mysql servers to runtime;
save mysql servers to disk;

11.4. 创建监控用户，并开启监控

1）# 51主库创建监控用户，给ProxySQL连接。
create user monitor@'%' identified with mysql_native_password by '123';
grant replication client on *.* to monitor@'%';

# proxySQL库修改variables表，让proxySQL可以通过monitor用户和123密码去连接和监控数据库
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;

2）# 查询监控日志
db01 [(none)]>select * from mysql_server_connect_log;
db01 [(none)]>select * from mysql_server_ping_log;
db01 [(none)]>select * from mysql_server_read_only_log;
db01 [(none)]>select * from mysql_server_replication_lag_log;

3）配置web节点等应用连接ProxySQL的用户
设定应用连接ProxySQL的用户为root，密码为123

# proxysql代理
insert into mysql_users(username,password,default_hostgroup) values('root','123',10);
load mysql users to runtime;
save mysql users to disk;

# 51主库创建允许proxysql代理访问的用户和密码
create user root@'%' identified with mysql_native_password by '123';
grant all on *.* to root@'%';

注意；早期版本，需要开启事务持续化。今天版本不需要，只是提及。
update mysql_users set transaction_persistent=1 where username='root';
load mysql users to runtime;
save mysql users to disk;

让事务里的所有SQL都读写同一个库：
begin;
select
update
insert
delete
commit;

4）读写规则流程或原理
架构图

5）配置读写规则mysql_query_rules
# ProxySQL
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;

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

6. 模拟web测试读写分离
成功:
[root@db01 /usr/local/bin]# mysql -uroot -p123 -P6033 -h 127.0.0.1 -e "select @@server_id;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
| 51 |
+-------------+
[root@db01 /usr/local/bin]# mysql -uroot -p123 -P6033 -h 127.0.0.1 -e "select @@server_id;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
| 52 |
+-------------+
[root@db01 /usr/local/bin]# mysql -uroot -p123 -P6033 -h 127.0.0.1 -e "select @@server_id;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
| 53 |
+-------------+

[root@db01 ~]# mysql -uroot -p123 -P6033 -h 127.0.0.1 -e "begin;select @@server_id;commit;"
遇到问题：上面语句可以访问到51,52,53，原因：
db01 [main]>select * from mysql_servers;
+--------------+-----------+------+-----------+--------+-------------------------+
| hostgroup_id | hostname | port | gtid_port | status | weight max_connections |
+--------------+-----------+------+-----------+--------+-------------------------+
| 10 | 10.0.0.51 | 3306 | 0 | ONLINE | 1 1000 |
| 20 | 10.0.0.51 | 3306 | 0 | ONLINE | 1 1000 |
| 20 | 10.0.0.53 | 3306 | 0 | ONLINE | 1 1000 |
| 10 | 10.0.0.52 | 3306 | 0 | ONLINE | 1 1000 |
| 10 | 10.0.0.53 | 3306 | 0 | ONLINE | 1 1000 |
| 20 | 10.0.0.52 | 3306 | 0 | ONLINE | 1 1000 |
+--------------+-----------+------+-----------+--------+-------------------------+
从上面那内容可以发现，所有节点都在写组10里，所有节点都在读组20里。

最终罪魁祸首，没有在从库配置read_only=1;临时设置如下；
52/53上配置；
set global read_only=1; ##普通用户只读。
set global super_read_only=1; ##管理员用户只读。

返回：proxysql查看，立刻发生吧变化
db01 [main]>select * from mysql_servers;
+--------------+-----------+------+-----------+--------+--------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | comment |
+--------------+-----------+------+-----------+--------+--------+---------+
| 10 | 10.0.0.51 | 3306 | 0 | ONLINE | 1 | |
| 20 | 10.0.0.51 | 3306 | 0 | ONLINE | 1 | |
| 20 | 10.0.0.53 | 3306 | 0 | ONLINE | 1 | |
| 20 | 10.0.0.52 | 3306 | 0 | ONLINE | 1 | |
+--------------+-----------+------+-----------+--------+--------+---------+
10是写组有51,20是读组，有51,52,53.

[root@db01 ~]# mysql -uroot -p123 -P 6033 -h 127.0.0.1 -e "select @@server_id;"
db01 [(none)]>select * from stats_mysql_query_digest\G

给开发人员:
root 123
6033
10.0.0.51
自动实现读写分离,根据语句过滤select读,其他写.

8. ProxySQL应用扩展——花式路由规则
1. 基于端口实现读写分离
## 修改ProxySQL监听SQL流量的端口号，监听多端口上。
set mysql-interfaces='0.0.0.0:6033;0.0.0.0:6034';
save mysql variables to disk;
## 重启生效
systemctl restart proxysql

## 设定路由规则
delete from 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;

说明：除了基于端口进行分离，还可以基于监听地址(修改字段proxy_addr即可)，也可以基于客户端地址(修改字段client_addr字段即可)。
给开发人员:
写:
root
123
6033
10.0.0.51

读
root
123
6034
10.0.0.51

2. 基于用户实现读写分离
insert into mysql_users(username,password,default_hostgroup)
values('writer','123',10),('reader','123',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,'writer',10,1),(2,1,'reader',20,1);

load mysql query rules to runtime;
save mysql query rules to disk;

#最后在主库创建writer,reader用户和密码。

作业：实践基于端口和基于用户实现读写分离。

给开发人员:
写:
writer
123
6033
10.0.0.51

读:
reader
123
6033
10.0.0.51

港南四大炮亡

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MySQL读写分离中间件

ProxySQL是一个高性能的MySQL代理软件，可以实现数据库读写分离，支持 Query路由功能，支持动态指定某个SQL进行缓存，支持动态加载配置信息（无需重启ProxySQL ），支持故障切换和SQL过滤功能。amoeba: 直接实现读写分离和负载均衡，不用修改代码，有很灵活的数据解决方案，自己分配账户，和后端数据库权限管理独立，权限处理不够灵活。程序修改mysql操作，直接和数据库通信，简单快捷的读写分离和随机的方式实现的负载均衡，权限独立分配，需要开发人员协助。# 为了测试，先清空已有规则。
复制链接

扫一扫