MySQL 从节点宕机重启后主键冲突

一、问题场景：从库重启后同步突然中断

某业务虚机在迁移过程中出现异常，运维人员执行重启操作后，发现虚机内的 MySQL 从库（版本 8.0.30）与主库同步完全停滞。登录从库后，首先通过核心命令排查复制状态：

 

mysql> show slave status\G

 

输出结果中的关键异常信息如下，直接指向同步故障的核心线索：

• Slave_IO_Running: Yes：IO 线程正常，说明从库能正常接收主库的二进制日志（binlog）；
• Slave_SQL_Running: No：SQL 线程停止，意味着从库无法将接收的中继日志（relay log）应用到本地；
• Last_Errno: 1062：错误码 1062 对应 “主键冲突”，提示中继日志中的事务在本地执行时，遇到了重复的主键记录；
• Last_SQL_Error：明确提到 “Worker 1 执行事务失败”，事务位于主库日志mysql-bin.000206的end_log_pos 590031097位置。

为进一步定位冲突细节，查询performance_schema库中复制 worker 的状态表：

mysql> select * from performance_schema.replication_applier_status_by_worker\G

 

结果显示，WORKER_ID=1的线程存在具体错误：Duplicate entry '123450002-x-102996-1-Bccount-20241030' for key 'abce.PRIMARY'，即从库的xxx.abce表中已存在该主键记录，而中继日志仍要求插入这条记录，导致 SQL 线程终止。

需要注意的是，该从库仅作为只读节点，未对外提供写入操作，因此排除了 “从库本地写入重复数据” 的可能性 —— 问题根源必然出在中继日志的应用环节。

二、深度解析：复制不一致的 “三大隐形坑”

MySQL 主从复制的核心逻辑是 “IO 线程接收 binlog→生成 relay log→SQL 线程应用 relay log”，而宕机、线程异常等场景可能破坏这一流程，导致复制不一致。结合 MySQL 官方文档与本次故障，复制不一致主要分为三类，也是本次主键冲突的关键背景：

1. 三类复制不一致场景

不一致类型	核心特征	典型触发场景
半应用事务	事务仅部分执行（如混合事务中，非事务表已更新但事务表未提交）	1. 宕机时正执行 “事务表 + 非事务表” 的混合更新； 2. 杀死 SQL 线程时事务未完成
间隙（Gaps）	事务序列不连续：前序事务未执行，后续事务已应用	多线程复制时，worker 线程执行顺序错乱，宕机后未补全前序事务
源日志位置滞后	Exec_Master_Log_Pos（已应用的主库日志位置）是 “低水位标记”，实际已应用的事务位置远超该值	多线程复制下，SQL 线程未及时更新 “已应用位置”，宕机后按旧位置重复应用事务

2. 本次故障的根源：位置复制 + 多线程的 “致命组合”

通过排查从库配置，发现两个关键问题：

• 未启用 GTID 复制：采用传统的 “基于日志位置” 的复制方式，依赖Master_Log_File和Exec_Master_Log_Pos定位同步进度，一旦位置记录滞后，就会重复应用事务；
• 开启多线程复制：slave_parallel_workers参数大于 0（默认 4 个 worker 线程），但未合理配置slave_preserve_commit_order=1（该参数需配合slave_parallel_type=LOGICAL_CLOCK，可避免事务执行顺序错乱）。

当从库异常宕机时，多线程复制的 “位置更新延迟” 问题被放大：
SQL 线程虽已将部分事务应用到本地，但未及时更新Exec_Master_Log_Pos（低水位标记）。重启后，从库仍按旧的Exec_Master_Log_Pos位置读取中继日志，重新执行已应用过的事务 —— 这就导致了 “主键冲突”（本地已存在该记录，再次插入必然失败）。

3. 复制不一致的连锁后果

若不及时处理，复制不一致会引发更严重问题：

• 从库数据与主库偏离，成为 “无效备份”；
• show slave status的位置信息失效，无法通过change master to重新配置复制；
• 启用--relay-log-recovery（中继日志恢复）时，因存在间隙会直接报错，无法自动恢复。

三、解决方案：临时修复与长期优化

针对本次主键冲突，需分 “临时恢复同步” 和 “长期根治隐患” 两步操作，确保业务不受影响且避免问题复发。

1. 临时修复：快速恢复同步（适用于紧急场景）

临时方案的核心是 “跳过重复事务”，但需先确认数据一致性，避免跳过关键事务导致数据偏差：

步骤 1：停止 SQL 线程，确认冲突数据

# 停止SQL线程，避免继续报错
mysql> stop slave sql_thread;

# 查看从库中冲突的主键记录，确认是否与主库一致
mysql> select * from xxx.abce where primary_key = '123450002-x-102996-1-Bccount-20241030';

 

若查询结果与主库该主键的记录完全一致，说明该事务已在从库执行过，可安全跳过；若数据不一致，需先通过主库 binlog 补全数据，再进行后续操作。

步骤 2：跳过冲突事务，重启 SQL 线程

 

# 跳过1个事务（针对单事务冲突，若多个冲突需多次执行）
mysql> set global sql_slave_skip_counter = 1;

# 重启SQL线程，验证同步状态
mysql> start slave sql_thread;
mysql> show slave status\G

 

若Slave_SQL_Running变为Yes，且Seconds_Behind_Master逐渐减小，说明同步已恢复。

注意：sql_slave_skip_counter仅适用于基于位置的复制，且需谨慎使用 —— 若跳过非重复事务，会导致主从数据不一致。

2. 长期优化：迁移到 GTID 复制（根治位置复制缺陷）

传统基于位置的复制依赖 “日志文件 + 偏移量”，易因位置滞后、日志文件删除等问题失效；而GTID（全局事务 ID）复制通过 “事务唯一标识” 替代位置，能自动跳过已执行的事务，从根本上避免重复应用导致的主键冲突。

GTID 复制的核心优势

• 事务级别的同步跟踪：每个事务有唯一 GTID，从库通过Retrieved_Gtid_Set（已接收）和Executed_Gtid_Set（已执行）管理进度；
• 自动跳过重复事务：主库仅发送从库未包含的 GTID 事务，无需手动处理位置；
• 简化复制配置：无需指定Master_Log_File和Exec_Master_Log_Pos，仅需master_auto_position=1即可。

迁移到 GTID 复制的关键步骤

以 MySQL 8.0 为例，主从库需按以下步骤配置（需重启数据库，建议在业务低峰期操作）：

（1）主库配置 GTID

修改主库my.cnf（或my.ini）配置文件：

 

[mysqld]
# 开启GTID模式
gtid_mode = ON
# 强制GTID一致性（避免非GTID事务）
enforce_gtid_consistency = ON
# 开启binlog（GTID依赖binlog）
log_bin = mysql-bin
# 从库应用的事务也记录到binlog（用于级联复制）
log_slave_updates = ON
# 服务器唯一ID（主从需不同）
server-id = 1

 

重启主库，验证 GTID 状态：

 

mysql> show variables like '%gtid%';
# 确认gtid_mode=ON，enforce_gtid_consistency=ON

 

（2）从库配置 GTID

修改从库my.cnf，除server-id与主库不同（如设为 2），其余 GTID 参数与主库一致：

[mysqld]
gtid_mode = ON
enforce_gtid_consistency = ON
log_bin = mysql-bin
log_slave_updates = ON
server-id = 2
# 多线程复制优化（可选，配合GTID更稳定）
slave_parallel_workers = 4
slave_parallel_type = LOGICAL_CLOCK
slave_preserve_commit_order = 1

 

重启从库后，重新配置主从复制（无需指定日志位置）：

 

# 先停止旧的复制（若已存在）
mysql> stop slave;
# 重置复制配置
mysql> reset slave all;

# 配置GTID复制（master_auto_position=1开启自动位置同步）
mysql> change master to
master_host = '11.11.11.11',  # 主库IP
master_user = 'repl',         # 复制账号
master_port = 3306,           # 主库端口
master_password = 'xxxxxx',   # 复制密码
master_auto_position = 1;     # 启用GTID自动定位

# 启动复制，验证状态
mysql> start slave;
mysql> show slave status\G

 

若Slave_IO_Running和Slave_SQL_Running均为Yes，且Retrieved_Gtid_Set与Executed_Gtid_Set正常更新，说明 GTID 复制已生效。

四、运维建议：避免同类问题再发生

一次主键冲突看似小事，实则暴露了复制配置的不规范。结合本次故障，建议从以下维度优化 MySQL 主从运维：

1. 复制配置标准化

• 强制启用 GTID：新搭建的主从架构必须使用 GTID，存量架构逐步迁移，彻底告别 “位置依赖”；
• 多线程复制参数合理化：开启多线程时，需配置slave_parallel_type=LOGICAL_CLOCK和slave_preserve_commit_order=1，避免事务执行顺序错乱；
• 禁用从库写入：通过read_only=1（普通账号）和super_read_only=1（超级账号）限制从库写入，防止人为操作导致数据不一致。

2. 监控体系完善

• 核心指标监控：实时监控Slave_IO_Running、Slave_SQL_Running、Seconds_Behind_Master，当 SQL 线程停止或延迟突增时立即告警；
• 错误日志监控：重点监控 1062（主键冲突）、1236（binlog 损坏）等复制相关错误码，通过 ELK、Zabbix 等工具实现日志告警；
• 数据一致性检查：定期使用pt-table-checksum（Percona 工具）对比主从数据，发现偏差及时通过pt-table-sync修复。

3. 宕机恢复流程规范化

• 从库重启后，先通过performance_schema.replication_applier_status_by_worker查看复制错误详情，再决定是否跳过事务；
• 若启用--relay-log-recovery（中继日志自动恢复），需确保无复制间隙，否则需先手动补全事务；
• 避免盲目执行sql_slave_skip_counter，跳过事务前必须确认数据一致性，必要时通过主库 binlog 回放补全数据。