はじめに
MySQL には、Query Rewrite プラグイン を使用することで、実行されるクエリを事前に定義した条件に合致した場合に、書き換えをして実行するという事が可能となっています。
この機能は、MySQL 5.7 から存在し、SELECTステートメントのみが対象となっていました。
その後、MySQL 8.0.12 で、INSERT、REPLACE、UPDATE、DELETEステートメントも対象となるように改善されています。
これについては、過去の弊社ブログにて紹介しておりますので、是非、一読頂ければと思います。
今回は、MySQL8.0.16 で追加された DDL の書き換えを行う ddl_rewriter プラグイン について紹介したいと思います。
リリースノートは以下となります。
ユースケース
「DDLをわざわざ実行したクエリと異なるクエリに書き換えて実行したいケースってどんな時？」って疑問を持つ方もおられるかもしれません。
ですが、こちらの機能は、Query Rewrite プラグインのように、明示的に「このクエリを、こんなクエリに書き換える」と定義して使用するような機能ではありません。
こちらのリファレンスに記載されていますが、簡単に書くと CREATE TABLE の 以下のオプション指定を半角スペースに置き換える機能となります。
- ENCRYPTION
- DATA DIRECTORY
- INDEX DIRECTORY
この書き換えを行うメリットが何なのかというと、SQLベースのダンプファイルを別の環境にリストアする際に、以下の問題を解消することができます。
暗号化したテーブルを、Keyring を使用していない環境にリストアしたい
テーブルを暗号化する為には、Keyring コンポーネント もしくは、Keyring プラグイン が必要となります。
なので、SQLベースのダンプファイルを取得した環境でテーブルを暗号化していたとしても、リストアする環境が、Keyring を使用していなければ、リストア時にエラーとなります。
MySQL の Keyring については、以下の弊社ブログ記事でも紹介しております。
データディレクトリ(datadir)外に保存されているテーブルスペースファイルのディレクトリが存在しない環境でリストアしたい
MySQL では datadir で指定したディレクトリ内に、テーブルスペースファイル等を保存しますが、ディスクI/Oの負荷を分散する等の目的により、テーブル単位で個別に格納するディレクトリパスを指定することができます。
詳細については、以下のリファレンスをご参照下さい。
また、以下の弊社ブログ記事でも紹介しております。
こちらで指定した個別のディレクトリパスが、リストアする環境では、アクセスできないもしくはディレクトリが存在しなかった場合は、リストア時にエラーとなります。
検証
Oracle Linux 8.10 に、執筆時点で LTS の最新バージョンとなる MySQL 8.4.2 をインストールして、実際の動作を確認してみました。
まずは、 Keyring コンポーネント をインストールした環境で、blog1 データベースに暗号化したテーブルを作成します。
暗号化テーブルの作成
|
1 2 3 |
mysql> CREATE DATABASE blog1; mysql> USE blog1; mysql> CREATE TABLE t1(id INT, PRIMARY KEY(id)) ENCRYPTION='Y'; |
テーブル定義を確認すると、以下のように ENCRYPTION='Y' となっており、テーブルが暗号化されていることが分かります。
|
1 2 3 4 5 6 7 8 |
mysql> SHOW CREATE TABLE t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE 't1' ( 'id' int NOT NULL, PRIMARY KEY ('id') ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='Y' 1 row in set (0.01 sec) |
DATA DIRECTORYを指定したテーブルの作成
事前に DATA DIRECTORY に指定するディレクトリを作成します。
|
1 2 3 |
# mkdir -p /data/mysql # chown -R mysql. /data # chmod -R 740 /data |
blog2 データベースにデータディレクトリ外に保存するテーブルを作成します。
|
1 2 3 |
mysql> CREATE DATABASE blog2; mysql> USE blog2; mysql> CREATE TABLE t1 (id INT, PRIMARY KEY(id)) DATA DIRECTORY = '/data/mysql'; |
こちらもテーブル定義を確認し、DATA DIRECTORY の指定があることを確認します。
|
1 2 3 4 5 6 7 |
mysql> SHOW CREATE TABLE t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE 't1' ( 'id' int NOT NULL, PRIMARY KEY ('id') ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='/data/mysql/' |
ダンプファイルの取得、リストア
mysqldump で、それぞれのデータベースのダンプファイルを取得し、
|
1 2 |
$ mysqldump -u root -p blog1 > blog1.sql $ mysqldump -u root -p blog2 > blog2.sql |
特にプラグインのインストールやデフォルト設定から変更していない別のサーバでインポートすると、それぞれ以下のようにエラーとなります。
|
1 2 3 |
$ mysql -u root -p blog1 < blog1.sql Enter password: ERROR 3185 (HY000) at line 25: Can't find master key from keyring, please check in the server log if a keyring is loaded and initialized successfully. |
|
1 2 3 |
$ mysql -u root -p blog2 < blog2.sql Enter password: ERROR 3121 (HY000) at line 25: The DATA DIRECTORY location must be in a known directory. |
blog1 の方は、Keyring コンポーネントも Keyring プラグインも使用していない環境にリストアした為、エラーとなります。
blog2 の方は、DATA DIRECTORY に指定したディレクトリが、リストアした環境に存在しない為にエラーとなっています。
このような問題を回避する為に、ddl_rewriter プラグインが導入されたと言えます。
ddl_rewriter プラグインをインストールして確認
それでは、リストアするサーバで、ddl_rewriter プラグインをインストールして確認してみます。
|
1 |
mysql> INSTALL PLUGIN ddl_rewriter SONAME 'ddl_rewriter.so'; |
※アンインストールする場合は UNINSTALL PLUGIN ddl_rewriter; を実行します。
プラグインをインストールした後、再度、同じダンプファイルをリストアしてみます。
|
1 2 3 |
$ mysql -u root -p blog1 < blog1.sql Enter password: $ |
|
1 2 3 |
$ mysql -u root -p blog2 < blog2.sql Enter password: $ |
今度はエラーなくリストアが終了しました。
リストアしたテーブル定義を確認してみると、ENCRYPTION 及び DATA DIRECTORY オプションが削除されていることが確認できます。
|
1 2 3 4 5 6 7 |
mysql> SHOW CREATE TABLE blog1.t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE 't1' ( 'id' int NOT NULL, PRIMARY KEY ('id') ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
|
1 2 3 4 5 6 7 |
mysql> SHOW CREATE TABLE blog2.t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE 't1' ( 'id' int NOT NULL, PRIMARY KEY ('id') ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
バイナリログのイベント上も、オプションが削除されています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
mysql> SHOW BINLOG EVENTS IN 'binlog.000003'\G ・・・(省略) *************************** 14. row *************************** Log_name: binlog.000003 Pos: 1234 Event_type: Query Server_id: 1 End_log_pos: 1452 Info: use 'blog1'; CREATE TABLE 't1' ( 'id' int NOT NULL, PRIMARY KEY ('id') ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /* xid=125 */ ・・・(省略) *************************** 22. row *************************** Log_name: binlog.000003 Pos: 2153 Event_type: Query Server_id: 1 End_log_pos: 2371 Info: use 'blog2'; CREATE TABLE 't1' ( 'id' int NOT NULL, PRIMARY KEY ('id') ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /* xid=167 */ ・・・(省略) |
このように、バックアップ元とリストア先で同じ設定でない環境でもリストア自体は問題なく行えるようになります。
ただ、リストア環境と全く同じ構成でリストアしたい場合には、暗号化しているはずのテーブルが暗号化されていない等といった状況が発生する為、ご使用の際は注意が必要です。
また、mysqldump で確認した手順で記載しましたが、昨今、パラレル実行が可能なことから、よく使用される MySQL Shell ユーティリティ でダンプ→リストアするようなユーティリティに関しても、テーブル定義自体は、SQLベースで行われるので、同じように作用します。
適用されないケース
ddl_rewriter プラグインが仕事をしてくれるのは冒頭にも記載しておりますが、 CREATE TABLE のみです。なおかつ、CREATE TABLE で始まる必要があります。
なので、CREATE /* test */ TABLE や ALTER TABLE t1 ENCRYPTION='Y'; とした場合は、オプションがそのまま適用されてエラーとなります。
また、データベースレベルで ENCRYPTION='Y' を指定されている場合は、特に CREATE TABLEで ENCRYPTION='Y'; を指定しなくても、内部的に同オプションを付与された形で実行されますが、これもエラーとなりますので注意が必要です。
加えて、ストアドプロシージャ内で実行される CREATE TABLE でも書き換えは行われません。
例えばレプリケーション環境で、レプリカに ddl_rewriter プラグイン を使用すれば、何かしらの問題に対処できるのでは？等と思われたとしても、上記のことから使用を推奨致しません。
あくまでも、SQLベースのダンプファイルをインポートする際に、元の構成を維持する必要がない場合にのみ使用できるものと考えた方が良いでしょう。
まとめ
ddl_rewriter プラグイン について機能を確認しましたが、仕様をちゃんと理解していないと、以下のような問題が発生しえるのではないかと思います。
- テーブルを暗号化しているつもりだったのが暗号化されていない
- 別ディレクトリにテーブルスペースファイルを配置しているはずが、データディレクトリに他テーブルと同じように配置されている
- プラグインの導入によってエラーにならないであろうと思ったステートメントがエラーになる
とはいえ、この機能は以下の Worklog の実装に相当し、少なからずニーズはあるのではないかと思いますので、ご使用の際は、よく検証を行った上でご使用頂ければと思います。
