«
»

Script to Monitor Replication

Since I sometimes get replication problem, so I make several script to check replication automatically and report to me:

To check replication status when I in the console and playing with replication configuration or when I have nothing to do yet I want to see a black screen :D

monitorreplication.sh

#!/bin/bash
while [ 1 ]
do
        # Your code goes here
        echo ""
        date
        echo "=============================="
        mysql -u backup -pmysqlback -e "show slave status\G;"

        # Modify sleep time (in seconds) as needed below
        sleep 10
done

Another scripts to send replication status via email
This script is for sending email to my dept. just incase replication stop. And I have manually delete file /root/backup/slave_problem.txt if problem solved.

checkreplication.sh

#!/bin/bash
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin

###check if already notified###
###you have to manually delete the file after resolve the problem
if [ -f /root/backup/slave_problem.txt ]; then
exit 1;
fi

###Check if slave running###
(
echo "show slave status \G;"
) | mysql -u backup -pmysqlback 2>&1 | grep "Slave_IO_Running: No"
if [ "$?" -ne "1" ]; then
SUBJECT="DB23 - Replication IO failed"
mysql -u backup -pmysqlback -e "show slave status \G;" > /root/backup/slave_problem.txt

else

(
echo "show slave status \G;"
) | mysql -u backup -pmysqlback 2>&1 | grep "Slave_SQL_Running: No"
if [ "$?" -ne "1" ]; then
SUBJECT="DB23 - Replication SQL failed"
mysql -u backup -pmysqlback -e "show slave status \G;" > /root/backup/slave_problem.txt
fi

fi

###Send notification if replication down###
if [ -f /root/backup/slave_problem.txt ]; then
mail -s "$SUBJECT" it@myholding.com < /root/backup/slave_problem.txt
fi

This script is just to send replication status to my email only so I can monitor it if it stops, or has an error, etc.

slave_report.sh

/root/backup/slave_report.sh
#!/bin/sh
mysql -u backup -pmysqlbackup -e "show slave status \G;" > /root/backup/slave_report.txt
mail -s "Replication report on DB23" amin@rpxholding.com < /root/backup/slave_report.txt

Added the last 2 scripts into crontab:

#check replication
1,11,21,31,41,51 1-23 * * * /root/backup/checkreplication.sh > /dev/null 2>&1
5 2,6,10,14,18,22 * * * /root/backup/slave_report.sh > /dev/null 2>&1

Ref:
http://blog.taragana.com/index.php/archive/how-to-write-infinite-loop-in-bash/
http://howtoforge.com/script-to-check-if-mysql-master-master-replication-is-working-correctly

SocialTwist Tell-a-Friend

Related posts:

  1. Replication stop – Relay log file corrupt Okay it’s the second time this problem arise, so I’ll...
  2. Skip Replication Error This is happen long time ago when Mysql Replication stop...
  3. Running MySQL 4 and MySQL 5 Concurrently From Howtoforge.com’s article: Running MySQL 4 And MySQL 5 Concurrently...
  4. Running MySQLd as standalone application on Windows My friend asked me how to get MySQL running as...
  5. MySQL 5 access via old MySQL-Front 2.5 Kusma!!!, Now I know how to make old mysql front...

This entry was posted on Monday, February 9th, 2009 at 16:42 and is filed under Web and System Administrator. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply