如何在MySQL中实现读写分离？ProxySQL配置读写分离的完整流程！-mysql教程-PHP中文网

答案：ProxySQL通过SQL解析与规则路由实现MySQL读写分离，将写请求路由至主库、读请求至从库，减轻主库压力，提升系统性能与扩展性。需配置主从环境、ProxySQL管理接口、后端服务器、用户权限及查询规则，并启用事务持久化与延迟监控，避免数据不一致与性能瓶颈。相较于应用层实现、MyCAT、云服务方案，ProxySQL在透明性、灵活性与性能间更均衡。

如何在mysql中实现读写分离？proxysql配置读写分离的完整流程！

在MySQL中实现读写分离，核心目的是为了提升数据库的性能和可扩展性，尤其是在读操作远多于写操作的场景下。简单来说，就是把写请求（INSERT, UPDATE, DELETE, DDL等）路由到主库（Master），而把读请求（SELECT）路由到从库（Replica），以此分担主库的压力。ProxySQL作为一款高性能、高可用的数据库代理，提供了一个非常优雅且对应用透明的解决方案，它能智能地解析SQL语句，并根据预设规则将请求分发到不同的后端数据库服务器。

解决方案

要通过ProxySQL配置MySQL读写分离，我们通常需要以下几个步骤。这个过程，在我看来，既考验对数据库架构的理解，也需要对ProxySQL配置细节的耐心。

环境准备： 首先，你得有一个已经搭建好的MySQL主从复制环境。确保主库和从库之间的数据同步是健康的，没有明显的延迟。这是所有读写分离策略的基础。然后，在独立的服务器上安装ProxySQL。安装过程相对直接，通常通过包管理器或者编译源码。
连接ProxySQL管理接口： ProxySQL有一个独立的管理接口，默认监听在6032端口。你可以像连接MySQL一样连接它：
```
mysql -u admin -padmin -h 127.0.0.1 -P 6032
```
登录后复制
这里
```
admin
```
登录后复制
是默认用户名和密码，
```
127.0.0.1
```
登录后复制
是ProxySQL所在服务器的IP，
```
6032
```
登录后复制
是管理端口。进入后，你就可以开始配置了。

添加MySQL后端服务器： 我们需要告诉ProxySQL有哪些MySQL服务器可用。通常我们会定义两个主机组（hostgroup），一个用于写操作（比如ID为10），一个用于读操作（比如ID为20）。

-- 添加主库到写主机组 (hostgroup_id=10)
INSERT INTO mysql_servers (hostname, port, hostgroup_id, weight, max_connections) VALUES ('your_master_ip', 3306, 10, 100, 1000);
-- 添加从库到读主机组 (hostgroup_id=20)
INSERT INTO mysql_servers (hostname, port, hostgroup_id, weight, max_connections) VALUES ('your_replica_ip', 3306, 20, 100, 1000);

-- 如果有多个从库，可以继续添加，它们都会被分配到hostgroup_id=20
-- INSERT INTO mysql_servers (hostname, port, hostgroup_id, weight, max_connections) VALUES ('your_replica_ip_2', 3306, 20, 100, 1000);

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

登录后复制

weight

登录后复制

参数用于负载均衡，

max_connections

登录后复制

则限制ProxySQL与后端MySQL的连接数。

配置MySQL用户： ProxySQL需要知道如何连接到这些后端MySQL服务器，以及应用程序将如何连接到ProxySQL本身。

-- 配置ProxySQL连接后端MySQL的用户
-- 这是一个ProxySQL内部使用的用户，它需要有足够的权限连接到主从库
INSERT INTO mysql_users (username, password, default_hostgroup, active, max_connections) VALUES ('proxysql_backend_user', 'your_backend_password', 10, 1, 1000);

-- 配置应用程序连接ProxySQL的用户
-- 应用程序会用这个用户连接到ProxySQL（默认端口6033），然后ProxySQL会根据规则转发请求
INSERT INTO mysql_users (username, password, default_hostgroup, active, max_connections, transaction_persistent) VALUES ('app_user', 'your_app_password', 10, 1, 2000, 1);

登录后复制

这里

transaction_persistent=1

登录后复制

非常重要，它确保在事务中的所有查询都会被路由到同一个后端服务器，通常是主库，避免了事务跨库的问题。

定义查询路由规则： 这是读写分离的核心。我们需要编写规则，告诉ProxySQL哪些查询应该去主库，哪些去从库。规则的顺序至关重要，更具体的规则应该放在前面。

-- 规则1: SELECT FOR UPDATE 必须去主库 (ID=10)，因为它是写锁
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES (1, 1, '^SELECT.*FOR UPDATE$', 10, 1);

-- 规则2: 所有以 SELECT, SHOW, EXPLAIN 开头的查询去从库 (ID=20)
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES (2, 1, '^(SELECT|SHOW|EXPLAIN)\s', 20, 1);

-- 规则3: 所有的 DDL 语句去主库 (ID=10)
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES (3, 1, '^(ALTER|CREATE|DROP|RENAME|TRUNCATE)\s', 10, 1);

-- 规则4: 所有其他查询 (INSERT, UPDATE, DELETE 等) 默认去主库 (ID=10)
-- 这条规则一般放在最后，作为兜底。
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES (4, 1, '.*', 10, 1);

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

登录后复制

正则表达式是这里的关键，需要仔细测试。

apply=1

登录后复制

表示一旦匹配到规则，就停止后续规则的匹配。

测试与监控： 配置完成后，将应用程序的数据库连接指向ProxySQL的默认监听端口（通常是6033）。然后通过
```
mysql_servers_stats
```
登录后复制
、
```
mysql_query_rules_stats
```
登录后复制
等视图来监控请求的路由情况和后端服务器的健康状态。

为什么需要读写分离？它能解决哪些痛点？

说起读写分离，我个人觉得它就像是给数据库系统做了一次“分流手术”。在很多业务场景下，数据库的读操作量远超写操作，比如电商网站的商品浏览、新闻网站的文章阅读等等。如果所有的请求都涌向同一个主库，很快它就会成为性能瓶颈，导致一系列的“痛点”。

首先，最直接的就是性能瓶颈。主库在处理大量读写请求时，CPU、内存、IO都会面临巨大压力。读写分离能将大量的读请求分散到多个从库上，大大减轻主库的负担，让主库可以专注于处理写操作，从而提升整体的响应速度。

其次，它能显著提升数据库的扩展性。当业务增长，读请求量继续攀升时，我们只需要增加从库的数量，就可以横向扩展读的能力，而不需要去升级主库的硬件，这种弹性是单一数据库架构难以比拟的。

再者，读写分离也是高可用架构的一个重要组成部分。虽然它本身不是一个高可用方案，但它依赖于主从复制。一旦主库出现故障，我们可以快速将一个从库提升为新的主库，保证服务的连续性。同时，从库也可以用于数据备份、跑报表或者进行一些耗时的数据分析，而不会影响线上主库的性能。

我记得以前遇到过一个系统，高峰期报表查询直接把主库拖垮，导致线上交易都受影响。引入读写分离后，这些耗时的报表查询被导向了专门的从库，主库的压力瞬间就释放了，业务体验也好了很多。所以，它解决的痛点主要包括：主库负载过高、查询响应慢、系统扩展性差、以及特定业务（如报表分析）对线上业务的干扰。

配置ProxySQL时有哪些常见陷阱和优化技巧？

ProxySQL虽然强大，但配置起来也有些门道，我当初踩过不少坑。这里分享一些常见的陷阱和优化技巧，希望能帮大家少走弯路。

小绿鲸英文文献阅读器

英文文献阅读器，专注提高SCI阅读效率

199

查看详情

一个非常常见的“坑”就是主从复制延迟（Replication Lag）。读写分离的本质是把读请求分发到从库，但如果从库的数据更新比主库慢，那么应用程序可能会读到“旧”数据。这对于对数据一致性要求高的业务来说是致命的。

优化技巧：
- 强一致性读： 对于那些需要立即读到最新数据的场景（比如用户注册后立即查询个人信息），一定要把这些
```
SELECT
```
  登录后复制
  语句也路由到主库。ProxySQL的规则里，
```
SELECT ... FOR UPDATE
```
  登录后复制
  默认就应该去主库，因为它涉及写锁。
- ProxySQL的延迟感知： ProxySQL本身可以通过
```
mysql_replication_hostgroups
```
  登录后复制
  来配置延迟阈值，当从库延迟超过某个值时，ProxySQL可以暂时不将读请求路由到这个从库。这需要主从库的
```
server_id
```
  登录后复制
  和
```
read_only
```
  登录后复制
  参数正确配置。不过，我个人觉得，对于简单的读写分离，更常见的是在应用层面接受最终一致性，或者通过规则将关键查询路由到主库。
- 监控复制状态： 持续监控MySQL的
```
SHOW SLAVE STATUS
```
  登录后复制
  输出，确保延迟在可接受范围内。

第二个容易出问题的地方是事务管理。在一个事务中，所有的操作必须在同一个数据库实例上完成，否则会破坏事务的原子性。

优化技巧：
- transaction_persistent=1
  登录后复制
  ：在配置应用程序连接ProxySQL的用户时，务必设置
```
transaction_persistent=1
```
  登录后复制
  。这会告诉ProxySQL，一旦一个会话开启了事务，它就会被“粘”在当前连接的后端MySQL服务器上，直到事务结束，所有后续的查询都会发送到同一个服务器。这通常意味着事务中的所有操作（包括读）都会被路由到主库，这是正确的行为。

规则顺序和正则表达式也是个大挑战。ProxySQL的查询规则是按

rule_id

登录后复制

从小到大顺序匹配的。一旦匹配成功且

apply=1

登录后复制

，就不会再往下匹配了。

优化技巧：
- “特殊”在前，“通用”在后： 比如
```
SELECT ... FOR UPDATE
```
  登录后复制
  这种必须去主库的读请求，它的规则应该在普通的
```
SELECT
```
  登录后复制
  去从库的规则之前。
- 测试正则表达式： 使用
```
mysql_query_rules_stats
```
  登录后复制
  视图来查看规则的命中情况，确保查询被正确路由。
```
SHOW PROXYSQL STATS
```
  登录后复制
  也能提供很多有用的信息。

ProxySQL本身的健康检查也需要细心配置。如果ProxySQL无法准确判断后端MySQL服务器的健康状态，可能会把请求发到宕机的服务器上。

优化技巧：
- 调整
  mysql_servers
  登录后复制
  参数：
```
max_ping_success_count
```
  登录后复制
  、
```
max_ping_fail_count
```
  登录后复制
  、
```
ping_interval
```
  登录后复制
  等参数决定了ProxySQL检查后端服务器的频率和判断其健康状态的阈值。根据实际网络环境和服务器响应速度进行调整，避免误判。

最后，配置持久化是个小但致命的细节。所有通过管理接口修改的配置，都需要执行

LOAD TO RUNTIME

登录后复制

使其生效，并执行

SAVE TO DISK

登录后复制

将其写入配置文件，否则ProxySQL重启后，你的所有配置都会丢失。我承认，我就是那个忘记

SAVE TO DISK

登录后复制

，然后重启ProxySQL后一脸懵逼的人。

除了ProxySQL，还有哪些读写分离的实现方案，各自优劣如何？

读写分离的实现方案其实挺多的，ProxySQL只是其中一种，而且是非常优秀的一种。但话说回来，没有银弹，不同的方案各有优劣，适用场景也不同。

应用层实现（ORM或自定义代码）：
- 原理： 应用程序自己维护两个数据库连接池，一个连主库，一个连从库。在代码中根据SQL类型或者业务逻辑来选择使用哪个连接。
- 优点：
  - 极致的灵活性和控制力： 应用程序可以根据最复杂的业务逻辑来决定路由，比如实现“读写分离后的读写一致性”（Read-Your-Own-Writes），即用户刚写入的数据，后续的读请求也要确保能从主库读到。
  - 无额外中间件： 架构相对简单，不需要引入额外的代理层。
- 缺点：
  - 侵入性强，开发成本高： 需要修改应用程序代码，增加开发和维护的复杂度。如果有多套应用程序，每套都要实现一遍。
  - 难以统一管理： 路由逻辑分散在各个应用中，升级或调整路由策略时非常麻烦。
  - 容易出错： 业务逻辑复杂时，手动管理路由容易引入bug。
数据库中间件（如MyCAT, ShardingSphere）：
- 原理： 这些是更重量级的数据库中间件，它们不仅能实现读写分离，还能提供分库分表、分布式事务等更高级的功能。它们通常会解析SQL，然后根据配置规则进行路由。
- 优点：
  - 功能强大： 除了读写分离，还能解决更复杂的数据库扩展性问题，如水平分片。
  - 对应用透明： 应用程序连接中间件，中间件负责与后端数据库交互。
- 缺点：
  - 部署和维护复杂： 引入了额外的中间件层，增加了架构的复杂性，需要专门的人员进行维护。
  - 资源消耗： 中间件本身需要消耗CPU和内存资源。
  - 学习曲线陡峭： 功能多，配置项也多，学习成本相对较高。
云服务商提供的解决方案（如AWS RDS Proxy, Azure Database for MySQL Flexible Server的内置读写分离）：
- 原理： 云服务商在数据库服务层面提供了内置的读写分离代理功能，用户只需要简单的配置就能启用。
- 优点：
  - 极简部署： 通常只需要在控制台点几下鼠标就能完成配置。
  - 托管服务： 云服务商负责代理的维护、升级和高可用，省心省力。
  - 与云生态集成： 更好地与云平台的其他服务集成。
- 缺点：
  - 厂商锁定： 强依赖特定云服务商，迁移成本高。
  - 灵活性受限： 通常配置选项不如ProxySQL或自定义中间件那样丰富。
  - 成本： 可能会产生额外的服务费用。
基于LVS/HAProxy等负载均衡器：
- 原理： 利用传统的TCP/HTTP负载均衡器，将请求分发到不同的数据库实例。但这种方式通常不具备SQL解析能力，无法智能区分读写请求。需要结合外部脚本或应用层逻辑进行辅助。
- 优点：
  - 成熟稳定： 负载均衡器技术非常成熟，性能高。
- 缺点：
  - 无SQL感知： 无法根据SQL语句内容进行路由，需要应用层配合或者通过端口区分（例如主库监听一个端口，从库监听另一个端口）。
  - 功能单一： 只能做简单的连接转发和健康检查，缺乏ProxySQL的连接池、查询重写等高级功能。