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: NULLnormalized_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 限制