凌晨两点的告警信息把手机震到桌上MySQL主从复制的状态页里一排NoSlave_SQL_Running卡成了NoLast_SQL_Errno是1062。查了一圈从库多了一条主库早就不存在的记录。网上的教程翻来翻去就是两条路stop slave之后跳过错误或者干脆reset slave重新指定位置。我当时跟着跳了一个错误确实恢复正常了结果第二天同一批1062换了个主键再次出现随后IO线程也挂了Last_IO_Errno报出1236binlog位置彻底对不上。从那一刻起我明白一件事主从数据一旦出现实质不一致继续在脏数据上做微创修复本质就是往漏水的桶上贴胶带。后来凡是遇到从库数据状态不可信、binlog位点丢失、relay log损坏这类问题我都直接上mysqldump做全量重建。这套办法很土但确实管用导出一份主库一致快照传到从库导入再把复制位点切到快照点让从库重新追binlog。整个过程半小时到一小时就能把主从架构拉回正轨。下面把完整流程拆开讲里面有我这些年踩过的坑适合正在被主从同步异常折磨的开发、运维和DBA参考。1. 同步异常的现场先把 show slave status 读透再动手很多人在处理主从同步时有个坏习惯只看Slave_IO_Running和Slave_SQL_Running两个字段是不是Yes看到Yes就走人。真正要读的信息远不止这两行。Last_IO_Errno、Last_SQL_Errno、Seconds_Behind_Master、Master_Log_File、Exec_Master_Log_Pos每一项背后都代表不同的故障阶段。我把常见的异常形态分成三类几乎覆盖了百分之八十的线上故障。1.1 三类异常形态IO线程挂、SQL线程挂、状态健康却追不上异常形态关键字段表现典型错误码常见原因IO线程挂掉Slave_IO_Running: No1236binlog被清理、MASTER_LOG_FILE/POS填错、server_id重复、网络不稳定SQL线程挂掉Slave_SQL_Running: No1062 / 1032主键冲突、update或delete找不到行、表结构不一致状态健康但落后两个Running都是Yes无大事务回放、无主键表更新量大、磁盘IO差、单线程回放瓶颈IO线程挂掉的场景我遇到最多的是1236。MySQL 5.7和8.0的binlog过期策略不一样8.0默认用binlog_expire_logs_seconds如果主库设置得很短比如只有3600秒而备库因为网络或负载在短时间内没有拉取日志主库直接把binlog清掉了IO线程就会报Got fatal error 1236 from master when reading data from binary log。这种错你无论怎么skip都没用只能让IO线程重新拉而重新拉又需要一个有效位点。SQL线程挂掉的1062和1032是另一种更麻烦的情况。1062是主键冲突让我印象最深的一次是主库执行了一条insert从库重放时发现同一条主键已经被占用了。当时我以为是偶发数据问题从库上手工删掉那条冲突记录继续start slave结果半小时后同样的错误换了个主键又冒出来。罪魁祸首是前一天凌晨有人往从库直接导过一批数据初始快照就不一致。1032则是主库删除或修改了一行从库根本没有这行这种情况多半是更早时期的MyISAM表崩溃、初始化漏了表或者之前跳过错误导致缺口扩大。还有一种是两个Running都是Yes、Seconds_Behind_Master却长期不为0。这种不是复制通道坏掉了更多是回放能力跟不上主库写入。大事务、无主键表被大量更新、磁盘阵列性能不足都会造成这种假健康状态。遇到这种优先考虑优化主库咀嚼SQL、开启并行复制或者升级从库硬件而不是重建从库。1.2 从错误码反推根因为什么数据级错误之后从库已经不可信数据级错误之后从库的真实状态已经和主库的时间线发生了错位。MySQL的复制机制本身是顺序重放binlog但重放的前提是起点数据一致。一旦起点坏了从库后续执行的每一条跨行、跨表的关联操作都可能继续错可复制线程自己并不知道。1062出现时如果你用sql_slave_skip_counter1跳过本质上是让从库漏掉一条binlog事件。漏掉这一条之后从库缺少了一个历史状态对应用层来说可能只是一次插入没复制但对数据库内部来说这条缺憾会在后续操作里被持续放大。举个例子主库先插入一个用户再在另一张订单表里引用这个用户从库插用户时被跳过后续订单表插入的外键关联、统计字段全部对不上。你以为跳过的只是一条SQL实际上已经断裂了一整条数据链。还有个容易被忽视的地方是relay log本身损坏。从库非正常断电、磁盘坏道、kill -9都可能让本地relay log文件产生损坏段。复制线程读到一半直接报relay log read failure这种修复非常麻烦手工把relay log文件删了重新拉也不是总能成功。真要是我现在的处理原则出现1062/1032/1236这类实质性错误且确认不是偶发的单条业务问题宁可花半小时重建从库也不要在已经不可信的状态上继续打补丁。2. 修复路线取舍为何最终选用 mysqldump 这个“笨办法”决定重建从库之后下一个问题是用什么方式建。网上方案五花八门有让用Percona工具做校验的有让手工补偿数据的还有直接吹物理备份快的。我自己的选型逻辑很简单先判断故障属于数据不一致还是属于复制通道损坏然后挑最稳、最容易控制的方案。2.1 几种修复思路的对比绕、补、验、重建方案适用场景主要风险时间成本sql_slave_skip_counter1确认是偶发错误从库数据整体可信跳过binlog事件数据缺口扩散分钟级手工补偿数据单条1062/1032能明确缺失或多余的数据内容多条错误时容易漏事务原子性难保证几十条以内可以一多就无法收场pt-table-checksum pt-table-sync只是最终数据有差异复制通道本身健康大表校验压主库部署依赖Perl环境视数据量而定可能比mysqldump还久mysqldump重建从库数据不一致、位点丢失、relay log损坏、GTID断层大库导出慢、导入窗口长几十GB的库全程一小时左右跳过和手工补偿本质上都是“绕着问题走”只能处理局部的错误。pt-table-sync能修数据差异但它修不动复制通道的底层问题比如binlog位点丢了、relay log坏了、GTID的history出现了真空。xtrabackup物理备份确实快不过在离线环境、内网环境、没有Percona工具的情况下装依赖都够你折腾半天。mysqldump虽然逻辑导出慢、文件也大但它有四个优势别的方案给不了工具随MySQL一起发布任何时候都能用不需要额外安装任何组件。dump文件是文本SQL完全可以审查出问题可以单表抽取、可以断点续导。配合--single-transaction和--master-data2既能拿到InnoDB一致快照导出的文件里又直接带着复制位点不需要再费劲去主库手抄binlog文件号和位置。不依赖从库的旧数据把旧库清掉导入新数据从根上重置初始状态。2.2 mysqldump重建的本质回到“可信任的初始状态”MySQL主从复制链条的本质就是一句话初始快照加上回放binlog。你从主库拿一份在某一个时间点上完全一致的数据记录下这个时间点对应的binlog位置从库从那个位置开始继续执行后续的binlog事件最终和主库保持一致。如果初始快照已经错了后面的回放步骤做得再精确也没有意义。mysqldump做的就是把初始快照强制拉回正确状态同时把复制位点也对齐到同一个快照时刻。数据在同一个时间点完全对得上后面重放binlog才能保证一致性。很多人觉得mysqldump导出慢这个观点我承认但不代表它不适合生产。它适合数据量控制在几十GB到一两百GB的场景这个区间是大头。我就处理过800G的实例mysqldump导出要两小时导入还要两小时中间网线抽风一次就全废。这种规模我坚决推荐xtrabackup或MySQL 8.0的Clone插件。你的库有多大、能接受多久的窗口决定方案而不是盲目追求某个工具的噱头。2.3 边界什么时候别硬上 mysqldump选项边界要提前想清楚。除了大库还有三种情况不建议直接上mysqldump主库是InnoDB和MyISAM混合引擎。--single-transaction只对InnoDB生效MyISAM表没有事务概念依然会被锁住导致主库写入阻塞。如果MyISAM表还是业务核心这个方案要慎重。导出窗口内主库正在执行DDL。--single-transaction提供的是事务一致性快照但DDL操作可能让快照和binlog之间出现错位导入后的表结构和后续binlog事件对不上从库会出现莫名其妙的错误。从库下面还挂着级联从库。重建这个从库时下游的从库会失去数据源必须提前规划暂停下游复制或者一起处理。3. 动手前准备权限、库清单、时间窗口一次理清楚决定重建之后千万别直接敲命令。我吃过一个亏用了新建的备份账号导库结果没有LOCK TABLES权限命令跑到一半直接报错主库连接瞬间飙满。准备工作看起来啰嗦但能省掉后面一大半的排障时间。3.1 需要确认的六件事第一主从版本。主库5.7从库8.0或者反过来都有可能在新版本默认字符集、认证插件、binlog格式上出问题。重建前先确认版本兼容矩阵能同版本尽量同版本。第二备份账号权限。mysqldump导库至少需要SELECT、RELOAD、LOCK TABLES、REPLICATION CLIENT、SHOW VIEW、EVENT、TRIGGER这几个权限。很多团队为了省事直接给root跑能跑是能跑但权限太大本身是风险。最好是独立备份账号单独授权。第三从库server_id。这个字段不能和主库或其他从库重复否则IO线程会来回断。主从两边都要查SHOW VARIABLES LIKE server_id。第四从库旧数据范围。只重建需要复制的业务库不要把mysql系统库连着一起动。第五磁盘和网络。先估算dump文件大小。逻辑备份文件通常比实际表文件小但导入后占用的物理空间可能更大从库磁盘要预留出至少和主库数据等量的空间。第六binlog保留时间。如果主库binlog_expire_logs_seconds只有一小时而从库导入需要两小时还没导入完binlog就清掉了即使位点正确也追不上。重建前先把主库binlog保留时间临时放宽等同步稳定后再改回去。3.2 --master-data2 到底做了什么--master-data2是这次重建的关键点。它的作用是在dump文件里写入一行注释形式的CHANGE MASTER语句记录开始导出那一刻主库正在写的binlog文件名和位点。我看到不少人手动去主库执行SHOW MASTER STATUS然后把File和Pos抄下来填到从库这样能行但容易抄错。--master-data2是mysqldump自己锁住状态后记录的准确得多。配合--single-transaction使用更稳妥。--single-transaction会在InnoDB存储引擎上开启一个一致快照事务期间不锁业务表利用MVCC让导出数据保持在同一个时间点。两个参数一起用的效果是拿到一份不带脏数据的数据快照同时记下和快照对应的binlog坐标后面从库导入完直接从这个坐标继续追。注意如果你用的是MyISAM表--single-transaction管不到它必须加--lock-tables这会锁住所有表。混合引擎库在导出前务必评估线上影响。4. 使用 mysqldump 重建从库的完整操作步骤准备工作做完下面进入实操。这里先按最常用的非GTID位点模式来讲如果你环境开了GTID最后我会单独补GTID模式的差异。4.1 主库导出命令与参数详解主库执行导出mysqldump \ -h 主库IP \ -u backup_user -p \ --single-transaction \ --master-data2 \ --set-gtid-purgedOFF \ --routines \ --triggers \ --events \ --databases db1 db2 db3 \ /data/backup/main_$(date %F).sql逐个拆解参数--single-transaction保证InnoDB一致性快照不锁业务表。没加这个参数mysqldump会逐个表加锁线上根本扛不住。--master-data2值等于2时CHANGE MASTER语句以注释形式写入文件不会被执行。等于1的时候导入时会真的执行生产环境不建议用1。--set-gtid-purgedOFF非GTID模式显式关闭gtid_purged写入防止不同MySQL版本默认行为不一致导致导入时报GTID相关错误。--routines --triggers --events把存储过程、触发器、事件一起导出。很多人漏掉这三个参数重建完从库发现少了一堆定时任务和存储过程同步状态却一切正常那种坑是事后才爆的。--databases db1 db2 db3指定库名导出的文件里会带CREATE DATABASE和USE语句导入时不用手工建库。导出完成后从文件里提取位点grep -m1 CHANGE MASTER TO /data/backup/main_$(date %F).sql输出内容类似-- CHANGE MASTER TO MASTER_LOG_FILEmysql-bin.000123, MASTER_LOG_POS854216781;这个文件号和pos值记下来后面填到从库的CHANGE MASTER语句里。4.2 从库清理与导入从库要先停止复制并清理旧的复制配置STOP SLAVE; RESET SLAVE ALL;RESET SLAVE ALL会清除之前的主库连接信息、relay log路径等防止旧配置干扰。这是从库没有业务写入的情况下才能执行的命令执行前再三确认你连的是从库而不是主库。接下来把dump文件传到从库rsync -avz /data/backup/main_20240601.sql 从库IP:/data/backup/为什么不用scprsync支持断点续传文件大的时候网络断一次不用全部重传压缩传输也能省不少时间。导入前建议先从库把对应业务库清掉不留旧表。虽然mysqldump默认会带DROP TABLE IF EXISTS但某些历史残留表不在dump文件里比如以前临时建的表这种表不会被删除会一直躲在角落里干扰后续同步。干净的清法是手工删库DROP DATABASE IF EXISTS db1; DROP DATABASE IF EXISTS db2; DROP DATABASE IF EXISTS db3;然后关闭从库binlog写入开始导入SET GLOBAL sql_log_bin0;注意MySQL 8.0里执行这个需要SYSTEM_VARIABLES_ADMIN权限普通账号会报权限不足用root或授权账号操作。导入命令用管道方式mysql -uroot -p /data/backup/main_20240601.sql或者后台导入日志输出到文件方便观察进度nohup mysql -uroot -p /data/backup/main_20240601.sql /data/backup/import.log 21 导入完成后把binlog写回打开状态SET GLOBAL sql_log_bin1;关闭binlog再导入是为了让重建数据不写进从库自己的binlog。如果从库的binlog被这些大SQL撑爆要么占磁盘要么影响级联从库这步骤建议保持。4.3 重建复制CHANGE MASTER 的正确姿势导入完成用之前在dump文件头部grep出来的位点重建复制关系CHANGE MASTER TO MASTER_HOST主库IP, MASTER_USERrepl_user, MASTER_PASSWORD你的密码, MASTER_PORT3306, MASTER_LOG_FILEmysql-bin.000123, MASTER_LOG_POS854216781; START SLAVE; SHOW SLAVE STATUS\G最容易出错的两个地方都出在这几步。第一种错是手抄pos抄错。有人从dump文件里拷贝CHANGE MASTER语句时不带注释行复制粘贴时多了一个分号或者pos值少了一位IO线程立刻报1236。所以我不建议手工从执行结果里抄写而是用grep直接从文件里读出完整行再用sed把注释符去掉生成Change语句时保持原值。简单做法是肉眼核对三遍或者把MASTER_LOG_FILE和MASTER_LOG_POS分别赋给变量再填入语句。第二种错是Server_id重复。两个MySQL实例的server_id一样从库连接时会被MySQL识别成同一个实例IO线程报错或者不断重连。这种时候先查主从两边配置把从库改成独立的值再重启。如果你填的位点并不是从库数据对应的时刻比如在从库还在同步的时候直接执行了RESET SLAVE然后从主库新dump的数据并没有真正导入完整那么从库执行START SLAVE后可能报各种奇怪的错误。遇到这种情况不要急着继续修回到导出步骤检查导入日志有没有报错。GTID模式下的差异在于你需要这样配置STOP SLAVE; RESET SLAVE ALL; CHANGE MASTER TO MASTER_HOST主库IP, MASTER_USERrepl_user, MASTER_PASSWORD你的密码, MASTER_PORT3306, MASTER_AUTO_POSITION1; START SLAVE;此时不要填写MASTER_LOG_FILE和MASTER_LOG_POS。使用MASTER_AUTO_POSITION1从库会从主库拉取gtid_executed之间缺失的事务。但是有一个前提条件从库的gtid_executed必须是空或者只包含你想丢弃的旧事务。如果从库之前有过业务写入gtid_executed里有自己的事务直接启用Auto Position会报错。我处理这类问题时会先确认从库没有业务写入再执行RESET MASTER清空gtid_executed这是有破坏性的操作务必确认。如果环境没有开启GTID直接用位点模式就足够了。它不依赖GTID的全局事务分配逻辑简单出错也容易排查。操作习惯上五年前的老集群和现在多数新集群都兼容位点模式。5. 修复后的验证与二次翻车预防重建不是以START SLAVE结束。说句难听的有些人重建完看到两个Running都是Yes就走了第二天用户反馈丢数据才回头查发现从库虽然显示Yes但数据已经和主库差了十万八千里。5.1 恢复成功的三个层次第一层看Slave_IO_Running和Slave_SQL_Running两个都必须是Yes。这是最基础的标准。第二层看Seconds_Behind_Master。刚启动的前几秒有值很正常因为从库在追binlog但如果几分钟后还是几千几万就要检查是否有大事务卡在回放。同时要对比Read_Master_Log_Pos和Exec_Master_Log_Pos前者是IO线程已经拉取到的位置后者是SQL线程已经执行到的位置。当两个值一致且持续稳定才说明已经追平。第三层看Last_SQL_Errno和Last_IO_Errno。这两个值在稳定状态下应该为0错误内容为空。不要只看当前还要注意Last_SQL_Error的时间戳是不是在你操作之后如果有残留错误信息说明上一轮没清干净。完整的检查命令SHOW SLAVE STATUS\G重点看这十行Slave_IO_RunningSlave_SQL_RunningMaster_Log_FileRead_Master_Log_PosExec_Master_Log_PosRelay_Master_Log_FileSeconds_Behind_MasterLast_IO_ErrnoLast_SQL_ErrnoLast_SQL_Error5.2 数据一致性抽查用SQL比对代替全表校验复制线程显示Yes只说明binlog重放没报错不代表从库每个表都跟主库一致。尤其刚重建完最怕的就是导出的dump文件本身不完整。我习惯做一轮快速数据抽查。对核心大表先对比总行数-- 主库执行 SELECT COUNT(*) FROM db1.user_table; -- 从库执行 SELECT COUNT(*) FROM db1.user_table;行数对不上说明导入有问题直接排查导入日志。行数一致还不够我还会做一个聚合校验SELECT COUNT(*) AS cnt, SUM(CRC32(CONCAT_WS(|, id, user_name, status, created_at))) AS checksum FROM db1.user_table;主从两边都执行对比cnt和checksum。CRC32不一定能保证百分之百的完整性但作为一种快速、低成本的手段大多数不一致都能抓出来。如果希望更严格建议用pt-table-checksum做主从差异校验。它按主键分块、做逐行比对能给出更精确的结果。但这个工具本身有额外部署成本且大表校验会对主库产生压力不推荐每次重建都全量跑一遍。5.3 如果第二天又断怎么判断根因重建完第二天又收到告警这种情况我也遇到过。先不要慌也不要急着再去改复制配置。第一件事是稳下心态确认错误码是不是之前的老错误。如果还是1062或1032大概率是dump出的数据文件和后续binlog事件之间存在缝隙。常见原因有两个一是导出期间主库执行了DDL导致从库重放时遇到表结构变化二是从库导入期间有应用直接写入了从库把数据污染了。解决办法是回查dump期间主库的DDL记录以及从库的general_log或binlog里有非导入来源的写入。如果错误码是1236那要重点看主库的binlog有没有被提前清理。我在生产环境就遇到过一次重建从库花了近两小时导入结束准备change master时发现主库binlog恰好轮转且被purge了历史文件同一时刻的位点已经不存在了。这种场景的预防办法是在重建前临时调大binlog_expire_logs_seconds给整个流程留出至少两倍的时间余量。如果错误码变成了1045、2003这类连接错误说明复制用户权限或网络出了问题而不是数据层面的问题。检查MASTER_USER、MASTER_PASSWORD以及主库grant给repl_user的权限确认REPLICATION SLAVE, REPLICATION CLIENT这些权限还在。最后分享一个实际经验每次重建从库把dump文件、grep出来的位点、从库内执行的CHANGE MASTER语句、当天的日期全部记录到运维文档里。这个习惯救过我很多次。下次再出问题直接翻文档看上次的位点和当时的binlog行为对比今天的新错误是同类问题还是新问题一眼就能看明白。我自己的感受是mysqldump这条老路虽然不华丽但它是把复杂故障拉回到简单状态最有效的手段踩过几次坑之后你会越来越喜欢这种“一了百了”的修复方式。