MySQL 8.0.16 でテーブルスペース・REDO ログ／UNDO ログ・システムテーブル暗号化

以前 MySQL 8.0.4 RCでテーブルスペース暗号化＋REDOログ／UNDOログ暗号化 という記事を書きましたが、

• マイナーバージョンが上がるにつれて必須設定項目が減ったこと
• MySQL 8.0.16 でシステムテーブル（mysqlスキーマ）の暗号化をサポートしたこと

から、今回、8.0.16 をベースに書き直しました。

【関連記事】

• MySQL 8.0.13 で一般テーブルスペース（General Tablespace）を暗号化する
• MySQL 8.0.14 でバイナリログ・リレーログを暗号化する

【参考】

• 15.6.3.9 InnoDB Data-at-Rest Encryption（MySQL 8.0 Reference Manual）

設定

UNDO ログの細かい設定は必須ではなくなっています。
また、innodb_file_per_table=1がデフォルト設定に変わったためmy.cnfの設定から省略しました。

なお、ダブルライトバッファには平文で記録されてしまうようですので、innodb_doublewrite=0は残しています。

---

2020/09/08 追記：
あらためて調査したところ、MySQL 8.0.18 以降はダブルライトバッファへの記録時に暗号化されていました。

• 小ネタ／MySQL 8.0 のダブルライトバッファがどのバージョンで暗号化されるようになったのか調べた

---

my.cnf（関連部分のみ）

early-plugin-load=keyring_file.so
keyring_file_data=/var/lib/mysql-keyring/keyring
innodb_doublewrite=0
innodb_redo_log_encrypt=1
innodb_undo_log_encrypt=1

設定を追加してmysqldを再起動するとキーリングファイルが生成されます。

再起動

# systemctl stop mysqld.service
# systemctl start mysqld.service
# ls -l /var/lib/mysql-keyring
total 4
-rw-r----- 1 mysql mysql 579 Apr 30 10:04 keyring

テーブル・REDO ログ／UNDO ログ暗号化

暗号化テーブルを作成するにはENCRYPTION='Y'を指定してCREATE TABLEします（既存テーブルを暗号化するにはALTER TABLEで。暗号化を解除するにはENCRYPTION='N'を指定）。

テーブル暗号化

mysql> CREATE DATABASE enc_test;
Query OK, 1 row affected (0.01 sec)

mysql> USE enc_test;
Database changed
mysql> CREATE TABLE enc_test (id int(10) PRIMARY KEY AUTO_INCREMENT, value VARCHAR(100)) ENGINE=innodb ENCRYPTION='Y';
Query OK, 0 rows affected (0.02 sec)

10 行ほどデータを入れた後、UPDATEを掛けてみます。

データ投入・更新

mysql> INSERT INTO enc_test SET value='1234567890ABCDEFGHIJKLMNOPQRSTabcdefghijklmnopqrst1234567890ABCDEFGHIJKLMNOPQRSTabcdefghijklmnopqrst';
Query OK, 1 row affected (0.01 sec)
（中略）
mysql> INSERT INTO enc_test SET value='1234567890ABCDEFGHIJKLMNOPQRSTabcdefghijklmnopqrst1234567890ABCDEFGHIJKLMNOPQRSTabcdefghijklmnopqrst';
Query OK, 1 row affected (0.01 sec)

mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE enc_test SET value='ENCRYPTED' WHERE id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> COMMIT;
Query OK, 0 rows affected (0.01 sec)

ファイルシステムを検索してみます。

ファイルシステム検索

# cd /var/lib/mysql
# fgrep 'ENCRYPTED' *
grep: enc_test: Is a directory
grep: #innodb_temp: Is a directory
grep: mysql: Is a directory
grep: mysql.sock: No such device or address
grep: performance_schema: Is a directory
grep: sys: Is a directory
# fgrep 'ENCRYPTED' enc_test/*

UPDATEで上書きしたENCRYPTEDの文字はテーブルスペース・REDO ログ／UNDO ログからは見つかりませんでした。

システムテーブル暗号化

一方、DB／テーブル名として使ったenc_testを検索してみると、

ファイルシステム検索(2)

# fgrep 'enc_test' *
grep: enc_test: Is a directory
grep: #innodb_temp: Is a directory
grep: mysql: Is a directory
Binary file mysql.ibd matches
grep: mysql.sock: No such device or address
grep: performance_schema: Is a directory
grep: sys: Is a directory

mysql.ibdの中から見つかりました。

システムテーブルを暗号化します。

【参考】

• mysql System Tablespace Encryption（MySQL 8.0 Reference Manual）

システムテーブル暗号化

mysql> ALTER TABLESPACE mysql ENCRYPTION = 'Y';
Query OK, 0 rows affected (0.21 sec)

再度ファイルシステムを検索してみます。

ファイルシステム検索(3)

# fgrep 'enc_test' *
grep: enc_test: Is a directory
grep: #innodb_temp: Is a directory
grep: mysql: Is a directory
grep: mysql.sock: No such device or address
grep: performance_schema: Is a directory
grep: sys: Is a directory

見つからなくなりました。

なお、システムテーブルの暗号化を解除するときはALTER TABLESPACE mysql ENCRYPTION = 'N'とします。

テーブルのデフォルト暗号化指定

MySQL 8.0.16 からは DB（スキーマ）ごとに作成テーブルのデフォルト暗号化の ON／OFF を指定できるようになりました。

【参考】

• Defining an Encryption Default for Schemas and General Tablespaces（MySQL 8.0 Reference Manual）

DBデフォルト暗号化

mysql> CREATE SCHEMA enc_test2 DEFAULT ENCRYPTION = 'Y';
Query OK, 1 row affected (0.00 sec)

mysql> USE enc_test2;
Database changed
mysql> CREATE TABLE enc_test2 (id int(10) PRIMARY KEY AUTO_INCREMENT, value VARCHAR(100)) ENGINE=innodb;
Query OK, 0 rows affected (0.03 sec)

mysql> SHOW CREATE TABLE enc_test2\G
*************************** 1. row ***************************
       Table: enc_test2
Create Table: CREATE TABLE `enc_test2` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `value` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='Y'
1 row in set (0.01 sec)

CREATE TABLEで自動的にENCRYPTION='Y'が付きました。

また、default_table_encryptionにより、DB（スキーマ）に関わらずデフォルト暗号化の ON／OFF が指定できるようになりました。

default_table_encryption

mysql> SET default_table_encryption=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE DATABASE enc_test3;
Query OK, 1 row affected (0.01 sec)

mysql> USE enc_test3;
Database changed
mysql> CREATE TABLE enc_test3 (id int(10) PRIMARY KEY AUTO_INCREMENT, value VARCHAR(100)) ENGINE=innodb;
Query OK, 0 rows affected (0.03 sec)

mysql> SHOW CREATE TABLE enc_test3\G
*************************** 1. row ***************************
       Table: enc_test3
Create Table: CREATE TABLE `enc_test3` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `value` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='Y'
1 row in set (0.00 sec)

但し、既存の DB（スキーマ）についてのデフォルト指定より優先されたり設定が上書きされるわけではないので、その点はご注意ください。

なお、新設されたTABLE_ENCRYPTION_ADMIN権限を持たないユーザがテーブルを作成する際の挙動についてはtable_encryption_privilege_check で指定可能です。

---

2019/07/13 追記：

8.0.16 ではALTER INSTANCEによるマスターキーのローテーションにも対応しました。

13.1.5 ALTER INSTANCE Syntax（MySQL 8.0 Reference Manual）

---

• Qiitaに投稿したMySQL 8.0関連記事
• MySQL 8.0 の薄い本（無料で配布中！）