MySQL process killer for long running Sleep processes

mysql-kill-sleeping.sh

#!/bin/bash

# Number of seconds a query is allowed to execute before it is killed. (default: 3h)
MAX_EXEC_TIME=${1:-10800}
MYSQL_USER=your-mysql-user

mysql -sNB -e "SHOW FULL PROCESSLIST" \
    | sed 's/\t/ /g' \
    | sed 's/ \+/ /g' \
    | while read process_id user host db command_type time state info time_ms rows_sent rows_examined; do

    if (( $time > $MAX_EXEC_TIME )); then
        if [ "$command_type" == "Sleep" ] && [ "$user" == "$MYSQL_USER" ]; then
            echo "$user - killing thread because sleeping for $time seconds: KILL $process_id"
            # echo "dry-run. Please run 'KILL $process_id' (or 'mysqladmin kill $process_id') manually!"
            # mysql -e "KILL $process_id"
            mysqladmin kill $process_id
        fi
    fi
done