Help us understand the problem. What is going on with this article?

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

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

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

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

【関連記事】

【参考】

設定

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

なお、ダブルライトバッファには平文で記録されてしまうようですので、innodb_doublewrite=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> 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 を指定できるようになりました。

【参考】

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)


Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away