适用范围

设置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 ;
...

RewriterACTIVEAUDITrewriter.soGPL

50 rows in set (0.00 sec)

查看rewriter_enabled状态,自动为on

root@localhost 16:08:08 [(none)]>show variables like '%rewrite%' ;
Variable_nameValue
rewriter_enabledON
rewriter_enabled_for_threads_without_privilege_checksON
rewriter_verbose1

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 ;
idpatternpattern_databasereplacementenabledmessagepattern_digestnormalized_pattern
1SELECT ?NULLSELECT ? + 1YESNULLd1b44b0c19af710b5a679907e284acd2ddc285201794bc69a2389d77baedddaeselect ?

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

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

root@localhost 16:43:27 [query_rewrite]>show status like 'rewriter_reload_error' ;
Variable_nameValue
Rewriter_reload_errorOFF

匹配语句的长度受max_digest_length 限制

标签: none