MySQL 管理长时间运行查询

最常用的查询

select concat ('kill ',id,';') from information_schema.processlist where time >= 2 -- and user = '业务账号' and command not in ('sleep','Connect') and state not like ('waiting for table%lock'); and info like '%Metabase%' mysql -uroot -s -N -p -h -e "select concat ('kill ',id,';') from information_schema.processlist where INFO like 'SELECT xxx FROM%' " > kill.sql 

RDS提供的存储过程：

create event my_long_running_query_monitor on schedule every 5 minute starts '2015-09-15 11:00:00' on completion preserve enable do begin declare v_sql varchar(500); declare no_more_long_running_query integer default 0; declare c_tid cursor for select concat ('kill ',id,';') from information_schema.processlist where time >= 3600 and user = substring(current_user(),1,instr(current_user(),'@')-1) and command not in ('sleep') and state not like ('waiting for table%lock'); declare continue handler for not found set no_more_long_running_query=1; open c_tid; repeat fetch c_tid into v_sql; set @v_sql=v_sql; prepare stmt from @v_sql; execute stmt; deallocate prepare stmt; until no_more_long_running_query end repeat; close c_tid; end;

参考：https://help.aliyun.com/knowledge_detail/41735.html?spm=a2c4g.11186631.2.20.51106998SvntYb

RDS中的参数

loose_max_statement_time

管理长查询的shell脚本

#!/bin/bash password=xxxxxx mysql -uroot -p$password -N -s -e "select concat ('kill ',id,';') from information_schema.processlist where time >= 300 -- and user = '业务账号' and command not in ('sleep','Connect') and state not like ('waiting for table%lock');" > killmysqlsession.txt #cat killmysqlsession.txt | while read line #do #echo $line #mysql -uroot -p$password -e "$line" #done mysql -uroot -p$password < killmysqlsession.txt #或者登陆实例source killmysqlsession.txt