1.DDL重写插件安装

mysql [localhost:8022] {root} ((none)) > INSTALL PLUGIN ddl_rewriter SONAME ‘ddl_rewriter.so’;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost:8022] {root} ((none)) > 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.00 sec)

2.使用测试

mysql [localhost:8022] {root} (sbtest) > CREATE TABLE t (i INT) DATA DIRECTORY ‘/data/mysql_data’;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql [localhost:8022] {root} (sbtest) > SHOW WARNINGS\G
*************************** 1. row ***************************
Level: Note
Code: 1105
Message: Query ‘CREATE TABLE t (i INT) DATA DIRECTORY ‘/data/mysql_data’’ rewritten to 'CREATE TABLE t (i INT) ’ by a query rewrite plugin
1 row in set (0.00 sec)

3.解析binlog
发现binlog中的语句记录的是改写后的语句

/root/opt/mysql/8.0.22/bin/mysqlbinlog --base64-output=‘decode-rows’ -vv binlog.000008
/!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1/;
/!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0/;
DELIMITER /!/;

at 4

#210629 17:23:37 server id 1 end_log_pos 125 CRC32 0xfda96cbf Start: binlog v 4, server v 8.0.22 created 210629 17:23:37 at startup

Warning: this binlog is either in use or was not closed properly.

ROLLBACK/!/;

at 125

#210629 17:23:37 server id 1 end_log_pos 156 CRC32 0xe89078d4 Previous-GTIDs

[empty]

at 156

#210629 17:27:43 server id 1 end_log_pos 233 CRC32 0x2ec35945 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no original_committed_timestamp=1624958863679202 immediate_commit_timestamp=1624958863679202 transaction_length=191

original_commit_timestamp=1624958863679202 (2021-06-29 17:27:43.679202 CST)

immediate_commit_timestamp=1624958863679202 (2021-06-29 17:27:43.679202 CST)

/!80001 SET @@session.original_commit_timestamp=1624958863679202//!/;
/!80014 SET @@session.original_server_version=80022//!/;
/!80014 SET @@session.immediate_server_version=80022//!/;
SET @@SESSION.GTID_NEXT= ‘ANONYMOUS’/!/;

at 233

#210629 17:27:43 server id 1 end_log_pos 347 CRC32 0x81d67cc7 Query thread_id=8 exec_time=0 error_code=0 Xid = 17
use sbtest/!/;
SET TIMESTAMP=1624958863/!/;
SET @@session.pseudo_thread_id=8/!/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/!/;
SET @@session.sql_mode=1168113696/!/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/!/;
/!\C utf8mb4 //!/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/!/;
SET @@session.lc_time_names=0/!/;
SET @@session.collation_database=DEFAULT/!/;
/!80011 SET @@session.default_collation_for_utf8mb4=255//!/;
/!80013 SET @@session.sql_require_primary_key=0//!/;
CREATE TABLE t (i INT)
/!/;
SET @@SESSION.GTID_NEXT= ‘AUTOMATIC’ /* added by mysqlbinlog / /!*/;
DELIMITER ;

End of log file

/!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE/;
/!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0/;

4.DDL应用场景

MySQL 8.0.16及更高版本包含了一个ddl_rewriter插件，该插件在服务器解析和执行CREATE TABLE语句之前修改它们。该插件删除加密，data DIRECTORY子句和INDEX DIRECTORY子句，当从数据库创建的SQL转储文件恢复表时，这些文件可能很有用。目前只对create table 改写，在存储过程中的create table语名不改写。
该插件可以加密转储文件恢复到未加密的实例中，或者将数据目录之外路径不可访问的表恢复。