Query Rewrite Pluginは以前から提供されていましたが、8.0.12以降でSELECT以外のDMLをサポートしたとの事でしたので、念の為に挙動を確認してみます。他に対応方法が無い場合以外で、積極的に利用するケースは思い付きませんが、選択肢の一つとして認識しておいて良いかと思います。
install_rewriter.sql: Rewriter プラグインとその関連要素をインストールするには、このスクリプトを選択します。
uninstall_rewriter.sql: Rewriter プラグインとその関連要素をアンインストールするには、このスクリプトを選択します。
Previously, the Rewriter query rewrite plugin permitted rewriting only for SELECT statements. Rewritable statements now include INSERT, REPLACE, UPDATE, and DELETE as well. See The Rewriter Query Rewrite Plugin.
Plugin Notes
[root@share]# mysql -u root -p -e "SHOW GLOBAL VARIABLES LIKE 'rewriter_enabled';"
Enter password:
[root@share]# mysql -u root -p < install_rewriter.sql
Enter password:
[root@share]# mysql -u root -p -e "SHOW GLOBAL VARIABLES LIKE 'rewriter_enabled';"
Enter password:
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| rewriter_enabled | ON |
+------------------+-------+
[root@share]#Scriptの実行内容
DDL、DMLに関してはレプリケートされますが、Pluginに関してはREADER(REPLICA)に同期されないのでもしREADERがWRITERになるケースがある場合は個別にインストールしておく必要がありますね。
[root@share]# cat install_rewriter.sql
/* Copyright (c) 2015, 2022, Oracle and/or its affiliates.
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License, version 2.0,
as published by the Free Software Foundation.
This program is also distributed with certain software (including
but not limited to OpenSSL) that is licensed under separate terms,
as designated in a particular file or component or in included license
documentation. The authors of MySQL hereby grant you an additional
permission to link the program and your derivative works with the
separately licensed software that they have included with MySQL.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License, version 2.0, for more details.
You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA */
CREATE DATABASE IF NOT EXISTS query_rewrite;
CREATE TABLE IF NOT EXISTS query_rewrite.rewrite_rules (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
pattern VARCHAR(5000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
pattern_database VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
replacement VARCHAR(5000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
enabled ENUM('YES', 'NO') CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL
DEFAULT 'YES',
message VARCHAR(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
pattern_digest VARCHAR(64),
normalized_pattern VARCHAR(100)
) DEFAULT CHARSET = utf8mb4 ENGINE = INNODB;
INSTALL PLUGIN rewriter SONAME 'rewriter.so';
CREATE FUNCTION load_rewrite_rules RETURNS STRING
SONAME 'rewriter.so';
DELIMITER //
CREATE PROCEDURE query_rewrite.flush_rewrite_rules()
BEGIN
DECLARE message_text VARCHAR(100);
COMMIT;
SELECT load_rewrite_rules() INTO message_text;
IF NOT message_text IS NULL THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = message_text;
END IF;
END //
DELIMITER ;
[root@share]# INSTALLコマンドはREPLICAに反映されて無いので、INSTALLだけ念の為に別途実行しておく。
root@localhost [mysql]> select * from mysql.plugin;
+----------+-------------------+
| name | dl |
+----------+-------------------+
| mecab | libpluginmecab.so |
| rewriter | rewriter.so |
+----------+-------------------+
2 rows in set (0.00 sec)
root@localhost [mysql]> select * from func;
+--------------------+-----+-------------+----------+
| name | ret | dl | type |
+--------------------+-----+-------------+----------+
| inttime | 1 | inttime.so | function |
| load_rewrite_rules | 0 | rewriter.so | function |
+--------------------+-----+-------------+----------+
2 rows in set (0.00 sec)DDL、DMLはREPLICA側にも反映されている。
root@localhost [mysql]> show create database query_rewrite\G
*************************** 1. row ***************************
Database: query_rewrite
Create Database: CREATE DATABASE `query_rewrite` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ /*!80016 DEFAULT ENCRYPTION='N' */
1 row in set (0.00 sec)
root@localhost [mysql]> show tables from query_rewrite;
+-------------------------+
| Tables_in_query_rewrite |
+-------------------------+
| rewrite_rules |
+-------------------------+
1 row in set (0.00 sec)
root@localhost [mysql]> READER(REPLICA)側：以下のコマンドを叩いておけば良さそう。
SET sql_log_bin = OFF;
select @@global.log_bin;
INSTALL PLUGIN rewriter SONAME 'rewriter.so';
-- CREATE FUNCTION load_rewrite_rules RETURNS STRING SONAME 'rewriter.so';
SET sql_log_bin = ON;
select @@global.log_bin;基本動作確認
以前提供されていたQuery Cacheと同様に、ハッシュ値を確認してSQLを処理しているので大文字小文字等の違いも関係してきます。SQLが完全一致しないと、Rewrite処理は実施されません。なのでパッケージ等でSQLが基本的には殆ど変わらないケース等には有用な様な気がします。ここは、これまでの挙動と同じですね。
プラグインは、ルールテーブルから各ルールを読み取るときに、パターンおよびダイジェストハッシュ値から正規化された (ステートメントダイジェスト) フォームを計算し、それらを使用して normalized_pattern および pattern_digest カラムを更新します:
5.6.4.2 リライタクエリーリライトプラグインの使用
参照処理
root@localhost [POC]> select id,note,footnote,updated_time FROM POC.t1 WHERE id = 1;
+----+---------+----------+---------------------+
| id | note | footnote | updated_time |
+----+---------+----------+---------------------+
| 1 | inplace | instant | 2022-07-28 03:10:31 |
+----+---------+----------+---------------------+
1 row in set (0.00 sec)
root@localhost [POC]> INSERT INTO query_rewrite.rewrite_rules (pattern, replacement)
-> VALUES('select id,note,footnote,updated_time FROM POC.t1 WHERE id = ?', 'select id,note,footnote,date(updated_time) as updated_time FROM POC.t1 where id = ?');
Query OK, 1 row affected (0.01 sec)
root@localhost [POC]> select id,note,footnote,updated_time FROM POC.t1 WHERE id = 1;
+----+---------+----------+---------------------+
| id | note | footnote | updated_time |
+----+---------+----------+---------------------+
| 1 | inplace | instant | 2022-07-28 03:10:31 |
+----+---------+----------+---------------------+
1 row in set (0.00 sec)
root@localhost [POC]> CALL query_rewrite.flush_rewrite_rules();
Query OK, 1 row affected (0.00 sec)
root@localhost [POC]> select id,note,footnote,updated_time FROM POC.t1 WHERE id = 1;
+----+---------+----------+--------------+
| id | note | footnote | updated_time |
+----+---------+----------+--------------+
| 1 | inplace | instant | 2022-07-28 |
+----+---------+----------+--------------+
1 row in set, 1 warning (0.00 sec)
root@localhost [POC]> select id,note,footnote,updated_time FROM t1 WHERE id = 1;
+----+---------+----------+---------------------+
| id | note | footnote | updated_time |
+----+---------+----------+---------------------+
| 1 | inplace | instant | 2022-07-28 03:10:31 |
+----+---------+----------+---------------------+
1 row in set (0.00 sec)
root@localhost [POC]> 
※もし、REPLICAでも変更を反映させる場合は、CALL query_rewrite.flush_rewrite_rules();はREPLICAでも実行する必要があります。
更新処理
root@localhost [POC]> select * from POC.t1;
+----+-----------------------+------------------------------------+---------------------+
| id | note | footnote | updated_time |
+----+-----------------------+------------------------------------+---------------------+
| 1 | inplace | instant | 2022-07-28 03:10:31 |
| 2 | configure replica | MySQL 8.0 SOURCE and REPLICA | 2022-08-07 08:21:06 |
| 3 | configure replica2 | MySQL 8.0 SOURCE and REPLICA | 2022-08-07 08:27:46 |
| 4 | configure replica3 | MySQL 8.0 SET IGNORE SESSION TABLE | 2022-08-07 08:52:03 |
| 5 | Reconfirm | This is confirmation of replica | 2022-08-12 00:25:38 |
| 6 | binlog check 20221011 | confirm binlog | 2022-10-11 05:29:39 |
| 7 | binlog check 20221011 | confirm binlog shinya | 2022-10-11 05:39:45 |
| 8 | test | replication | 2022-10-17 03:58:07 |
+----+-----------------------+------------------------------------+---------------------+
8 rows in set (0.00 sec)
root@localhost [POC]> INSERT INTO query_rewrite.rewrite_rules (pattern, replacement)
-> VALUES('DELETE FROM POC.t1 WHERE id = ?','UPDATE POC.t1 SET note = "-", footnote = current_user WHERE id = ?');
Query OK, 1 row affected (0.01 sec)
root@localhost [POC]> CALL query_rewrite.flush_rewrite_rules();
Query OK, 1 row affected (0.00 sec)
root@localhost [POC]> DELETE FROM POC.t1 WHERE id = 8;
Query OK, 1 row affected, 1 warning (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 1
root@localhost [POC]> show warnings;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1105 | Query 'DELETE FROM POC.t1 WHERE id = 8' rewritten to 'UPDATE POC.t1 SET note = "-", footnote = current_user WHERE id = 8' by a query rewrite plugin |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
root@localhost [POC]> select * from POC.t1;
+----+-----------------------+------------------------------------+---------------------+
| id | note | footnote | updated_time |
+----+-----------------------+------------------------------------+---------------------+
| 1 | inplace | instant | 2022-07-28 03:10:31 |
| 2 | configure replica | MySQL 8.0 SOURCE and REPLICA | 2022-08-07 08:21:06 |
| 3 | configure replica2 | MySQL 8.0 SOURCE and REPLICA | 2022-08-07 08:27:46 |
| 4 | configure replica3 | MySQL 8.0 SET IGNORE SESSION TABLE | 2022-08-07 08:52:03 |
| 5 | Reconfirm | This is confirmation of replica | 2022-08-12 00:25:38 |
| 6 | binlog check 20221011 | confirm binlog | 2022-10-11 05:29:39 |
| 7 | binlog check 20221011 | confirm binlog shinya | 2022-10-11 05:39:45 |
| 8 | - | root@localhost | 2022-10-17 03:58:07 |
+----+-----------------------+------------------------------------+---------------------+
8 rows in set (0.00 sec)
root@localhost [POC]> 
実際に利用される場合には、マニュアルを熟読して動作検証して頂いた方が良いかと思います。
参考：
5.6.4.1 リライタのクエリーリライトプラグインのインストールまたはアンインストール
ddl_rewriter プラグイン
MySQL8.0.16からは運用向けに以下のDDL Rewriteプラグインが含まれています。
MySQL now includes a ddl_rewriter plugin that modifies CREATE TABLE statements received by the server before it parses and executes them. The plugin removes ENCRYPTION, DATA DIRECTORY, and INDEX DIRECTORY clauses, which may be helpful when restoring tables from SQL dump files created from databases that are encrypted or that have their tables stored outside the data directory. For example, the plugin may enable restoring such dump files into an unencrypted instance or in an environment where the paths outside the data directory are not accessible. When installed, ddl_rewriter exposes the Performance Schema memory/rewriter/ddl_rewriter instrument for tracking plugin memory use. For more information, see The ddl_rewriter Plugin.
Changes in MySQL 8.0.16 (2019-04-25, General Availability)
サーバーが受け取る CREATE TABLE ステートメントを解析および実行する前に変更する ddl_rewriter プラグインが含まれています。 プラグインは、ENCRYPTION、DATA DIRECTORY および INDEX DIRECTORY 句を削除します。これらは、暗号化されているか、テーブルがデータディレクトリ外に格納されているデータベースから作成された SQL ダンプファイルからテーブルをリストアする場合に役立ちます。 たとえば、このようなダンプファイルを暗号化されていないインスタンス、またはデータディレクトリ外のパスにアクセスできない環境にリストアできます。
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS, PLUGIN_TYPE FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'ddl%';
Empty set (0.00 sec)
mysql> install plugin ddl_rewriter soname 'ddl_rewriter.so';
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS, PLUGIN_TYPE FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'ddl%';
+--------------+---------------+-------------+
| PLUGIN_NAME | PLUGIN_STATUS | PLUGIN_TYPE |
+--------------+---------------+-------------+
| ddl_rewriter | ACTIVE | AUDIT |
+--------------+---------------+-------------+
1 row in set (0.01 sec)
mysql> use POC
Database changed
mysql> CREATE TABLE `T_ENCRYPTION` (
-> `id` int NOT NULL AUTO_INCREMENT COMMENT 'PGの場合はSEQUENCE',
-> `note` varchar(10) DEFAULT NULL,
-> `who` varchar(100) DEFAULT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ENCRYPTION='Y';
Query OK, 0 rows affected, 1 warning (0.04 sec)
mysql> show warnings\G
*************************** 1. row ***************************
Level: Note
Code: 1105
Message: Query 'CREATE TABLE `T_ENCRYPTION` (
`id` int NOT NULL AUTO_INCREMENT COMMENT 'PGの場合はSEQUENCE',
`note` varchar(10) DEFAULT NULL,
`who` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ENCRYPTION='Y'' rewritten to 'CREATE TABLE `T_ENCRYPTION` (
`id` int NOT NULL AUTO_INCREMENT COMMENT 'PGの場合はSEQUENCE',
`note` varchar(10) DEFAULT NULL,
`who` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=I
1 row in set (0.00 sec)
mysql> UNINSTALL PLUGIN ddl_rewriter;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show warnings\G
*************************** 1. row ***************************
Level: Warning
Code: 1620
Message: Plugin is busy and will be uninstalled on shutdown
1 row in set (0.00 sec)
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS, PLUGIN_TYPE FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'ddl%';
+--------------+---------------+-------------+
| PLUGIN_NAME | PLUGIN_STATUS | PLUGIN_TYPE |
+--------------+---------------+-------------+
| ddl_rewriter | DELETED | AUDIT |
+--------------+---------------+-------------+
1 row in set (0.00 sec)
mysql>