锐客网

  1. 首页 > 睿知 > it技术 > >

ProxySQL的基于sql指纹的阻断

IT知识proxysql指纹

一身转战三千里，一剑曾当百万师。这篇文章主要讲述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指纹信息如下:
【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 ?

    推荐阅读


    上一篇：Hadoop运维记录系列（二十八）

    下一篇：#yyds干货盘点#剑指 Offer 07. 重建二叉树