mysql 自动杀死慢查询

黄振国

原文地址：http://www.codeshows.com/mysql-%E8%87%AA%E5%8A%A8%E6%9D%80%E6%AD%BB%E6%85%A2%E6%9F%A5%E8%AF%A2/

mysql慢查询通常会锁死服务器，从而导致服务器负载增加，以下脚本可以杀死查询超过10秒的慢查询
DB_HOST=”localhost”
DB_PORT=3306
DB_USER=”test”
DB_PASS=”test”
maxtime=10
sql=”SHOW PROCESSLIST”
sss=$(/usr/local/mysql/bin/mysqladmin processlist  -u$DB_USER  -h$DB_HOST -p$DB
PASS|sed -e “s/\s//g”|awk -F’|’ ‘{print $2,$6,$7,$9}’|awk ‘{if($3>’”$maxtime”‘
& $2==”Query”){print $1}}’)
for pid in $(echo “$sss”); do
/usr/local/mysql/bin/mysql -h$DB_HOST -P$DB_PORT -u$DB_USER -p$DB_PASS -e “kill
$pid”
done
echo “$sss”
date
DB_HOST=”localhost”DB_PORT=3306DB_USER=”root”DB_PASS=”13823388967″maxtime=10sql=”SHOW PROCESSLIST”sss=$(/usr/local/mysql/bin/mysqladmin processlist  -u$DB_USER  -h$DB_HOST -p$DBPASS|sed -e “s/\s//g”|awk -F’|’ ‘{print $2,$6,$7,$9}’|awk ‘{if($3>’”$maxtime”‘& $2==”Query”){print $1}}’)for pid in $(echo “$sss”); do/usr/local/mysql/bin/mysql -h$DB_HOST -P$DB_PORT -u$DB_USER -p$DB_PASS -e “kill$pid”doneecho “$sss”date



最后加上定时运行： crontab -e
*/1 * * * * /root/killslowquery.sh