MySQL Kill sleep进程_MySQL

有空再慢慢研究。。。

--------------------------------------------------------

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