MySQL传统主从复制实战：从binlog原理到配置与排障

聊到MySQL高可用和读写分离，我估计不少人都被问过这么一句话：“你先搞个主从复制把读流量分流一下。” 这句话背后藏着MySQL最经典、也最绕不开的一个技术底座——基于binlog的传统主从复制。这篇文章我就把这个老牌方案从头到尾拆一遍，从binlog为什么能当复制媒介，到主库从库参数怎么配、数据怎么对齐、复制线程怎么跑，再到我实际运维中踩过的几个典型坑，一次性说透。适合刚接手数据库的运维同学、准备搭读写分离的后端工程师，以及想搞懂复制原理再去追GTID、半同步这些新玩意的朋友。

这个系列我打算分几篇写，今天先聊最基础的传统异步复制。它虽然“老”，但现在大量生产环境还在跑，而且后面的GTID、并行复制、半同步都是在这套机制上长出来的。地基不打牢，后面很容易糊。

1. 为什么先聊基于binlog的传统主从复制

1.1 主从复制到底解决了什么问题

主从复制的核心就一句话：把主库的变更日志同步到从库，让从库帮你分担读压力或者当灾备。实际业务中它解决的痛点很典型：

第一个是读多写少的场景。某次我在一个业务系统里看到，主库CPU动不动冲到80%，一看慢查询日志，全是报表查询和后台管理页面的统计语句，真正写数据的接口反而没多少负载。这种时候把查询挪到从库，主库就能专心处理写事务，效果立竿见影。

第二个是备份与容灾。做物理备份或者逻辑备份的时候，直接在从库上跑，能避免备份任务把主库IO打满。同时如果主库机器硬件故障，从库可以快速切换顶上来，虽然传统异步复制做不到零丢失，但至少能把业务中断时间从“等修复”缩短到“分钟级切换”。

第三个是读取扩展。业务量涨了，一台从库不够就再加一台，扩容成本比业务层做复杂缓存方案要低得多。

但有一点必须说清楚：传统主从复制解决不了写瓶颈。所有写操作还是集中在主库上，它也不是自动故障转移方案，切换需要人工介入或者依赖外部高可用组件。理解这个边界，你就不会对它有不切实际的期待。

1.2 复制方案对比：传统binlog复制、GTID、半同步、组复制

MySQL这些年衍生出好几个复制形态，我简单列一下它们的关系：

• 传统异步复制：基于binlog的文件名和偏移位点定位，主库不等待从库确认，性能最好，但主库宕机时可能丢事务。
• 半同步复制：主库提交事务后，至少要等一个从库确认收到binlog才返回客户端，减少了数据丢失窗口，但会增加写入延迟。
• 组复制（MGR）：基于Paxso类共识协议的多主或单主复制，能自动选主，但对网络和版本要求更高，运维复杂度也上来了。
• GTID复制：用全局事务ID代替文件名+位点，定位事务更简单，故障切换时不用再费劲找位点。

我为什么建议大家先学传统异步复制？因为它虽然机制简单，却是理解其他所有方案的基础。binlog怎么产生、IO线程怎么拉日志、SQL线程怎么回放，这套链路在所有复制方案里都是通用的。你把传统方案彻底吃透，再看GTID其实就是给每个事务打了个唯一标号，半同步就是在提交确认环节加了个等待，理解成本会低很多。

而且现实环境里，老系统、第三方同步工具、某些云数据库实例，很多还是传统复制模式。你去排查别人留下的环境，不懂这个是真的寸步难行。

1.3 这个方案适合什么样的业务阶段

如果业务刚起步、数据量不大、访问量也不高，其实没必要上主从。单库扛得住的时候，多一套复制就是多一份运维负担。但一旦出现下面这些信号，就可以考虑上了：

• 读流量已经明显让主库CPU或IO出现周期性峰值；
• 报表、统计分析类的只读SQL开始影响在线交易业务；
• 需要有一个尽量接近实时的备份副本，用来恢复误操作数据；
• 团队准备做高可用切换演练，需要先具备一主一从的基础架构。

传统异步复制在这些场景里是最稳妥的起步方案。它不强制依赖额外插件，MySQL原生就能配置，排错思路也清晰，适合作为团队第一套高可用架构的起点。

2. binlog：复制的数据底座

2.1 binlog是怎么记录数据的

binlog是MySQL的二进制日志，所有会改变数据的操作，比如INSERT、UPDATE、DELETE、CREATE TABLE、ALTER TABLE，都会以“事件”的形式按顺序写进binlog文件。SELECT和普通的SET这类不改变数据内容的操作不会记。

复制能成立，靠的就是binlog这个“数据底座”。主库把变更写进binlog，从库通过网络把binlog拉到本地，然后一条一条重新执行一遍，最终从库就变成了主库的数据副本。整个过程跟出版社印报纸很像：主库是编辑部，binlog是排版好的底稿，从库是印刷厂，拿着底稿一份份印出来。

binlog是按顺序追加的，每个文件写到一定大小就切换下一个，文件名通常是 mysql-bin.000001、mysql-bin.000002 这样的序号。复制位点就是“哪个文件的哪个偏移量”，这个偏移量在复制协议里用Position表示。

2.2 三种记录格式：STATEMENT、ROW、MIXED

binlog有三种格式，选哪个直接影响复制的一致性和日志体积。我把它们放在一起对比：

我在生产环境一直推荐直接用ROW。尤其是MySQL 5.7以后，ROW格式配合binlog_row_image=FULL，能把变更前和变更后的完整镜像记录下来。真遇到误删数据，你可以用binlog解析工具把删除前的行捞回来，这是STATEMENT格式做不到的。虽然日志文件变大了，但磁盘现在便宜，一致性带来的安心感比省那点空间值钱得多。

有个细节要注意：ROW格式下，一条UPDATE如果影响了十万行，binlog里会产生十万条行事件，同步到从库也要逐行应用，这也是大事务导致主从延迟的常见原因之一。

2.3 和复制强相关的几个关键参数

配置主从复制，不是只开个log_bin就行。下面这些参数直接影响复制的稳定性，我整理成表：

这些参数里面，server_id是必须给的，而且主从一定要不同。我之前遇到过一兄弟直接把主库的配置文件拷贝到从库，忘了改server_id，结果IO线程怎么都连不上，报错信息还是“server id ... conflicts”，排查半天才发现是配置克隆的锅。

binlog_expire_logs_seconds这个参数值得多说一句。很多从库报错“Got fatal error 1236”就是因为主库binlog已经被purge清理掉了，从库需要的位点找不回来了。保留时间建议结合全量备份周期来设，至少要比备份周期长，最稳妥是备份完成后才purge旧的binlog。

3. 传统主从复制的完整配置实操

3.1 场景假设和准备工作

为了把步骤讲清楚，我虚构一个标准环境：主库IP是192.168.56.10，从库IP是192.168.56.20，操作系统是Linux，MySQL版本用8.0.x。如果你用的是5.7，命令上有些小差异，我会在关键位置单独标注。

开始之前，先确认两件事：两台机器端口3306都能互通，防火墙或者安全组规则放行；两套MySQL实例都已完成初始化，root或具备SUPER权限的账户可用。

我建议在动手前先停掉业务写入，至少不要在配置过程中执行大批量DML。如果是生产环境，最好选业务低峰期来操作，并且先在测试环境完整演练一遍。

3.2 主库配置：开启binlog并设置server_id

编辑主库的my.cnf或my.ini（Windows环境），在[mysqld]段下加下面这些配置：

[mysqld] server_id = 1 log_bin = mysql-bin binlog_format = ROW sync_binlog = 1

server_id保证实例唯一，log_bin指定binlog文件前缀名，binlog_format显式使用ROW格式，sync_binlog=1让每个binlog事件都同步到磁盘，降低宕机丢日志风险。

改完后重启MySQL服务。重启后登录MySQL，执行：

SHOW MASTER STATUS;

正常情况下会看到类似这样的输出：

File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set mysql-bin.000001 | 157 | | |

这里的File就是当前正在写的binlog文件名，Position是当前写入偏移量。这两个值就是后面配置从库复制位点时要用到的关键数据。

3.3 创建专用的复制账号

主库需要一个从库用来连接它的账号。复制账号权限不用给太大，只需要REPLICATION SLAVE权限就够了。千万不要图省事直接用root当复制账号，那是给自己埋雷。

在主库执行：

CREATE USER 'repl'@'192.168.56.%' IDENTIFIED BY 'your_strong_password'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.56.%'; FLUSH PRIVILEGES;

注意授权对象里的网段。192.168.56.%表示允许这个网段内的从库来连接，更严格可以写成具体的从库IP。密码建议用强密码，复制账号泄露就等于数据库数据泄露，因为通过binlog能读取到几乎所有的数据变更内容。

3.4 从库配置：独立的server_id和relay log

从库同样要改my.cnf，关键是不能跟主库用同一个server_id：

[mysqld] server_id = 2 log_bin = mysql-bin relay_log = relay-bin read_only = ON super_read_only = ON

从库开log_bin不是必须的，但建议开。因为从库可能作为更高一级从库的主库，或者未来要做主从切换，提前开好binlog能省不少事。

read_only和super_read_only一起开，能防止应用程序和误操作写从库。注意read_only对主从复制本身没有影响，SQL线程应用relay log时不受read_only限制，这是机制上就设计好的。

relay_log参数建议显式设置。如果不设置，某些版本在主机名变化时可能导致relay log文件名混乱，排查问题的时候特别头疼。

改完配置记得重启从库实例。

3.5 数据初始化和复制位点对齐（最容易翻车的一步）

这一步是整个搭建过程里最容易被忽略、也最容易出问题的环节。如果主库和从库的初始数据不一致，复制一开始就会报错或者产生静默的数据差异。

分两种情况看：

第一种，主库是刚初始化的空库，比如新装的测试环境，没有任何业务数据。这种情况最简单，直接把从库的change master位点设为主库当前SHOW MASTER STATUS的位置就行。

第二种，主库已有存量数据，这是生产环境最常见的情况。做法是先做主库备份，再恢复到从库，同时拿到备份那一刻的binlog位点。推荐用mysqldump：

mysqldump --single-transaction --master-data=2 -h192.168.56.10 -uroot -p --all-databases > master_full.sql

这个命令里有两个关键点：--single-transaction利用InnoDB的MVCC机制获取一致性快照，不锁表，对在线业务影响小；--master-data=2会在备份文件的头部注释里自动记录当时的binlog文件名和Position。

打开备份文件头部，能看到这样两行：

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=157;

这两个值就是之后从库启动复制的起点。

把备份文件导入从库：

mysql -h192.168.56.20 -uroot -p < master_full.sql

导入后从库的数据就跟主库备份那一刻完全一致了。剩下的就是告诉从库从哪个位点开始继续同步。

这里我要特别强调：如果你跳过备份对齐这一步，直接按当前SHOW MASTER STATUS的位点配从库，而主库的binlog里某些事务并没有全量包含你缺的那部分数据，复制虽然能起来，但主从数据会差一截。更糟的是，这差的一截很难被发现，等到你真正需要从库切换或者查询时才会暴露。

3.6 在从库执行change master并启动复制

数据就位后，在从库执行复制配置。MySQL 8.0推荐用新的CHANGE REPLICATION SOURCE TO语法，5.7及更早版本用CHANGE MASTER TO。下面以8.0为例：

CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.168.56.10', SOURCE_PORT=3306, SOURCE_USER='repl', SOURCE_PASSWORD='your_strong_password', SOURCE_LOG_FILE='mysql-bin.000003', SOURCE_LOG_POS=157;

如果你的备份文件是从主库mysqldump出来的，SOURCE_LOG_FILE和SOURCE_LOG_POS一定要用文件头部注释里记录的位点，而不是你现在SHOW MASTER STATUS查到的值。这两个值必须精确匹配备份时刻的binlog位置，差一个字节都同步不起来。

如果你用的是5.7，执行的语句是：

CHANGE MASTER TO MASTER_HOST='192.168.56.10', MASTER_PORT=3306, MASTER_USER='repl', MASTER_PASSWORD='your_strong_password', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=157;

配置完成后启动复制：

START REPLICA; -- 8.0 -- 5.7 使用 START SLAVE;

然后我建议立刻查看复制状态：

SHOW REPLICA STATUS\G -- 8.0 -- 5.7 使用 SHOW SLAVE STATUS\G

有两点重要提示：如果从库之前配置过其他主库，执行CHANGE REPLICATION SOURCE TO之前最好先STOP REPLICA，再RESET REPLICA ALL，清掉旧的复制上下文，避免残留位点造成干扰。ALTER USER和CHANGE语句执行后不会马上生效，STOP和START交替操作时注意顺序。

复制启动后，通常几十秒内两个线程就会进入Yes状态。如果一直显示Connecting，大概率是网络不通、账号权限不对或者server_id有冲突，可以看Last_IO_Error具体报错。

4. 复制跑起来之后，怎么确认它真的健康

4.1 SHOW REPLICA STATUS核心指标解读

复制启动后，最直观的判断方法就是看SHOW REPLICA STATUS的输出。输出字段非常多，我平时只看这几个关键项：

两个Running只要有一个是No，复制就是中断状态。Seconds_Behind_Master这个值我提醒一下：它只在SQL线程空闲时才重新计算，如果SQL线程一直忙，这个值可能不准确甚至不更新。所以不能只看它来断定延迟，要结合Master_Log_File和Relay_Master_Log_File的差距一起看。

4.2 写数据验证从主到从的完整链路

配置完成后别急着收工，做一次端到端验证。在主库执行：

CREATE DATABASE testdb; USE testdb; CREATE TABLE t_user (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50)); INSERT INTO t_user(name) VALUES ('zhangsan'), ('lisi'), ('wangwu');

然后到从库查询：

USE testdb; SELECT * FROM t_user;

能看到三行数据就说明复制链路基本通了。再分别在主从执行SHOW MASTER STATUS和SHOW REPLICA STATUS，确认binlog文件名和Position都在推进，说明数据是持续流动的。

顺手验证一下从库read_only是否生效：在从库尝试写入一条INSERT，如果报错“The MySQL server is running with the --read-only option”，说明只读保护已经起作用了。这也是防止未来误写从库的第一道防线。

4.3 日常监控需要盯哪些指标

复制搭好只是开始，日常运维重点是监控几个信号：

• Replica_IO_Running和Replica_SQL_Running的状态变化。这两个只要变No，就得立刻告警。
• Seconds_Behind_Master超过阈值。通常设个30秒或60秒的告警阈值比较合理，但要根据业务对数据新鲜度的要求调整。
• 主库binlog增长速度和磁盘空间。ROW格式下，日志增长可能非常快，磁盘打满会让整个数据库hang住。
• 从库relay log的增长。如果SQL线程追不上IO线程，relay log会一直累积，也可能塞满从库磁盘。

监控方式可以用脚本定时抓SHOW REPLICA STATUS输出，也可以接入你现有的监控系统。如果是小型环境，一个简单的crond脚本加短信通知就够用。

5. 传统主从复制常见故障与排查实战

5.1 Slave_IO_Running显示Connecting

这是一个高频问题。IO线程连不上主库，通常原因有几种：网络不通、复制账号密码错、权限不对、server_id冲突。

排查顺序我建议是这样的：

先看Last_IO_Error的具体内容。如果报错是：

error connecting to master 'repl@192.168.56.10:3306' - retry-time: 60 retries: 1

先测试网络和端口：

telnet 192.168.56.10 3306

能通的话，再用复制账号手动登录一下主库，验证账号和密码是否正确：

mysql -h192.168.56.10 -urepl -p

登录成功后查权限：

SHOW GRANTS FOR 'repl'@'192.168.56.%';

确认至少有REPLICATION SLAVE权限。如果都正常，检查从库和主库的server_id是否不同。这个坑我到现在都记得，有次搭建三台实例的集群，用了同一份配置模板，结果三台server_id全是1，复制全部失败。

5.2 Slave_SQL_Running显示No

SQL线程停住，常见原因是从库数据与主库不一致，SQL回放时报错。比如从库已经有了主键冲突记录，或者某张表被意外修改过。

处理前一定要搞清楚原因，执行：

SHOW REPLICA STATUS\G

重点看Last_SQL_Error字段，里面会明确告诉你哪条语句执行失败、在哪个库哪张表上。

最稳妥的恢复方案是重新做一次全量数据对齐：在主库重新mysqldump，导入从库，重置复制位点。我见过不少人在SQL线程报错后习惯性用sql_slave_skip_counter 跳过一个事务，但跳过的是事务内容而不是错误本身，很可能让数据差异越来越大。除非你明确知道报错事务是无效数据、跳过它无损，否则别跳。

如果确实要临时跳过，MySQL 8.0可以这样：

STOP REPLICA; SET GLOBAL SQL_SLave_SKIP_COUNTER = 1; START REPLICA;

这个操作要极其谨慎，我的建议是万不得已不用。

5.3 UUID冲突导致的复制报错

从库IO线程报错信息里如果出现：

Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs

说明两台实例的server-uuid一样。这种情况大多是复制了数据目录或者克隆虚拟机造成的，因为uuid存在数据目录下的auto.cnf文件里。

解决办法很简单：停掉从库实例，删除或修改auto.cnf中的uuid，然后重启。比如：

systemctl stop mysqld rm -f /var/lib/mysql/auto.cnf systemctl start mysqld

重启后MySQL会自动生成一个新的uuid，复制就能正常继续。注意在这之前要确认从库数据没有被意外写入，因为重启不会重置数据。

这个坑在虚拟机模板克隆场景特别常见。以后做相关操作，第一反应就检查auto.cnf，能省很多时间。

5.4 其他高频问题速查表

5.5 我的一些避坑心得

搭过几套主从之后，我总结出几条原则：

从库越简单越好，所有应用连接和写操作一律禁止在从库执行。read_only加上super_read_only都打开，哪怕暂时没有应用程序连接从库，也要先养成好习惯。

数据对齐永远不要省。备份、校验、恢复、位点确认，一步一步来。我见过很多线上事故，起因都是“我先粗略配一下，后面再补数据”。后面永远不会补。

不要迷信Seconds_Behind_Master等于0。有时候主库写入频率低，从库早就追上了，它当然是0；一旦有大的批量任务，延迟瞬间飙升。平时要把binlog位点和延迟结合起来看。

还有一个容易被忽略的问题：如果主库是大内存实例，binlog缓存相关的参数比如binlog_cache_size如果设置不当，大事务写binlog时可能频繁发生磁盘临时写入，影响主库性能。这个参数可以后续再调优，但至少要心里有数。

6. 写在最后：当年搭第一套主从复制踩过的坑

回想我第一次在生产环境搭主从，因为没改server_id，IO线程反复重试连接，我还以为是主库防火墙问题，折腾了大半夜。后来发现主库binlog被清理，从库报1236错误，被迫全量重做了一次。这些教训说白了都是“基础概念不牢被现实教育”。传统主从复制原理看起来简单，但真正跑起来，涉及位点、日志格式、账号权限、数据一致性，每一环都容不得马虎。

我个人现在搭这类环境，流程已经固化了：先确认版本和参数，再设计账号和网段，然后低峰期做一致性备份，恢复后核对位点，最后启动复制并连续观察几个小时的延迟曲线。每一步不图快，反而从来不返工。

先把这个方案玩熟，后面聊GTID、半同步复制、并行复制的时候，你会发现很多概念都是相通的。下一篇我打算专门写GTID复制，它解决了传统复制最让人头疼的位点定位问题，也是现在新建集群时更推荐的方式。到时候我们再接着拆。