MySQL主从复制与延迟读一致性实践

--- title: MySQL主从复制与延迟读一致性实践 keywords: - MySQL - 主从复制 - 延迟读 - 只读副本 - GTID - seconds_behind_source description: 配置MySQL主从复制与读写分离，并通过延迟读策略保障一致性，提供可验证的命令与监控方法。 date: 2025-11-26 tags: - GTID - MySQL - seconds_behind_source - 主从复制 - 只读副本 - 复制 - 延迟读 - 数据库 categories: - 文章资讯 - 技术教程 --- 概述 - 目标：启用主从复制并在应用层进行读写分离，结合延迟读策略保证关键读与写一致性，降低热点压力。 - 适用：高并发读写场景、报表与查询分担。 核心与实战 - 启用GTID复制（MySQL 8）： ``` -- 主库配置 SET GLOBAL gtid_mode = ON; SET GLOBAL enforce_gtid_consistency = ON; -- 创建复制用户 CREATE USER 'repl'@'%' IDENTIFIED BY 'secret'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'; -- 从库配置并建立复制 CHANGE REPLICATION SOURCE TO SOURCE_HOST='primary', SOURCE_USER='repl', SOURCE_PASSWORD='secret', SOURCE_AUTO_POSITION=1; START REPLICA; ``` - 读写分离要点： ``` -- 应用层将写路由到主库，读路由到只读从库 -- 关键读（写后读）采用强一致：读主库或等待复制追上 ``` - 延迟读策略（等待复制追上）： ``` -- 在事务结束后记录当前GTID或binlog位置；读从库前检查 SELECT SOURCE_UUID, SOURCE_LOG_FILE, SOURCE_LOG_POS FROM performance_schema.replication_connection_status\G SELECT seconds_behind_source FROM performance_schema.replication_applier_status_by_worker\G -- 当延迟小于阈值（如<500ms）则允许读从库，否则读主库 ``` 示例 - 监控复制状态： ``` SHOW REPLICA STATUS\G ``` - ProxySQL路由（简要示例）： ``` INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup) VALUES (1,1,'^SELECT',20); INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup) VALUES (2,1,'^(INSERT|UPDATE|DELETE)',10); LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK; ``` 验证与监控 - 延迟与一致性： - 观察`seconds_behind_source`与复制错误；关键请求执行强一致策略。 - 复制健康： - 监控`Replica_IO_Running/Replica_SQL_Running`状态与错误码；设置告警。 - 容灾与切换： - 使用`SOURCE_AUTO_POSITION=1`与GTID简化切换；确保只读副本`read_only=ON`。 常见误区 - 盲目读从库导致写后读不一致；关键请求需读主库或等待复制追上。 - 未启用GTID导致切换复杂且易错；复制建议使用GTID。 - 未监控复制延迟与错误；需建立指标与告警。 结语 - 通过GTID复制与读写分离，并结合延迟读策略，可在保障一致性的同时提升读性能与稳定性。