Kill long MySQL queries

kill_long_queries.sh

##################################################
#            Kill long queries                   #
# This shell script will kill queries that have  #
# passed the $MAX execution time.                #
# Julian Capilla-krumbak                         #
# [email protected]                           #
# 21-05-2014                                     #  
##################################################

#!/bin/bash
MAX=1000 #Time in seconds
db_user="username"
db_pass="password"
query="mysql -u$db_user -p$db_pass -e"
tmp="/tmp/$(basename "$0").$RANDOM.tmp"

# Optional logfile name
#log_file="{log file path}"

$query "show processlist\G" | egrep 'Id|Time' | tr -d ' ' > "$tmp"

while read i; do
    if [ "$(echo "$i" | cut -c -2)" == Id ]; then
        ID=$(echo "$i" | cut -c 4-)
        continue
    elif [ "$(echo "$i" | cut -c -4)" == Time ]; then
        Time=$(echo "$i" | cut -c 6-)
    fi
    if  [ "$Time" -gt "$MAX" ]; then
        echo "$(date +%Y-%m-%d' '%T) :Process $ID has passed $MAX seconds ($Time)." >> $log_file

        # Check that query can be terminated safely

        flag="$($query "select * from information_schema.processlist where Id='$ID'" | grep "$ID" | egrep -i 'locked|insert|update|delete|alter|replace')"

        if [ ! -z "$flag" ]; then
           echo "Waiting for process $ID to finish" >> $log_file
           echo "$flag" >> $log_file
        else
            echo "Process $ID has been terminated" >> $log_file
            $query "select * from information_schema.processlist where Id='$ID'" | grep "$ID" >> $log_file
            $query "kill $ID;"
        fi
    fi
done < "$tmp"


rm "$tmp" -f