Long-running MySQL queries can bring down the whole database even before you react. One of the ways to keep the application running and debug long running query issues is by monitoring, logging and killing them. This will give you opportunity to keep application running while you debug the issues.
Script
This script finds queries that are running longer than 20 seconds, writes the kill commands to a file and executes them using mysql command line. Script then sends an email to someone that needs to wake up and work on the issue and then deletes the file.
# Credentials for a MySQL user with PROCESS, SUPER permissions USERNAME=xxxx PASSWORD=xxxx # MySQL Server location HOST=aaa.bbb.ccc.ddd PORT=xxxx TIMEOUT=0 # 1 minute TARGET_DB=XXXX # MySQL user to monitor DATE=$(date +"%Y%m%d%H%M") MYSQL="mysql -u $USERNAME --password=$PASSWORD -h $HOST -P $PORT -B" #All queries taking longer than 20 seconds SQL="SELECT ID, INFO FROM information_schema.processlist WHERE DB='XX' and COMMAND='Query' AND TIME > 20" $MYSQL -N -e "SELECT ID, INFO FROM information_schema.processlist WHERE DB='XX' and COMMAND='Query' AND TIME > 20" | cut -f 1,2 | awk '{print "# " $0 "\nKILL " $1 ";" }' > /tmp/mysql_kill_sql.$DATE if [ -s /tmp/mysql_kill_sql.$DATE ] then $MYSQL < /tmp/mysql_kill_sql.$DATE mail -s " executed query killer! CHECK "aaa@bbbb.com < /dev/null else rm -f /tmp/mysql_kill_sql.$DATE fi