有空再慢慢研究。。。
--------------------------------------------------------
mysql> SELECT concat('KILL ',id,';') FROM information_schema.processlist WHERE user='root' INTO OUTFILE '/tmp/a.txt';Query OK, 2 rows affected (0.00 sec) mysql> source /tmp/a.txt;Query OK, 0 rows affected (0.00 sec)
---------------------------------------------------------
#!/bin/bash
#while [ "2" -gt "1" ]
#do
log_command=`mysqladmin processlist | grep -i sleep | wc -l`
date=`date +%Y%m%d/[%H:%M:%S]`
#echo $log_command
if [ "$log_command" -gt 250 ]
then
for i in `mysqladmin processlist | grep -i sleep | awk '{print $2}'`
do
mysqladmin kill $i
done
echo "$date : $log_command, sleep is too many i killed it" >> /data/killsleep/sleep.log
else
echo "$date : $log_command, not need to kill" >> /data/killsleep/sleep.log
fi
# sleep 180
#done
注意mysqladmin执行的用户，直接在命令行执行mysqladmin processlist | grep -i sleep | wc -l，实际上是-uroot。在命令行执行这个脚本时候用的是登录服务器的用户。而用crontab执行的时候，虽然编辑的是root的crontab，但执行的时候不是root用户，是哪个我也不知道。最好在crontab的命令里加上su - root。
--------------------------------------------------------------------------------------
#It is used to kill processlist of mysql sleep~~~
#!/bin/sh
while ：
do
n=`/opt/mysql/bin/mysqladmin processlist|grep -i sleep |wc -l`
date=`date +%Y%m%d/[%H:%M:%S]`
echo $n
# id=`/opt/mysql/bin/mysqladmin processlist |grep Sleep |awk '{if ($12 > 100) {print $2}}'`
# echo "$date : $n" >> /tmp/sleep.log
# if [ id != "" ]
# then
# for j in $id
# do
# /opt/mysql/bin/mysqladmin kill $j
# echo "$date : sleep is to long " >> /tmp/sleep.log
# fi
#
if [ "$n" -gt 60 ]
then
for i in `/opt/mysql/bin/mysqladmin processlist|grep -i sleep |awk '{print $2}'`
do
/opt/mysql/bin/mysqladmin kill $i
done
echo "sleep is too many i killed it " >> /tmp/sleep.log
echo "$date : $n" >> /tmp/sleep.log
fi
sleep 1
done
