MySQL的主从复制算是我在生产环境里接触最多、也最常被问到的功能之一。凡是项目用户量稍微上来一点DBA和开发脑子的第一个念头基本都是把读流量拆到从库去。这个思路本身没错但真正做主从复制的时候binlog格式、GTID、半同步、并行复制、延迟排查每个环节都有不少坑。这篇就把主从复制的原理和实操一起盘一遍适合刚开始搭主从的运维和开发也适合准备面试想系统梳理的人。1. 主从复制到底解决了什么问题1.1 为什么需要主从复制大多数业务系统是典型读多写少一台数据库实例在CPU、内存、磁盘IO上都扛不住所有读请求时最自然的扩容方式不是无限堆配置而是横向加从库。主从复制在这个背景下几乎是MySQL生态的标配主库负责写从库负责读读写分离后主库压力瞬间小一大截。除此之外主从复制还带来三个附加价值。第一是容灾主库机器故障时可以手动或自动把某个从库提升为新主库业务恢复时间从“重新搭库、导数据”缩短到“切换连接串”。第二是备份从库可以作为备份源mysqldump或xtrabackup从从库上跑避免备份任务挤占主库IO。第三是分析场景报表、数据抽取、OLAP类查询都丢到从库上执行不影响线上主库的稳定性。我见过不少团队把主从复制当成数据库集群来用其实这是个误区。MySQL自带的复制本质是“日志搬运日志重放”它不是分布式数据库也没有自动分片、全局一致性协议。主从复制解决的是读扩展和部分高可用不是水平扩展写能力更不是数据分片方案。1.2 主从复制能做什么不能做什么能做的事情很明确读写分离把大量select请求分流到从库。数据备份从从库执行减轻主库压力。高可用切换主库故障时用从库顶上去。容灾场景下跨机房部署一个从库保留一份异地数据。测试环境或数据分析环境直接使用从库避免污染主库。不能做的事情也同等重要不能解决写入瓶颈写入还是单点。不能保证强一致默认异步复制下从库数据有延迟。不能自动故障恢复MySQL本身没有原生的failover管理组件需要配合MHA、Orchestrator或其他高可用方案。不能替代备份复制不等于备份一个误删除操作会被复制到所有从库备份才是最后兜底。1.3 常见拓扑一主一从、一主多从、级联、双主实际生产里拓扑就这么几种选型时按业务规模决定。一主一从最简单适合刚上主从的场景成本和运维复杂度最低。一主多从适合读流量明显、且多个业务线需要独立读库的情况比如一个从库给线上读一个从库给报表和数据分析。级联复制是master - slave - slave2主要解决跨机房带宽和从库数量过多导致主库dump线程压力大的问题但级联会导致延迟放大中间层的从库既要写relay log又要写binlog故障链更长。双主互为主从容易给人带来误解它并不能提升写能力只是让两个节点都能接受写请求并且通过复制保持一致。实际使用时通常还是只允许一端写入另一端作为standby避免出现冲突和脑裂。双主真正的价值在于切换方便不用重新配置复制关系。2. 复制链路背后的核心原理2.1 主库侧binlog与dump线程主从复制的起点是binlog。主库开启binlog之后所有变更语句都会按顺序以事件形式写入binlog文件。这里有个关键点binlog记录的是逻辑变化不是物理数据页变化这意味着复制可以跨版本、跨架构只要SQL语义能被从库正确执行即可。主库有一个专门的线程叫binlog dump线程。从库发起连接后dump线程会读取binlog并推送给从库。从库IO线程请求的位点不同dump线程就从对应位点开始推送。这里我要提醒一句主库重启不会丢binlog但磁盘上的binlog文件有清理策略如果从库长时间断连且binlog已经被purge复制就会断掉这也是后续会讲的1236错误。2.2 从库侧IO线程、SQL线程与relay log从库内部有两套线程这个设计值得仔细品。IO线程负责连接主库接收dump线程推过来的binlog事件然后原样写入本地的relay log。SQL线程负责读取relay log并逐条执行。两个线程解耦的好处是即使SQL线程因为慢查询被卡住IO线程仍可以继续拉取日志不至于把网络开销和本地重放混在一起。relay log本质就是一份临时的binlog副本后缀通常是-relay-bin。SQL线程执行完的事件会记录到relay log的Info文件中用来标记当前重放的位置。这个位置信息在复制异常排查时非常关键对应到从库状态里就是Relay_Log_File和Relay_Log_Pos。2.3 基于日志位置和基于GTID两种复制方式怎么选老一代的主从复制都是基于文件名位点filepos来定位同步位置的。配置复制时主库执行SHOW MASTER STATUS得到一个当前binlog文件名和偏移量从库的CHANGE MASTER TO里写死这个位置。问题很致命如果主库发生切换从库需要手动找新主库的binlog位点找错就直接断层。GTIDGlobal Transaction Identifier是针对这个痛点设计的。每个事务在主库执行时分配一个全局唯一编号格式为UUID:序列号。从库执行过的GTID会被记录复制时不需要指定文件和位点只需要告诉主库“我已经执行到哪些GTID”主库自动补发缺失部分。切换和故障恢复时从库只要开着GTID就能自动找齐数据。到了MySQL 8.0我强烈建议直接用GTID不要再碰filepos了。GTID_DETAILED的配置也简单两个参数一开就完事。2.4 binlog三种格式ROW、STATEMENT、MIXED怎么选binlog_format是主从复制里最不能随便选的参数。STATEMENT格式记录SQL语句本身日志量小但如果SQL里有UUID()、NOW()等非确定性函数从库执行结果会和主库不一致。ROW格式记录实际变更的行数据日志量大但最安全。MIXED模式是MySQL根据语句能否被安全复制来自动判断。生产环境我基本只推荐ROW格式。原因有二一是row格式在从库回放时更接近物理执行对数据一致性最友好二是后续很多工具比如Canal、Flink CDC、clickhouse同步都只解析row格式的binlog。热词里那些“使用flink实现mysql同步到clickhouse”的场景如果binlog_format不是row解析出来的数据就是残缺的。关于binlog格式还有个小细节从库的binlog_format是独立设置的默认会沿用主库但如果从库还担着下游主库的职责就必须保证它的binlog也设置成row。3. 完整搭建一主一从的实操流程3.1 环境准备与版本匹配搭建前先定版本。主从版本不一致能跑但我不建议差太多尤其是跨大版本的主从字符集排序规则、数据字典、权限系统都可能有兼容问题。最稳的做法是主从都用同一个版本或者从库版本不低于主库避免发生“主库写了新特性、从库不认识”的尴尬。操作系统层面要保持时间同步防火墙放通3306端口从库能通过网络访问主库的3306。如果机器之间延迟太高复制延时会成为长期痛点。跨机房部署时建议加上半同步复制和网络专线这个后面专门讲。3.2 主库配置server-id、binlog、GTID一个都不能少主库my.cnf配置文件里至少要包含这些参数[mysqld] server_id 1 log_bin mysql-bin binlog_format ROW binlog_row_image FULL gtid_mode ON enforce_gtid_consistency ON expire_logs_days 7server_id必须全局唯一主从都不能一样否则复制会异常。binlog文件名前缀默认是mysql-bin也可以自定义。binlog_row_imageFULL表示记录完整行数据更新前后都有这对于后续数据校验和Canal解析很关键。配置完后执行SHOW MASTER STATUS;记下当前binlog文件和位点。如果已经开启GTID也可以直接看gtid_executed变量后续用GTID方式复制时会用到。接着建一个复制专用账号。MySQL 8.0默认认证插件是caching_sha2_password这种账号在复制连线上如果没用SSL从库第一次连接时会要求获取主库RSA公钥不然会出现认证失败。稳妥做法是建号时用mysql_native_password或者在建完号后配置SSL。复制账号必须只授REPLICATION SLAVE权限不要顺手给ALL PRIVILEGESCREATE USER repl% IDENTIFIED WITH mysql_native_password BY StrongPass123; GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl%; FLUSH PRIVILEGES;3.3 从库配置建立复制链路从库my.cnf设置[mysqld] server_id 2 relay_log mysql-relay-bin read_only ON从库设置read_only是我个人的硬性要求除非你用的是MHA这类需要写入的切换工具。read_only能避免业务误连从库写数据导致的主从数据不一致。登录从库后执行以下命令建立复制。MySQL 8.0.23之后CHANGE MASTER TO开始被CHANGE REPLICATION SOURCE TO取代老命令在8.0里仍然兼容但新环境建议直接用新命令CHANGE REPLICATION SOURCE TO SOURCE_HOST10.0.0.10, SOURCE_PORT3306, SOURCE_USERrepl, SOURCE_PASSWORDStrongPass123, SOURCE_AUTO_POSITION1;这里SOURCE_AUTO_POSITION1是GTID模式的关键等于告诉从库用GTID自动定位不再需要手填MASTER_LOG_FILE和MASTER_LOG_POS。如果实在要用日志位点方式就把上面命令换成SOURCE_LOG_FILEmysql-bin.000003, SOURCE_LOG_POS154但不要混用两种方式。启动复制START REPLICA; SHOW REPLICA STATUS\G;重点看三行Slave_IO_Running和Slave_SQL_Running是否均为YesSeconds_Behind_Master是否为0。有时这两行显示的是Replica_IO_Running和Replica_SQL_Running取决于MySQL版本含义一样。3.4 历史数据初始化mysqldump与物理备份两条路一个生产库不可能从空库开始搭建从库全量初始化必须做。最常用的是mysqldump逻辑备份加上--single-transaction参数可以做到InnoDB表的一致性快照备份而且不锁表mysqldump -uroot -p --single-transaction --master-data2 --set-gtid-purgedON --all-databases backup.sql--master-data2会在dump文件里注释记录备份时刻的binlog位点--set-gtid-purgedON会把当时的GTID集合写进去。导出的SQL文件在从库上执行导入mysql -uroot -p backup.sql导入完成后从库已经有一份和主库备份时刻一致的数据。由于GTID已经记录在GTID_PURGED里START REPLICA之后从库会请求主库推送剩余事务自动补齐。数据量很大时逻辑备份恢复太慢生产上建议用物理备份工具xtrabackup备份主库数据文件后直接恢复到从库目录速度快一个量级。要注意的是xtrabackup备份出来的文件需要确保权限和路径正确重启MySQL后会自动进入复制连接等待状态。3.5 验证复制状态与第一次故障排除复制建立后做一个真实的写入测试。在主库建个测试表插入几行数据然后在从库查。如果数据立即出现说明链路基本通。此时再回过去看SHOW REPLICA STATUS仔细检查Executed_Gtid_Set里面包含主库推送过来的所有事务号。我第一次搭的时候踩过一个典型问题从库的server_id没设置或者和主库一样启动复制后IO线程一直显示Connecting主库err log里一堆“server_id duplicate”的报错。所以server_id这个细节一定要在主从两边的配置文件里写清楚不要用默认值。4. 复制延迟是老大难怎么定位怎么优化4.1 Seconds_Behind_Master的含义与误区看主从复制状态时几乎所有新手第一眼都是看Seconds_Behind_Master但它的计算逻辑容易被误解。这个值表示的是SQL线程回放到最后一个事务的时间与IO线程最近读到的事件时间之差不是从库和主库实时的精确时间差。它受影响的因素很多从库系统时间不准、IO线程在等新事件、大事务跨时间点执行都会产生不准的幻觉。更麻烦的是如果SQL线程正在执行一个超长事务Seconds_Behind_Master会一直涨直到这个事务执行完才突然归零中间你根本无法判断它到底还要多久。所以结论是这个值适合做粗粒度告警不适合做精确定位。4.2 定位延迟的真正手段大事务、慢SQL、锁竞争延迟问题的根源按频率排是这三个。第一个大事务。主库一条UPDATE影响了几百万行SQL线程在从库要重放同样长时间。这类延迟是无解的唯一的办法是从业务层面拆分事务把大UPDATE拆成小批量循环执行。第二个是慢SQL。从库要承载读业务如果某些没有索引的查询在从库上跑得很慢MySQL的线程调度和IO都会被拖累SQL线程的优先级并不高。常见场景就是查了张几百万行的表直接全表扫描。这种问题的解法是上索引或者把分析型查询挪到专门的从库上去。第三个是锁竞争。主库DDL或大事务产生了锁等待从库SQL线程在执行对应语句时同样会被阻塞。我在生产里排查时delay升高的同时去看从库的PROCESSLIST经常能看到一条SQL处于Waiting for table metadata lock状态。这种锁竞争问题需要从主库的DDL规范来根治例如禁止业务直接执行大表的ALTER TABLE语句。4.3 并行复制怎么设置早期MySQL复制最大的短板就是SQL线程单线程重放主库并发再高从库只能一条条执行。MySQL 5.7开始支持基于LOGICAL_CLOCK的并行复制8.0做了进一步增强。所谓LOGICAL_CLOCK是指同在主库上并行提交的事务在从库也可以并行回放。这依赖主库的binlog中commit_parent机制。从库配置示例replica_parallel_type LOGICAL_CLOCK replica_parallel_workers 4实际效果取决于主库的并行度。如果主库写并发不高开8个线程也不会明显加速如果主库本身是并发写比较高的OLTP4到8个并行线程能显著改善延迟。还有个参数是replica_preserve_commit_order默认ON保证并行回放的事务提交顺序和主库一致建议保持开启。4.4 半同步复制降低数据丢失风险的必选项半同步复制解决的是一个特定问题异步复制下主库提交后直接返回客户端binlog还没来得及送到从库主库就宕机了这部分日志就丢了。半同步复制要求至少一个从库确认收到binlog后主库才对客户端返回提交成功。这个“确认收到”意味着从库把事件写入relay log不一定已经执行完。MySQL 8.0里开启半同步需要主库和从库都加载插件INSTALL PLUGIN rpl_semi_sync_source SONAME semisync_source.so; INSTALL PLUGIN rpl_semi_sync_replica SONAME semisync_replica.so;主库和从库的参数分别设置[mysqld] rpl_semi_sync_source_enabled 1 rpl_semi_sync_source_timeout 3000rpl_semi_sync_source_timeout的3秒表示如果主库在3秒内没等到从库ACK就自动降级为异步复制避免整个集群不可写。这个超时比较敏感设太短会频繁降级设太长可能造成业务写入卡顿。半同步能极大降低丢数据的概率但并不是强同步超时降级后依然有丢数据的可能。4.5 主从数据不一致的常见成因与修复思路即使复制状态显示正常也可能是“带病运行”。最常见的成因从库被手动写入过数据、初始化数据不全、ROW格式下主从引擎不同导致回放结果不同。检查数据一致性社区标配是pt-table-checksum它对每个表分块计算校验和在主从两边对比。发现差异后用pt-table-sync修复但修复过程要非常谨慎先在测试环境验证。另一个更靠平时的习惯是定期做主从数据一致性巡检至少一周一次。如果巡检发现diff第一时间确认从库是否曾被写入不要盲目用主库数据覆盖从库因为有时差异其实在主库身上。5. 常见故障与运维避坑实录5.1 复制中断最常见的几个错误编号复制中断会反映在SHOW REPLICA STATUS的Last_IO_Error或Last_SQL_Error字段里。错误编号是定位问题的第一步这里列几个我实际踩过的错误编号含义处理思路1236binlog拉取失败常见是binlog已被purge或位点非法重新初始化从库或跳过缺失事务重建复制1594relay log损坏常见于非正常关机或磁盘故障停复制清空relay log并重置复制源1062主键冲突从库已有同主键记录定位冲突来源修正数据后继续复制1045复制账号权限或密码不对重新授权或修改复制源账号1872切片不一致导致的初始化失败检查GTID_PURGED和主库的差异遇到这些错误第一时间不要盲目执行START REPLICA就完事要先记录当时的错误信息、binlog位点和GTID然后再做处理。一次性操作可能把排查线索都覆盖了。5.2 IO线程连不上、SQL线程停住的定位思路IO线程显示Connecting一般从三方面排查网络不通、账号密码错误、主库binlog位点失效。在从库机器上直接测试mysql -urepl -p -h主库IP -P3306 -e SELECT 1这条命令能通说明网络和账号没问题接下来看防火墙或安全组是否放行复制账号来源IP。账号能连接但IO还是Connecting就要重点看主库那边的binlog是否还存在SHOW BINARY LOGS查看。SQL线程停住最麻烦因为错误信息里通常只给一条binlog位置。处理套路是先用STOP REPLICA把线程停下来再用SET GLOBAL sql_replica_skip_counter 1跳过导致问题的语句最后START REPLICA。但跳过是治标不治本跳过的语句如果后面还有依赖它的变更会造成更深层的数据不一致。生产环境我强烈不建议靠skip解决宁可重新拉一份数据。5.3 主库切换时最容易忽略的三个点主从切换是运维里风险最高的操作之一。第一切换前必须确认从库的relay log已经全部消化完Seconds_Behind_Master为0否则直接提升从库会丢事务。第二老主库要设置read_only和super_read_only防止应用继续向老主库写入两边同时写入会造成脑裂。第三切换后需要把所有业务连接串改成新主库同时把老主库重新作为新主库的从库接入注意这时候要新建复制账号、清空旧复制配置。很多高可用工具比如Orchestrator可以把这些步骤自动化但手动演练仍然必要。我建议每季度做一次主从切换演练别等真故障时才发现某个从库根本补不上主库的日志。5.4 从库做备份和报表任务别把鸡蛋全放一个篮子里从库虽然承载了备份和报表需求但也要考虑它的资源隔离。我见过一个团队把慢查询分析、定期大报表、binlog同步解析全都压在同一台从库上结果从库常年负载很高复制延迟长期在几十秒到几分钟之间徘徊。更合理的做法是区分从库角色一个专门做线上读一个专门做离线分析和备份。如果机器资源紧张至少要限制这些离线任务的优先级比如账号限流、语句超时、只在低峰期调度。热词里的“mysql性能调优”在这里也有了实际含义从库的innodb_buffer_pool_size、io能力、磁盘类型都要跟着它的职责来配。6. 面试与深入理解那些常被问到的点6.1 binlog和redo log到底是什么关系很多面试官爱问“binlog和redo log有什么区别”。redo log是InnoDB引擎层面的物理日志记录页的物理修改用于崩溃恢复。binlog是服务器层面的逻辑日志记录事务的变更内容用于复制和数据恢复。两者一个管crash safe一个管逻辑备份和复制职责完全不同。MySQL内部通过两阶段提交把redo log和binlog绑定在一起事务提交时先写redo log的prepare状态再写binlog最后把redo log改成commit状态。这样任何一个环节宕机重启后都能判断事务到底是提交还是回滚不会出现主库的redo log和binlog不一致。这件事不仅面试常考也是复制数据不丢的基础。6.2 主从复制丢失数据的极端场景异步复制下主库提交事务后如果立刻宕机且binlog还没被从库拉走这个事务就丢了。半同步复制能减少这种窗口但半同步超时后降级成异步同样有丢的风险。真要追求极致可靠只能用强同步方案比如MySQL Group Replication或MySQL InnoDB Cluster它们内部用的是Paxos协议。生产上到底选半同步还是组复制取决于对一致性的要求常规读多写少场景半同步足够。6.3 主从复制高频面试题速答主从复制原理是什么主库写binlog从库IO线程拉日志写relay logSQL线程回放。为什么从库要分IO和SQL两个线程解耦网络接收和本地执行提高响应速度。主从延迟怎么排查先看Seconds_Behind_Master再用PROCESSLIST看SQL线程在跑什么重点排查大事务、慢SQL、锁等待。如何防止从库数据被改坏设置read_only和super_read_only权限上严格控制。如果从库落后太多怎么办检查binlog是否还在落后太多直接用物理备份重建从库不要硬追。主库宕机怎么切换选数据最新的从库保证它的relay log已应用完提升为新主库并重定向业务流量。整个主从复制体系讲下来核心其实就一句话日志是复制的命脉延时的本质是日志消费速度跟不上生产速度。把binlog格式、GTID、并行复制、半同步这几个关键点吃透日常运维和面试应答都会从容很多。最后说点体感。主从复制配置上不难但真正考验人的是细节判断力比如要不要开半同步、并行线程设多少、延迟告警阈值怎么定。每个参数背后的取舍都要结合实际业务流量来定没有一套万能配置。我在实际运维中见过太多“复制挂着就好”的心态结果一到主库宕机就手忙脚乱。所以搭建完成只是开始定期的状态巡检、延迟告警、数据一致性校验和切换演练才是主从复制真正稳定运行的基础。