If you’re managing large MySQL installations where lot of queries (written by lot of people) are executed, this is going to sound familiar. Sometimes due to some faulty query or to heavy traffic, your MySQL server could be overloaded and queries start to stall there, making your PROCESS LIST grow and grow and grow…
So, here it is a quick script that kills every SELECT (we don’t want to kill other query types) older than a certain amount of second and that can help you when you find yourself in one of these situations.
It’s rather quick’n’dirty, but for it’s small use case, it works. It expects GNU userland
#!/bin/bash
SEC=$1
IFS='|'
if [[ $SEC -lt 1 ]]; then
echo "Usage: $0 SECONDS"
exit 1
fi
mysqladmin proc -v|grep Query|grep -Evi "delete|update|insert|alter table" |while read dummy qid qusr qhost qdb qstat qsec qstat2 query
do
if [ $qsec -gt $SEC ]; then
echo "Killing query $qid..."
mysqladmin kill $qid
fi
done
The eternal fight between admins and computers 
Script to Kill the queries running more than 10 mins ( ~ 700 secs ) :-
————————————————————————————
#!/bin/bash
> /home/srinivas/MySQL_Long_Query_Kill.log
RECIPIENT=srin@udbco.com,santhu@udbco.com
RECIPIENTCC=saj@udbco.com
RECIPIENTBCC=srinivas@gmail.com,santhoshudb@gmail.com
SEC=700
IFS=’|’
if [[ $SEC -lt 700 ]]; then
echo “Usage: All the MySQL queries are less than $SEC seconds, So no MySQL process to Kill ….”
exit 1
fi
mysqladmin proc -v|grep Query|grep -Evi “delete|update|insert|alter table” |while read dummy qid qusr qhost qdb qstat qsec qstat2 query
do
if [ $qsec -gt $SEC ]; then
echo “Hi Team,” > /home/srinivas/MySQL_Long_Query_Kill.log
echo ” ” >> /home/srinivas/MySQL_Long_Query_Kill.log
echo ” ” >> /home/srinivas/MySQL_Long_Query_Kill.log
echo “Killing the MySQL query with PID :$qid which has been running for $qsec seconds .” >> /home/srinivas/MySQL_Long_Query_Kill.log
mysqladmin kill $qid
echo ” ” >> /home/srinivas/MySQL_Long_Query_Kill.log
echo “Below is the Query that has been killed : ” >> /home/srinivas/MySQL_Long_Query_Kill.logs
echo ” ” >> /home/srinivas/MySQL_Long_Query_Kill.log
echo ” $query ” >> /home/srinivas/MySQL_Long_Query_Kill.log
echo ” ” >> /home/srinivas/MySQL_Long_Query_Kill.log
echo “Query killed Time : `date +”%Y-%m-%d %H:%M:%S”`” >> /home/srinivas/MySQL_Long_Query_Kill.log
echo ” ” >> /home/srinivas/MySQL_Long_Query_Kill.log
echo ” ” >> /home/srinivas/MySQL_Long_Query_Kill.log
echo “Regards, ” >> /home/srinivas/MySQL_Long_Query_Kill.log
echo “IT Team, ” >> /home/srinivas/MySQL_Long_Query_Kill.log
mail -s “HIGH: MySQL Long running Queries are Killed as on `date +”%Y-%d-%m %H:%M:%S”`.” $RECIPIENT -c $RECIPIENTCC -b $RECIPIENTBCC — -f ITteam@udbco.com < /home/srinivas/MySQL_Long_Query_Kill.log
fi
done
Best,
Srinivas Mutyala