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