MySQL的读写分离


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读写分离

1ProxySQL是一款开源的使用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_hostgroupsmysql_group_replication_hostgroupsruntime_mysql_galera_hostgroupsruntime_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读写分离通过拆分读写操作、利用主从复制同步数据,有效提升了系统的并发能力和可用性。实际应用中需根据业务规模选择合适的实现方式(应用层或中间件),并重点解决数据一致性、故障转移等问题。