MySQL问题排查汇总

 


 

步骤一

//1.查看最大连接数
mysql> show variables like 'max_connections';

//2.查看历史上实际使用的最大连接数
mysql> show global status like 'max_used_connections';

//3.查询当前running sql执行时间最长的10条
mysql> select * from information_schema.processlist where info is not null order by time desc limit 10;

//4.查看线程、连接数
mysql> show global status like 'Threads_%';

//5.查看正常运行的线程
mysql> show processlist;

 

步骤二

//1、按客户端IP分组,看哪个客户端的链接数最多
mysql> select client_ip, count(client_ip) as client_num from (select substring_index(host,':' ,1) as client_ip from information_schema.processlist ) as connect_info group by client_ip order by client_num desc;

//找出所有执行时间超过5分钟的线程,拼凑出kill语句
mysql> select concat('kill ', id, ';') from information_schema.processlist where Command != 'Sleep' and Time > 300 order by Time desc into outfile '/var/lib/mysql-files/kill.txt';
mysql> source /var/lib/mysql-files/kill.txt;