#!/usr/bin/env bash
# $Id$
# Author: Meethune Bhowmick <meethune@gmail.com>
# Location: /usr/local/sbin
# Scenario: User query is monopolizing the db server
# Resolution: Execute him with extreme prejudice
# Notes: Any additional information
MYSQL_BIN=/usr/bin/mysql
AWK_BIN=/usr/bin/awk

# This user must have PROCESS and SUPER privilege
MYSQL_USER=root
MYSQL_ARGS="-u ${MYSQL_USER} -p"

# Set DEBUG=1 to prevent execution of the kill command
DEBUG=0

usage() {
    echo "Usage: $(basename ${0}) [-n] -u <abuser_name> -t <runtime>"
    echo "Kill all Mysql queries for a user based on runtime."
    echo
    echo -e "\t-n\tDry run. Print what processes would have been killed. (optional)"
    echo -e "\t-u\tThe username owning the offending queries. (mandatory)"
    echo -e "\t-t\tThe minimum runtime threshold of the queries, in seconds. (mandatory)"
    exit 1
}

if [ $# -eq 0 ]
then
    usage
fi

# Process cli options
while getopts ":u:t:n" option
do
    case ${option} in
        u ) ABUSER=${OPTARG};;
        t ) TIMEOUT=${OPTARG};;
        n ) DEBUG=1;;
        * ) echo "Invalid Option"; usage;;
    esac
done

# Enforce mandatory options
if [ -z "${ABUSER}" ] || [ -z "${TIMEOUT}" ]
then
    usage
fi

# Get list of processes we want to kill
OFFENSIVE_PROCS=\
$(\
    ${MYSQL_BIN} ${MYSQL_ARGS} -e "show processlist;" | \
    ${AWK_BIN} -v abuser="${ABUSER}" -v timeout="${TIMEOUT}" '
    {
        if ( $2 == abuser && $6 >= timeout ) 
            print "KILL "$1";";
    }'\
)

if [ -z "${OFFENSIVE_PROCS}" ]
then
    echo "No processes to kill"
    exit 0
else
    # Die!
    OFFENSIVE_PROCS=$(echo ${OFFENSIVE_PROCS} | tr '\n' ' ')
    echo ${MYSQL_BIN} ${MYSQL_ARGS} -e "${OFFENSIVE_PROCS}"
    if [ ${DEBUG} -eq 0 ]
    then
        ${MYSQL_BIN} ${MYSQL_ARGS} -e "${OFFENSIVE_PROCS}"
    fi
fi

exit 0

# vim:syntax=sh
# vim:sw=4:softtabstop=4:expandtab