Query Rewrite Plugin 安装与使用

适用范围

设置SQL语句匹配规则，REWRITE PLUGIN 自动改写匹配的语句

MySQL8.0.12 前版本，只指定select

MySQL8.0.12+版本支持： SELECT, INSERT, REPLACE, UPDATE , DELETE语句

问题概述

问题原因

解决方案

1、 Rewriter Query Rewrite Plugin 安装

需要在安装软件包中的share目录找到脚本  install_rewriter.sql  ，执行时会自动安装 rewriter.so

mysql@s2:/home/db/mysql/product/share$ ls -l *rewri*
-rw-r--r-- 1 mysql mysql 2216 Dec 17  2022 install_rewriter.sql
-rw-r--r-- 1 mysql mysql 1248 Dec 17  2022 uninstall_rewriter.sql

直接执行脚本install_rewriter.sql

root@localhost 16:07:59 [(none)]>source install_rewriter.sql
Query OK, 1 row affected (0.00 sec)
Query OK, 0 rows affected (0.05 sec)
Query OK, 0 rows affected (0.34 sec)
Query OK, 0 rows affected (0.69 sec)
Query OK, 0 rows affected (0.02 sec)

查看rewrite插件，已经加载

root@localhost 16:08:05 [(none)]>show plugins ;
...
| Rewriter                         | ACTIVE   | AUDIT              | rewriter.so          | GPL     |
+----------------------------------+----------+--------------------+----------------------+---------+
50 rows in set (0.00 sec)

查看rewriter_enabled状态，自动为on

root@localhost 16:08:08 [(none)]>show variables like '%rewrite%' ;
+-------------------------------------------------------+-------+
| Variable_name                                         | Value |
+-------------------------------------------------------+-------+
| rewriter_enabled                                      | ON    |
| rewriter_enabled_for_threads_without_privilege_checks | ON    |
| rewriter_verbose                                      | 1     |
+-------------------------------------------------------+-------+
3 rows in set (0.00 sec)

同时自动创建query_rewrite库，及规则表rewrite_rules 

root@localhost 16:08:16 [(none)]>show databases ;
+--------------------+
| Database           |
+--------------------+
| query_rewrite      |
+--------------------+

root@localhost 16:32:29 [query_rewrite]>show tables ;
+-------------------------+
| Tables_in_query_rewrite |
+-------------------------+
| rewrite_rules           |
+-------------------------+

添加改下规则

select ? 替换 为 select ? + 1 

root@localhost 16:09:10 [(none)]>select 1 ;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

root@localhost 16:09:23 [(none)]>INSERT INTO query_rewrite.rewrite_rules (pattern, replacement)
    -> VALUES('SELECT ?', 'SELECT ? + 1');
Query OK, 1 row affected (0.02 sec)

root@localhost 16:09:27 [(none)]>select 1 ;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

发现不没有生效，需要把改写规则加载到共享内存中，执行flush_rewrite_rules(); 

root@localhost 16:09:31 [(none)]>SELECT * FROM query_rewrite.rewrite_rules \G ;
*************************** 1. row ***************************
                id: 1
           pattern: SELECT ?
  pattern_database: NULL
       replacement: SELECT ? + 1
           enabled: YES
           message: NULL
    pattern_digest: NULL
normalized_pattern: NULL
1 row in set (0.00 sec)

ERROR: 
No query specified

root@localhost 16:10:01 [(none)]>CALL query_rewrite.flush_rewrite_rules(); 
Query OK, 1 row affected (0.03 sec)

root@localhost 16:10:12 [(none)]>select 1 ;
+-------+
| 1 + 1 |
+-------+
|     2 |
+-------+
1 row in set, 1 warning (0.00 sec)

再次执行select 1 ,发现结果变成2 ，说明改写规则生效。

root@localhost 16:32:34 [query_rewrite]>select * from rewrite_rules ;
+----+----------+------------------+--------------+---------+---------+------------------------------------------------------------------+--------------------+
| id | pattern  | pattern_database | replacement  | enabled | message | pattern_digest                                                   | normalized_pattern |
+----+----------+------------------+--------------+---------+---------+------------------------------------------------------------------+--------------------+
|  1 | SELECT ? | NULL             | SELECT ? + 1 | YES     | NULL    | d1b44b0c19af710b5a679907e284acd2ddc285201794bc69a2389d77baedddae | select ?           |
+----+----------+------------------+--------------+---------+---------+------------------------------------------------------------------+--------------------+

规则生效后 normalized_pattern和normalized_pattern 被更新。

当有错误发生时，错误信息会写入字段message。统计刷新 Rewriter_reload_error 为on

root@localhost 16:43:27 [query_rewrite]>show status like 'rewriter_reload_error' ;
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Rewriter_reload_error | OFF   |
+-----------------------+-------+

匹配语句的长度受max_digest_length 限制