MySQL8.0以下版本重启后自增主键值丢失问题

文章目录

问题
原因分析
- - MySQL8.0以下版本
  - MySQL8.0版本
  - MySQL5.7版本
问题复现
- MySQL 5.7.22
- MySQL 8.0.23
解决方案
- 升级MySQL到8.0
- 业务代码预防
参考

问题

上周运维同学重启了UAT环境的MySQL数据库服务，刚开始我是不知道数据库重启了，先是业务服务开始异常，初步定位到的原因是一个表的主键ID出现了回退，又查到MySQL低版本（8.0以下）重启可能会导致自增主键值回退或重置，最后和运维同学确认了下重启时间，确认了原因。
因为生产环境使用的版本和UAT一致，所以生产环境一旦宕机或重启，也会出现这个问题，后果很严重，马上进行了复现并修复。

原因分析

MySQL8.0以下版本

Innodb引擎表的自增值是存放在内存中的，并没有持久化到磁盘。当服务重启后，会将表当前MAX(ID)+自增步长，作为当前表的自增值。这就是导致了一个问题：当存在DELETE数据的场景中，如果最大的自增主键的记录删除了，重启后自增值就会回退；如果表中的数据全被删除了，重启后自增值重置为配置的auto_increment_offset值，默认为1。
这个问题可能会对业务造成非常大的影响，但是却存在了很长时间，官方一直到MySQL8.0中才进行了修复。见MySQL5.7官方文档
在这里插入图片描述

MyISam引擎表的自增值是存放数据文件中的，重启后不会丢失。但因为MyISam不支持事务，所以企业基本使用的都是Innodb引擎。

MySQL8.0版本

MySQL8.0版本，自增值逻辑发生了变化。自增值会持久化到redo日志中当服务重启后，会将redo日志中的自增值和表当前MAX(ID)比较，取二者较大的值，再加自增步长作为当前表自增值。 保证自增值能够正确恢复。见MySQL8.0官方文档
在这里插入图片描述

MySQL5.7版本

既然有bug，那么为啥不升级到8.0呢？主要是因为MySQL5.7比较稳定+历史遗留问题。。。
MySQL5.7版本是在2015年发布的，距今已经有近10年的时间了。官方虽然已经在2023年10月已经停止运维了，但目前在企业中使用仍然广泛，占比46%，数据统计于2022年5月，链接、数据来源。
在这里插入图片描述

问题复现

用docker分别搭建了MySQL5.7.22版本和8.0.23版本，使用一个示例，复现问题。

MySQL 5.7.22

-- 1.创建user表,InnoDB引擎,自增主键为id列
CREATE TABLE `user` (`id` int(11)  NOT NULL AUTO_INCREMENT,`name` varchar(255) NOT NULL DEFAULT '',`sex` tinyint(4)  DEFAULT '0',PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1;-- 2.插入5条数据
INSERT INTO `user`(`name`, sex) VALUES('user1', 0);
INSERT INTO `user`(`name`, sex) VALUES('user2', 0);
INSERT INTO `user`(`name`, sex) VALUES('user3', 0);
INSERT INTO `user`(`name`, sex) VALUES('user4', 0);
INSERT INTO `user`(`name`, sex) VALUES('user5', 0);-- 3.查看AUTO_INCREMENT，值为6 ，正常
show create table user; -- 4.删掉id=4,5的记录
delete from user where id > 3;-- 5.再次查看AUTO_INCREMENT，值为6 ，正常
show create table user; -- 6.重启服务
docker restart mysqld 
-- 7. 再次查看AUTO_INCREMENT，值为4，出现回退

重启MySQL服务前查看AUTO_INCREMENT
在这里插入图片描述
重启MySQL服务后查看AUTO_INCREMENT

MySQL 8.0.23

在MySQL8.0中，执行同样的操作，自增值仍为6，没有丢失，这里就不演示啦！但我在实际操作过程中，也遇到一个其他小问题。
查看自增值可以通过show create table 表名；也可以通过show table status from 数据库名 like 表名，查看表的一些元数据信息INFORMATION_SCHEMA ，原数据信息中一列为Auto_increment，表示自增值。
无论哪个版本，使用show create table查看，获取的AUTO_INCREMENT都是最新的。但是MySQL8.0版本使用show table status查看到AUTO_INCREMENT却不是最新的。这是由于在MySQL8.0版本中引入了缓存机制，如果想要查最新的AUTO_INCREMENT，需要设置缓存过期为0.

SET SESSION information_schema_stats_expiry = 0

在这里插入图片描述

解决方案

升级MySQL到8.0

正如前文中所说，这种方式复杂且风险很大，MySQL8.0并不完全兼容低版本，升级后这个问题解决了，但可能会导致其他方面的问题，所以生产环境一般是不会同意这样做的。

业务代码预防

踩中这个坑的主要原因，还是因为使用了DELETE 删除了最大自增主键的数据。我这里主要是数据量大需要及时清理，不得不删，一般都是先逻辑删除的，再定期清理。除了升级MySQL，还可以用业务代码预防。
如延迟删除最大的ID记录，最大的ID记录先不删，等到出现更大的ID，再把之前的ID删掉，本次我是用了这种方法。
如果业务场景的自增主键没有暴露给外部，丢失其实也没什么影响。又或者需要暴露给外部，可以新增一列UID，作为幂等键、唯一标识等等，不要直接使用主键ID。

使用业务代码预防，方法是很灵活的，可以根据具体场景具体分析。

参考

官方5.7文档： https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html#innodb-auto-increment-initialization
官方8.0文档：https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html#innodb-auto-increment-initialization
https://www.shadowserver.org/news/over-3-6m-exposed-mysql-servers-on-ipv4-and-ipv6/
https://roll.sohu.com/a/728274763_121124377
https://blog.csdn.net/Seky_fei/article/details/107756788