欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

MySQL Kill sleep进程_MySQL

程序员文章站 2022-06-15 22:29:48
...
有空再慢慢研究。。。

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


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