mysql主从复制中的数据冲突与冲突解决方法

MySQL 主从复制数据冲突主因是 slave 被误写入，导致与 master 同步变更冲突；典型表现是复制中断、SQL 线程停止及重复键等错误；定位需查 Last_SQL_Error 等字段并解析中继日志；跳过可用 sql_slave_skip_counter 或 GTID 方式；长期应启用 read_only 和 ROW 格式并强化读写分离管控。

主从复制中 为什么 会出现数据冲突

MySQL 主从复制默认是单向异步的，slave 节点只读、不写，但一旦人为或通过应用误操作在 slave 上执行了写入（比如 INSERT、UPDATE、DELETE），就可能和后续从 master 同步过来的同一条记录变更产生冲突。典型表现是复制中断，SHOW SLAVE STATUSG 中出现 Seconds_Behind_Master: NULL 和 Slave_SQL_Running: No，错误信息常含 Duplicate entry、Can't find record 或 Deadlock found。

常见诱因包括：

• 运维人员直接连 slave 执行 DML 修复数据
• 应用未严格区分读写库，部分写请求发到了 slave
• 双主架构（Active-Active）下未配置自增偏移（auto_increment_increment / auto_increment_offset）
• 使用 STATEMENT 格式复制时，非确定性函数（如 NOW()、UUID()）导致主从结果不一致

如何快速定位当前冲突的具体 SQL 和表

最直接的方式是查 SHOW SLAVE STATUSG 输出中的三个关键字段：

• Last_SQL_Error：显示最后失败的 SQL 及错误码，例如 ERROR 1062 (23000): Duplicate entry '1001' for key 'PRIMARY'
• Relay_Log_File 和 Relay_Log_Pos：指向出错事务在中继日志中的位置
• Exec_Master_Log_Pos：对应 master 二进制日志中的位置，可用于反查原始事件

进一步分析可使用：

mysqlbinlog --base64-output=DECODE-ROWS -v /path/to/relay-log.000001 | grep -A 5 -B 5 "1001"

注意：必须用 --base64-output=DECODE-ROWS（如果是 ROW 格式）才能看到真实行变更；若为 STATEMENT 格式，直接查看 SQL 即可。

跳过冲突的几种安全操作方式

跳过不是万能解法，但紧急恢复时常用。核心原则是：只在明确知道该事件可丢弃、且不影响业务一致性时才跳过。

• 跳过单个事件（适用于 ROW 或 STATEMENT 格式）：

  SET GLOBAL sql_slave_skip_counter = 1;

  然后 START SLAVE;

• 基于 GTID 跳过（推荐，更精准）：

  STOP SLAVE; SET GTID_NEXT="f1a2b3c4-5678-90ab-cdef-1234567890ab:12345"; BEGIN; COMMIT; SET GTID_NEXT="AUTOMATIC"; START SLAVE;

  其中 f1a2b3c4……:12345 是 SHOW SLAVE STATUSG 中 Retrieved_Gtid_Set 或 Executed_Gtid_Set 里报错的那个事务 ID

• 临时关闭唯一性检查（高风险！仅限调试）：

  SET SESSION unique_checks=0; SET SESSION foreign_key_checks=0;

  执行完再恢复为 1，但不能解决根本冲突，还可能导致数据损坏

避免冲突的长期工程实践

真正减少冲突，得从架构和流程入手，而不是依赖事后跳过：

• 强制 slave 只读：

  SET GLOBAL read_only = ON;

  并在 配置文件 中固化 read_only=1；注意：此设置对 super 权限用户无效，应收回不必要的 SUPER 权限

• 使用 ROW 复制格式（binlog_format=ROW），避免非确定性 SQL 在主从间执行结果不同
• 双主场景下必须配对设置：

  auto_increment_increment = 2 auto_increment_offset = 1   # master A auto_increment_offset = 2   # master B

• 应用层做读写分离时，所有写操作必须 路由 到 master，可用中间件（如 ProxySQL、ShardingSphere）或客户端 SDK 强制约束

实际环境中，read_only 和 ROW 格式是最容易落地也最有效的两道防线。很多冲突问题，追根溯源都是因为 slave 被意外写入，而这个“意外”往往来自权限管理松散或监控缺失。