MySQL的读写分离

1454 views

MySQL的读写分离

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

一、为什么需要读写分离？

在多数业务场景中，读操作的频率远高于写操作（例如电商商品浏览、新闻查看等）。如果读写操作都依赖单一数据库，会导致：

  • 写操作（如订单提交）占用数据库资源，影响读操作响应速度；
  • 单库负载过高，难以应对高并发请求；
  • 单点故障风险高，一旦数据库宕机，整个系统不可用。

读写分离通过"主库处理写，从库处理读"的方式，解决上述问题：

  • 分担主库压力，提高读操作吞吐量；
  • 从库可横向扩展（增加从库数量），应对读请求增长；
  • 主从架构天然具备数据备份能力，提高系统可用性。

二、实现基础：主从复制（Master-Slave Replication）

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

1.主库（Master）：

  • 当主库执行写操作时，会将操作记录到binlog（二进制日志） 中（需在主库配置开启）。

2.从库（Slave）：

  • 从库启动两个线程：IO线程和SQL线程。
  • IO线程：连接主库，读取主库的binlog，写入从库的relay log（中继日志）。
  • SQL线程：读取relay log，解析并执行日志中的SQL操作，同步主库数据。

三、读写分离的实现方式

根据路由逻辑的位置，常见实现方式分为两类：

1. 应用层实现（代码层面路由）

在应用程序中直接判断SQL操作类型，手动将写操作路由到主库，读操作路由到从库。
实现方式：

  • 配置多数据源（主库数据源、从库数据源）；
  • 通过ORM框架（如MyBatis、Hibernate）或自定义拦截器，根据SQL类型选择数据源。

示例（伪代码）：

// 数据源路由逻辑
if (sql是写操作) {
  使用主库数据源执行SQL;
} else {
  使用从库数据源执行SQL; // 从库可轮询选择，负载均衡
}

优点：实现简单，无额外中间件依赖，性能损耗低。

缺点：与应用代码耦合，若需修改路由规则（如新增从库），需修改应用代码，适合小型项目。

2. 中间件实现（代理层路由）

通过专门的数据库中间件代理所有数据库请求，自动解析SQL并路由到对应库，应用程序只需连接中间件，无需关心底层主从架构。

常用中间件：

  • Sharding-JDBC：轻量级Java框架，嵌入应用作为JDBC驱动，无独立进程；
  • MyCat：基于MySQL协议的分布式中间件，独立部署，支持读写分离、分库分表；
  • ProxySQL：高性能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的读写分离

1、读写分离，简单地说是把对数据库的读和写操作分开，以对应不同的数据库服务器;

2、主数据库提供写操作，从数据库提供读操作，这样能有效地减轻单台数据库的压力;

3、基于mysql主从复制，实现读写分离前需要先配置好主从复制。

IP 角色 软件
192.168.8.100 Master MySQL,ProxySQL
192.168.8.101 Slave MySQL

安装proxysql

CentOS7安装proxysql

# 配置官方yum源,或直接下载rpm包（下载地址：https://github.com/sysown/proxysql/releases）
cat > /etc/yum.repos.d/proxysql.repo << EOF
[proxysql]
name=ProxySQL YUM repository
baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/centos/\$releasever
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 -uadmin -padmin -h127.0.0.1 -P6032
# 或者使用第三方客户端：mycli -uadmin -padmin -h"127.0.0.1" -P6032

Admin> show databases;

image-20240910171438970

这些库的含义：

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

ProxySQL运行机制

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

proxysql

ProxySQL账号

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

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

Admin> select @@admin-admin_credentials;
+---------------------------+
|@@admin-admin_credentials  |
+---------------------------+
|admin:admin                |
+---------------------------+
1 row in set (0.001 sec)
Admin> set admin-admin_credentials='admin:admin;root:root123';
Query OK, 1 row affected (0.000 sec)
Admin> select @@admin-admin_credentials;
+---------------------------+
|@@admin-admin_credentials  |
+---------------------------+
| admin:admin;root:root123  |
+---------------------------+
1 row in set (0.001 sec)
Admin> load admin variables to runtime; # 使修改立即生效
Query OK, 0 rows affected (0.000 sec)
Admin> save admin variables to disk;  # 使修改永久保存到磁盘
Query OK, 35 rows affected (0.004 sec)

配置内容

使用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表;

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

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

2.将监控用户加入到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';

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

注意：是在proxysql中操作

-- Master主节点
INSERT INTO mysql_servers(hostgroup_id,hostname,port,weight,comment) VALUES (1,'192.168.8.100',3306,1,'Write group');

-- slave节点
INSERT INTO mysql_servers(hostgroup_id,hostname,port,weight,comment) VALUES (2,'192.168.8.101',3306,1,'Read group');

-- 保存
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

4.查看添加的mysql集群

SELECT * FROM mysql_servers;

5.在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;

6.在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;

7.在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;

8.测试能否正常读取

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

mysql -uread -p123456 -h 127.0.0.1 -P6033 -e "SELECT @@hostname,@@port"

# 在代理服务器上执行读操作
mysql -uread -p123456 -h 127.0.0.1 -P6033 -e "show databases;"

# 执行创建操作，测试效果
mysql -uadm -p123456 -h 127.0.0.1 -P6033 -e "create database test2;"

# 再次查询
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 配置错误，需要删除或重置配置，通常可以按照以下步骤操作：

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

3. 其他检查

确保 ProxySQL 配置文件（通常是 proxysql.cnf）中没有错误，并检查任何可能的日志文件来确认问题的具体细节。

这些步骤应该能帮助你解决 ProxySQL 配置错误。如果问题依然存在，可能需要更详细地检查 ProxySQL 的日志或配置文档。

总结

MySQL读写分离通过拆分读写操作、利用主从复制同步数据，有效提升了系统的并发能力和可用性。实际应用中需根据业务规模选择合适的实现方式（应用层或中间件），并重点解决数据一致性、故障转移等问题。