Mysql too many connections问题处理

方案1（推荐）

查看所有连接

select * from information_schema.PROCESSLIST;

过滤连接

select * from information_schema.PROCESSLIST where host like '10.90.113.198:%';

select * from information_schema.PROCESSLIST where host like '10.90.113.198:%' and command='Sleep';

合成命令

复制后执行

select concat('kill ',id,';') from information_schema.PROCESSLIST where host like '10.90.113.198:%' and command='Sleep';

输出到文件

应该是存储在数据库服务器文件目录中，此处不合适

select concat('kill ',id,';') from information_schema.PROCESSLIST where host like '10.90.113.198:%' and command='Sleep' into outfile 'D:\test.txt';

删除连接

kill 1454972;

方案2

#查看所有连接

show full processlist;

#查看所有IP连接

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;

#过滤连接

select * from information_schema.PROCESSLIST where command !='Sleep' order by Time desc;

参考

批量杀死MySQL连接的几种方法详解_小麦苗的技术博客_51CTO博客

https://blog.51cto.com/lhrbest/5224438

(50条消息) （转）mysql查看连接客户端ip和杀死进程_PacosonSWJTU的博客-CSDN博客_mysql查询客户端ip

https://blog.csdn.net/PacosonSWJTU/article/details/120740845