MySQL 8.0的自增主键持久化特性

MySQL 8.0 的AUTO_INCREMENT值为什么重启后不丢

因为 InnoDB 把当前自增计数器的值（不是“下一个值”，而是已分配过的最大值）作为元数据，随每次INSERT或显式ALTER TABLE ... AUTO_INCREMENT = N操作，一并写入redo log。崩溃或正常重启时，InnoDB 会从redo log中重放这条记录，重建内存中的计数器。

这和 5.7 完全不同：5.7 的计数器纯内存维护，重启时执行SELECT MAX(ai_col) FROM table_name FOR UPDATE扫描 B+ 树来推导初始值——它根本不知道你曾手动设过AUTO_INCREMENT = 999，更不会保留事务回滚后“已预分配但未提交”的 ID。

• SHOW CREATE TABLE输出的AUTO_INCREMENT值在 8.0 是可信的（来自持久化数据字典），5.7 只是内存快照，随时可能失效
• 即使表为空，只要执行过ALTER TABLE t AUTO_INCREMENT = 500，8.0 就立刻落盘；5.7 重启即归零
• 首次建表时，8.0 会立即把AUTO_INCREMENT = 1写入redo log；5.7 此时不写，直到第一次插入才隐式更新内存

哪些场景下 5.7 的自增丢失会导致真实故障

不是“理论上可能出错”，而是线上高频踩坑点：

• 用自增 ID 作业务主键（比如订单号、用户 ID），且依赖“单调递增不重复”语义：重启后分配到已归档/删除的旧 ID，引发唯一键冲突ERROR 1062 (23000): Duplicate entry 'X' for key 'PRIMARY'
• 主从复制中，从库重启后AUTO_INCREMENT回退，与主库不一致，导致INSERT ... SELECT、REPLACE INTO或 binlog 回放失败
• 使用Xtrabackup备份恢复后，ibdata1中的数据 ID 已到 6038085，但新插入却从 6038081 开始——因为备份时没捕获内存中的计数器，恢复后靠扫描取 max+1 初始化
• 归档老数据后清空表，再手动ALTER TABLE ... AUTO_INCREMENT = 1000000跳过历史段，5.7 重启就变回max(id)+1，大概率撞上残留数据或下游系统缓存

innodb_autoinc_lock_mode和持久化是两回事，别混

很多人以为调高innodb_autoinc_lock_mode（比如设为 2）能缓解自增问题，其实它只控制并发插入时的锁行为（影响性能和分配顺序），完全不影响重启是否丢值。持久化与否，只取决于 MySQL 版本和 InnoDB 是否把计数器写进redo log。

也就是说：

• 即使innodb_autoinc_lock_mode = 0（传统锁模式），8.0 仍不丢值
• 即使innodb_autoinc_lock_mode = 2（交错模式），5.7 重启照样丢
• 持久化解决的是“生存周期”问题，锁模式解决的是“并发竞争”问题

迁移前必须验证的三个细节

升级到 8.0 后，不能默认“自动就安全了”。以下三点容易被忽略：

• 确认表引擎确实是InnoDB：MyISAM表的AUTO_INCREMENT在 8.0 也不持久，它走的是另一套逻辑
• 检查是否启用了innodb_redo_log_capacity过小或innodb_flush_log_at_trx_commit = 0：虽然计数器写入redo log是强制的，但极端配置下日志刷盘延迟可能影响崩溃恢复的完整性
• 注意mysql.innodb_autoinc系统表的存在：它在 8.0.30+ 才正式引入并用于辅助持久化，早期 8.0.x（如 8.0.20）仅靠redo log+ 检查点，需确认实际版本行为

最隐蔽的问题往往出在混合环境：主库 8.0、从库 5.7，或者应用层缓存了SHOW CREATE TABLE里的AUTO_INCREMENT值并做本地判断——这些地方不会报错，但会在某个凌晨重启后悄悄开始主键冲突。