2016年，我在一个项目中需要搭建MySQL主从复制，实现读写分离和数据备份。

一开始我觉得很简单——不就是改几个配置项，执行几条命令嘛。网上教程一大堆，照着做就行了。

但是，真正搭起来之后，我才发现，MySQL主从复制的坑一个接一个。主从不同步、延迟大、数据不一致、server-id冲突、binlog格式不对、GTID配置出错、半同步复制不生效、读写分离的坑……

那段时间，我经常熬夜排查问题，查文档、翻论坛、做实验，踩了不少坑，也积累了不少经验。

今天就来聊聊，那些让我熬夜的MySQL主从复制配置，希望后来人能少踩坑。

一、主从复制的基本原理

在聊踩坑之前，先简单回顾一下MySQL主从复制的基本原理。

MySQL主从复制基于binlog（二进制日志）实现，整个过程分为三步：

  1. 主库（Master）将数据变更记录到binlog中
  2. 从库（Slave）的IO线程将主库的binlog复制到本地，写入relay log（中继日志）
  3. 从库的SQL线程读取relay log，将变更应用到从库，实现数据同步

主从复制的模式：

  • 异步复制（Asynchronous）：默认模式，主库写入成功后立即返回，不等待从库复制。性能好，但是主库宕机可能丢失数据。
  • 半同步复制（Semi-synchronous）：主库写入成功后，至少等待一个从库确认收到binlog后才返回。比异步更安全，但是性能稍差。
  • 全同步复制（Synchronous）：主库等待所有从库都确认收到binlog后才返回。最安全，但是性能最差，MySQL原生不支持。

主从复制的用途：

  • 数据备份：从库作为主库的备份，主库宕机可以切换到从库
  • 读写分离：写操作走主库，读操作走从库，减轻主库压力
  • 高可用：主库宕机时，从库可以提升为主库，实现故障转移
  • 数据分析：从库用于报表、统计等分析操作，不影响主库性能

二、搭建步骤（看起来很简单）

MySQL主从复制的搭建步骤，网上教程一大堆，看起来很简单：

1. 主库配置（my.cnf）：

[mysqld]
server-id=1
log-bin=mysql-bin
binlog-format=ROW
datadir=/var/lib/mysql

2. 从库配置（my.cnf）：

[mysqld]
server-id=2
relay-log=relay-bin
read_only=1

3. 主库创建复制用户：

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

4. 主库锁表，记录binlog位置：

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
-- 记录File和Position

5. 主库导出数据：

mysqldump -u root -p --all-databases --master-data=2 > backup.sql

6. 主库解锁：

UNLOCK TABLES;

7. 从库导入数据：

mysql -u root -p < backup.sql

8. 从库配置复制：

CHANGE MASTER TO
  MASTER_HOST='主库IP',
  MASTER_USER='repl',
  MASTER_PASSWORD='password',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=12345;

9. 从库启动复制：

START SLAVE;

10. 检查复制状态：

SHOW SLAVE STATUS\G
-- 检查Slave_IO_Running和Slave_SQL_Running是否都是Yes

看起来是不是很简单？十几步就能搭好。但是，真正用起来，你会发现，坑才刚刚开始。

三、踩过的坑

坑1：server-id冲突，主从不复制

现象：从库SHOW SLAVE STATUS显示SlaveIORunning: No，报错Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids。

原因：主库和从库的server-id相同。MySQL主从复制要求主库和从库的server-id必须唯一，不能相同。

我第一次搭建的时候，从库直接复制了主库的配置文件，server-id都是1，结果主从复制根本启动不了。

解决方案：

  • 确保主库和每个从库的server-id都不同
  • 主库用1，从库依次用2、3、4……
  • 修改后重启MySQL
# 主库
server-id=1

# 从库1
server-id=2

# 从库2
server-id=3

教训：复制配置文件的时候，一定要记得改server-id！

坑2：binlog格式不对，主从数据不一致

现象：主从复制看起来正常（SlaveIORunning和SlaveSQLRunning都是Yes），但是从库的数据和主库不一致。比如主库执行了一条UPDATE ... WHERE ... LIMIT 1，从库更新的行和主库不一样。

原因：binlog格式设置成了STATEMENT模式。

MySQL的binlog有三种格式：

  • STATEMENT：记录SQL语句本身。优点是binlog小，缺点是某些非确定性函数（如NOW()、UUID()、RAND()）和LIMIT语句在主从执行结果可能不同，导致数据不一致。
  • ROW：记录每行数据的变更。优点是数据一致，缺点是binlog大（特别是大表的ALTER TABLE）。
  • MIXED：混合模式，MySQL自动判断，一般语句用STATEMENT，非确定性语句用ROW。

我一开始图binlog小，用了STATEMENT模式，结果主从数据不一致，排查了很久才发现是binlog格式的问题。

解决方案：

  • 生产环境推荐用ROW格式，保证数据一致性
  • 如果担心binlog太大，可以用MIXED模式
  • 不要用STATEMENT模式，除非你确定所有SQL都是确定性的
[mysqld]
binlog-format=ROW

教训：数据一致性比binlog大小重要，不要为了省空间用STATEMENT模式。

坑3：主从延迟大，读操作读到旧数据

现象：主从复制状态正常，但是从库的数据比主库慢很多。刚在主库写入的数据，从库要等几秒甚至几分钟才能读到。读写分离后，用户刚提交的数据，刷新页面就看不到了（因为读操作走从库，从库还没同步）。

原因：主从延迟的原因有很多，常见的有：

  1. 从库性能差：从库的CPU、内存、磁盘比主库差，追不上主库的写入速度
  2. 大事务：主库执行了一个大事务（如大批量UPDATE/DELETE），从库执行同样的事务需要很长时间
  3. 锁等待：从库的SQL线程被锁等待阻塞
  4. 网络延迟：主从之间网络不好，binlog传输慢
  5. 单线程复制：MySQL 5.5及之前，从库的SQL线程是单线程的，主库并发写入，从库单线程回放，追不上
  6. 大表DDL：主库执行大表的ALTER TABLE，从库执行同样的DDL需要很长时间，期间所有后续操作都被阻塞

我遇到的主从延迟，主要是因为从库性能差+大事务+单线程复制。

解决方案：

  1. 从库性能和主库相当：不要用太差的机器做从库，从库的CPU、内存、磁盘应该和主库相当
  2. 避免大事务：把大事务拆分成小事务，分批执行
  3. MySQL 5.6+开启多线程复制：MySQL 5.6支持按库多线程复制，5.7支持按表多线程复制（基于GTID）

``ini [mysqld] slave-parallel-type=LOGICAL_CLOCK slave-parallel-workers=4 ``

  1. 优化网络：主从之间用内网，保证网络稳定
  2. 大表DDL用pt-online-schema-change：避免长时间锁表
  3. 监控主从延迟：用SecondsBehindMaster监控，延迟过大时报警
  4. 读写分离时考虑延迟：刚写入的数据，读操作可以走主库，或者等从库同步后再读

教训：主从延迟是读写分离最大的敌人，一定要做好监控和应对方案。

坑4：GTID配置出错，复制启动不了

现象：配置了GTID（全局事务标识）后，从库复制启动不了，报错The slave is configured with GTIDMODE = ON but the master has GTIDMODE = OFF，或者GTIDNEXT cannot be set to ANONYMOUS when GTIDMODE = ON。

原因：GTID是MySQL 5.6引入的特性，用于简化复制和故障转移。但是GTID的配置有很多坑：

  1. 主库和从库的gtid_mode必须一致（都ON或都OFF）
  2. 开启GTID需要重启MySQL，而且不能直接从OFF切到ON，需要经过OFF→OFFPERMISSIVE→ONPERMISSIVE→ON四个阶段
  3. 开启GTID后，CHANGE MASTER TO需要用MASTERAUTOPOSITION=1，而不是指定MASTERLOGFILE和MASTERLOGPOS
  4. 从库导入数据时，如果数据是在非GTID模式下导出的，导入到GTID模式的从库会出错

我一开始图省事，直接把gtid_mode=ON加到配置文件里重启，结果主从复制彻底乱了，折腾了很久才恢复。

解决方案：

  1. 如果不需要GTID的特性（如多源复制、自动故障转移），可以先不开GTID，用传统的文件+位置方式
  2. 如果要开GTID，按照官方文档的步骤，分四个阶段逐步开启：

`` 阶段1：gtidmode=OFF, enforcegtidconsistency=OFF 阶段2：gtidmode=OFFPERMISSIVE, enforcegtidconsistency=WARN 阶段3：gtidmode=ONPERMISSIVE, enforcegtidconsistency=ON 阶段4：gtidmode=ON, enforcegtidconsistency=ON 每个阶段至少等一个事务周期，确保没有匿名事务 ``

  1. 开启GTID后，从库配置复制用：

``sql CHANGE MASTER TO MASTERHOST='主库IP', MASTERUSER='repl', MASTERPASSWORD='password', MASTERAUTO_POSITION=1; ``

  1. 导出数据时用--set-gtid-purged=ON参数

教训：GTID是好东西，但是配置复杂，开启前一定要仔细看官方文档，不要图省事直接开。

坑5：半同步复制不生效，还是异步

现象：配置了半同步复制（semi-sync），但是实际还是异步复制，主库宕机还是会丢数据。

原因：半同步复制需要主库和从库都安装插件并开启，而且有很多细节：

  1. 主库和从库都需要安装半同步插件：

``sql -- 主库 INSTALL PLUGIN rplsemisyncmaster SONAME 'semisyncmaster.so'; -- 从库 INSTALL PLUGIN rplsemisyncslave SONAME 'semisyncslave.so'; ``

  1. 需要开启半同步：

``ini [mysqld] # 主库 rplsemisyncmasterenabled=1 rplsemisyncmastertimeout=1000 # 超时时间，毫秒 # 从库 rplsemisyncslaveenabled=1 ``

  1. 从库开启半同步后，需要重启IO线程才能生效：

``sql STOP SLAVE IOTHREAD; START SLAVE IOTHREAD; ``

  1. 半同步复制要求至少一个从库确认，如果所有从库都超时（超过rplsemisyncmastertimeout），主库会自动降级为异步复制

我一开始只在主库开了半同步，从库没开，结果半同步根本不生效。后来从库也开了，但是没重启IO线程，还是不生效。折腾了半天才发现是这些细节问题。

解决方案：

  1. 主库和从库都安装插件并开启
  2. 从库开启后重启IO线程
  3. 检查半同步状态：

``sql -- 主库 SHOW STATUS LIKE 'Rplsemisyncmasterstatus'; -- 从库 SHOW STATUS LIKE 'Rplsemisyncslavestatus'; ``

  1. 合理设置超时时间，避免网络抖动导致频繁降级
  2. 监控半同步状态，如果降级为异步要及时报警

教训：半同步复制的配置细节很多，每一步都要确认生效，不要以为加了配置就完事了。

坑6：主从切换后，原来的主库数据不一致

现象：主库宕机，把从库提升为主库。原来的主库恢复后，重新加入复制，但是数据不一致，复制报错。

原因：主从切换（failover）是主从复制最复杂的部分。常见的问题：

  1. 主库宕机时，有些binlog还没传到从库，从库提升为主库后，这些数据就丢了
  2. 原来的主库恢复后，还有一些"额外"的事务（宕机前已提交但是从库没收到的），和新主库的数据冲突
  3. 原来的主库重新加入复制时，server-id、GTID等配置不对
  4. 应用层的连接没切换，还在往原来的主库写数据

我做过一次主从切换演练，结果原来的主库恢复后，数据和新主库冲突，复制根本启动不了，最后只能重新初始化从库（重新导出导入数据）。

解决方案：

  1. 用半同步复制，减少主库宕机时的数据丢失
  2. 主从切换时，确保原来的主库确实宕机了（避免脑裂），可以用MHA、Keepalived等工具
  3. 原来的主库恢复后，先不要急着加入复制，先检查数据，必要时重新初始化（从新主库导出数据导入）
  4. 开启GTID，简化主从切换和重新加入
  5. 应用层用VIP或代理（如ProxySQL、MaxScale），切换时自动路由到新主库
  6. 定期做主从切换演练，确保切换流程可靠

教训：主从切换是高可用的核心，一定要提前演练，不要等到真正出问题才手忙脚乱。

坑7：读写分离的坑

现象：搭了主从复制，做了读写分离（写主读从），但是问题不断：刚写入的数据读不到、从库延迟导致数据不一致、事务内的读操作走了从库导致数据不对、连接池的读写路由混乱……

原因：读写分离看起来简单，但是实际用起来有很多坑：

  1. 主从延迟：刚写入主库的数据，从库还没同步，读操作走从库就读不到
  2. 事务内的读：事务内的读操作应该走主库，否则可能读到不一致的数据
  3. 强制读主：某些场景（如刚提交后立即查询）需要强制读主库
  4. 连接池：连接池中的连接可能被复用，读写路由可能混乱
  5. 从库负载均衡：多个从库之间的负载均衡，需要考虑从库的性能和延迟
  6. 从库宕机：从库宕机时，读操作需要自动切换到其他从库或主库

我一开始用了一个简单的读写分离中间件，结果问题不断，最后换成了ProxySQL，才稳定下来。

解决方案：

  1. 用成熟的读写分离中间件（如ProxySQL、MaxScale、MyCat），不要自己造轮子
  2. 配置读写路由规则：

- 写操作（INSERT/UPDATE/DELETE）走主库 - 事务内的所有操作走主库 - 刚写入后的查询走主库（可以用hint强制） - 其他读操作走从库

  1. 监控主从延迟，延迟过大时，读操作自动切换到主库
  2. 从库负载均衡，考虑从库的性能和延迟
  3. 从库宕机时自动故障转移
  4. 应用层可以用hint（如/+ master /）强制某些查询走主库

教训：读写分离不是简单地"写主读从"，有很多细节需要处理，用成熟的中间件比自己写靠谱。

四、排查思路

主从复制出问题时，我的排查思路一般是这样的：

1. 看复制状态

SHOW SLAVE STATUS\G

重点看：

  • SlaveIORunning：IO线程是否运行（Yes/No/Connecting）
  • SlaveSQLRunning：SQL线程是否运行（Yes/No）
  • LastIOError：IO线程的最后错误
  • LastSQLError：SQL线程的最后错误
  • SecondsBehindMaster：主从延迟（秒）
  • MasterLogFile/ReadMasterLog_Pos：IO线程读取到的主库binlog位置
  • RelayMasterLogFile/ExecMasterLogPos：SQL线程执行到的主库binlog位置

2. 判断是IO线程问题还是SQL线程问题

  • IO线程No：网络问题、认证问题、server-id冲突、binlog不存在
  • SQL线程No：SQL执行错误（数据冲突、表结构不一致、主键冲突等）

3. IO线程问题排查

  • 检查网络：从库能否ping通主库，主库3306端口是否开放
  • 检查认证：复制用户的用户名密码是否正确，权限是否足够
  • 检查server-id：主从server-id是否不同
  • 检查主库binlog：SHOW MASTER STATUS，确认binlog文件和位置存在
  • 查看错误日志：LastIOError和MySQL错误日志

4. SQL线程问题排查

  • 查看错误：LastSQLError，通常会告诉你具体的SQL错误和位置
  • 常见的SQL线程错误：

- 主键冲突（1062）：从库已经有这条数据 - 表不存在（1146）：从库缺少表 - 列不存在（1054）：主从表结构不一致 - 外键约束失败（1452）：数据不一致

  • 解决方案：

- 跳过错误（不推荐，可能导致数据不一致）：SET GLOBAL sqlslaveskip_counter=1; START SLAVE; - 修复数据：根据错误信息，手动修复从库的数据 - 重新初始化：数据不一致严重时，重新从主库导出导入

5. 主从延迟排查

  • 看SecondsBehindMaster
  • 看主库的写入压力（QPS、TPS）
  • 看从库的性能（CPU、内存、磁盘IO）
  • 看是否有大事务、大表DDL
  • 看网络延迟

五、最佳实践

总结一下MySQL主从复制的最佳实践：

  1. server-id唯一：主库和每个从库的server-id都不同
  2. binlog用ROW格式：保证数据一致性
  3. 从库性能和主库相当：避免从库追不上
  4. 开启多线程复制：MySQL 5.6+按库并行，5.7+按表并行（基于GTID）
  5. 避免大事务：大事务拆分成小事务
  6. 大表DDL用pt-online-schema-change：避免长时间锁表
  7. 半同步复制：至少一个从库确认，减少数据丢失
  8. GTID：简化复制和故障转移（配置复杂，按需开启）
  9. 监控：监控复制状态、延迟、错误，及时报警
  10. 定期演练：定期做主从切换演练，确保高可用
  11. 备份：主从复制不是备份，还要有定期的物理/逻辑备份
  12. 读写分离用成熟中间件：ProxySQL、MaxScale等，不要自己造轮子
  13. 从库只读：从库设置read_only=1，避免误写导致数据不一致
  14. 定期校验数据一致性：用pt-table-checksum定期检查主从数据是否一致

六、写在最后

MySQL主从复制，看起来简单，用起来坑多。从搭建到运维，从配置到排错，每一步都有很多细节需要注意。

我踩过的这些坑，每一个都让我熬过夜、掉过头发。但是，踩坑的过程也是成长的过程。现在再遇到主从复制的问题，我已经能比较从容地排查和解决了。

如果你正在搭建MySQL主从复制，或者正在被主从复制的问题困扰，希望这篇文章能帮到你。记住：

  • 搭建前仔细看官方文档，不要只看网上的简略教程
  • 每一步配置后都要验证是否生效
  • 做好监控，问题早发现早解决
  • 定期演练，确保高可用方案可靠
  • 遇到问题不要慌，按照排查思路一步步来

最后，用一句话总结：主从复制不是搭起来就完事了，运维和监控才是重点。

愿每一个DBA和后端开发者，都能少踩主从复制的坑，多睡几个安稳觉。那些让我熬夜的配置，希望你们不用再熬。