MYSQL Long kill long Querys via Script in Cron

As part of my private life i sometimes help friends with their Webservers. Recently i had a case of a LAMP Server including Maria / MySQL DB. Some Queries lasted “forever” in the DB and consumed a lot of CPU. I created small script which looks into the DB and kills thoos querys.

This Script was done for a Server where the Password of MySQL is stored in a File (Plesk) Therefore the -p Command contains a cat of the password. Please adjust the script to take the right username and password.


#!/bin/sh
# Kill long Mysql Queries
mysql -uadmin -p`cat /etc/psa/.psa.shadow` -ANe"SELECT id FROM information_schema.processlist WHERE time > 360;" | cut -d: -f2 |\
while read id
do
mysql -uadmin -p`cat /etc/psa/.psa.shadow` -ANe"kill $id;"
done


Cron looks like this to check this all 10 minutes and not receive any notification about it.

*/10 * * * * /root/killlongdbqueries.sh >/dev/null 2>&1