MySQL 中 Sleep 进程连接数过多问题解决

解决方法

批量删除 sleep 进程状态的连接数.

1), 一种直接在 MySQL 命令控制台操作:

MySQL> show processlist;
MySQL> SELECT concat('KILL',id,';') FROM information_schema.processlist WHERE user='user1';
MySQL> select concat('KILL',id,';') from information_schema.processlist where user='user1' into outfile '/tmp/a.txt';
MySQL> source /tmp/a.txt;

其中上面 user1 用户是 "show processlist;" 命令下, User 列对应最多的 Sleep 进程连接状态的用户.

注: 如上面导出为指定文件路径报 "ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement" 错误.

是由于 MySQL 在文件的导入, 导出有个默认的文件路径. 通过 "show variables like'%secure%';" 命令查看默认导入, 导出文件路径.

从上面可看出默认路径为 "/var/lib/mysql-files/"; 所以改下导出文件路径:

然后在执行 "source /var/lib/mysql-files/a.txt;" 命令, 接下来是出现一堆 "Query OK, 0 rows affected (0.00 sec)", 执行成功.

2), 另一种是写 Shell 脚步方法, 配合设置 cron 定时任务

#!/bin/sh
while :
do
sleep_num=`mysqladmin processlist -u user -ppassword -h 10.10.10.100 | grep -ie sleep |wc -l`
date=`date +%Y%m%d\[%H:%M:%S]`
echo $sleep_num
if [ "$sleep_num" -gt 100 ];then
    for i in `mysqladmin processlist -u user -ppassword -h 10.10.10.100 | grep -ie sleep | awk '{print $2}'`
    do
        mysqladmin -u user -ppassword -h 10.10.10.100  kill $i
    done
echo "sleep is too many more than 100, killed it">> /tmp/sleep.log
echo "$date : $sleep_num">> /tmp/sleep.log
fi
sleep 1
done

接下来设置个 cron 定时任务, 设置多长时间跑一次.

来源: http://www.bubuko.com/infodetail-3650987.html