做数据库相关工作的人不管是开发还是运维几乎都会遇到同一个需求把 MySQL 的数据在另一台机器上同步一份。最开始可能只是为了备份后来业务量上来一台主库扛不住大量查询的压力就想加一台从库把读流量分出去这就是最常见的 MySQL 主从架构。这篇文章把主从配置从原理到实操完整过一遍为什么需要它、底层是怎么工作的、配置文件怎么写、命令怎么敲最后把运维里最容易踩的坑和排查思路一起整理出来。无论你是在准备搭建主从还是已经被复制报错折磨到怀疑人生都可以对照着参考。1. 主从复制到底解决了什么问题1.1 从单库到主从业务发展的必然过程先讲一个很常见的场景。某个业务系统早期就一台 MySQL部署在机房一台物理机上开发连库、业务读库、定时备份全指望这一台。前期数据量不大一切风平浪静。跑了大半年之后单量上来单机开始扛不住下午高峰期 CPU 飙到 90%慢查询日志里挤满了 SELECT报表系统跑一次统计直接把线上业务卡到超时。这时候能怎么办两种思路。一种是升级硬件加内存、换 SSD但这解决的不只是延迟成本还不线性——今天换完明天量再涨你还要再换。另一种是在架构层面做调整把一台库拆成多台库让不同用途的流量走不同机器这就是主从复制落地的开始。等你真正搭完一套主从再回头看会发现这其实是数据库架构演进里成本最低、收益最明显的一步。1.2 主从复制的三大价值先说清楚它是什么主从复制本质上就是把主库上产生的变更日志持续传输到从库并重新执行让从库的数据时刻跟在主库后面。它能解决什么问题我总结成三类读写分离。主库承接写入从库承接读请求。大多数业务的读流量远大于写十比一都很正常。把读查询压到从库后主库的压力立刻降下来这是多数团队搭主从的第一动机。容灾与备份。从库就是一份实时的热备数据。主库要是宕了、磁盘坏了可以把流量切到从库去先把业务保住。即便不切换日常逻辑备份放在从库上做也比在业务主库上做对系统的影响小得多。重查询与报表隔离。很多报表查询非常重在主库跑容易拖垮线上。有了从库把这类查询直接丢给从库放心大胆跑业务毫无感知。1.3 主从不是万能药不过我得先泼一盆冷水。主从不是高可用的全部也不是解决所有数据库问题的钥匙。要是遇到下面这些问题加从库帮不了什么忙写入量本身已经很大瓶颈在写而不是读。那该考虑的是分库分表、分布式数据库不是加从库。业务要求强一致写完立即要读到刚写入的数据。主从复制本质上是异步的从库永远会落后主库一点点这种场景必须强制走主库或者引入分布式事务方案。还有一个所有使用主从的人都绕不开的问题复制延迟。不管机房网络多好从库的执行速度很难时刻跟上主库尤其遇到大事务、DDL 或者慢 SQL 的时候延迟会非常明显。这个我在第五章会专门展开讲。2. 复制原理两份日志与三个线程2.1 binlog复制的数据源头主从复制能成立关键在主库上开着的一份日志二进制日志binlog。MySQL 在执行数据变更操作的时候会把操作按一定格式记录进 binlog。这个日志不只用于主从复制也是时间点恢复、闪回这类操作的基础。你用一句话把某张表某行数据改掉binlog 里留下的可能是一段这行数据改前是这样、改后是那样的完整记录。主从复制要做的事情说穿了就是把这个记录持续送到另一台机器再放一遍。注意binlog 和普通查询日志、错误日志不是一回事。它记录的只是数据变更相关的可复制事件不是所有执行的 SELECT。你跑一条 SELECT 不会写进 binlog。2.2 从库怎么把数据拉下来IO 线程和中继日志复制过程可以拆成三条线程。主库上有一条 dump 线程负责给从库发日志。每当一个从库连接上来要求同步主库就会为它开一个 binlog dump 线程持续从 binlog 里读出新的变更事件经网络发给从库。从库这边负责收数据的是 IO 线程。它从主库拉取 binlog 事件写入从库本地的一个文件叫中继日志 relay log。这一步只做搬运不执行任何 SQL。拉到手之后IO 线程继续向主库要下一批循环往复。2.3 SQL 线程把中继日志变成数据第三位是 SQL 线程它在从库本地读取中继日志里已经搬过来的事件把变更回放到从库真正改变从库的数据。把整条链路理一遍就是主库 binlog → dump 线程 → 网络 → 从库 IO 线程 → relay log → SQL 线程 → 从库数据。这两条线程相对独立。排查的时候第一步先分清是哪个线程停了IO 线程出问题通常是网络、账号、主库 binlog 访问出问题SQL 线程出问题一般是 SQL 回放时出现冲突。如果你看到 IO 线程正常但 SQL 线程报了错就不要去主库上瞎查问题大概率出在从库数据本身。2.4 binlog 格式STATEMENT、ROW、MIXED 怎么选MySQL 的 binlog 有三种格式直接影响复制是否可靠格式记录内容优点缺点STATEMENTSQL 语句本身日志量小可读性强非确定性操作可能主从不一致比如 NOW()、UUID()ROW被修改行的前后值最准确主从结果必然一致日志量大尤其是大范围更新MIXEDMySQL 自动判断默认多数用语句模式遇到不确定语句自动切行模式逻辑相对不够直观提示MySQL 8.0 默认 binlog_format 就是 ROW。我强烈建议生产环境直接用 ROW不要在格式上省那点磁盘空间。后期一旦要做数据恢复、解析 binlog 搭数据管道ROW 格式都是最稳的基础。2.5 GTID从对坐标到自动对账传统主从复制有个非常头疼的细节从库要知道自己该从主库 binlog 的哪个位置开始拉也就是文件名加偏移量比如MASTER_LOG_FILEmysql-bin.000003, MASTER_LOG_POS4125。这套坐标模式能用但有个要命的问题——只要主库 binlog 被清理过、从库重搭过、或者发生过主从切换坐标就得重新人工对齐非常容易出错。GTID 就是为解决这个而生的。每个事务都有全局唯一的一个事务编号从库依靠 GTID 可以自动判断该从哪个点继续拉不再关心文件名和偏移量。只要你主库的历史 binlog 还在GTID 复制就能自动衔接上。我强烈建议8.0 里直接开 GTID5.7 能开也开。所以后面的实操部分我直接以 GTID 模式为主来讲。3. 搭建前必须做好的规划3.1 版本与拓扑选型配置之前先问自己几个问题比敲命令重要得多主库现在有没有存量数据。全新空库配置最简单已经跑业务的库需要先做一次一致性备份再搬到从库这个放到第四章 4.3 节专门讲。拓扑是单主单从还是一主多从。起步阶段单主单从就够后面读流量再涨按同样的步骤再加从库即可。版本怎么定。建议主从用同一个大版本比如都是 MySQL 8.0 的某个稳定小版本。大版本不要混搭一个 5.7 配一个 8.0 很容易在复制行为上出幺蛾子。3.2 关键参数清单参数主要写在 my.cnf 里。我把核心参数整理成一张表配置时直接对着抄参数主库从库说明server-id12整个复制拓扑里必须全局唯一log-binmysql-bin按需开启主库必须开从库要开才能支持级联复制或后续升级为主库binlog_formatROWROW主从保持一致gtid_modeONON8.0 推荐直接开启enforce_gtid_consistencyONON约束不允许 GTID 不安全语句出现relay-log无relay-bin从库必备read_onlyOFFON从库禁止直接写入避免数据分叉binlog_expire_logs_seconds604800-比如保留 7 天防止日志被过早清理导致从库追不上有个参数容易翻车server-id。如果两台库的 server-id 一样复制表现会非常诡异从库可能显示连接成功但日志就是不推进查半天才发现是这个原因。每次加新节点之前先确认这个值没重复。3.3 复制账号与网络安全主库上需要建一个专门的复制账号权限越小越好只给 REPLICATION SLAVE 权限不要顺手给个超级管理员。账号的主机限定范围也尽量收紧别搞成任何主机都能登录。网络也要提前确认从库要能访问主库的 3306 端口中间有防火墙或者安全组就提前放通主库的 bind-address 不能只监听 127.0.0.1否则从库远程连不上。4. 手把手主从配置从零到验证接下来用两台机器举例主库地址 192.168.10.10从库地址 192.168.10.11。生产环境换成你实际的 IP命令可以直接抄。4.1 主库配置编辑主库的 my.cnf在[mysqld]段下加[mysqld] server-id 1 log-bin mysql-bin binlog_format ROW gtid_mode ON enforce_gtid_consistency ON binlog_expire_logs_seconds 604800改完重启主库登录确认配置生效SHOW VARIABLES LIKE server_id; SHOW VARIABLES LIKE log_bin; SHOW VARIABLES LIKE gtid_mode;如果 log_bin 显示 ON、gtid_mode 显示 ON说明主库已经准备就绪。注意如果之前主库从来没开过 binlog重启后新老日志之间会有个断层后续配置复制时要以当前最新的 binlog 状态为基准别拿旧状态对。4.2 从库配置在从库的 my.cnf 里加[mysqld] server-id 2 relay-log relay-bin gtid_mode ON enforce_gtid_consistency ON read_only ON log_replica_updates ON最后一个参数log_replica_updates在旧版本里叫log_slave_updates作用是让从库在回放中继日志时也写自己的 binlog。如果你预计这台从库将来有可能升级成新的主库或者要做级联复制这个必须开。即便暂时用不上开着也不吃亏。4.3 已有数据时如何初始化从库如果主库是一台空库直接跳过这一节。但如果主库已经跑了一段时间里面有存量数据那必须先让从库拿到一份主库的完整快照再启动复制。常用的做法是使用逻辑备份工具在主库上执行一次一致性备份。命令类似mysqldump --single-transaction --set-gtid-purgedON --all-databases --triggers --routines --events backup.sql执行完把备份文件传到从库在从库上恢复mysql -uroot -p backup.sql这里有个关键点在 GTID 模式下恢复完成后需要让从库知道这些数据对应哪些 GTID 事务这样它才能自动跳过已经被数据包含的事务不会重复执行。mysqldump 的--set-gtid-purgedON会在备份文件里写入对应的SET GLOBAL.gtid_purged语句恢复时如果看到自动执行了就没问题。如果因为权限或者其他原因没执行成功就需要手动设置。提示备份恢复期间从库上不要并发执行任何写操作否则会让数据基准错乱。恢复完成后可以先登录从库执行SHOW GLOBAL VARIABLES LIKE gtid_purged确认有值再继续下一步。4.4 建立复制链路先在主库上创建复制账号CREATE USER repl192.168.10.% IDENTIFIED BY StrongPass_2024; GRANT REPLICATION SLAVE ON *.* TO repl192.168.10.%; FLUSH PRIVILEGES;然后在从库上指定主库信息并启动CHANGE MASTER TO MASTER_HOST192.168.10.10, MASTER_USERrepl, MASTER_PASSWORDStrongPass_2024, MASTER_AUTO_POSITION1; START SLAVE;MySQL 8.0.22 之后官方把 SLAVE 相关术语改成了 REPLICA你也会看到下面这种写法CHANGE REPLICATION SOURCE TO SOURCE_HOST192.168.10.10, SOURCE_USERrepl, SOURCE_PASSWORDStrongPass_2024, SOURCE_AUTO_POSITION1; START REPLICA;两种写法效果一样老写法也仍然被兼容。执行完成后查看复制状态SHOW SLAVE STATUS\G重点看三个字段Replica_IO_Running和Replica_SQL_Running都要是 YesSeconds_Behind_Master应该是 0 或者很小的数字。如果 IO 线程显示 Connecting多半是网络、账号或者防火墙问题如果 SQL 线程显示 No则要先去看Last_SQL_Error的报错内容。4.5 验证复制是否真的在走配置完别急着收工动手造一点数据验证一下。在主库执行CREATE DATABASE testdb; USE testdb; CREATE TABLE user ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) ); INSERT INTO user(name) VALUES (zhangsan), (lisi);然后到从库上查询USE testdb; SELECT * FROM user;如果能看到zhangsan和lisi这两行说明主从链路已经通了。这时候还可以再验证一下从库的只读保护在从库上执行INSERT应该会收到类似The MySQL server is running with the --read-only option的报错这才是正常状态防止有人在从库上把数据写乱。4.6 一主多从与读写分离落地一主多从就是重复 4.4 的步骤每个从库一个独立 server-id指向同一个主库。从库数量上来以后要注意别让每个从库都在主库上各占一个 dump 线程这个影响一般不大但如果你有几十个从库就得评估主库的网络和 IO 负载了。读写分离的落地方式有两类一类是在应用层自己配置数据源把写操作路由到主库、读操作路由到从库另一类是引入数据库中间件让中间件根据语句类型自动分发。我个人的建议是业务初期直接在应用层做就够了成本最低也最好排查。无论哪种方式都要在代码里区分必须读实时的数据和允许读从库的数据前者强制走主库后者可以放心走从库。5. 日常监控与延迟问题5.1 复制健康检查一条命令就够了检查复制状态最常用的就是SHOW REPLICA STATUS\G输出里字段很多核心看这么几个字段正常值含义Replica_IO_RunningYesIO 线程正常拉取Replica_SQL_RunningYesSQL 线程正常回放Seconds_Behind_Master0 或很小与主库的落后秒数Last_IO_Error / Last_SQL_Error空最近一次报错内容Retrieved_Gtid_Set持续增长已拉取的 GTID 集合Executed_Gtid_Set持续增长已执行的 GTID 集合线上环境建议写一个定时脚本把Replica_SQL_Running和Seconds_Behind_Master采集出来做监控。复制不会自动恢复SQL 线程一旦报错停下后续所有新数据都会积压多等一分钟恢复成本就高一分钟。报警阈值我一般设成SQL_Running 不是 Yes 立刻报警延迟超过 30 秒或者持续上涨就告警。5.2 主从延迟为什么会产生Seconds_Behind_Master归零是理想状态但线上经常做不到。延迟的根源主要有几类SQL 线程天生的短板。主库是并发执行事务的而从库的 SQL 线程本质上接近串行回放。8.0 提供了并行复制但也需要针对性地配置。大事务。主库一个事务更新几百万行对主库来说事务内是并行的对从库来说整个事务必须等中继日志全部拉过来再完整回放中间的时间差就是延迟。从库硬件比主库弱。这种情况不少见成本考虑给从库配了低配机器结果回放速度跟不上。从库上跑着重查询。本来从库就是承接读压力的如果再有个糟糕的没有索引的分析查询把 CPU 打满SQL 线程自然被拖累。解决办法还是分层看混合业务大事务拆成小事务从库硬件别省分析查询独立到专门库。同时开启并行复制比较关键的参数是replica_parallel_workers和replica_parallel_type在从库上设为合理的并发值能明显改善高并发场景的延迟。5.3 从库数据不一致是复制中断的根源很多 SQL 线程报错本质都是从库数据和主库已经产生偏差。而偏差又往往来自早期的错误习惯直接在从库上手工执行 SQL、关闭 read_only、跳过错误时跳错了事务等等。数据一旦偏差复制基本就不敢信任了。所以在运维习惯上我有三条底线从库必须开 read_only任何跳过错误操作都要记录在案并尽快核对数据差异发现不可修复的偏差最稳妥的方案不是在那堆数据上反复修补而是直接用最近备份重建从库。重建速度往往比你想的要快也比重建信心容易得多。6. 常见错误排查实录6.1 IO 线程一直 Connecting 怎么办现象SHOW REPLICA STATUS里 IO 线程一直显示 Connecting。排查按这个顺序走先看网络从库上手动用 mysql 客户端去连主库的 3306能连通说明网络没问题再确认账号权限连接时会不会报Access denied再看主库SHOW PROCESSLIST正常情况能看到repl账号建立的连接如果看不到说明请求都没到主库。最后不要忽略防火墙和安全组策略。修复如果是CHANGE MASTER TO里的参数错了用STOP REPLICA; RESET REPLICA;清掉然后重新CHANGE ...。注意别在 IO 线程显示 Connecting 时反复重启先找到根因再动手。6.2 SQL 线程报错 1062主键冲突最常见的一条错误Error Duplicate entry 100 for key PRIMARY。意思是从库上已经存在这个主键而主库的事件又要插入同样的值。说明从库和主库的数据已经产生了偏差。处理思路要先止损停掉复制避免错误越积越多。然后判断这个事务能不能安全跳过。如果非常明确就是某一条历史误操作导致的可以用STOP REPLICA; SET GLOBAL SQL_SLAVE_SKIP_COUNTER 1; START REPLICA;但这只是把当下的错误跳过去不是修复数据。如果跳过的不是一个事务而是好几个后面的连锁报错会让你更头疼。我的经验是跳错只适用于确认单点误操作而且要事后把从库数据和主库做一次对比校验。偏差范围如果比较大就别跳了直接重建从库。6.3 找不到行的 1032 报错Could not execute Delete_rows event ... Cant find record in xxx这类错误意思是从库上找不到要更新或删除的那一行。在 ROW 格式下说明两边数据已经不对称了这不是靠跳过就能解决的小问题往往是某次误操作把从库数据搞乱了。正确姿势停止复制对涉及的表做主从数据比对如果差异是局部的可以考虑从主库把对应行补过去如果差异范围很广重建从库是最省心的选择。不要想着手工逐条把数据改成一致你会被隐藏的差异折磨到怀疑人生。6.4 从库拉不到日志主库 binlog 被清理了复制中断太久主库的 binlog 已经按保留策略被清掉从库再回来请求时主库已经找不到它需要的那个坐标或 GTID 对应的日志。现象通常是 IO 线程报错提示在master log ...处找不到 binlog。预防措施就是主库的binlog_expire_logs_seconds设置足够长同时从库异常停止必须有告警。已经发生的话唯一出路是用最近的备份把从库重建起来没法从当前主库补拉历史。所以别总把等会再处理当口头禅复制问题务必修完一个再走。6.5 排查速查表现象核心原因首选处理IO 线程 Connecting网络、账号、防火墙先手动连接主库验证SQL 线程 No从库数据与主库偏差看 Last_SQL_Error单点错误可跳过偏差大则重建1062 主键冲突从库已有相同记录确认后跳过或补数据1032 找不到行从库缺数据比对差异局部补全或整体重建Seconds_Behind_Master 持续上涨大事务、慢查询、低配拆事务、优化查询、开并行复制复制一切正常但数据对不上有人在从库写过数据开启 read_only重建从库6.6 值得长期坚持的运维习惯最后分享几条我个人的经验都是踩过坑换来的。第一任何主从变更先在测试环境完整走一遍特别是CHANGE MASTER TO这条命令生产环境敲错一个 IP 或者密码排查成本极高。第二复制账号和密码务必记录在内部密码管理系统里别随手写在某个文档后面没人知道密码的时候重配复制链路会变成一场灾难。第三从库只读这个开关是底线很多数据不一致都源于有同事在从库上手工改了数据。第四定期做数据校验可以自己写脚本对比关键表的行数和校验值遇到可疑差异提前处理别等到复制完全停下才动手。这套主从配置流程我前前后后搭过很多套从最初的单主单从到后面的一主多从回过头看最核心的从来不是命令和参数而是对复制原理的清晰认知。只要把 binlog、relay log、三个线程、GTID 这几个概念吃透了遇到任何复制问题你都能顺着链路一步步定位到具体环节。新项目刚开始搭数据库的时候不妨就顺手把主从方案定下来等业务量真起来的那天你会感谢当初多花的那一个小时。
