MySQL读写分离

什么是读写分离

MySQL读写分离是一种数据库架构优化方案，核心思想是将数据库的写操作（INSERT/UPDATE/DELETE）和读操作（SELECT） 分配到不同的数据库实例上处理，以提高系统性能和稳定性。

为什么要读写分离

在多数业务场景中，读操作的频率远高于写操作（例如电商商品浏览、新闻查看等）。如果读写操作都依赖单一数据库，会导致：
写操作（如订单提交）占用数据库资源，影响读操作响应速度；
单库负载过高，难以应对高并发请求；
单点故障风险高，一旦数据库宕机，整个系统不可用。
读写分离通过"主库处理写，从库处理读"的方式，解决上述问题：
分担主库压力，提高读操作吞吐量；
从库可横向扩展（增加从库数量），应对读请求增长；
主从架构天然具备数据备份能力，提高系统可用性。

如何读写分离

读写分离的前提是主从数据同步，即主库的写操作需要实时（或近实时）同步到从库，确保从库数据与主库一致。MySQL通过主从复制机制实现这一目标。

注意事项

1.数据一致性问题
主从复制存在延迟（如网络延迟、从库SQL执行耗时），可能导致"刚写入主库的数据，从库读不到"。
解决方案：
核心业务的读操作强制走主库（如用户余额查询）；
采用半同步复制（主库等待至少一个从库确认接收binlog后再返回），减少延迟；
给从库设置"过期时间"，超过阈值自动切换到主库读取。
2.从库负载均衡
多从库场景下，需通过轮询、权重分配等策略分摊读压力，避免单个从库过载。
3.故障转移
主库宕机：需手动或自动将从库提升为主库（如通过MHA工具）；
从库宕机：中间件或应用需自动剔除故障从库，避免读请求失败。
4.适用场景
读写分离更适合"读多写少"场景（如内容管理系统、电商商品页）；若写操作频繁（如高频交易系统），需结合分库分表进一步优化。

使用ProxySQL实现MySQL读写分离

 1、ProxySQL是一款开源的使用C++编写的MySQL集群代理中间件；
 
 2、用于在MySQL数据库和客户端之间进行负载均衡、查询缓存、故障转移和查询分发；
 
 3、它可以作为中间层插入到应用程序和数据库之间；
 
 4、特点是高效灵活，使用简单，并且性能是所有中间件中比较优秀的。
 
 5、在搭建好mysql的主从复制后，可以利用ProxySQL实现mysql数据库的读写分离；

主要功能

ProxySQL主要支持MySQL的主从集群和组复制集群，其主要功能有以下几个：

1、代理服务：代理后端MySQL服务，进行相关指标的监控

2、负载均衡：后端多节点的访问进行负载均衡

3、高可用：自动识别异常节点，并屏蔽异常节点，保障集群的稳定

4、读写分离：自动动态的识别读写节点，转发SQL至对应节点执行

5、数据分片：通过路由规则，进行SQL分发，达到数据分片的目的

使用原理

ProxySQL兼容MySQL协议，可以像连接MySQL一样连接ProxySQL，执行简单的SQL语句。

ProxySQL本身相关信息数据的存储使用的是sqlite3，部分MySQL语句功能没有完全兼容，所以通过 mysql client 可以正常连接并使用ProxySQL，如果通过dbeaver或者navicat等工具则无法正常使用。

当Proxysql启动后，将监听两个端口：

admin管理接口，默认端口为6032。该端口用于查看、配置ProxySQL
接收SQL语句的接口，默认端口为6033，这个接口类似于MySQL的3306端口

使用proxysql实现MySQL读写分离

proxysql安装位置

IP	角色	软件
192.168.7.31	Master（主）	MySQL,ProxySQL
192.168.7.32	Slave（从）	MySQL

CentOS7安装proxysql（主）

 # 配置官方yum源,或直接下载rpm包（下载地址：https://github.com/sysown/proxysql/releases）
 cat > /etc/yum.repos.d/proxysql.repo << EOF
 [proxysql_repo]
 name=ProxySQL repository
 baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/centos/8
 gpgcheck=1
 gpgkey=https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/repo_pub_key
 EOF
 
 #yum安装proxysql
 yum makecache
 yum install proxysql

Ubuntu22.04安装proxysql（主）

 # ubuntu下添加源：
 apt install -y --no-install-recommends lsb-release wget apt-transport-https ca-certificates gnupg
 
 wget -O - 'https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/repo_pub_key' | apt-key add - 
 echo deb https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/$(lsb_release -sc)/ ./ | tee /etc/apt/sources.list.d/proxysql.list
 
 wget -nv -O /etc/apt/trusted.gpg.d/proxysql-2.5.x-keyring.gpg 'https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/repo_pub_key.gpg'
 
 # 命令下载装：
 apt-get update
 apt-get install proxysql

启动proxysql服务并加入开机自启（主）

 systemctl start proxysql
 systemctl enable proxysql

通过管理界面配置ProxySQL（主）

使用mysql客户端，并使用以下admin凭据在本地端口（6032）上进行连接。默认账号密码admin:admin

 # 使用mysql客户端登录proxysql
 mysql -uadmin -padmin -h127.0.0.1 -P6032
 
 # 或者使用第三方客户端：mycli -uadmin -padmin -h"127.0.0.1" -P6032
 show databases;

会显示：+-----+---------------+-------------------------------------+

| seq | name | file |

+-----+---------------+-------------------------------------+

| 0 | main | |

| 2 | disk | /var/lib/proxysql/proxysql.db |

| 3 | stats | |

| 4 | monitor | |

| 5 | stats_history | /var/lib/proxysql/proxysql_stats.db |

+-----+---------------+-------------------------------------+

这些库的含义：

main：内存配置数据库。使用此数据库，可以很容易地以自动化方式查询和更新ProxySQL的配置。使用从内存中加载MYSQL用户和类似命令，可以将存储在此处的配置传播到运行时ProxySQL使用的内存数据结构。
disk："main"的基于磁盘的镜像。在重新启动过程中，“ main”不会保留，而是根据启动标志以及磁盘上是否存在数据库从“磁盘”数据库或从配置文件加载。
stats：包含从代理的内部功能收集的运行时指标。指标示例包括每个查询规则匹配的次数，当前正在运行的查询等。
monitor：包含与ProxySQL连接的后端服务器相关的监视指标。度量标准示例包括连接到后端服务器或对其进行ping操作的最小和最大时间。

ProxySQL运行机制

runtime：运行中使用的配置文件
memory：提供用户动态修改配置文件
disk：将修改的配置保存到磁盘SQLit表中（即：proxysql.db）

ProxySQL账号

ProxySQL默认的管理员账户和密码为admin:admin，但是这个默认的用户只能在本地使用。如果想要远程连接到ProxySQL，例如用windows上的navicat连接Linux上的ProxySQL管理接口，必须自定义一个管理员账户。

例如：添加一个root:root123的用户和密码

 # 查询当前 ProxySQL 的管理员账号密码
 select @@admin-admin_credentials;
 
 # 修改管理员账号配置，在原有 admin:admin 基础上新增一个管理员 root:root123
 set admin-admin_credentials='admin:admin;root:root123';
 ## 这里不会覆盖原账号，而是追加。如果想只保留新账号，应写
 set admin-admin_credentials='root:root123';
 
 # 再次查询验证修改是否生效
 select @@admin-admin_credentials;
 
 # 将管理变量的配置加载到运行时，使修改立即生效
 load admin variables to runtime;
 
 # 将配置保存到磁盘（/var/lib/proxysql/proxysql.db 文件）
 save admin variables to disk;
 ## 即使 ProxySQL 重启，管理员账号配置也不会丢失

配置内容

使用proxysql，主要需要完成以下几项内容的配置：

 1、配置监控账号。监控账号用于检测后端mysql实例是否健康（是否能连接、复制是否正常、复制是否有延迟等）;
 
 2、到后端mysql实例创建监控账号;
 
 3、配置后端mysql实例连接信息。实例连接信息存储在mysql_servers表;
 
 4、配置连接proxysql和后端实例的账号。账号信息存储在mysql_users表;
 
 5、配置查询路由信息。路由信息存储在mysql_query_rules表;
 
 6、配置后端mysql集群信息。根据后端mysql集群架构，配置分别存储在mysql_replication_hostgroups、mysql_group_replication_hostgroups、runtime_mysql_galera_hostgroups、runtime_mysql_aws_aurora_hostgroups等表中;
 
 7、根据具体需要，调优相关参数。参数存储在global_variables表;

在主节点的MySQL中创建监控用户

 CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor';
 GRANT USAGE, REPLICATION CLIENT ON *.* TO 'monitor'@'%';

将监控用户加入到proxysql中

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

将Master和slave节点添加到mysql_servers表中

注意：是在proxysql中操作

 -- Master主节点
 INSERT INTO mysql_servers(hostgroup_id,hostname,port,weight,comment) VALUES (1,'192.168.7.31',3306,1,'Write group');
 
 -- slave节点
 INSERT INTO mysql_servers(hostgroup_id,hostname,port,weight,comment) VALUES (2,'192.168.7.32',3306,1,'Read group');
 
 -- 保存
 LOAD MYSQL SERVERS TO RUNTIME;
 SAVE MYSQL SERVERS TO DISK;

查看添加的mysql集群（ProxySQL中）

 SELECT * FROM mysql_servers;

在MySQL主从节点上创建用户，赋予权限，并将其存储到代理服务器中。

 -- 在Master节点和slave节点上创建adm用户,设置为管理员权限。
 CREATE USER 'adm'@'%' IDENTIFIED BY '123456';
 GRANT ALL PRIVILEGES ON *.* TO 'adm'@'%';
 
 -- 在Master节点和slave节点上创建用户read,并设置权限为只读。
 CREATE USER 'read'@'%' IDENTIFIED BY '123456';
 GRANT SELECT ON *.* TO 'read'@'%';
 
 ## 重新加载权限表
 FLUSH PRIVILEGES;

在proxysql上添加用户

 -- 在proxysql上添加用户
 INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('adm','123456',1);
 INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('read','123456',2);
 
 -- 让用户配置立即生效
 LOAD MYSQL USERS TO RUNTIME;
 
 -- 保存用户到磁盘
 SAVE MYSQL USERS TO DISK;
 
 -- 查看运行时后端服务器状态
 select hostgroup_id, hostname, port,status from runtime_mysql_servers;

在proxysql上配置读写规则

参数介绍：

rule_id为1，表示规则的唯一标识符。
active为1，表示规则处于激活状态。
match_digest为'^SELECT.*FROM UPDATE$'，表示匹配查询语句，以SELECT开头，中间可以有任意字符，以FROM UPDATE结尾。
destination_hostgroup为1，表示匹配成功后，将查询请求发送到hostgroup 1。
apply为1，表示应用该规则。

 -- 规则配置
 INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) VALUES (1,1,'^SELECT.*FROM UPDATE$',1,1);
 INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) VALUES (2,1,'^SELECT',2,1);
 INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) VALUES (3,1,'^SHOW',2,1);
 
 -- 保存规则
 LOAD MYSQL QUERY RULES TO RUNTIME;
 SAVE MYSQL QUERY RULES TO DISK;
 
 -- 查看规则
 select rule_id,match_digest,destination_hostgroup,apply from mysql_query_rules order by rule_id;

测试能否正常读取

直接在ProxySQL所在服务器的Linux命令行执行下列命令

 # 查看 read 用户连接的服务器信息
 mysql -uread -p123456 -h 127.0.0.1 -P6033 -e "SELECT @@hostname,@@port"
 
 # read 用户查看所有数据库
 ## 在代理服务器上执行读操作
 mysql -uread -p123456 -h 127.0.0.1 -P6033 -e "show databases;"
 
 # adm 用户创建数据库
 ## 执行创建操作，测试效果
 mysql -uadm -p123456 -h 127.0.0.1 -P6033 -e "create database test2;"
 
 # read 用户再次查看数据库
 ## 再次查询
 mysql -uread -p123456 -h 127.0.0.1 -P6033 -e "show databases;"

在代理服务器中查询执行记录

 # 可以看到proxysql将不同的请求分别发送给了不同的服务器处理，读取发送给slave节点，写入和删除发送给Master节点。
 -- 在proxysql服务器上执行
 mysql -uadmin -padmin -h127.0.0.1 -P6032
 
 # 查看路由统计
 select hostgroup,digest_text from stats_mysql_query_digest\G;
 
 ## ProxySQL保存配置：
 
 -- 修改后重新加载服务并保存配置，防止重启服务器后配置丢失。
 
 -- 重新加载并保存服务器设置
 LOAD MYSQL SERVERS TO RUNTIME;   -- 加载到运行时（生效）
 SAVE MYSQL SERVERS TO DISK;      -- 保存到磁盘（持久化）
 
 -- 重新加载并保存查询设置
 LOAD MYSQL QUERY RULES TO RUNTIME;   -- 加载到运行时
 SAVE MYSQL QUERY RULES TO DISK;      -- 保存到磁盘
 
 -- 重新加载并保存用户设置
 LOAD MYSQL USERS TO RUNTIME;   -- 加载到运行时
 SAVE MYSQL USERS TO DISK;      -- 保存到磁盘
 
 -- 重新加载并保存变量设置
 LOAD MYSQL VARIABLES TO RUNTIME;   -- 加载到运行时
 SAVE MYSQL VARIABLES TO DISK;      -- 保存到磁盘
 
 # 一次性保存所有配置你也可以用简化命令：
 在较新版本的 ProxySQL 已经不再支持了
 -- 一次性加载所有配置到运行时
 LOAD ALL TO RUNTIME;
 
 -- 一次性保存所有配置到磁盘
 SAVE ALL TO DISK;

重置配置方法

如果你遇到 ProxySQL 配置错误，需要删除或重置配置，通常可以按照以下步骤操作：

1. 删除错误配置

1.连接到 ProxySQL 数据库：

使用 mysql 命令行工具或其他 MySQL 客户端连接到 ProxySQL 实例。默认情况下，ProxySQL 的管理界面监听在 3306 端口（也可能是其他端口，取决于你的配置）。

 mysql -u admin -p -h 127.0.0.1 -P 6032

这里 6032 是 ProxySQL 默认的管理端口，admin 是默认的管理用户。

2.查看当前配置：在管理控制台中，你可以使用以下命令查看当前配置：

 SELECT * FROM mysql_servers;
 SELECT * FROM mysql_users;

3.删除或修改错误配置：假设你要删除某个错误的服务器配置：

 DELETE FROM mysql_servers WHERE hostname='错误的主机名';

对于用户配置，可以用类似的方式删除：

 DELETE FROM mysql_users WHERE username='错误的用户名';

4.应用更改：删除或修改配置后，记得应用更改到 ProxySQL 配置：

 LOAD MYSQL SERVERS TO RUNTIME;
 SAVE MYSQL SERVERS TO DISK;

对于用户配置：

 LOAD MYSQL USERS TO RUNTIME;
 SAVE MYSQL USERS TO DISK;

2. 重置 ProxySQL 配置

方法一：可以执行初始化命令：

 proxysql --initial

方法二：也可以删除所有配置并重新加载：

1.删除所有配置：

 -- 删除所有的 MySQL 服务器配置
 DELETE FROM mysql_servers;
 
 -- 删除所有的 MySQL 用户配置
 DELETE FROM mysql_users;
 
 -- 删除所有的 MySQL 规则配置
 DELETE FROM mysql_query_rules;
 
 -- 删除所有的 MySQL 监控配置
 DELETE FROM stats_mysql_status;

2.重新加载配置：

 -- 将删除的配置应用到运行时
 LOAD MYSQL SERVERS TO RUNTIME;
 LOAD MYSQL USERS TO RUNTIME;
 LOAD MYSQL QUERY RULES TO RUNTIME;
 
 -- 将运行时配置保存到磁盘
 SAVE MYSQL SERVERS TO DISK;
 SAVE MYSQL USERS TO DISK;
 SAVE MYSQL QUERY RULES TO DISK;
 
 ## 退出重启服务
 sudo systemctl restart proxysql

FanXing