主主复制因AUTOINCREMENT引发主键重复报错，求最优解决方案

一、更优解决方案分析 #

1. 业务层限制单主写入（最稳妥） #

主主复制的核心设计初衷是高可用故障切换，而非双写扩容。如果能在业务层面约定：特定业务模块仅向其中一个主库写入，仅当该主库故障时才切换到另一主库写入，就能从根源避免双节点同时插入导致的自增ID冲突。这是成本最低、一致性风险最小的方案，绝大多数生产环境的主主架构都会采用这种模式。

2. 替换自增ID为全局唯一ID生成器 #

放弃AUTOINCREMENT字段，改用全局唯一的主键生成策略：

• UUID/GUID：直接生成全局唯一字符串作为主键，无需依赖数据库自增机制，彻底避免ID冲突。缺点是字符串主键的索引性能略低于整数，且占用存储空间更大。
• 雪花算法（Snowflake）：生成64位整数ID，包含时间戳、机器ID、序列号，既保证全局唯一，又能维持ID的递增性，索引性能接近自增ID。需要注意维护集群节点的时钟同步，避免时钟回拨导致ID重复。

3. 基于ON DUPLICATE KEY UPDATE的冲突补救（非根治） #

如果业务无法避免双写，可在INSERT语句末尾添加ON DUPLICATE KEY UPDATE逻辑，让冲突发生时不中断复制，而是执行更新操作。例如：

INSERT INTO your_table (col1, col2) VALUES ('val1', 'val2')
ON DUPLICATE KEY UPDATE 
  id = LAST_INSERT_ID(id),  -- 保证1847185返回正确值
  col1 = VALUES(col1),      -- 可选：用新值覆盖旧值，或根据业务逻辑合并
  col2 = VALUES(col2);

但需注意：这种方式仅能处理冲突后的异常，无法从根源避免ID重复。如果双节点插入的是不同业务数据，更新操作会导致其中一方的数据被覆盖，需确保业务逻辑允许这种情况，否则会引发数据一致性问题。

4. 优化自增ID配置（容忍空洞） #

你提到的auto_increment_increment和auto_increment_offset配置，虽然会产生ID空洞，但在绝大多数业务场景下，空洞是完全可接受的——自增ID的核心作用是唯一标识记录，而非保证连续。如果业务对ID连续性有强需求，可通过业务层维护独立的序列表（需加分布式锁避免并发问题），或使用分布式序列生成组件，但这会增加架构复杂度。

二、MIXED日志模式未判定为不安全语句的原因 #

MIXED模式下，MySQL/MariaDB判断语句是否“安全”的核心标准是：语句在主库执行的结果，与在从库执行的结果是否完全一致，判断逻辑仅针对单主复制场景，不会考虑多主双写的特殊情况。

对于普通的INSERT语句（不指定自增ID），在单个节点上，自增ID的生成由auto_increment_increment和auto_increment_offset决定，从库复制该语句时，只要配置与主库一致，生成的ID会和主库完全相同，因此MIXED模式会判定这类语句是“安全”的，采用基于语句的复制（SBR）。

但在主主双写且未调整自增配置的场景下，两个主库会生成相同的自增ID，当其中一个节点的复制进程同步另一个节点的INSERT语句时，就会触发主键冲突——这种跨节点的冲突是MIXED模式的安全判断逻辑未覆盖的场景，因为它默认假设只有一个主库写入，不会预判多主同时写入的情况。

内容的提问来源于stack exchange，提问作者Collector