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_monitoron schedule every 5 minutestarts '2015-09-15 11:00:00'on completion preserve enable dobegin  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/bashpassword=xxxxxxmysql -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"#donemysql -uroot -p$password < killmysqlsession.txt#或者登陆实例source killmysqlsession.txt