MySQL Status Alert

• Tweet
• 

        // log status to a file when the MySQL service is restarted, Queries processed are too high/ low or slow queries are too high.

#!/bin/sh
# myalert.sh - this filename
# a cron that will record the mysql status to a text file every minute
# Monitor mysql
# change the name of standard out file to the hostname of the current server
#* * * * * mysqladmin -uroot -proot@123 status >> /root/status_success_host_name.txt 2>> /root/status_err.txt
#* * * * * sh /root/myalert.sh host_name 200 25 >> /root/alert_succ.txt 2>> /root/alert_err.txt
# messages.txt file found in $path will record all the statistics
# write to a file, mail and SMS if mysql service restarted
# write to a file if the queries processed in the last minute are more than 200 (default 100)
# write to a file if the slow queries detected in the last minute are more than 25 (default 10)

mypath='/root'
myhostname=`hostname`
server=${1:-"$myhostname"}
myquestions=${2:-100}
myslow=${3:-10}

# mobile numbers if SMS alert required
number1='01702977470'
number2='01702977471'

uptime=$(tail -2 $mypath/status_success_$server.txt | awk ' {
  gsub(/[a-zA-Z: ]+/," ")
  m=split($0,a," ");
  for (i=1;i<=m;i++)
    if (NR==1) b[i]=a[i]; else print a[i] - b[i]
} ' | head -1)

threads=$(tail -2 $mypath/status_success_$server.txt | awk ' {
  gsub(/[a-zA-Z: ]+/," ")
  m=split($0,a," ");
  for (i=1;i<=m;i++)
    if (NR==1) b[i]=a[i]; else print a[i] - b[i]
} ' | head -2 | tail -1)

questions=$(tail -2 $mypath/status_success_$server.txt | awk ' {
  gsub(/[a-zA-Z: ]+/," ")
  m=split($0,a," ");
  for (i=1;i<=m;i++)
    if (NR==1) b[i]=a[i]; else print a[i] - b[i]
} ' | head -3 | tail -1)

slow=$(tail -2 $mypath/status_success_$server.txt | awk ' {
  gsub(/[a-zA-Z: ]+/," ")
  m=split($0,a," ");
  for (i=1;i<=m;i++)
    if (NR==1) b[i]=a[i]; else print a[i] - b[i]
} ' | head -4 | tail -1)

opens=$(tail -2 $mypath/status_success_$server.txt | awk ' {
  gsub(/[a-zA-Z: ]+/," ")
  m=split($0,a," ");
  for (i=1;i<=m;i++)
    if (NR==1) b[i]=a[i]; else print a[i] - b[i]
} ' | head -5 | tail -1 )

flush=$(tail -2 $mypath/status_success_$server.txt | awk ' {
  gsub(/[a-zA-Z: ]+/," ")
  m=split($0,a," ");
  for (i=1;i<=m;i++)
    if (NR==1) b[i]=a[i]; else print a[i] - b[i]
} ' | head -6 | tail -1 )

otables=$(tail -2 $mypath/status_success_$server.txt | awk ' {
  gsub(/[a-zA-Z: ]+/," ")
  m=split($0,a," ");
  for (i=1;i<=m;i++)
    if (NR==1) b[i]=a[i]; else print a[i] - b[i]
} ' | head -7 | tail -1)

aqueries=$(tail -2 $mypath/status_success_$server.txt | awk ' {
  gsub(/[a-zA-Z: ]+/," ")
  m=split($0,a," ");
  for (i=1;i<=m;i++)
    if (NR==1) b[i]=a[i]; else print a[i] - b[i]
} ' | head -8 | tail -1)

echo "differnce in uptime is " $uptime
echo "differnce in Threads are " $threads
echo "difference in Questions are "  $questions
echo "difference in Slow queries are "  $slow
echo "difference in Opens "  $opens
echo "difference in Flush tables " $flush
echo "difference in Open tables " $otables
echo "difference in Queries per second " $aqueries


if [[ $uptime -lt 0 ]];then

# the message that will be written to a file, mail and SMS
# the word space is black listed by SMS gateway
mymessage="`date '+%y%m%d %H:%M:%S'` MySQL restarted on $server "

# write to a file or email / SMS mobile numbers 1 and 2 should be declared 
 echo "$mymessage" >> $mypath/messages.txt 2>> $mypath/messages_err.txt
 echo "$mymessage" | mail -s "mysql service restarted on $server" $ADMIN

# sms alert add as many numbers as you want to the list
while read mnumber
do
curl -Ld'user=shantanu.oak@gmail.com:password&state=4&senderID=TEST SMS&receipientno='$mnumber'&msgtxt='"'$mymessage'"'' http://api.mVaayoo.com/mvaayooapi/MessageCompose

done << mnumber_list
$number1
$number2
mnumber_list

fi


if [[ $questions -gt $myquestions ]];then

# the message that will be written to a file, mail and SMS
# the word space is black listed by SMS gateway
mymessage="`date '+%y%m%d %H:%M:%S'` on $server processessing queries $questions"

# write to a file or email / SMS
 echo "$mymessage" >> $mypath/messages.txt 2>> $mypath/messages_err.txt

fi


if [[ $slow -gt $myslow ]];then

# the message that will be written to a file, mail and SMS
# the word space is black listed by SMS gateway
mymessage="`date '+%y%m%d %H:%M:%S'` slow queries on $server detected $slow"

# write to a file or email / SMS
 echo "$mymessage" >> $mypath/messages.txt 2>> $mypath/messages_err.txt

fi

Tags:
• forge
• MySQL
• oksoft
• shell_script