暂无图片
暂无图片
2
暂无图片
暂无图片
暂无图片
首页 / MySQL Rewrite Plugin插件

MySQL Rewrite Plugin插件

原创 CuiHulong 2022-10-20
1301

MySQL5.7版本开始就支持语句重写插件，可以在执行语句之前，服务层检查并修改接收到的SQL语句。
Query Rewrite插件通过解析每个SQL语句并将其解析树传递，遍历。并将原始树构造一个不同的树并返回给服务层。虽然很少场景下使用Rewrite插件，但还是有一定的补助作用，解决一些极端问题。目前8.0高版本支持2种类型的重写插件。

MySQL Query Rewrite Plugin

对于Query Rewriter插件比较很好理解，就是对DML语句进行重写，插件会检查语句，并基于其内存中的重写规则缓存重写。之后提交给后端进行处理。
目前支持的重写如下:

  • 从MySQL 8.0.12支持: SELECT, INSERT, REPLACE, UPDATE 和 DELETE语句；
  • 在MySQL 8.0.12之前: 仅支持SELECT语句；
  • 独立语句 和 prepared准备语句都可能被重写；
  • 视图 或 存储过程 中的语句不会被重写；

插件原理

Query Rewriter插件使用query_rewrite的数据库，其中包含rewrite_rules的表。这个表为插件用来决定是否重写语句的规则提供持久存储。用户通过修改存储在这个表中的规则集 与插件通信。插件通过设置rules表信息与服务层进行通信。

query_rewrite数据库包含flush_rewrite_rules()的存储过程，它将规则表的内容加载到插件中。
flush_rewrite_rules()存储过程使用load_rewrite_rules()的可加载函数。

Rewriter插件配置的系统变量和运行的状态变量，了解插件情况。在MySQL 8.0.31及以后的版本中，这个插件还支持一个特权(SKIP_QUERY_REWRITE)来保护给定用户的查询不被重写。

插件脚本

对于插件的使用方式，官方提供的安装软件share目录下存在Query Rewriter插件SQL脚本。
SQL脚本有5部分内容组合：库，表，插件加载，函数，存储过程。

1.脚本内容：

#mysql8.0/share/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.
   。。。
   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 ;

2.加载插件：

#1.直接导入提供的sql语句
shell# mysql -uroot -p < /opt/idc/mysql8.0/share/install_rewriter.sql

#my.cnf自动配置插件加载
[mysqld]
rewriter_enabled=ON


#2.动态设置
SET GLOBAL rewriter_enabled = ON;
SET GLOBAL rewriter_enabled = OFF;

#3.查看插件状态，sql导入情况 等
mysql> SHOW GLOBAL VARIABLES LIKE 'rewriter_enabled';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| rewriter_enabled | ON    |
+------------------+-------+
1 row in set (0.01 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| query_rewrite      |
| sys                |
+--------------------+
7 rows in set (0.01 sec)

mysql> show create table query_rewrite.rewrite_rules;
+---------------+----------------------------------------------------------------------------+
| Table         | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
+---------------+----------------------------------------------------------------------------+
| rewrite_rules | CREATE TABLE `rewrite_rules` (
  `id` int NOT NULL AUTO_INCREMENT,
  `pattern` varchar(5000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `pattern_database` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `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 DEFAULT NULL,
  `pattern_digest` varchar(64) DEFAULT NULL,
  `normalized_pattern` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 
+---------------+----------------------------------------------------------------------------+
1 row in set (0.00 sec)

插件实践

通过设置规则，替换sql语句实现。对应的变量用“?”进行替换，可通过warnings查看替换信息。

例1：SELECT计算

#1.设置规则
mysql> INSERT INTO query_rewrite.rewrite_rules (pattern, replacement)
       VALUES('SELECT ?', 'SELECT ? + 1');
Query OK, 1 row affected (0.00 sec)

#2.规则加载
mysql> CALL query_rewrite.flush_rewrite_rules();
Query OK, 1 row affected (0.00 sec)

#3.检查是否发生重写:
mysql> SELECT 10;
+--------+
| 10 + 1 |
+--------+
|     11 |
+--------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1105
Message: Query 'SELECT 10' rewritten to 'SELECT 10 + 1' by a query rewrite plugin
1 row in set (0.00 sec)

例2：INSERT替换VALUES

#1.创建测试表
mysql>CREATE TABLE `t1` (
  `id` int NOT NULL,
  `name` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
  `age` int DEFAULT '0',
 `create_time` datetime DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)

#2.创建insert 替换规则：新数据age 都是50
mysql> INSERT INTO query_rewrite.rewrite_rules (pattern, pattern_database,replacement) 
VALUES('INSERT INTO t1(id,name,age) VALUES(?,?,?)','db2','INSERT INTO t1(id,name,age) VALUES(?,?,50)' );
Query OK, 1 row affected (0.00 sec)

#3.规则加载
```sql
mysql> CALL query_rewrite.flush_rewrite_rules();
Query OK, 1 row affected (0.00 sec)

#4.插入数据
mysql> insert into t1(id,name,age) values(1,"C",100);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql>show warnings;
+-------+------+-------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                     |
+-------+------+-------------------------------------------------------------------------------+
| Note  | 1105 | Query 'insert into t1(id,name,age) values(1,"C",100)' 
      rewritten to 'INSERT INTO t1(id,name,age) VALUES(1,'C',50)' by a query rewrite plugin |
+-------+------+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)

#5.验证
mysql>select *from t1;
+----+------+------+---------------------+
| id | name | age  | create_time         |
+----+------+------+---------------------+
|  1 | C    |   50 | 2022-10-20 12:04:37 |
+----+------+------+---------------------+
1 row in set (0.00 sec)

例3：INSERT替换 DELETE语句

#1.insert 替换delete 规则
mysql> INSERT INTO query_rewrite.rewrite_rules (pattern, pattern_database,replacement) 
    -> VALUES('INSERT INTO t1(id,name,age) VALUES(?,?,?)','db2','DELETE FROM t1 WHERE id=? ' );
Query OK, 1 row affected (0.00 sec)

#2.加载规则
mysql> CALL query_rewrite.flush_rewrite_rules();
Query OK, 1 row affected (0.01 sec)

#3.查看初始化数据
mysql> select *from t1;
+----+------+------+---------------------+
| id | name | age  | create_time         |
+----+------+------+---------------------+
|  1 | C    |   50 | 2022-10-20 12:04:37 |
+----+------+------+---------------------+
1 row in set (0.00 sec)

#4.插入数据
mysql> insert into t1(id,name,age) values(1,"C",100);
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                  |
+-------+------+--------------------------------------------------------------------------------------------------------------------------+
| Note  | 1105 | Query 'insert into t1(id,name,age) values(1,"C",100)' 
                 rewritten to 'DELETE FROM t1 WHERE id=1' by a query rewrite plugin |
+-------+------+--------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

#5.验证
mysql> select *from t1;
Empty set (0.00 sec)

参数和状态

插件必不可少参数和状态，便于设置和监控。
参数：

status 说明
rewriter_enabled 是否启用Rewriter查询重写插件。
rewriter_enabled_for_threads_without_privilege_checks 特权检查的情况下执行的复制线程应用重写。

状态：

status 说明
Rewriter_number_loaded_rules 从rewrite_rules表中成功加载到内存供Rewriter插件使用的重写插件重写规则的数量。
Rewriter_number_reloads rewrite_rules表被加载到Rewriter插件使用的内存缓存中的次数。
Rewriter_number_rewritten_queries 自Rewriter查询重写插件加载以来，由它重写的查询数。
Rewriter_reload_error rewrite_rules表最近一次被加载到Rewriter插件使用的内存缓存中时是否发生了错误

DDL Rwriter Plugin

MySQL 8.0.16及更高版本包括ddl_rewriter插件，它在解析和执行服务器收到的CREATE TABLE语句之前修改。该插件只修改CREATE TABLE语句。删除语句中的ENCRYPTION、DATA DIRECTORY和INDEX DIRECTORY子句。

插件用处：

  • 从加密的数据库创建的SQL转储文件中恢复表；
  • 不可访问的环境中，表存储在数据目录之外；

条件重写

  • ddl_rewriter只考虑CREATE TABLE语句，并且只有出现在输入行开头或准备好的语句文本开头的独立语句时才会考虑；
  • ddl_rewriter不考虑存储程过程中的CREATE TABLE语句；
  • 在考虑重写的语句中，以下子句的实例被重写，每个实例被一个空格替换:
    1.ENCRYPTION加密。
    2.DATA DIRECTORY(在表和分区级别): Innodb引擎，MyISAM引擎。
    3.INDEX DIRECTORY(在表和分区级别)：MyISAM引擎。
  • 重写不依赖于字母大小写。

插件实践

#1.启动方式
#my.cnf配置文件
[mysqld]
ddl-rewriter=ON

#命令行
mysql> INSTALL PLUGIN ddl_rewriter SONAME 'ddl_rewriter.so';
Query OK, 0 rows affected (0.01 sec)

#2.查看插件是否启动
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS, PLUGIN_TYPE        FROM INFORMATION_SCHEMA.PLUGINS        WHERE PLUGIN_NAME LIKE '%rewrite%';
+--------------+---------------+-------------+
| PLUGIN_NAME  | PLUGIN_STATUS | PLUGIN_TYPE |
+--------------+---------------+-------------+
| Rewriter     | ACTIVE        | AUDIT       |
| ddl_rewriter | ACTIVE        | AUDIT       |
+--------------+---------------+-------------+
2 rows in set (0.01 sec)

#3.卸载插件
#命令行
mysql>UNINSTALL PLUGIN ddl_rewriter;

#my.cnf配置文件
[mysqld]
ddl-rewriter=OFF

DATA DIRECTORY

mysql> CREATE TABLE t (i INT) DATA DIRECTORY '/var/mysql/data';
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> SHOW WARNINGS;
+-------+------+----------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                          |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1105 | Query 'CREATE TABLE t (i INT) DATA DIRECTORY '/var/mysql/data'' 
                 rewritten to 'CREATE TABLE t (i INT) ' by a query rewrite plugin |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

ENCRYPTION

mysql> CREATE TABLE t1 (i INT)  ENCRYPTION='Y';
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> show warnings;
+-------+------+-------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                           |
+-------+------+-------------------------------------------------------------------------------------------------------------------+
| Note  | 1105 | Query 'CREATE TABLE t1 (i INT)  ENCRYPTION='Y'' 
                 rewritten to 'CREATE TABLE t1 (i INT) ' by a query rewrite plugin |
+-------+------+-------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

INDEX DIRECTORY

mysql> CREATE TABLE t2 (i INT)  INDEX DIRECTORY '/var/mysql/data';
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                              |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1105 | Query 'CREATE TABLE t2 (i INT)  INDEX DIRECTORY '/var/mysql/data'' 
                 rewritten to 'CREATE TABLE t2 (i INT) ' by a query rewrite plugin |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

总结

对于Query Rewrite Plugin来说，使用插件快速改写SQL，消除慢查，恢复数据库服务，是个不错的选择。当然触发器也可以做到一定的功能，但触发器是没法对SELECT语句进行控制。

  • 整体开启query rewrite plugin因为是语法树解析替换，对数据库整体影响非常小的，可以忽略不计。
  • 使用场景如下：
    1.在加索引无法解决；
    2.加索引时间过长；
    3无法停止业务；
    4.简单替换SQL语句；
    5.修改代码重新发布时间过长等情况；

对于DDL Rewrite Plugin只针对CREATE TABLE加密表。

墨力计划mysql
最后修改时间：2022-12-27 13:27:05
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

文章被以下合辑收录

专家专栏 | 崔虎龙（共172篇）
云和恩墨开源MySQL技术顾问，长期服务于数据中心（银行，金融、游戏、物流）行业，熟悉数据中心运营管理的流程及规范、自动化运维等方面。擅长MySQL，Redis，MongoDB数据库高可用设计和运维故障处理、备份恢复、升级迁移、性能优化。自我学习通过了MySQL OCP5.6 和 MySQL OCP5.7认证证书。

评论