MySQL主从复制实战：从binlog原理到延迟排查全解析

一个看起来很简单的问题——主从复制为什么要写因为我见过太多人栽在这上面配置了半天Slave_IO_Running和Slave_SQL_Running显示两个Yes结果一写数据主从就断还有人把主库 binlog 格式搞错从库直接追不上更有人一听“主从”就觉得是“读写分离”结果把业务写流量也怼到从库数据不一致了才回来查日志。这篇不是抄官方文档是把我在生产环境里实际配置、排错、压测的经验拆开揉碎讲清楚从原理到实操从 binlog 格式选型到延迟排查一条线走完希望能帮你少走几步弯路。1. 内容整体设计与思路拆解1.1 主从复制到底解决了什么问题先说个最常见的误区主从复制不等于读写分离。读写分离只是主从复制的一种典型应用不是它的全部。围绕“Mysql之主从复制”这个核心主题我梳理了几个真实场景备份与容灾从库作为热备节点主库硬件故障时能快速切换避免业务长时间停摆。注意从库是“热备”而不是“备份”它不能替代定时全量备份因为一个DROP TABLE会在主从同时执行。读写分离主库处理写请求从库分担读请求降低单库压力。但前提是业务能接受一定的读延迟通常几毫秒到几秒不等。数据分析与报表从库专门跑复杂的SELECT、JOIN、汇总查询不拖累主库的 OLTP 性能。多环境复用从库可以作为测试环境的数据源避免直接连生产库。异地容灾与多机房部署通过主从复制把数据同步到异地机房配合高可用架构实现机房级容灾。一句话总结主从复制的本质是把主库的 binlog“传输”到从库并“重放”让从库的数据尽可能接近主库。1.2 复制架构的三种主流形态一主一从最简单常用于小型项目或单一容灾需求。一主多从主库承担所有写流量多个从库分摊读流量。注意从库数量不宜过多否则主库的 binlog dump 线程压力会上升一般建议不超过 5 个左右。级联复制主库 - 中继从库 - 其他从库。适合从库数量多的场景中继从库承担转发压力。但一定要在中间节点上开启log_slave_updates否则它的从库拿不到数据。双主互备主主复制两个库互为主从适用于特殊高可用场景但要注意冲突与脑裂问题生产慎用必须有完善的冲突检测机制。1.3 为什么选 binlog 复制而不是其他方案很多人问过我为什么不直接用触发器、定时任务同步或者干脆用消息队列这里我拉个表对比一下方便你理解选型的逻辑。方案同步实时性数据一致性实现复杂度对主库性能影响适用场景触发器 应用双写高难以保证依赖应用逻辑高业务侵入强中极少使用定时任务全量同步分钟级甚至更低差存在窗口期低高锁表现象明显数据量小的冷备Canal / Debezium 解析 binlog近实时高基于 binlog中需要额外组件中主要是网络和解析开销异构同步、数据集成如同步到 ES、ClickHouseMySQL 原生主从复制近实时秒级以内高基于 binlog 日志低内置能力低主库只需 dump binlog数据库自身冗余、读写分离、容灾首选原生主从复制的核心优势在于它复用 MySQL 自身的 binlog 机制不需要业务代码介入能够保证数据的最终一致性且对主库的性能影响最小。这也是它成为 MySQL 高可用架构基石的原因。2. 核心细节解析与实操要点2.1 主库必须关注的三个参数配置主从之前先理解主库要做哪些事。主库需要开启 binlog并且要为每个从库维护一个 dump 线程专门负责读取 binlog 并推送给从库。这里有几个关键参数和它们的含义server_id每台 MySQL 实例的唯一标识必须不同1~2^32-1否则主从会互相认为对方是自己导致复制链路错乱。这是最常见的低级错误之一。log_bin开启 binlog 并指定文件名前缀如/data/mysql/logs/mysql-bin。binlog_format强烈建议使用ROW格式。理论上还有STATEMENT和MIXED但 ROW 是主流具体原因下面单独讲。binlog_row_image5.6 支持默认值是FULL使用MINIMAL可以减少日志体积详情见下文。sync_binlog控制 binlog 刷盘策略。0由系统决定刷新1每次事务提交都刷盘最安全但对性能有一定影响。生产环境如果追求极致数据安全强烈建议1配合innodb_flush_log_at_trx_commit1。server_uuid不用手动配置MySQL 自动生成但要注意克隆数据目录或复制配置文件时不要把这个文件也复制过去否则 UUID 重复会导致从库连接异常。2.2 binlog 格式选型ROW 为什么是主流很多新手喜欢用STATEMENT因为它日志量小、可读性强。但它有个很隐蔽的问题存储过程、UUID()、NOW()这类非确定性函数在某些场景下主从执行结果会不一致。比如DELETE ... LIMIT 1在主从上删除的记录行不同从库数据就产生了偏差。ROW格式记录的是每行数据的实际变更前后值以“事件”方式存储。无论主库执行什么 SQL从库都看到最终结果。它的缺点很明确日志量相对更大特别是一条UPDATE涉及大量行时会明显膨胀。但 MySQL 从 5.7 开始支持binlog_row_imageMINIMAL只记录被修改的字段和识别行所需的主键/唯一键信息能显著减少 ROW 日志的体积。实测下来很多业务场景下体积增幅从 3~5 倍可以降到 1.5 倍左右完全可以接受。性能优先兼安全可控生产环境首选 ROW MINIMAL。2.3 从库的关键参数和“自毁式”陷阱从库的配置相比主库少很多但有个“自毁式陷阱”必须注意server_id必须与主库不同不必多说。relay_log中继日志文件名前缀默认使用主机名建议手动指定比如/data/mysql/logs/mysql-relay-bin避免主机名变化导致找不到中继日志。read_only强烈建议开启read_onlyON防止应用误连从库写入数据。需要注意read_only只对普通账号生效super权限账号如 root依然可写所以权限管理要配套。log_slave_updates在级联复制或主主复制架构中必须开否则该从库不会把“从主库同步来的数据”继续写入自己的 binlog下游节点就断了。注意有些数据库版本默认关闭。relay_log_recovery从库崩溃后自动丢弃未执行完的中继日志并从主库重新拉取。生产强烈建议打开。skip_slave_startMySQL 重启后自动拉起复制线程的开关建议按需设置。2.4 GTID 复制经典的事务一致性保障再讲一个复制机制的大头——GTID全局事务标识符。GTID 是 MySQL 5.6 引入的5.7 和 8.0 已完全是主流配置。它给每个事务分配一个全局唯一 ID由server_uuid:transaction_id组成从库不再依赖 binlog 文件名和位置而是通过 GTID 追踪已经执行过的事务。这带来了几个显性优势故障切换后新主库的从库不需要重新计算 binlog 位置只需要“根据 GTID 集合拉取缺失事务”自动校验。从库跳过异常事务时更精确不会误跳过正确事务。拓扑变更如从库提升为主库时位置点追踪变得非常简单。但是 GTID 也有约束不支持 MyISAM 引擎事务性引擎要求某些 DDL 操作有限制。所以在老版本5.5 及以下上升级时要评估业务里是否大量使用 MyISAM。现在的主流版本5.7/8.0默认 InnoDBGTID 基本无感。别的不说“”这句话值得刻在脑子里——8.0 是原生主从复制的舒适区但前提是配置正确。3. 实操过程与核心环节实现3.1 环境规划与版本约定以常见的生产环境为例我用两台 Linux 服务器做演示。为了简化我直接用 MySQL 8.0重点关注 8.0.4x/8.4 等版本但同样的思路和方法对 5.7 完全适用只需注意个别参数名差异。大家自己测试时可以用 Docker 启动两个 MySQL 容器效果等同。3.2 主库配置开启 binlog 并创建复制账号先编辑主库的my.cnf添加以下关键配置[mysqld] server_id 101 log_bin /data/mysql/logs/mysql-bin binlog_format ROW binlog_row_image MINIMAL expire_logs_days 7 # 或 MySQL 8.0 中使用 binlog_expire_logs_seconds 604800 sync_binlog 1 innodb_flush_log_at_trx_commit 1解释一下每行的意义server_id 101指定主库标识。log_bin开启 binlog 日志目录及文件名前缀。注意目录需要提前创建好并赋予 mysql 用户写权限否则 MySQL 启动会报错。expire_logs_days 7或binlog_expire_logs_seconds 604800自动清理超过 7 天的 binlog。千万别配成 0否则 binlog 会无限增长把磁盘写满。sync_binlog 1和innodb_flush_log_at_trx_commit 1保证事务提交时 binlog 和 InnoDB 日志同时落盘避免主库崩溃后数据丢失或被从库跳过。当然性能会有一些折扣在数据一致性要求高的场景这个代价必须付。修改完配置重启主库/etc/init.d/mysql restart登录主库验证 binlog 已经打开SHOW MASTER STATUS;记录下返回的文件名和位置如mysql-bin.000001、157后面配置从库时有用但更推荐使用 GTID 自动定位。创建复制专用账号CREATE USER repl192.168.1.% IDENTIFIED BY YourStrongPass123; GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl192.168.1.%; FLUSH PRIVILEGES;这里权限只给REPLICATION SLAVE和REPLICATION CLIENT不需要给其他权限。REPLICATION CLIENT用于监控复制状态尽量保留。3.3 从库配置中继日志与复制链路建立从库的my.cnf添加[mysqld] server_id 102 relay_log /data/mysql/logs/mysql-relay-bin read_only ON relay_log_recovery 1 log_slave_updates 1重启从库后执行复制链路的建立命令假设使用 GTIDCHANGE MASTER TO MASTER_HOST 192.168.1.101, MASTER_USER repl, MASTER_PASSWORD YourStrongPass123, MASTER_AUTO_POSITION 1;若是非 GTID 模式则需要指定文件和位置CHANGE MASTER TO MASTER_HOST 192.168.1.101, MASTER_USER repl, MASTER_PASSWORD YourStrongPass123, MASTER_LOG_FILE mysql-bin.000001, MASTER_LOG_POS 157;执行完启动复制START SLAVE;检查状态重点看三行SHOW SLAVE STATUS\G这里输出很长重点关注Slave_IO_Running必须为Yes表示从库 IO 线程能正常连上主库并拉取 binlog。Slave_SQL_Running必须为Yes表示 SQL 线程能正常应用中继日志。Seconds_Behind_Master从库落后主库的秒数通常应为 0 或一个很小的值。注意这个值在某些异常情况下可能不准确详见常见问题。如果出现Connecting一般是网络不通、账号错误、MySQL 端口未放行等原因。逐个排查即可。3.4 验证复制链路基础测试三板斧链路建立后先做三件事确认复制真实可用。第一件在主库建一个测试库和测试表插入几条数据CREATE DATABASE IF NOT EXISTS testdb; USE testdb; CREATE TABLE t_user (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50), create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP); INSERT INTO t_user(name) VALUES (zhangsan), (lisi), (wangwu);第二件去从库查看USE testdb; SELECT * FROM t_user;如果能看到三行数据说明普通 DML 复制正常。第三件测试 DDL 复制。在主库执行ALTER TABLE t_user ADD COLUMN age INT DEFAULT 0;然后在从库DESC t_user确认字段已同步。这一步能验证 DDL 也能通过 binlog 同步而不是只有数据变化才能同步。3.5 从库误写入的防护实验前面提到read_onlyON这里实际验证一下。用普通业务账号在从库执行INSERTUSE testdb; INSERT INTO t_user(name) VALUES (evil);如果权限配置正确普通账号会被拒绝ERROR 1299 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement但 root 或 super 权限账号依然可以写入。这说明一条经验从库防误写read_only只是第一道防线真正的兜底要靠账号权限和规范管理。很多事故就出在“以为开了 read_only 就安全了结果 DBA 或应用用了 root”。4. 常见问题与排查技巧实录4.1 Slave_IO_Running 一直显示 Connecting这是新手问得最多的一个问题原因其实就那么几类。优先按这个顺序排查网络telnet 主库IP 3306通不通云服务器安全组是否放通本机 firewalld 是否阻止账号复制账号是否存在、密码是否正确、主机限制是否匹配SELECT user, host FROM mysql.user;直接看。主库bind-address如果只绑定了127.0.0.1外部自然无法访问改成0.0.0.0或用 Docker 时正确映射端口。主库skip_name_resolve如果开启从库 IP 必须能正确反向解析否则连接失败。防火墙与 SELinuxCentOS 上特别容易踩 SELinux 的坑临时关闭测试setenforce 0确认后再配置放行规则。4.2 Slave_SQL_Running 变 No错误码 1062 或 1032This happens when the same data already exists in the slave (duplicate key) or the target row cannot be found (non-existent row). The most common cause is manual writes to the slave or inconsistent data before replication was set up.处理思路分几步先确认是不是有人或应用往从库写过数据。有的话把从库数据修正再STOP SLAVE; START SLAVE;。如果错误码是1032找不到行很可能是主从数据此前不一致。使用方法STOP SLAVE; SET GLOBAL sql_slave_skip_counter N; START SLAVE;跳过指定数量事件但这种方式要谨慎使用因为可能跳过有效事务。如果错误是1062主键冲突优先考虑从库存在多余记录删掉记录后重试。不要一上来就跳过容易越跳越乱。使用 GTID 时更推荐用GTID_EXECUTED集合或者自建空事务方式跳过错误事务精确处理。4.x 小坑slave_skip_errors别乱开这里专门提醒一句slave_skip_errors 1062,1032这个配置只适合极端业务场景比如已知某张表经常出现必然的重复冲突否则千万不要在生产环境全局开启。它会掩盖真正的数据问题让不一致悄悄蔓延。4.3 主从延迟一直很大排队追不上主从延迟不只是网络慢的问题。遇到Seconds_Behind_Master持续飙升我一般按以下维度排查主库有大事务比如一次性UPDATE百万行数据、大批量DELETE、DDL 重建大表。这些操作在从库上同样耗时直接导致延迟。从库硬件差从库 CPU、内存、磁盘性能不如主库重放同样负载时能力不足。从库在跑复杂查询业务把大查询打到从库会占用 IO 和 CPU挤占复制线程资源。网络带宽瓶颈binlog 量太大传输速度跟不上。复制是单线程的早期版本直到 MySQL 5.7 才支持 MTS多线程复制并行从库重放。如果你的环境是 5.6 或更老版本大并发场景下延迟会更明显。MySQL 5.7 及以上可配置slave_parallel_workers。数据量膨胀ROW MINIMAL 未开启时binlog 体积可能巨大。针对以上我的建议大事务拆分不要一条 SQL 干几十万行。从库配置不能比主库差太多至少同等规格。低峰期做 DDL或者使用 gh-ost / pt-online-schema-change 之类的在线变更工具。开启并行复制slave_parallel_type LOGICAL_CLOCK和slave_parallel_workers 4或更高按 CPU 核数调整通常 4~8 比较合适。降低 binlog 冲击开启binlog_row_image MINIMAL压缩传输或在内网低延迟环境下优化。4.4 主从数据不一致如何双向校验不知道有没有人经历过“明明复制线程正常但对账发现数据对不上”的情况。我的经验是这种问题通常不是复制线程中断而是早期数据或特殊操作造成的隐藏不一致。比如之前从库手动改过数据、误用过sql_slave_skip_counter、遇到过非 GTID 模式下位置错位等。我现在的做法是定期做数据校验工具用pt-table-checksumPercona Toolkit它能在不影响主库正常运行的前提下通过 CRC32 或类似机制按块对比主从数据最后生成差异报告。发现差异后再用pt-table-sync修复。注意数据校验建议在业务低峰期进行因为会占用一定资源。对比前确认主从链路是正常的否则校验结果没有意义。修复数据一定要先在测试环境演练修复过程通常涉及在主库或从库执行变更要评估影响。使用pt-table-checksum时工具依赖账号需要SELECT, PROCESS, SUPER, REPLICATION SLAVE权限合理授权。若库非常大分批次按表校验不要一次性全量跑。4.5 binlog 无法清理磁盘快满了binlog 太多导致磁盘告警也是常事。排查前先分析为什么 binlog 增长快是否开启了慢查询但没优化不慢查询本身不写 binlog。是否有大量UPDATE或DELETE产生了海量 ROW 日志是否binlog_expire_logs_seconds或expire_logs_days配置失效或为 0若确认 binlog 清理机制已配置但仍增长迅猛可以用下面的命令手动清理但一定小心PURGE BINARY LOGS TO mysql-bin.000010;这会删除指定文件之前的所有 binlog。注意如果有从库还在读取旧文件比如从库中断很久直接清理会导致从库断链且无法追回必须确认所有从库的读取位置都已越过被清理的文件后再执行。稳妥的做法是用SHOW SLAVE STATUS查看各从库的Master_Log_File和Read_Master_Log_Pos确保它们都远超待清理的 binlog 文件。还有个习惯要养成监控主库的 binlog 目录磁盘使用率预留 20% 以上的空间余量避免突发大事务直接打满磁盘。4.6 GTID 模式下从库无法复制报错不一致这是 8.0 环境最容易遇到的问题。比如从库曾经手动执行过事物导致GLOBAL.GTID_EXECUTED集合包含了主库尚未分配或已经分配的 GTID。此时启动复制主库会认为该事务已被执行而不下发从库缺数据或反之从库多出主库没有的事务。建议的排查路径先在从库执行SHOW MASTER STATUS;和SELECT GLOBAL.GTID_EXECUTED;对照。若从库存在主库没有的 GTID最简方案是让从库清空 GTID_EXECUTED危险操作务必先备份或者在确认事务无副作用后手动重置从库链路STOP SLAVE; RESET MASTER; RESET SLAVE ALL;但这会把从库的 binlog 和中继日志全部清空操作前必须再三确认。更好的做法是重新搭建从库从主库全量备份恢复再用RESET SLAVE ALL和CHANGE MASTER TO ... MASTER_AUTO_POSITION1重建链路。虽然慢但最干净。日常防患绝对不要在从库执行写操作尤其是自己手动往业务表里插数据。5. 进阶能力锤炼从能用到用好5.1 半同步复制把数据安全再推一步异步复制下主库提交事务后不管从库是否收到如果主库宕机且从库未同步该事务就会丢数据。半同步复制Semisynchronous Replication在两者之间求平衡——主库至少等待一个从库确认收到并刷盘 binlog 后才向客户端返回提交成功从而减少数据丢失窗口。开启方式5.7/8.0动态安装插件INSTALL PLUGIN rpl_semi_sync_master SONAME semisync_master.so; INSTALL PLUGIN rpl_semi_sync_slave SONAME semisync_slave.so; SET GLOBAL rpl_semi_sync_master_enabled 1; SET GLOBAL rpl_semi_sync_slave_enabled 1;注意半同步复制在从库无响应或超时时会退化为异步复制保证主库可用性但退化的时间窗口仍可能丢数据。它只是把风险降低不能完全消灭。5.2 多源复制合并多主库到一从库8.0 支持多源复制一个从库同时从多个主库拉取数据适用于数据汇总、分库分表聚合等场景。配置方法和普通复制类似只是需要给每个复制通道起一个名字CHANGE MASTER TO ... FOR CHANNEL channel_1; CHANGE MASTER TO ... FOR CHANNEL channel_2; START SLAVE FOR CHANNEL channel_1; START SLAVE FOR CHANNEL channel_2;多源复制下表名冲突要提前规划不同主库要写入的目标库表尽量隔离否则数据互相覆盖。5.3 读写分离路由策略配置好主从后如何在业务层正确路由读写流量这个事情比建链路更考验架构能力。常见方案业务代码层自己封装数据源根据方法名如select、get、query走从库insert、update、delete走主库路由。实现简单但容易遗漏特殊场景。ORM/中间件层如 MyCat、ShardingSphere、TDDL配置读写分离规则。功能丰富但增加运维复杂度。服务层通过 Proxy 或网关层路由应用无需感知。对现有业务改造最少但会引入新的网络跳数。我个人的经验是小项目直接在数据访问层做简单路由就够了团队大、业务复杂后再引入中间件。不要一上来就全公司重建读写分离体系容易从工具问题变成项目问题。5.4 高可用切换预案不能省主从复制搭建完成只是第一步高可用切换才是真正的考验。你需要提前准备切换方案主库宕机后从库如何提升为新主库应用如何感知并切换连接要不要引入 MHA、Orchestrator 之类的管理工具最简单的切换流程手工版确认主库真的挂了网络、进程、硬件。在从库执行STOP SLAVE; RESET SLAVE ALL;。在从库确认没有应用写入后将其设为可写SET GLOBAL read_onlyOFF。修改应用连接配置指向新主库。其余从库重新CHANGE MASTER TO ... MASTER_AUTO_POSITION1指向新主库。注意所有步骤都要先在测试环境演练不要在生产第一次“彩排”。我见过太多团队平时不演练真宕机时手忙脚乱越急越错。5.5 监控复制延迟与异常状态最后提一下监控事项复制不是配好就永远没事你需要关注SHOW SLAVE STATUS的Slave_IO_Running、Slave_SQL_Running、Seconds_Behind_Master。主库 binlog 磁盘使用率、从库中继日志目录使用率。GTID 模式下注意Retrieved_Gtid_Set和Executed_Gtid_Set是否持续增长且基本一致。定期跑pt-table-checksum检查数据一致性。对于“从库延迟多少算危险”这个问题我的看法是如果业务允许读旧数据延迟 10 秒以内可接受如果业务要求读到最近 1 秒内的数据延迟在 1 秒以上就要开始排查了。不要把Seconds_Behind_Master0当成绝对安全它有时候也会骗人——比如 IO 线程中断但 SQL 线程还在空转时这个值可能不为零或表现异常还是要结合Relay_Log_*位置和 GTID 集合综合判断。最后分享几个实践心得写到这里结合我自己这些年操作 MySQL 主从复制的经历分享几点实在的体会。第一查问题先把SHOW SLAVE STATUS\G从头到尾看一遍不要只看Running两行。很多信息Last_IO_Errno、Last_SQL_Errno、Relay_Log_Name、Exec_Master_Log_Pos都指向问题根源两分钟能看明白的事不必上网搜半小时。第二搭主从不要贪快先测 DML、再测 DDL、再做延时观察每一步都留好记录。我见过太多人一条CHANGE MASTER TO完事就下班第二天从库废了都不知道。第三备份永远独立于主从复制存在。主从是“冗余”不是“备份”哪怕半同步复制也一样。在数据可靠性这件事上一份异地全量备份 定期 binlog 归档永远是你的最后防线。第四8.0 主从复制坑确实少了但升级前还是要看官方 release notes特别是不兼容变更。操作之前先在低版本上熟悉一遍参数名变化别等到生产环境才发现expire_logs_days在老版本可用、在新版本要换写法。MySQL 主从复制是个老话题但每一次配置都可能有新坑。希望这篇文章里的经验能让你少踩几个雷省下几个深夜。如果你也踩过什么有意思的坑欢迎在评论区聊聊大家一起补全避坑清单。