Bash Script to Check MySQL Replication Status, Notification
Posted on 07 April 2010 by Jason Grimme
The first few days after setting up MySQL replication, I noticed that one small error would stop the replication. When this went production, we couldn’t afford to have the replication stop and not know about it. We can’t log into all our servers and check the status of things on a daily basis, we want to be notified about them.
Luckily for me since I’m relatively new to bash, I found what somebody else had done and modified it a bit to suit our purposes. I then setup a cronjob for it to run every day and send us a notification email whether the status is good or bad.
It is very important that the two lines that contain the MySQL calls be enclosed in grave accent (`) characters. These let bash know that these strings will be executed.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 | #!/bin/bash # Jason Grimme # March 26, 2010 # Originally from http://lists.mysql.org/replication/1672 # Checks MySQL Replication status. Sends user(s) a notification of status status=0 MasterHost="10.1.100.1" SlaveHost="10.2.200.2" emails="admin@xxxxx.com it@xxxxx.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=xxxxxx test -e "show slave status \G" |grep -i "Slave_SQL_Running"|gawk '{print $2}'` IOresponse=`mysql -u root --password=xxxxx test -e "show slave status \G" |grep -i "Slave_IO_Running"|gawk '{print $2}'` if [ "$SQLresponse" = "No" ]; then error="Replication on the slave MySQL server($SlaveHost) has stopped working.\nSlave_SQL_Running: No\n" status=1 fi if [ "$IOresponse" = "No" ]; then error="Replication on the slave MySQL server($SlaveHost) has stopped working.\nSlave_IO_Running: No\n" status=1 fi # If the replication is not working if [ $status = 1 ]; then for address in $emails; do echo -e $error | mail -s $DownSubject $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 |
Tags | Bash, Linux, MySQL, Replication
Thanks for the publishing the script. Currently this script considers everything except ‘No’ to be a good state (including a missing values). You may want to be more conservative and consider everything a failure except ‘Yes’.
Hi, thanks for you valuable post.
Is it a way to do the same under Windows 2008 Server?
Thanks in advance