在运维工作中，主从复制的过程中主键冲突了怎么办？

在 MySQL 主从复制环境中，主键冲突是一个常见的问题，尤其是在多主复制（Multi-Master）或主从复制中手动插入数据时。主键冲突会导致 SQL 线程停止，从而影响数据同步的正常进行。在运维工作中，处理主键冲突需要快速定位问题并采取合适的解决方案，以确保主从复制的稳定性和数据一致性。

以下是处理主从复制中主键冲突的详细步骤和方法：

1. 主键冲突的原因

1. 多主复制环境：

   • 在多主复制（Master-Master）环境中，两个主节点可能同时插入具有相同主键的记录，导致冲突。

2. 手动插入数据：

   • 在从节点（Slave）上直接插入数据，而这些数据的主键与主节点（Master）上的数据冲突。

3. 数据迁移或恢复：

   • 从其他数据库恢复数据时，未正确处理主键，导致与现有数据冲突。

4. 复制延迟：

   • 在主从复制中，由于网络延迟或从节点负载过高，导致某些事务在从节点上执行时发生冲突。

2. 如何发现主键冲突

当主从复制发生主键冲突时，从节点的 SQL 线程会停止，并记录错误信息。可以通过以下方式发现主键冲突：

1. 查看从节点状态

SHOW SLAVE STATUS\G;

重点关注以下字段：

• Slave_SQL_Running：如果为 NO，说明 SQL 线程停止了。
• Last_SQL_Error：显示错误信息，通常会包含“Duplicate entry”字样，表明发生了主键冲突。

示例输出：

Slave_SQL_Running: NO
Last_SQL_Error: Error 'Duplicate entry '123' for key 'PRIMARY'' on query. Default database: 'mydb'. Query: 'INSERT INTO mytable (id, name) VALUES (123, 'test')'

3. 解决主键冲突的方法

1. 跳过冲突的事务

如果主键冲突是暂时的或不重要的，可以通过跳过冲突的事务来恢复复制。

STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;  -- 跳过一个事务
START SLAVE;

注意：

• sql_slave_skip_counter 的值表示跳过的事务数量。如果需要跳过多个事务，可以设置为更大的值。
• 跳过事务可能会导致数据不一致，需谨慎使用。

2. 手动修复数据

如果主键冲突是由于数据问题引起的，可以手动修复数据以解决冲突。

1. 定位冲突的数据：

   • 在主节点和从节点上查询冲突的记录：

     SELECT * FROM mytable WHERE id = 123;

2. 删除或更新冲突的记录：

   • 如果从节点上的数据是多余的，可以删除冲突的记录：

     DELETE FROM mytable WHERE id = 123;

   • 如果需要更新数据，可以手动修改冲突的记录：

     UPDATE mytable SET name = 'new_name' WHERE id = 123;

3. 重新启动复制：

   STOP SLAVE;
   RESET SLAVE;  -- 清除中继日志中的错误事务
   START SLAVE;

3. 调整主键生成策略

为了避免主键冲突，可以调整主键生成策略，确保主键在主从节点之间不会重复。

1. 使用自增主键的偏移量：

   • 在多主复制环境中，为每个主节点设置不同的自增偏移量：

     -- 在主节点1上
     SET GLOBAL auto_increment_offset = 1;
     SET GLOBAL auto_increment_increment = 2;
      
     -- 在主节点2上
     SET GLOBAL auto_increment_offset = 2;
     SET GLOBAL auto_increment_increment = 2;

   • 这样，每个主节点生成的主键值不会重复。

2. 使用 UUID 或其他唯一标识符：

   • 如果业务允许，可以使用 UUID 作为主键，避免冲突：

     INSERT INTO mytable (id, name) VALUES (UUID(), 'test');

4. 使用 ON DUPLICATE KEY UPDATE

在插入数据时，可以使用 ON DUPLICATE KEY UPDATE 语法，避免主键冲突导致的错误。

INSERT INTO mytable (id, name) VALUES (123, 'test')
ON DUPLICATE KEY UPDATE name = VALUES(name);

这样，如果主键冲突发生，MySQL 会自动更新现有记录，而不是报错。

4. 预防主键冲突的方法

1. 规范数据操作

• 在从节点上避免直接插入数据，所有数据操作应通过主节点完成。
• 如果必须在从节点上插入数据，确保主键不会与主节点冲突。

2. 监控复制状态

定期监控从节点的状态，及时发现并处理主键冲突：

SHOW SLAVE STATUS\G;

3. 使用半同步复制

在多主复制环境中，使用半同步复制可以减少主键冲突的可能性：

-- 在主节点上
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
SET GLOBAL rpl_semi_sync_master_enabled = 1;
 
-- 在从节点上
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_slave_enabled = 1;

半同步复制确保主节点在提交事务前，至少有一个从节点确认收到 Binlog，从而减少冲突的可能性。

5. 我的总结

在 MySQL 主从复制中，主键冲突是一个常见的问题，但通过以下方法可以有效解决和预防：

1. 发现冲突：通过 SHOW SLAVE STATUS 查看从节点状态，定位主键冲突。
2. 解决冲突：根据实际情况选择跳过事务、手动修复数据或调整主键生成策略。
3. 预防冲突：规范数据操作、监控复制状态，并使用半同步复制减少冲突的可能性。

综上所述，在运维工作中，快速定位和解决主键冲突是确保主从复制稳定运行的关键。