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: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)

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