MySQL中AUTO_INCREMENT达到最大值怎么办

在使用 MySQL 的时候，如果一个表增长非常快，记录条数越来越多，那么可能自增 ID 会遇到不够用的情况：

ERROR 1062 (23000): Duplicate entry '2147483647' for key 'my_table.PRIMARY'
ERROR 1467 (HY000): Failed to read auto-increment value from storage engine

此时怎么办？默认建表的时候 int 是 SIGNED 有符号类型，所以快速想变更为 UNSIGNED 类型，为了不影响服务，期望采取非阻塞的形式：

mysql > ALTER TABLE t1 MODIFY id int UNSIGNED NOT NULL AUTO_INCREMENT, ALGORITHM=INPLACE, LOCK=NONE;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
 
mysql > ALTER TABLE t1 MODIFY id int UNSIGNED NOT NULL AUTO_INCREMENT, ALGORITHM=INSTANT;
ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: Need to rebuild the table to change column type. Try ALGORITHM=COPY/INPLACE.

可以看到很简单的一个操作，实际上如果使用 ALTER 会是一个重建表的操作，必然产生阻塞，那怎么办呢？

1：直接 ALTER

这种操作就会整个锁表，相当于影响服务了，所有的操作都会等待：

mysql > show processlist\G
*************************** 1. row ***************************
Id: 11
User: msandbox
Host: localhost
db: db1
Command: Query
Time: 28
State: copy to tmp table
Info: alter table t1 modify id int unsigned NOT NULL AUTO_INCREMENT
*************************** 2. row ***************************
Id: 24
User: msandbox
Host: localhost
db: db1
Command: Query
Time: 27
State: Waiting for table metadata lock
Info: update t1 set a=2 where id=100

使用第三方工具 pt-online-schema-change或gh-ost 有一定的缓解作用，所以本质上还是会影响服务。

2：第二种方案本质上也没有解决问题，但可能会比直接 ALTER 影响时间少一点。

create table t_2 like t2;
alter table t_2 modify id int unsigned NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8388608;
rename table t2 to t2_old, t_2 to t2;

先拷贝一个表，然后评估一定时间会恢复，预估一个可能的 AUTO_INCREMENT 最大值，看上去后续的操作对于插入是没有问题的，但修改和删除就没有办法了。

接下去就可以拷贝数据。

INSERT INTO t2 SELECT * FROM t2_old;

这个操作不建议，会导致很大的性能问题。

ySQL Shell 导入/导出工具可以加速速度，是最快的逻辑导入和还原工具之一。

也可以尝试Percona Toolkit套件之一的 Pt-archiver。

当然最好的方法就是提前预测 max 错误什么时候到来，及早做好准备。