「MySQLあるある」の1つ、「MySQLを再起動するとInnoDBテーブルのAUTO_INCREMENT値がレコードの最大値+1に巻き戻る件」が、MySQL 8.0(DMR)で解消されているということで、今更ですが実際に試してみました。

※本当は「透過的データ暗号化のその後」として、REDO/UNDOログの暗号化のほうを試そうかと思っていたのですが…MySQL 8.0.1以降で実装された機能はまだリリースされていませんでした。残念。Percona Live 2017の前に次のDMRがリリースされると良いのですが。

1. MySQL 5.7までの挙動

まず、テスト用のスキーマ／テーブル（いずれも名前は「test_ai」）を作成し、PK列としてAUTO_INCREMENT値が入るようにします。

# mysql -u root -h localhost -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.17 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE DATABASE test_ai;
Query OK, 1 row affected (0.00 sec)

mysql> USE test_ai;
Database changed
mysql> CREATE TABLE test_ai (id int(10) PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100)) ENGINE innodb;
Query OK, 0 rows affected (0.01 sec)

次に、このテーブルに続けて3行INSERTします。

mysql> INSERT INTO test_ai SET name = 'first';
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO test_ai SET name = 'second';
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test_ai SET name = 'third';
Query OK, 1 row affected (0.00 sec)

こんなふうにレコードが入っています。

mysql> SELECT * FROM test_ai;
+----+--------+
| id | name   |
+----+--------+
|  1 | first  |
|  2 | second |
|  3 | third  |
+----+--------+
3 rows in set (0.00 sec)

3行目をDELETEしてみます。

mysql> DELETE FROM test_ai WHERE id = 3;
Query OK, 1 row affected (0.02 sec)

続いて、4行目をINSERTしてみます。

mysql> INSERT INTO test_ai SET name = 'fourth';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test_ai;
+----+--------+
| id | name   |
+----+--------+
|  1 | first  |
|  2 | second |
|  4 | fourth |
+----+--------+
3 rows in set (0.00 sec)

「id=3」は飛ばされて、「id=4」でINSERTされたことがわかります。

ここで、「id=4」の行を削除し、テーブル定義を確認してみます。

mysql> DELETE FROM test_ai WHERE id = 4;
Query OK, 1 row affected (0.00 sec)

mysql> SHOW CREATE TABLE test_ai\G
*************************** 1. row ***************************
       Table: test_ai
Create Table: CREATE TABLE `test_ai` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

次に割り当てられるAUTO_INCREMENT値が「5」であることがわかります。

MySQLを再起動してみます。

mysql> QUIT;
Bye
# systemctl restart mysqld.service

再起動後、新しい行をINSERTすると、「id=3」でINSERTされてしまうことがわかります。

# mysql -u root -h localhost -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.17 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> USE test_ai;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> INSERT INTO test_ai SET name = 'fifth';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test_ai;
+----+--------+
| id | name   |
+----+--------+
|  1 | first  |
|  2 | second |
|  3 | fifth  |
+----+--------+
3 rows in set (0.00 sec)

mysql>

これがMySQL 5.7までの挙動です。
外部キー制約を使わない（シャーディング・パーティショニング等のために使えない）場合、テーブルの最終行を削除したところでMySQLを再起動すると、他テーブルとのレコード間の紐づきがおかしくなる危険性があります。

2. MySQL 8.0では

同じように進めてみます。

# mysql -u root -h localhost -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 8.0.0-dmr MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE DATABASE test_ai;
Query OK, 1 row affected (0.00 sec)

mysql> USE test_ai;
Database changed
mysql> CREATE TABLE test_ai (id int(10) PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100)) ENGINE innodb;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO test_ai SET name = 'first';
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO test_ai SET name = 'second';
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test_ai SET name = 'third';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test_ai;
+----+--------+
| id | name   |
+----+--------+
|  1 | first  |
|  2 | second |
|  3 | third  |
+----+--------+
3 rows in set (0.00 sec)

mysql> DELETE FROM test_ai WHERE id = 3;
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test_ai SET name = 'fourth';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test_ai;
+----+--------+
| id | name   |
+----+--------+
|  1 | first  |
|  2 | second |
|  4 | fourth |
+----+--------+
3 rows in set (0.00 sec)

mysql> DELETE FROM test_ai WHERE id = 4;
Query OK, 1 row affected (0.00 sec)

mysql> SHOW CREATE TABLE test_ai\G
*************************** 1. row ***************************
       Table: test_ai
Create Table: CREATE TABLE `test_ai` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> QUIT;
Bye
# systemctl restart mysqld.service
# mysql -u root -h localhost -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 8.0.0-dmr MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> USE test_ai;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SHOW CREATE TABLE test_ai\G
*************************** 1. row ***************************
       Table: test_ai
Create Table: CREATE TABLE `test_ai` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

再起動しても、次のAUTO_INCREMENT値は「5」のままです。

mysql> INSERT INTO test_ai SET name = 'fifth';
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM test_ai;
+----+--------+
| id | name   |
+----+--------+
|  1 | first  |
|  2 | second |
|  5 | fifth  |
+----+--------+
3 rows in set (0.00 sec)

mysql>

再起動前に削除した「id=3, 4」が飛ばされて「id=5」でINSERTされました。

3. 注意

実は、1. の続きでMySQL 8.0にバージョンアップ（途中手順ミスあり）して同じことを試したところ、なぜかMySQL 5.7までと同じ挙動になってしまいました。

正確には、

MySQL 5.7で 1. の手順を実行後、「test_ai」テーブル・スキーマを削除
MySQL Community Server用のYUMリポジトリの設定を書き換えて、MySQL 5.7を無効化（enable=0）、MySQL 8.0を有効化（enable=1）
MySQL 5.7を止めずに「yum update mysql-community-server」
誤って「mysql_secure_installation」したら最後で「システムテーブルがおかしい」と怒られる
MySQLを再起動し、「mysql_upgrade」してからあらためて「mysql_secure_installation」
この状態で 2. の手順を実行

という流れで進めたところ、再起動後にINSERTした行が「id=3」になってしまいました。

MySQL 8.0のGA版ではどうなるかわかりませんが、5.7からのバージョンアップで8.0を使う場合は、手順を間違えないよう注意したほうが良さそうです。

【おまけ】
MySQL 8.0関連投稿記事へのリンクを集めました。

Qiitaに投稿したMySQL 8.0関連記事

MySQL 8.0でInnoDBのAUTO_INCREMENT問題解消を確認してみる

1. MySQL 5.7までの挙動

2. MySQL 8.0では

3. 注意