Mail notification on long running queries

 

 

The below shell scripts send mail notification on queries which are executing more than 60 seconds.

#!/bin/bash

date_now=$(mysql -h 127.0.0.1 -u Read -p"Read@987" -e" select  now() from   information_schema.PROCESSLIST limit 1"|awk {'print $1" "$2'} |grep -v "now")

query_time=$(mysql -h 127.0.0.1 -u Read -p"Read@987" -e" select  Time from   information_schema.PROCESSLIST where command not in ('Daemon','Binlog Dump','Slave_SQL','Slave_IO','Sleep') and Time >= 0 order by Time desc limit 1" |awk {'print $1'} |grep -v "Time")

long_query=$(mysql -h 127.0.0.1 -u Read -p"Read@987" -e" select  Time,INFO as Query from   information_schema.PROCESSLIST where command not in ('Daemon','Binlog Dump','Slave_SQL','Slave_IO','Sleep') and Time >= 60 order by Time desc")

#echo "$long_query"

if [ $query_time -ge 60 ]; then

#echo "1"

# Email subject

subject="Urgent!! Long running querie(s) on DB"

# Email recipient

#recipient="notify@gmail.com skamarthi@gmail.com"

recipient="skamarthi@gmail.com"

# Email content

email_content=$(cat <<EOF

The following querie(s) are executing for more than 60seconds.

Tigger Time: $date_now

Query Details:

$long_query

EOF

)

#Send the email

echo "$email_content" | mail -s "$subject" -r "security@gmail.com" "$recipient"

else

echo "Nothing"

fi