[MySQL]: Long running query stopper shell script & alerting mechanism

March 30, 2015 · by · in MySQL

#!/bin/bash
> /home/srinivas/MySQL_Long_Query_Kill.log
RECIPIENT=srinivas@dbversity.com,santhosh@dbversity.com
RECIPIENTCC=test@dbversity.com
RECIPIENTBCC=dbversity@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.log
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@dbversity.com < /home/srinivas/MySQL_Long_Query_Kill.log

fi
done
————–
*/5 * * * * sh /home/srinivas/MySQL_Long_Query_Kill.sh

/home/srinivas/MySQL_Long_Query_Kill.log

#!/bin/bash
IFS=’|’
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 100 ]; then
echo “Killing query $qid…”
mysqladmin kill $qid
else
echo “No queries to kill.”
fi
done
sh /home/srinivas/MySQL_Long_Query_Kill.sh >

  • Ask Question
    • Tags: