利用Shell一次清除Mysql所有的Sleep用户连接
把下面的shell保存为killmysqlsleep.sh, 然后执行./killmysqlsleep.sh 192.168.1.5 3306 root (IP 为mysql server IP,用户为你需要清除的sleep链接用户,3306为端口)。需要把MYSQLPASS改为你的mysql root 的password。
#It is used to kill processlist of mysql sleep
#!/bin/sh
MYSQLDIR=/usr/bin
MYSQLTOOL=$MYSQLDIR/mysql
MYSQLUSER=root
MYSQLPASS="password"
usage()
{
echo "Usage: $0 ip port db_user"
echo "Example: $0 1.1.1.1 3306 db_user"
exit
}
if [ $# -ne 3 ];then
usage
fi
hostip=$1
port=$2
user=$3
sleepid="SELECT ID FROM information_schema.PROCESSLIST t WHERE t.COMMAND='Sleep' and user='$3' and>
echo $sleepid
$MYSQLTOOL -h$hostip -u$MYSQLUSER -p$MYSQLPASS -P$port -e "$sleepid" >sleepIDs
while read sID
do
echo $sID
$MYSQLTOOL -h$hostip -u$MYSQLUSER -p$MYSQLPASS -P$port -e "kill $sID"
done<sleepIDs