当前正在运行的SQL
select id,user,db,info,Command,Time,State from information_schema.processlist where info is not null and user not in ('dba','repl') order by time desc limit 50;
-- 杀连接
select concat('kill ',id,';') as ids from information_schema.processlist where
time>50
and info is not null
and user like '%'
and db like '%'
order by time desc ;
-- 杀连接shell
mysqlw -h 127.0.0.1 -P 3306 -e "select concat('kill ',id,';') as ids from information_schema.processlist where db like 'dboop%' and user like '%' " >>3306kill.txt
排查连接
-- 连接数分组
SELECT USER,DB,SUBSTRING_INDEX(HOST,':',1) AS IP,COUNT(1) AS Total FROM INFORMATION_SCHEMA.PROCESSLIST
where user not in ('replication','dba','system user') AND HOST NOT LIKE '127.0.0.1%'
GROUP BY IP,DB,USER ORDER BY Total DESC;
-- 活跃连接分组
SELECT USER,DB,SUBSTRING_INDEX(HOST,':',1) AS IP,COUNT(1) AS Total FROM INFORMATION_SCHEMA.PROCESSLIST
where user not in ('replication','dba','system user') AND HOST NOT LIKE '127.0.0.1%' and info is not null
GROUP BY IP,DB,USER ORDER BY Total DESC;
查阻塞
select waiting_pid as '被阻塞的线程',waiting_query as '被阻塞的 SQL',blocking_pid as '阻塞线程',blocking_query as '阻塞 SQL',
wait_age as '阻塞时间',sql_kill_blocking_query as '建议操作' from sys.innodb_lock_waits
where (UNIX_TIMESTAMP ()-UNIX_TIMESTAMP (wait_started)) > 30
>> Home
