Detect mysql deadlocks and notify developers by email. Notification sent on new deadlocks only

notify-mysql-deadlock.sh

#!/bin/bash
#
# Detects mysql deadlocks and notify developers by email
# Notification sent on new deadlocks only
# monuser must have "PROCESS" permission in mysql server
# Set EXCLUDE_PTRN to avoid alerting if the pattern was found
#

host=$1
user=monuser
pass=monpass
tmp=/tmp/.${host}.output.txt

TO="dev@dom.local"
FROM="noreply@dom.local"
SUB="$host: Deadlock found"

#-- do not alert if the following pattern was found
EXCLUDE_PTRN=""

[ "x$host" = "x" ] && exit

mysql -h $host -u $user --password=$pass -be 'show engine innodb status \G' \
    | awk '/TRANSACTIONS/{flag=0}flag;/LATEST DETECTED DEADLOCK/{flag=1}' > $tmp
[ -s $tmp ] || exit

if [ ! "x${EXCLUDE_PTRN}" = "x" ]; then
    grep -qiE "${EXCLUDE_PTRN}" $tmp
    if [ $? -eq 0 ]; then
        /bin/rm -f $tmp
        exit
    fi
fi

timestamp=$(head -n 2 $tmp | tail -n 1 | sed -e 's/ /_/g')
if [ -s "$tmp-$timestamp" ]; then
    /bin/rm -f $tmp
    exit
fi

/bin/rm -f $tmp-*
cat $tmp | mail -s "${SUB}" -r "${FROM}" $TO
/bin/mv $tmp "$tmp-$timestamp"