ProxySQL的基于sql指纹的阻断
我这里的实验环境：
单机proxysql+mgr单主，已经配置好了读写分离策略。这些都不是本文的重点，就一带而过吧。
我下面只贴基于sql指纹的阻断的配置。
我们这里先查看下当前proxysql的 query rule表
(none)>select rule_id,active,digest,match_pattern,re_modifiers,destination_hostgroup,apply
from runtime_mysql_query_rules;
+---------+--------+--------------------+----------------------+--------------+-----------------------+-------+
|rule_id | active | digest |match_pattern | re_modifiers |destination_hostgroup | apply |
+---------+--------+--------------------+----------------------+--------------+-----------------------+-------+
|11 | 1 | <null> | ^SELECT.*FOR UPDATE$ | CASELESS | 2 | 1 |
|12 | 1 | <null> | ^SELECT | CASELESS | 3 | 1 |
+---------+--------+--------------------+----------------------+--------------+-----------------------+-------+先重置下统计信息，方便下面的实验结果的查看
(none)>SELECT * FROM stats_mysql_query_digest_reset;连接proxysql的sql端口，去执行些sql模拟业务查询操作
(sbtest)>
select id,count(*) from sbtest2 where id<=400 group by id limit 2;
-- 假设这条是正常的query
+----+----------+
|id | count(*) |
+----+----------+
| 7 | 1 |
| 8 | 1 |
+----+----------+
2rows in set (0.01 sec)
(sbtest)>
select id,pad,count(*) from sbtest2 where id<=400 group by id,pad limit 2;
-- 假设这条是正常的query
+----+-------------------------------------------------------------+----------+
|id | pad | count(*) |
+----+-------------------------------------------------------------+----------+
| 7 | 26081374730-86321700986-51212137094-30635959762-03880194434| 1 |
| 8 |64289062455-51067794311-09919261228-11533354367-07401173317 | 1 |
+----+-------------------------------------------------------------+----------+
2rows in set (0.00 sec)
(sbtest)>
select id,pad,kfrom sbtest2
where pad LIKE '%300'
order by pad desc, k asc
limit 2;
-- 假设这条是bad query，我们准备拦截它，防止对后端db造成过大的压力。
+---------+-------------------------------------------------------------+---------+
|id | pad | k |
+---------+-------------------------------------------------------------+---------+
|8925030 | 99994086258-68190733814-17047039939-13659566296-25654191300 | 4529558
|
|3481579 | 99958957217-55749742121-80155456371-67621596004-37323551300 | 4485745
|
+---------+-------------------------------------------------------------+---------+
2 rows in set (8.09 sec) -- 它查询花费了8秒！去proxysql 后台看下捕获到的sql指纹信息如下:
(none)> select hostgroup,count_star,sum_time,digest,digest_text from stats_mysql_query_digest ORDER BY digest_text;
+-----------+------------+----------+--------------------+-------------------------------------------------------------------------------+
| hostgroup | count_star | sum_time | digest | digest_text |
+-----------+------------+----------+--------------------+-------------------------------------------------------------------------------+
| 3 | 1 | 870 | 0x1A2E8BD55D37EAC0 | select id,count(*) from sbtest2 where id<=? group by id limit ? |
| 3 | 1 | 1964 | 0xACD8CB8C5FC6877F | select id,pad,count(*) from sbtest2 where id<=? group by id,pad limit ? |
| 3 | 3 | 19831 | 0xE196C3EEFFB3B335 | select id,pad,k from sbtest2 where pad LIKE ? limit ? |
| 3 | 1 | 8086104 | 0xE1AC73804CF6181A | select id,pad,k from sbtest2 where pad LIKE ? order by pad desc,k asc limit ? |
+-----------+------------+----------+--------------------+-------------------------------------------------------------------------------+
4 rows in set其中，digest 为 0xE1AC73804CF6181A 的就是上面那条耗时8秒的 query，我们下面在proxysql上做下拦截。
添加一条query rule 如下
INSERT INTO mysql_query_rules
(rule_id,active,destination_hostgroup,digest,error_msg,log,apply)
VALUES (
2,
1,
3,
'0xE1AC73804CF6181A',
'bad sql,denied by proxysql,rule_id=2',
0,
1
);将规则生效并落库
SAVE MYSQL QUERY RULES TO DISK;
LOAD MYSQL QUERY RULES TO RUNTIME;查看下当前的query rule表
(none)> select rule_id,active,digest,match_pattern,re_modifiers,destination_hostgroup,apply from runtime_mysql_query_rules;
+---------+--------+--------------------+----------------------+--------------+-----------------------+-------+
| rule_id | active | digest | match_pattern | re_modifiers | destination_hostgroup | apply |
+---------+--------+--------------------+----------------------+--------------+-----------------------+-------+
| 2 | 1 | 0xE1AC73804CF6181A | <null> | CASELESS | 3 | 1 |
| 11 | 1 | <null> | ^SELECT.*FOR UPDATE$ | CASELESS | 2 | 1 |
| 12 | 1 | <null> | ^SELECT | CASELESS | 3 | 1 |
+---------+--------+--------------------+----------------------+--------------+-----------------------+-------+然后，我们再通过proxysql去查下下数据，可以看到被拦截了。
(sbtest)> select id,pad,k from sbtest2
where pad LIKE '%300' order by pad desc, k asc limit 2;
ERROR 1148 (42000): bad sql,denied by proxysql,rule_id=2
(sbtest)> select id,pad,k from sbtest2
where pad LIKE '300' order by pad desc, k asc limit 2;
ERROR 1148 (42000): bad sql,denied by proxysql,rule_id=2
(sbtest)> select id,pad,k from sbtest2 where pad LIKE '300%'
order by pad asc, k asc limit 2;
-- 如果执行其它sql是不会被拦截的
+---------+-------------------------------------------------------------+---------+
| id | pad | k |
+---------+-------------------------------------------------------------+---------+
| 8191240 | 30000005131-79141572261-89369326990-51166512062-21675569833 | 4511925 |
| 7379633 | 30000013448-81007441931-68992994405-46942257115-78566983734 | 4520728 |
+---------+-------------------------------------------------------------+---------+在proxysql里面，也可以统计查询匹配到的规则的情况
(none)> SELECT * from stats_mysql_query_rules;
+---------+------+
| rule_id | hits |
+---------+------+
| 2 | 7 |
| 11 | 0 |
| 12 | 4 |
+---------+------+本文参与 腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。
原始发表：2021/11/23 ，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
腾讯云开发者
