Replication Monitor Shell Script

High Performance MySQL: Optimization, Backups, Replication, and MoreHigh Performance MySQLMySQL Transactions and Replication HandbookThere should be automatic way to initiate alerts if MySQL replication has stopped working because it is impossible for human being to monitor replication all the time and fix it if there is any issue. Following is the shell script which is very useful.




status=0
MasterHost="192.168.0.14"
SlaveHost="192.168.0.12"
emails="zaeem@zaeem.com" #multiple emails space separated
DownSubject="Replication status - Down"
GoodSubject="Replication status - Good"
GoodMessage="Everything regarding MySQL replication on $SlaveHost is good.\nHave a great day!\n\n"

#Grab the lines for each and use Gawk to get the last part of the string(Yes/No)
SQLresponse=`mysql -u root --password=password test -e "show slave status \G" |grep -i "Slave_SQL_Running"|gawk '{print $2}'`
IOresponse=`mysql -u root --password=password test -e "show slave status \G" |grep -i "Slave_IO_Running"|gawk '{print $2}'`
# Sometimes there is scheduled backup which takes backup by stopping slave but if this script finds slave stopped then it start it immediately. So when there is backup going on a file is created under tmp partition and this scripts keep checking it and if file exist it assume backup is going on so it doesn't start slave, otherwise it start slave.
FILE=/tmp/backup.back
if [ -f $FILE ];
then
echo "Backup is in progress"
echo -e "Backup is taking place on 192.168.0.12"
else

if [ "$SQLresponse" = "No" ]; then
       error="Replication on the slave MySQL server($SlaveHost) has stopped working.\nSlave_SQL_Running: No\n. \nTrying to start it\n"
       status=1
       mysqladmin --user=root --password=password start-slave
fi

if [ "$IOresponse" = "No" ]; then
      error="Replication on the slave MySQL server($SlaveHost) has stopped working.\nSlave_IO_Running: No\n. \nTrying to start it\n"
      status=1
      mysqladmin --user=root --password=password start-slave
fi

# If the replication is not working
if [ $status = 1 ]; then
      for address in $emails; do
               # echo -e $error | mail -s $DownSubject $address
                echo -e $error | mail -s "Replication is Down" $address
                echo "Replication down, sent email to $address"
      done
fi

# If the replication is working fine
if [ $status = 0 ]; then
for address in $emails; do
                echo -e $GoodMessage | mail -s $GoodSubject $address
                 echo "Replication is up, still sent email to $address"
      done
fi
fi


You can schedule this script to run after each five minutes to make sure that replication is up and running all the times.

High Performance MySQL: Optimization, Backups, Replication, and More
MySQL High Availability: Tools for Building Robust Data Centers
High Performance MySQL
MySQL Transactions and Replication Handbook

Command to calculate number of rows in a file

Sometimes there are so many files and you want to count number of rows in each file the a command or a shell script is handy to get it done otherwise its almost impossible to count number of rows in file. Following are some ways to calculate this.

1. wc -l *.csv (This command will count rows per csv file)
2. #!/bin/bash
   rows=0;
   for f in `*.CSV`;
     do
       let rows+=`wc $f | awk '{print $1}'`
   
     done
   
   echo "Total rows=$rows"

I hope this small tutorial is of help to you guys.