select*from information_schema.processlist where HOST like"101.86.16.1%"
基于数据库过滤
select*from information_schema.processlist where DB="test";
会话详情
SELECT*FROM sys.sessionWHERE CONN_ID =62200
查询会话执行过的sql
SELECT@dt_ts:=UNIX_TIMESTAMP(NOW());SELECT@dt_timer:=MAX(SH.TIMER_START)FROM performance_schema.threads AS T1
INNERJOIN performance_schema.events_statements_history AS SH
ON T1.`THREAD_ID`=SH.`THREAD_ID`WHERE T1.PROCESSLIST_ID=CONNECTION_ID();SELECT
SH.CURRENT_SCHEMA AS database_name,REPLACE(REPLACE(REPLACE(SH.`SQL_TEXT`,'
',' '),'
',' '),' ',' ')AS executed_sql,
FROM_UNIXTIME(@dt_ts-CAST((@dt_timer-SH.TIMER_START)/1000000000000AS SIGNED))AS start_time,
FROM_UNIXTIME(@dt_ts-CAST((@dt_timer+SH.TIMER_END)/1000000000000AS SIGNED))AS end_time,(SH.TIMER_END-SH.TIMER_START)/1000000000000AS used_seconds,
SH.TIMER_WAIT/1000000000000AS wait_seconds,
SH.LOCK_TIME/1000000000000AS lock_seconds,
SH.ROWS_AFFECTED AS affected_rows,
SH.ROWS_SENT AS send_rows
FROM performance_schema.threads AS T1
INNERJOIN performance_schema.events_statements_history AS SH
ON T1.`THREAD_ID`=SH.`THREAD_ID`WHERE T1.PROCESSLIST_ID=[会话id]AND SH.TIMER_START<@dt_timerORDERBY SH.TIMER_START ASC;
杀死会话
杀死指定会话
kill1000;
生成杀死某一IP下会话SQL
select concat("kill ", ID,";")as command
from information_schema.processlist
where HOST like"127.0.0.1%"
select concat("kill ", ID,";")as command
from information_schema.processlist
where HOST like"101.86.16.1%"
文章目录MySQL 排查锁表常用Sql锁信息查询是否被锁的表查询innodb事务获取的锁查询会话当前会话列表基于IP过滤基于数据库过滤会话详情查询会话执行过的sql杀死会话杀死指定会话生成杀死某一IP下会话SQLMySQL 排查锁表常用Sql锁信息查询是否被锁的表show OPEN TABLES where In_use > 0;查询innodb事务获取的锁select * from information_schema.innodb_trx;查询会话当前会话列表show PRO