Monitoring MySQL slave replication is running

Having recently added some mysql replication slaves I wanted to be sure that the slaves are always running. In order to do this I’ve selected Monit though you could do this several different ways.

What I’ve done is put a quick little bash script together that runs every minute via cron.

What this does is grabs the Slave_IQ_Running and Slave_SQL_Running from a SHOW SLAVE STATUS and if they are both Yes indicating the replication is running smoothly then it touches the /opt/slave_running file.

This is my simple monit script, I drop this into /etc/monit/conf.d on an ubuntu system and it gets included by default. Just restart monit. Monit runs every 2min and if the /opt/slave_running is a couple minutes out of date I’m alerted to take a look.

I’ve seen this idea around on other blogs using python or ruby so I can’t take credit for the idea, just dropping in my notes for how I did it.

2 thoughts on “Monitoring MySQL slave replication is running

  1. Arve Seljebu

    You could also use cron to send mail. Use /etc/aliases to forward root’s mail and run the following script in /etc/cron.daily (all cron output is mailed to root).

    #!/bin/bash
    MYSQL=`mysql –defaults-file=/etc/mysql/debian.cnf -e “SHOW SLAVE STATUS \G”`
    IO=`echo “$MYSQL” | grep ‘Slave_IO_Running:’ | awk ‘{print $2}’`
    SQL=`echo “$MYSQL” | grep ‘Slave_SQL_Running:’ | awk ‘{print $2}’`
    if [ “Yes” != “$IO” ] && [ “Yes” != “$SQL” ]; then
    echo “MySQL Replication not running!!”
    fi
    exit

    Reply
  2. Arve Seljebu

    Ooops, forgot the OR:

    #!/bin/bash
    MYSQL=`mysql –defaults-file=/etc/mysql/debian.cnf -e “SHOW SLAVE STATUS \G”`
    IO=`echo “$MYSQL” | grep ‘Slave_IO_Running:’ | awk ‘{print $2}’`
    SQL=`echo “$MYSQL” | grep ‘Slave_SQL_Running:’ | awk ‘{print $2}’`
    if [ “Yes” != “$IO” ] || [ “Yes” != “$SQL” ]; then
    echo “MySQL Replication not running!!”
    fi
    exit

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *