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

以前 **[MySQL 8.0.4 RCでテーブルスペース暗号化＋REDOログ／UNDOログ暗号化](https://qiita.com/hmatsu47/items/1c6252c9a4b98dc936b1)** という記事を書きましたが、

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

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

**【関連記事】**

 - **[MySQL 8.0.13 で一般テーブルスペース（General Tablespace）を暗号化する](https://qiita.com/hmatsu47/items/f3519532c134ba0018af)**
 - **[MySQL 8.0.14 でバイナリログ・リレーログを暗号化する](https://qiita.com/hmatsu47/items/96980d508f79f9510aa2)**

**【参考】**

 - **[15.6.3.9 InnoDB Data-at-Rest Encryption](https://dev.mysql.com/doc/refman/8.0/en/innodb-tablespace-encryption.html)（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 のダブルライトバッファがどのバージョンで暗号化されるようになったのか調べた](https://qiita.com/hmatsu47/items/3ee2e3c695733124e498)**

---

```text: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`を再起動するとキーリングファイルが生成されます。

```bash:再起動
# 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'`を指定）。

```sql:テーブル暗号化
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`を掛けてみます。

```sql:データ投入・更新
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)
```

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

```bash:ファイルシステム検索
# 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`を検索してみると、

```bash:ファイルシステム検索(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](https://dev.mysql.com/doc/refman/8.0/en/innodb-tablespace-encryption.html#innodb-mysql-tablespace-encryption-enabling-disabling)（MySQL 8.0 Reference Manual）**

```sql:システムテーブル暗号化
mysql> ALTER TABLESPACE mysql ENCRYPTION = 'Y';
Query OK, 0 rows affected (0.21 sec)
```

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

```bash:ファイルシステム検索(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](https://dev.mysql.com/doc/refman/8.0/en/innodb-tablespace-encryption.html#innodb-schema-tablespace-encryption-default)（MySQL 8.0 Reference Manual）**

```sql: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 が指定できるようになりました。

```sql: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](https://dev.mysql.com/doc/refman/8.0/en/alter-instance.html)（MySQL 8.0 Reference Manual）**

---

 - **[Qiitaに投稿したMySQL 8.0関連記事](https://hmatsu47.hatenablog.com/qiita_mysql80)**
 - **[MySQL 8.0 の薄い本](https://github.com/hmatsu47/mysql80_no_usui_hon)（無料で配布中！）**