MySQL 主从复制这件事说简单也简单一条CHANGE REPLICATION SOURCE TO加两个START就能跑起来说难也难真放到生产环境里延迟抖动、数据不一致、切换翻车任何一个都能让人半夜爬起来。我自己经手过的几套系统里主从复制既是读写分离的地基也是备份恢复、灰度验证、报表抽取的底座搭建本身半小时的事把它调稳、调快、调得敢切往往要按月计。下面这些内容是我把“能跑”打磨成“敢用”的过程中攒下的东西从复制链路原理、参数容量账、搭建实操、延迟治理、故障排查到切换演练都覆盖到了。不管你是刚接触复制、照着文档敲命令的新手还是已经被Seconds_Behind_Master忽悠过几次的老手应该都能从里面挑到能直接抄的配置和能少走弯路的判断逻辑。1. 先把复制链路拆开看明白1.1 三个线程的分工决定了所有故障的定位方向很多人搭复制是照抄命令出问题就懵根子在于没把链路上谁干了什么搞清楚。主库上有一个binlog dump 线程它不是常驻的从库每连上来一次就起一个专门负责把 binlog 事件读出来、通过网络推给从库。注意“推”这个字主库是主动方所以网络层的问题、大事务卡住的问题都会先在这里表现出来——比如你看到主库SHOW PROCESSLIST里有个Binlog Dump GTID状态的线程Time值一直涨通常说明从库那边消费不动了。从库上有两类线程。一类是IO 线程replica_io_thread负责跟主库建立连接、接收 binlog 事件、原封不动写进本地的 relay log同时把接收到的位点记到mysql.slave_master_info8.0 里是mysql.slave_master_info表早期是master.info文件。另一类是SQL 线程replica_sql_thread它读 relay log把事件解析出来在从库上重放。MySQL 5.7 以后 SQL 线程可以拆成多个 worker这就是所谓的并行复制。这个分工极其重要因为绝大多数复制故障都能靠一句话定位IO 线程挂了是“连不上、读不到”SQL 线程挂了是“读到了、执行不了”。前者去查网络、账号权限、主库 binlog 是否被清理后者去查数据冲突、表结构不一致、权限不足。我见过太多次有人一看Slave_SQL_Running: No就去重启网络方向从一开始就错了。判断方法很简单SHOW REPLICA STATUS\G里Replica_IO_Running和Replica_SQL_Running两个字段分开看再看Last_IO_Error和Last_SQL_Error一一对应。1.2 异步、半同步、组复制三种模式的取舍逻辑默认的复制是全异步的主库写完 binlog、提交事务、给客户端返回成功根本不管从库收没收到。好处是主库性能几乎不受影响坏处是主库一崩最后那一段 binlog 可能还没传出去切过去就丢数据。丢多少取决于网络往返和从库消费速度闲时几毫秒忙时可能几百毫秒甚至更多。半同步复制就是在这个缺口上打补丁主库提交后要等至少一个从库确认“我已经收到并写入 relay log 了”才给客户端返回成功。这里有个关键细节5.7.2 之后默认是after_sync模式也就是从库确认之后主库才做存储引擎层提交能做到真正的不丢事务老版本或者改成after_commit的话主库先提交再等确认只能保证“不丢到从库没收到的程度”语义弱一档。半同步的代价是每次提交多一个网络往返同机房内通常是 0.1~0.5ms 的额外延迟跨机房就可能到几毫秒。还有一个坑rpl_semi_sync_master_timeout默认 10 秒超时之后会自动降级成异步而且是永久降级除非手动再打开。这意味着网络抖一下你的半同步可能就悄悄没了监控里必须盯Rpl_semi_sync_master_status这个值。组复制MGR走的是另一条路基于 Paxos 变种做多数派确认天然带故障自动选举单主模式下切换体验比传统主从好很多。但它对网络延迟敏感、对主键冲突零容忍、对表必须有主键这一条卡得死写冲突时会直接回滚事务。我的经验是如果你的业务写并发高、事务里有大量非主键唯一约束、团队又没有专职 DBA 兜底MGR 的运维成本会明显高于收益反过来如果是要做同城双活、要求 RPO 接近 0那 MGR 或者半同步 外部选主工具更合适。选型这件事没有银弹只有“你的故障容忍度和运维能力匹配哪个”。模式数据丢失风险主库写性能影响运维复杂度适用场景异步复制主库宕机可能丢最后一段几乎无低读扩展、备份、报表、可容忍少量丢失半同步after_sync理论上不丢已提交事务每事务多一次网络往返中核心交易库、同城机房组复制单主多数派确认RPO 接近 0有明显额外开销高高可用要求高、有专人维护2. 上线前必须算清的参数与容量账2.1 server-id、binlog 格式与日志保留这三样错了后面全是坑server_id必须全局唯一这句话人人都会说但真正踩坑的是复制拓扑变更之后忘了改。级联复制里 A→B→C如果 B 和 C 的 server_id 撞了C 会把自己的事件当成回环事件丢掉表现为复制看起来正常但数据就是不同步。我的习惯是给 server_id 编规则机房位 角色位 序号比如 1 代表主机房、2 代表备机房中间位 1 是主库、2 是从库最后三位是实例号写成112003这种形式。这样一眼就能看出这台机器的位置和角色扩容时也不容易撞。binlog_format现在基本没有悬念选ROW。STATEMENT 格式在NOW()、UUID()、LIMIT不带 ORDER BY 这类场景下主从结果会不一致是历史包袱MIXED 看着聪明实际上让你没法预判某条 SQL 到底记成什么格式排查问题时很痛苦。ROW 格式的代价是 binlog 体积大一条UPDATE改了 10 万行binlog 里就是 10 万条行变更记录这也是后面容量估算的重点。binlog_expire_logs_seconds8.0.11 之后取代expire_logs_days决定 binlog 保留多久。这个值不能拍脑袋要满足两个约束大于最长的一次从库故障恢复时间大于你做全量备份的周期。比如你每周日做一次全备那保留时间至少 8 天给 604800 秒7 天都嫌紧我一般给 14 天。别小看这条我遇到过一次从库磁盘故障换机器重建花了 5 天结果主库 binlog 只留了 3 天最后只能从备份恢复再补数据多花了一整天。2.2 磁盘和网络容量怎么估给个能套的算法binlog 增长速度必须提前算不然磁盘写满导致主库挂掉这是最冤的一种事故。算法不复杂但要有真实的业务数据打底。假设某业务峰值写入 3000 TPS平均每个事务涉及 2 行变更平均行长 300 字节ROW 格式下每行变更记录的额外开销表 ID、列类型、位图、前后镜像大约是行数据本身的 1.2~1.5 倍取 1.3单事务 binlog 大小 ≈ 2 行 × 300 字节 × 1.3 ≈ 780 字节 峰值每秒 binlog ≈ 3000 × 780 ≈ 2.3 MB/s 一天按 8 小时高峰等效计算 ≈ 2.3 × 3600 × 8 ≈ 66 GB/天这个数字意味着什么保留 14 天要接近 1 TB 的 binlog 空间。看到这个量级你就该考虑两件事一是把binlog_row_image设成MINIMAL只记变更列能砍掉相当一部分体积代价是某些依赖前镜像的工具会受影响要评估二是重新审视保留策略或者上 binlog 归档。反过来如果你的库一天才 2 GB binlog那就别折腾默认配置足够。网络带宽同样要算。从库拉 binlog 的流量基本等于主库的 binlog 写入速率峰值 2.3 MB/s 就是大约 20 Mbps看起来不大但如果是跨机房、链路上还有其他业务就得留 3 倍以上余量。更值得注意的是大事务和 DDL 的瞬时冲击一个 5 GB 的表加索引binlog 里可能要传几个 GB瞬间打满带宽从库延迟直接起飞。这也是我后面要讲的“大事务是延迟第一杀手”的由来。2.3 持久化参数的取舍别为了性能把复制搞丢sync_binlog和innodb_flush_log_at_trx_commit这两个参数直接决定主库崩溃时 binlog 和数据的匹配程度。sync_binlog1表示每次事务提交都 fsync binloginnodb_flush_log_at_trx_commit1表示每次提交都刷 redo log两个都开就是所谓的“双 1”是金融级配置代价是每次提交多两次磁盘刷写。如果主库sync_binlog0而innodb_flush_log_at_trx_commit1崩溃后可能出现 redo 里已提交但 binlog 没落盘的情况主从就会不一致。从库这边还有个容易被忽略的点log_slave_updates8.0 叫log_replica_updates必须打开。它的作用是让从库重放的事件也写进自己的 binlog。不开会怎样级联复制直接废掉而且从库基于 binlog 做时间点恢复也做不了。开启的代价是从库多一份 binlog 写开销这个开销是值得的别省。另外max_allowed_packet主从必须一致。主库能写进去的大事务从库如果 packet 上限更小IO 线程接收时就会报错甚至断开错误信息通常是Packet too large或者莫名其妙的Lost connection。我的做法是主从统一设成 64M 或 128M具体看业务里最大的单条记录有多大。还有一个replica_preserve_commit_order8.0 并行复制下必须开否则从库的提交顺序可能和主库不一致在有外键或者依赖提交顺序的逻辑里会出问题而且开半同步之后不开这个参数从库确认的语义都不对。3. 从零搭一套主从能直接抄的实操流程3.1 主库侧的配置与复制账号主库my.cnf里至少要落这几项我按能直接用的形式写出来[mysqld] server_id 112001 log_bin /data/mysql/binlog/mysql-bin binlog_format ROW binlog_row_image MINIMAL binlog_expire_logs_seconds 1209600 sync_binlog 1 innodb_flush_log_at_trx_commit 1 max_allowed_packet 64M gtid_mode ON enforce_gtid_consistency ON log_replica_updates ON注意gtid_mode从 OFF 直接改成 ON 是不允许的必须走OFF → OFF_PERMISSIVE → ON_PERMISSIVE → ON四步每一步之间要等所有从库追平。生产上切换 GTID 一定要挑低峰期并且在从库上同步执行同样的步骤顺序是先主后从。账号这块复制专用账号只给REPLICATION SLAVE权限不要图省事用 root。8.0 默认认证插件是caching_sha2_password如果从库是 5.7握手会失败报Authentication plugin caching_sha2_password cannot be loaded。解决办法是建账号时显式指定CREATE USER repl10.20.% IDENTIFIED WITH mysql_native_password BY 一串足够长的密码; GRANT REPLICATION SLAVE ON *.* TO repl10.20.%; FLUSH PRIVILEGES;网段限制要写别用%。我见过复制账号开成repl%还被扫到弱密码的虽然复制权限本身不能改数据但它能拉走全量 binlog等于把整个库的变更历史泄露了。3.2 用备份搭从库两种方式的选择依据方式一mysqldump 逻辑备份适合数据量在几百 GB 以内、能接受较长重建时间的场景。关键是三个参数不能少mysqldump -uroot -p \ --single-transaction \ --source-data2 \ --set-gtid-purgedON \ --routines --triggers --events \ --default-character-setutf8mb4 \ --databases app_db /backup/app_db_$(date %F).sql--single-transaction保证 InnoDB 表拿到一致性快照且不锁表--source-data28.0.26 之前的版本叫--master-data2把当时的 binlog 位点以注释形式写进文件头部这是后续CHANGE REPLICATION SOURCE的依据--set-gtid-purgedON会把 GTID 信息一并导出用 GTID 复制时必须有。用 GTID 模式的话其实不需要手工记位点SOURCE_AUTO_POSITION1会自动协商但导出的 GTID 集合要正确否则从库会重复执行已包含的事务。方式二物理备份Percona XtraBackup 或 MySQL 企业版工具适合 TB 级、要求快速重建的场景。流程是备份、--prepare、拷到从库、启动、用备份里的 binlog 位点或 GTID 接上复制。物理备份的优势是快1 TB 的库逻辑导出可能要几小时物理拷贝加 prepare 可能一小时搞定劣势是版本、页大小、字符集必须完全一致跨大版本基本不可行。我自己的一条经验从库重建期间把innodb_flush_log_at_trx_commit临时设成 2、sync_binlog设成 0导入完再改回来导入速度能快一倍以上。前提是这台从库此刻不对外提供读服务而且你能接受重建期间它自己崩了重来。3.3 启动复制与第一轮验证导入完成后在从库执行STOP REPLICA; CHANGE REPLICATION SOURCE TO SOURCE_HOST10.20.1.11, SOURCE_PORT3306, SOURCE_USERrepl, SOURCE_PASSWORD******, SOURCE_AUTO_POSITION1, SOURCE_CONNECT_RETRY10, SOURCE_RETRY_COUNT86400; START REPLICA;SOURCE_RETRY_COUNT86400意思是断线后重试一天默认值是 86400 秒其实也是这个数但很多人不知道可以调遇到网络抖动时从库直接停在那儿等人肉拉起。如果你用的是基于位点的方式就把SOURCE_AUTO_POSITION去掉换成SOURCE_LOG_FILE和SOURCE_LOG_POS。启动后别只看Replica_IO_Running: Yes就收工我做验证会看四样东西Replica_IO_Running和Replica_SQL_Running都是 YesRetrieved_Gtid_Set和Executed_Gtid_Set的差集在收敛Seconds_Behind_Master归零再随便找一张业务表在主库插一条测试数据确认从库能查到、主从CHECKSUM TABLE结果一致。最后这条最有价值因为前面三个都可能是假象。测试数据记得删掉别留在生产表里。4. GTID 与并行复制把延迟摁下去的关键4.1 GTID 到底解决了什么问题传统基于位点的复制最烦的是切换时找位点。主库挂了你得从各个从库的SHOW REPLICA STATUS里挑一个Executed_Gtid_Set最全的然后算出它还差哪些事务再让其他从库指向它并指定SOURCE_LOG_FILE和SOURCE_LOG_POS。这一步在故障现场手忙脚乱的时候极容易算错算错的结果就是数据错乱或者重复执行。GTID 用uuid:事务序号的方式给每个事务盖章从库只需要告诉主库“我已经执行到哪了”主库自动把缺的推过来。切换时只要CHANGE REPLICATION SOURCE TO SOURCE_AUTO_POSITION1剩下的交给协议自己算。这个改变带来的运维收益非常大尤其是多级拓扑和频繁切换的场景。但 GTID 有两个必须知道的约束。一是不支持在事务里临时关 GTID 再开也就是那些SET GTID_NEXT的技巧只在事务外有效二是跳过事务的方式变了传统sql_slave_skip_counter在 GTID 模式下无效必须用空事务顶掉STOP REPLICA; SET GTID_NEXT3f9a1b2c-1234-11ee-9d8a-0242ac110002:10457; BEGIN; COMMIT; SET GTID_NEXTAUTOMATIC; START REPLICA;这个操作意味着“这个事务我认了但我不执行它的内容”。用之前一定确认这个事务到底是干嘛的是无害的重复插入还是有实际数据变更。我见过有人连跳 200 个 GTID 把复制拉起来结果发现跳过的是一个批量更新数据静默错了半个月。4.2 并行复制的三个版本选错了等于白配并行复制的核心矛盾是主库可以多线程并发写从库的 SQL 线程默认只有一个回放速度天然跟不上。MySQL 给了三代方案5.6 的库级并行slave_parallel_typeDATABASE按操作的库分发到不同 worker。这个方案在单库多表业务里完全无效因为你只有一个库所有事务还是排在一个 worker 上。现在基本可以淘汰。5.7 的组提交并行LOGICAL_CLOCK依据是主库上同一组提交的事务之间没有锁冲突可以并行回放。这个方案效果显著提升配置是slave_parallel_typeLOGICAL_CLOCK加上slave_parallel_workersN。N 取多少经验值是 CPU 核数的 1/2 到 1 倍但更重要的是看主库的并发度。如果主库本身写入就是串行的开 32 个 worker 也没用因为没有可并行的窗口。8.0 的 WRITESET 并行把判断依据从“组提交”细化到“行级冲突检测”通过binlog_transaction_dependency_trackingWRITESET开启。它能识别出“两个事务虽然不在同一组提交但改的行完全不重叠”从而允许并行对低并发长事务的场景提升明显。代价是主库要额外维护一份 writeset 哈希内存开销随并发事务数增长一般设binlog_transaction_dependency_history_size到 25000 就够用。我实际调优时的一套组合拳是replica_parallel_type LOGICAL_CLOCK replica_parallel_workers 16 replica_preserve_commit_order ON binlog_transaction_dependency_tracking WRITESET binlog_transaction_dependency_history_size 25000然后观察performance_schema.replication_applier_status_by_worker里各 worker 的APPLYING_TRANSACTION是否经常同时非空如果只有一个 worker 在忙说明并行度没吃到要么是业务太串行要么是有大事务堵住了。4.3 延迟到底怎么测才准Seconds_Behind_Master这个指标名声很差因为它算的是“从库当前时间减去正在执行的事务在主库上的时间戳”主库一空闲它就归零哪怕实际还有几万个事务堆在 relay log 里没执行主从时钟不同步它还能给你算出负数。拿它做监控告警误报漏报都是常态。靠谱的做法是用pt-heartbeat或者自己在主库定时写一张心跳表从库读这张表的时间戳做差。原理是主库每秒更新一次心跳表从库回放到这条记录时计算时间差这个值反映的是真实的回放滞后。用pt-heartbeat --update --interval1在主库跑从库上用--monitor --daemonize采集延迟超过阈值就告警。还有一个辅助指标是relay_log_space也就是 relay log 里堆积的字节数它涨得快说明回放跟不上接收两者结合看基本不会误判。5. 故障排查与数据一致性校验的实战套路5.1 高频报错速查表下面这张表是我这些年被问得最多、也是自己踩得最多的几类错误按“现象-根因-处理”整理错误码/现象典型报错关键字根因处理思路1236Could not find first log file name in binary log index file从库要的 binlog 在主库已被清理重建从库或从备份补上缺失区间1032Cant find record in xxx从库缺行主库执行了 UPDATE/DELETE补数据确认是否人为误删1062Duplicate entry for key从库已有该行主库执行了 INSERT对比主从数据决定覆盖还是跳过1045Access denied for user repl密码错、网段不匹配、认证插件不兼容检查账号主机位与认证插件网络类Lost connection during query大事务、packet 超限、连接超时调大max_allowed_packet和超时从库只读The MySQL server is running with the --read-only option误连到从库执行写操作确认连接串必要时开super_read_only处理 1032 和 1062 之前请一定先确认一件事这个不一致是复制本身的问题还是有人直接在从库上写了数据。后者占了我遇到案例的一半以上。很多团队为了临时导数据、跑脚本会习惯性连从库写一旦read_only没开、super_read_only也没开超级用户照样能写。所以从库上read_onlyON和super_read_onlyON要同时开后者才是真正拦住超级用户的开关。5.2 数据一致性怎么校验别等出事才想起复制“看起来在跑”和“数据真的一致”是两回事。我在一个系统里遇到过因为从库磁盘静默损坏导致某张表部分页错误复制一直正常直到业务读从库发现金额对不上才暴露。所以定期做校验是必要的。工具层面pt-table-checksum是主流选择它在主库上分块计算校验和通过复制传到从库再在从库比对。用法大致是pt-table-checksum --host主库IP --userchecker --passwordxxx \ --databasesapp_db --tablesorders,order_items \ --chunk-size1000 --no-check-binlog-format \ --replicatepercona.checksum要注意两点一是校验账号需要有针对percona库的写权限用来存结果二是低峰期跑因为分块扫描本质上是全表读大表会带来明显的 IO 压力。发现差异之后用pt-table-sync修复但修复前一定先确认方向的正确性——它默认认为主库是对的如果你的场景是主库被人误操作了那就反了。如果没有条件上工具至少做到每次重建从库后做一次全库校验之后每周抽核心表按主键区间做CHECKSUM TABLE抽样。抽样逻辑可以用SELECT COUNT(*), SUM(CRC32(CONCAT_WS(#, id, amount, status))) FROM ...分段比对虽然不严谨但能抓住大部分问题。5.3 切换这件事演练次数比方案本身重要再完美的切换方案没演练过就等于没有。我参与过的一次故障里方案文档写着“提升从库、改 VIP、重启应用”实际执行时发现应用连接池里有一批长连接没断开VIP 切过去了它们还在往老主库写造成了一段时间的双写。这个问题在演练里很容易被发现但没人练就只能拿生产事故换经验。切换前必须确认的几个检查项我整理成一个清单照着过一遍基本不会出大错老主库是否已经停止写入super_read_onlyON所有从库的Executed_Gtid_Set是否已经追平新主库的read_only是否已关闭应用侧连接串和 VIP 的切换顺序是否明确切换后第一件事是不是做数据校验。还有一条容易被忽略的切换后老主库不要急着重新加入拓扑先把它当备份留着等新主库稳定运行一段时间、数据校验通过再考虑把它作为从库挂回去挂之前要RESET MASTER清掉自己原来的 GTID 历史。6. 几个我觉得最有价值的踩坑经验Seconds_Behind_Master为 0 不代表没延迟这一点前面说过但我要再强调一次它造成的实际后果。有一次大促前做压测监控上延迟一直是 0结果业务方从从库读到的数据比主库晚了近 20 秒原因是那段流量全是读、写很少主库 binlog 稀疏Seconds_Behind_Master根本没机会体现。换成pt-heartbeat之后才看到真实曲线。指标选错比没有监控更危险因为它给你一种虚假的安全感。大事务是延迟的头号杀手而且它的破坏力是放大的。一个DELETE删 500 万行的操作在主库上可能跑了 30 秒在从库上因为只有一个 worker 在处理它其他事务全部排队实际延迟可能到十几分钟。解决办法不是调大 worker 数而是把大事务拆小分批删、分批改每批 1000~5000 行批间 sleep 几十毫秒。DDL 同理8.0 虽然支持在线 DDL但大表加索引依然会产生大量 binlog尽量用gh-ost或者pt-online-schema-change这类工具它们在切换瞬间才产生短暂的写入阻塞。最后一条从库的硬件规格不要低于主库。很多人觉得从库只是备胎给个低配机器就行结果主库 32 核、从库 8 核回放能力差了四倍延迟是必然的。从库要承担的其实更多它既要回放写又要扛读流量还要跑备份。我现在配从库的原则是 CPU 和内存不低于主库磁盘 IOPS 甚至要更高一点因为回放是随机写 relay log 随机写数据页比主库的写模式更碎。还有个小技巧可以分享一下给每个从库的my.cnf里加一行注释写清楚“这台从库的用途、上游是谁、负责人是谁”听起来很土但当你有十几个从库、拓扑图又没及时更新的时候这行注释能救命。我做过一次紧急扩容靠的就是SHOW REPLICA STATUS加上配置文件里的注释十分钟理清了整张拓扑图。