阻断垃圾SQL执行的方式(一)

一、确定阻断的SQL

select * from t union select * from t where c=?


二、查找并确认阻断的SQL指纹和主机组
清空表stats_mysql_query_digest SELECT * FROM stats_mysql_query_digest_reset;
查询digest： select hostgroup,count_star,sum_time,digest,digest_text,last_seen from stats_mysql_query_digest ORDER BY last_seen;

三、根据查询出来的结果确认阻断的查询规则

INSERT INTO mysql_query_rules
(rule_id,active,destination_hostgroup,digest,error_msg,log,apply)
VALUES (
1,
1,
500,
'0x1622A14C12992801',
'slow sql,denied by proxysql,rule_id=1',
0,
1
);
确认规则：
select rule_id,active,digest,match_pattern,re_modifiers,destination_hostgroup,apply from runtime_mysql_query_rules;


四、保存规则
SAVE MYSQL QUERY RULES TO DISK;LOAD MYSQL QUERY RULES TO RUNTIME;

五、阻断SQL统计
SELECT * from stats_mysql_query_rules;

阻断SQL效果