如何在MySQL中实现读写分离？ProxySQL配置读写分离的完整流程！（读写.分离.流程.完整.配置...）

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

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

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

解决方案

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

  1. 环境准备： 首先，你得有一个已经搭建好的MySQL主从复制环境。确保主库和从库之间的数据同步是健康的，没有明显的延迟。这是所有读写分离策略的基础。然后，在独立的服务器上安装ProxySQL。安装过程相对直接，通常通过包管理器或者编译源码。

  2. 连接ProxySQL管理接口： ProxySQL有一个独立的管理接口，默认监听在6032端口。你可以像连接MySQL一样连接它：

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

    这里

    admin
    是默认用户名和密码，
    127.0.0.1
    是ProxySQL所在服务器的IP，
    6032
    是管理端口。进入后，你就可以开始配置了。

  3. 添加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的连接数。

  4. 配置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
    非常重要，它确保在事务中的所有查询都会被路由到同一个后端服务器，通常是主库，避免了事务跨库的问题。

  5. 定义查询路由规则： 这是读写分离的核心。我们需要编写规则，告诉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
    表示一旦匹配到规则，就停止后续规则的匹配。

  6. 测试与监控： 配置完成后，将应用程序的数据库连接指向ProxySQL的默认监听端口（通常是6033）。然后通过

    mysql_servers_stats
    、
    mysql_query_rules_stats
    等视图来监控请求的路由情况和后端服务器的健康状态。
为什么需要读写分离？它能解决哪些痛点？

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

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

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

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

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

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

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

一个非常常见的“坑”就是主从复制延迟（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只是其中一种，而且是非常优秀的一种。但话说回来，没有银弹，不同的方案各有优劣，适用场景也不同。

  1. 应用层实现（ORM或自定义代码）：

    • 原理： 应用程序自己维护两个数据库连接池，一个连主库，一个连从库。在代码中根据SQL类型或者业务逻辑来选择使用哪个连接。
    • 优点：
      • 极致的灵活性和控制力： 应用程序可以根据最复杂的业务逻辑来决定路由，比如实现“读写分离后的读写一致性”（Read-Your-Own-Writes），即用户刚写入的数据，后续的读请求也要确保能从主库读到。
      • 无额外中间件： 架构相对简单，不需要引入额外的代理层。
    • 缺点：
      • 侵入性强，开发成本高： 需要修改应用程序代码，增加开发和维护的复杂度。如果有多套应用程序，每套都要实现一遍。
      • 难以统一管理： 路由逻辑分散在各个应用中，升级或调整路由策略时非常麻烦。
      • 容易出错： 业务逻辑复杂时，手动管理路由容易引入bug。

  2. 数据库中间件（如MyCAT, ShardingSphere）：

    • 原理： 这些是更重量级的数据库中间件，它们不仅能实现读写分离，还能提供分库分表、分布式事务等更高级的功能。它们通常会解析SQL，然后根据配置规则进行路由。
    • 优点：
      • 功能强大： 除了读写分离，还能解决更复杂的数据库扩展性问题，如水平分片。
      • 对应用透明： 应用程序连接中间件，中间件负责与后端数据库交互。
    • 缺点：
      • 部署和维护复杂： 引入了额外的中间件层，增加了架构的复杂性，需要专门的人员进行维护。
      • 资源消耗： 中间件本身需要消耗CPU和内存资源。
      • 学习曲线陡峭： 功能多，配置项也多，学习成本相对较高。

  3. 云服务商提供的解决方案（如AWS RDS Proxy, Azure Database for MySQL Flexible Server的内置读写分离）：

    • 原理： 云服务商在数据库服务层面提供了内置的读写分离代理功能，用户只需要简单的配置就能启用。
    • 优点：
      • 极简部署： 通常只需要在控制台点几下鼠标就能完成配置。
      • 托管服务： 云服务商负责代理的维护、升级和高可用，省心省力。
      • 与云生态集成： 更好地与云平台的其他服务集成。
    • 缺点：
      • 厂商锁定： 强依赖特定云服务商，迁移成本高。
      • 灵活性受限： 通常配置选项不如ProxySQL或自定义中间件那样丰富。
      • 成本： 可能会产生额外的服务费用。

  4. 基于LVS/HAProxy等负载均衡器：

    • 原理： 利用传统的TCP/HTTP负载均衡器，将请求分发到不同的数据库实例。但这种方式通常不具备SQL解析能力，无法智能区分读写请求。需要结合外部脚本或应用层逻辑进行辅助。
    • 优点：
      • 成熟稳定： 负载均衡器技术非常成熟，性能高。
    • 缺点：
      • 无SQL感知： 无法根据SQL语句内容进行路由，需要应用层配合或者通过端口区分（例如主库监听一个端口，从库监听另一个端口）。
      • 功能单一： 只能做简单的连接转发和健康检查，缺乏ProxySQL的连接池、查询重写等高级功能。

在我看来，ProxySQL在性能、功能和易用性之间找到了一个很好的平衡点。它比应用层实现更透明、更集中，比MyCAT等重量级中间

以上就是如何在MySQL中实现读写分离？ProxySQL配置读写分离的完整流程！的详细内容，更多请关注知识资源分享宝库其它相关文章！

标签:  读写 分离 流程 

相关阅读

发表评论:

◎欢迎参与讨论，请在这里发表您的看法、交流您的观点。

最近发表

标签列表