Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
Help us understand the problem. What is going on with this article?

MySQL 8.0.4 RCでテーブルスペース暗号化+REDOログ/UNDOログ暗号化

More than 1 year has passed since last update.

2019/04/30追記:
MySQL 8.0.16 をベースに記事を書き直しました。


2018/04/30追記:
MySQL 8.0.11 GAで動作確認を行いました。なお、8.0.4 RCからのデータコンバートによる移行はできませんのでご注意ください(アップデート前にダンプ→アップデートしてデータ初期化→リストア)。


2018/02/18追記:
MySQL 8.0.4 RCに合わせて、タイトルを含めて加筆修正しました(取り消し線での訂正をやめました)。
Deprecatedになった「innodb_undo_tablespaces」をコメントアウトして動作することを確認しました。
また、一部ログ出力や「mysql_secure_installation」の表示内容が変更されたため、本文中の表記を修正しました。
※過去のバージョンから暗号化したままアップグレードできない点は8.0.3と同じです。


2017/09/29追記:
MySQL 8.0.3 RCにて、MySQL 8.0.2 DMRと同じ設定で動作することを確認しました。

但し、MySQL 8.0.2 DMR以前のデータを入れたままMySQL 8.0.3 RCにyum updateすると移行に失敗しデータが破損するため、設定ファイルのバックアップとデータDUMPを先に行い、yum update後の起動が失敗した時点で

  • データディレクトリ
  • UNDOログディレクトリ

の内容をクリアしてから、起動し直してDUMPデータのリストアを行ってください。


2017/08/07追記:
MySQL 8.0.2 DMRに合わせて、一部追記・変更を行いました。


先日まで、「Not Yet Released」だったMySQL 8.0.1(DMR)がリリースされました。
※長らく8.0.2も同じ状態だったので、てっきり8.0.3以降がリリースされるものと思っていましたが、予想と違う展開でした。

MySQL 8.0.1では、テーブルスペースの暗号化に加えて、REDOログ/UNDOログの暗号化もサポートされたとのことなので、試してみました。
※その後、8.0.4 RCに合わせて内容を書き直しています。

15.7.11 InnoDB Tablespace Encryption
 ⇒ページ中ほどにREDOログ/UNDOログ暗号化についての説明があります。
A.15 MySQL 8.0 FAQ: InnoDB Tablespace Encryption
 ⇒FAQです。
15.4.6 Doublewrite Buffer
 ⇒暗号化とは直接関係ありませんが、後で言及します。

※MySQL 5.7のテーブルスペース暗号化の記事はこちらです。
MySQL 5.7の透過的データ暗号化

1. MySQL 8.0のインストールと初期設定

テーブルスペースの暗号化については、基本的にはMySQL 5.7の頃と変わりません。
MySQL 8.0では、UNDOログ・REDOログの暗号化機能が追加されています。

MySQL8インストールと設定
# yum install https://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm
# vi /etc/yum.repos.d/mysql-community.repo
(以下の部分を書き換え)

[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/$basearch/
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql80-community]
name=MySQL 8.0 Community Server
baseurl=http://repo.mysql.com/yum/mysql-8.0-community/el/7/$basearch/
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

(保存)
:wq

# yum install mysql-community-server
(yum installの経過は省略)

# vi /etc/my.cnf
(以下を[mysqld]セクションに追記)

innodb_file_per_table=1
early-plugin-load=keyring_file.so
keyring_file_data=/var/lib/mysql-keyring/keyring

innodb_doublewrite=0
innodb_undo_directory=/var/lib/mysql-undo
# innodb_undo_logs=35 ※MySQL 8.0.2で無効化
innodb_rollback_segments=35
# innodb_undo_tablespaces=2 ※MySQL 8.0.4でDepreceted
innodb_redo_log_encrypt=1
innodb_undo_log_encrypt=1

(保存)
:wq

# mkdir /var/lib/mysql-undo
# chown mysql.mysql /var/lib/mysql-undo

最初の3行はMySQL 5.7と同様ですが、キーリングファイルの格納場所を(デフォルトで作られる)/var/lib/mysql-keyring/内に変更してみました。
但し、yum updateの際にディレクトリの更新やパーミッション書き換え等があると不具合が生じるため、以前の例のように別のディレクトリを作るのもいいかもしれません。

「innodb_doublewrite=0」については後述します(ダブルライトバッファ無効化)。
「innodb_undo_directory」で独立したUNDOログスペースの置き場所を指定します。
また、この例では「innodb_rollback_segments」「35」にしています。

その続きの2行が、REDOログとUNDOログの暗号化の指定です(デフォルトはいずれも「0」で、暗号化しない)。

初期化
# systemctl start mysqld.service
# fgrep 'password' /var/log/mysqld.log

2018-02-18T13:15:32.656204Z 5 [Note] [MY-010454] A temporary password is generated for root@localhost: 【初期パスワードが表示される】

# mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root:【初期パスワードを入力】

The existing password for the user account root has expired. Please set a new password.

New password:【新しいパスワードを入力】

Re-enter new password:【新しいパスワードを再入力】

VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?

Press y|Y for Yes, any other key for No: 【「n」を入力】
Using existing password for root.
Change the password for root ? ((Press y|Y for Yes, any other key for No) : 【「n」を入力】

 ... skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : 【「y」または「n」を入力】
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : 【「y」を入力】
Success.

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : 【「y」を入力】
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : 【「y」を入力】
Success.

All done!

# ls -l /var/lib/mysql-keyring/
total 4
-rw-r-----. 1 mysql mysql 699 Feb 18 13:15 keyring
# ls -l /var/lib/mysql-undo/
total 20480
-rw-r-----. 1 mysql mysql 10485760 Feb 18 13:25 undo_001
-rw-r-----. 1 mysql mysql 10485760 Feb 18 13:25 undo_002

2. 暗号化テーブルを作成してみる

こちらも基本的にはMySQL 5.7の頃と変わりません。

テーブル作成とデータINSERT
mysql> CREATE DATABASE sample_db;
Query OK, 1 row affected (0.00 sec)

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

mysql> INSERT INTO sample_db.sample_tbl SET value='1234567890ABCDEFGHIJKLMNOPQRSTabcdefghijklmnopqrst1234567890ABCDEFGHIJKLMNOPQRSTabcdefghijklmnopqrst';
Query OK, 1 rows affected (0.00 sec)
(複数行入れます)

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

mysql> UPDATE sample_db.sample_tbl SET value='hogefuga' WHERE id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

この状態で、

  • システムテーブルスペース(ibdata1)
  • 一時テーブルスペース(ibtmp1)
  • 分離UNDOテーブルスペース(指定ディレクトリ内のundo0xx)
  • REDOログスペース(ib_logfile0など)
  • テーブルスペース(sample_db/sample_tbl.ibd)

に対して「1234567890」「hogefuga」などで検索を掛けてみると…当初は、システムテーブルスペースだけから検出されました。

色々考えてみた結果、途中で設定ミスして起動しなかったときに「ダブルライトバッファが云々」というメッセージが出ていたことを思い出し、MySQL 8.0でのデフォルトを確認してみたところ(特定の環境を検出した場合を除き)「有効」であることが分かりました。
1. で示した通り、無効化してみたところ、検索に掛からなくなりました。

3. 速度を比較してみる

  • 暗号化なし
  • テーブルスペースのみ暗号化
  • テーブルスペース+REDOログ+UNDOログ暗号化

の3パターンについて、

  • バイナリログあり
  • バイナリログなし

のそれぞれで比較してみました(設定は先ほどの内容=ほぼ最小限の構成で。バイナリログありの場合はその設定を追記)。
実行環境はAWS EC2(t2.nano)のAmazon Linux 64ビット版です。

当然の話ですが、ベンチマーク結果は環境と設定、ワークロードに左右されますので、参考程度に見てください。

主キーを使ったSELECT(バッファプール暖機なし、先のテーブル50,000行×10テーブル分)については、暗号化の有無の差はほとんど誤差の範囲でした。
また、INSERT(先のテーブル50,000行)についても、(意外ですが)バイナリログありの場合は同様に誤差の範囲でした。
バイナリログなしの場合、テーブルスペース+REDOログ+UNDOログも暗号化のときだけ、他と比べて9~10%遅くなりました。

4. その他

システムテーブルスペースに保存されているメタデータ等や、バイナリログは暗号化されません。
最終的には、これらの点が導入上のネックになりそうです(いずれ何らかの対策が入りそう)。

また、メモリ上のデータも暗号化されません。

REDOログ/UNDOログの暗号化は、運用中にON/OFFすることができますが(UNDOログの分離だけは最初にしておく必要あり⇒MySQL 8.0.3 RCでは標準で分離する形に変更)、OFFにしている間に記録されたログは暗号化されません(ONにしている間に記録された内容が、OFFに変更されたときに自動的に平文に戻ることもないようです)。
それに関連して、一旦REDOログの暗号化をONにした場合は、キーリングの設定を無効化してはいけないようです(おそらくUNDOログも同様)。

あと、mysqldの動作ログ(/var/log/mysqld.log)の出力が不安定?なようで、「REDO(UNDO)ログの暗号化が完了した」旨の行が記録されたりされなかったり(されなくても暗号化されている)、そもそもmysqld起動から停止までのログが完全に抜け落ちたりしています(出なくなったのが正しい?)。


hmatsu47
名古屋で士業向けWebサービスのインフラ構築管理、たまにアプリケーション開発をやっています。 業務利用しているもの、個人研究など、気長にのんびり投稿していきます。ニッチ狙いが多めです。 IPA RISS(001158)・NW・DB/日商・大商2級コレクター?(簿記・ビジネス法務・ビジネス会計)。
https://hmatsu47.hatenablog.com/
infra-workshop
インフラ技術を勉強したい人たちのためのオンライン勉強会です
https://wp.infra-workshop.tech/
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