Shantanu's Blog

#!/bin/sh
# filename myalert.sh
# a cron that will record the mysql status to a text file every minute
# record status of both masters
# * * * * * mysqladmin -h111.222.333.101 -uroot -pPassWord status >> /home/ricie/status_success_101.txt 2>> /home/develop/status_err_101.txt
# * * * * * sh -xv /home/develop/myalert.sh 101 1> /home/develop/alert_succ_101.txt 2> /home/develop/alert_err.txt

# analyse the change in the status figures and prompt the user when MySQL is restarted, receives less than 3 queries or more than 10 slow queries
# myalert email

if [ -z "$1" ]
then
filename='/home/develop/status_success.txt'
else
filename="/home/develop/status_success_$1.txt"
fi

path='/home/shantanu'
User='root'
PassWord='root'
mydate=`date '+%d %B %H:%M'`
myhostname=`hostname`
ADMIN="shantanu.oak+$myhostname@gmail.com"

uptime=$(tail -2 $filename | 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 $filename | 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 $filename | 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 $filename | 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 $filename | 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 $filename | 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 $filename | 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 $filename | 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="MySQL restarted on $1 at $mydate"

# write to a file, email and SMS
echo "$mymessage" >> $path/messages.txt 2>> $path/messages_err.txt
echo "$mymessage" | mail -s "mysql restarted on $1" $ADMIN

# sms alert add as many numbers as you want to the list
while read mnumber
do
# one of the gateway not working
# curl -Ld'username=soak&password=43417010&source=oksoft&dmobile='$mnumber'&message='"'$mymessage'"'' http://67.23.229.95/smsclient//api.php
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
09702977470
mnumber_list

fi



if [[ $questions -lt 3 ]];then

# the message that will be written to a file, mail and SMS
# the word space is black listed by SMS gateway
mymessage="Queries $questions on $1 too few at $mydate"

# write to a file, email and SMS
echo "$mymessage" >> $path/messages.txt 2>> $path/messages_err.txt
echo "$mymessage" | mail -s "Few questions on $1" $ADMIN

# sms alert add as many numbers as you want to the list
while read mnumber
do
# one of the gateway not working
# curl -Ld'username=soak&password=43417010&source=oksoft&dmobile='$mnumber'&message='"'$mymessage'"'' http://67.23.229.95/smsclient//api.php
#curl -Ld'user=shantanu.oak@gmail.com:PassWord&state=4&senderID=TEST SMS&receipientno='$mnumber'&msgtxt='"'$mymessage'"'' http://api.mVaayoo.com/mvaayooapi/MessageCompose
echo "dummy text"
done << mnumber_list
09702977470
mnumber_list

fi



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

# the message that will be written to a file, mail and SMS
# the word space is black listed by SMS gateway
mymessage="Slow queries $slow on $1 are inordinately high as on $mydate"

# write to a file, email and SMS
echo "$mymessage" >> $path/messages.txt 2>> $path/messages_err.txt
echo "$mymessage" | mail -s "slow queries high on $1" $ADMIN

# sms alert add as many numbers as you want to the list
while read mnumber
do
# one of the gateway not working
# curl -Ld'username=soak&password=43417010&source=oksoft&dmobile='$mnumber'&message='"'$mymessage'"'' http://67.23.229.95/smsclient//api.php
#curl -Ld'user=shantanu.oak@gmail.com:PassWord&state=4&senderID=TEST SMS&receipientno='$mnumber'&msgtxt='"'$mymessage'"'' http://api.mVaayoo.com/mvaayooapi/MessageCompose
echo "dummy text"
done << mnumber_list
09702977470
mnumber_list

fi



# count the processes and alert the user if the processes are more than 230
myvar2=$(mysqladmin -u$User -p$PassWord processlist | wc -l | awk '{print int($1)}')
if [[ $myvar2 -gt 230 ]];then
mymessage="processes $myvar2 on $1 are inordinately high as on $mydate"

echo "$mymessage" >> $path/messages.txt 2>> $path/messages_err.txt
echo "$mymessage" | mail -s "slow queries high on $1" $ADMIN

# sms alert add as many numbers as you want to the list
while read mnumber
do
# one of the gateway not working
# curl -Ld'username=soak&password=43417010&source=oksoft&dmobile='$mnumber'&message='"'$mymessage'"'' http://67.23.229.95/smsclient//api.php
#curl -Ld'user=shantanu.oak@gmail.com:PassWord&state=4&senderID=TEST SMS&receipientno='$mnumber'&msgtxt='"'$mymessage'"'' http://api.mVaayoo.com/mvaayooapi/MessageCompose
echo "dummy text"
done << mnumber_list
09702977470
mnumber_list

fi

Labels: mysql tips