Mysql 杀死sleep进程

查询数据库当前设置的最大连接数：查看mysql数据库连接数、并发数相关信息

show global variables like '%max_connections%';

show processlist;

查看sleep退出时间限制：

show variables like “%timeout%”;

+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| slave_net_timeout | 3600 |
| wait_timeout | 28800 |
+-----------------------------+----------+
11 rows in set (0.00 sec)

临时生效命令(注意全局变量和session变量的区别，下面是全局变量的修改)：

set global interactive_timeout=60; flush PRIVILEGES;
set global wait_timeout=60; flush PRIVILEGES;
show global variables like '%timeout%';

永久生效命令：

编辑 /etc/my.cnf,在mysqld 下新增 timeout参数，设置为120秒，如下：

【mysqld】
wait_timeout=120
interactive_timeout=120

注意：要同时设置interactive_timeout和wait_timeout才会生效。

编写脚本杀死sleep进程：

#!/bin/sh

user=root
passwd=mysql密码
host=localhost

while : 
do
  n=`mysqladmin processlist -u$user -p$passwd -h$host | grep -i sleep | grep '10.10.11' | wc -l`
  date=`date +%Y%m%d[%H:%M:%S]`
  echo $n >> /tmp/sleep.log

  if [ "$n" -gt 20 ]
  then
  for i in `mysqladmin processlist -uroot -p5432b400aa9c9fb0c92b543dce97c244 -hlocalhost | grep -i sleep | grep '10.10.11'  | awk '{print $2}'`
  do
    mysqladmin -u$user -p$passwd -h$host kill $i
  done
  echo "sleep is too many I killed it " >> /tmp/sleep.log
  echo "$date : $n" >> /tmp/sleep.log
  fi
  sleep 1
done

秒客网

Mysql 杀死sleep进程

相关文章