mysql 自动杀死慢查询

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