Skip to content

Monitoring the MySQL master slave replication status

If you run MySQL in a production environment chances are you are using a master/slave replication set-up. This brings two major benefits: higher availability (multiple nodes to read from) and quicker response times (when doing reads from slaves).

When you are running a master/slave replication set-up, monitoring the replication state becomes critical. Especially when doing reads from slave nodes you want them to stay in sync as much as possible. Using a simple Bash script we can easily monitor the replication link state.

Monitoring the master/slave replication link state

There are usually two things you want to know when monitoring MySQL replication: whether it’s running at all and whether it’s delayed. In the first case you have a critical problem: the replication link has been broken and needs to be restored as soon as possible. However, the second case is bad as well: if the slave is seriously delayed behind its master the data it contains can be seriously out-dated.

Checking the status of master/slave replication link is simple. On the MySQL command line run:

SHOW SLAVE STATUS\G

Mind that the command delimiter used is “\G”, which tells MySQL to print the columns vertically allowing for much easier reading. Running that command will return all kind of information about the slave. Who its master is, on what position the replication log is, which databases should be excluded from replication and most importantly for monitoring the link state: the Seconds_Behind_Master variable.

Reading the Seconds_Behind_Master variable

The Seconds_Behind_Master variable contains the number of seconds that the slave node is behind its master. Preferably this is 0: meaning that it’s (almost) completely in sync. Almost, because due to network delays and such real-time synchronization is impossible. When the slave node is delayed, this number will rise. Now, the interesting thing is that when the replication link is broken, the value of this variable will be “NULL”. So all we need to do is read this variable and take action based on its output. However, you don’t want to make day task of running “SHOW SLAVE STATUS\G”, so let’s script that.

Checking the MySQL replication link state from the shell

We can run MySQL commands directly from the shell by using the “-e” parameter, like:

/usr/bin/mysql -e “SHOW SLAVE STATUS\G”

Note: it’s assumed that you have the MySQL login credentials set-up in your user’s .my.cnf file. If not, see the MySQL documentation on Option Files.

As this command will return all the slave output, and we’re only interested in the Seconds_Behind_Master variable, we use grep to filter out this variable:

/usr/bin/mysql -e “SHOW SLAVE STATUS\G”| grep “Seconds_Behind_Master”

However, we are only interested in its value. So we use awk to get the value behind the colon:

/usr/bin/mysql -e “SHOW SLAVE STATUS\G”| grep “Seconds_Behind_Master” | awk -F”: ” {‘ print $2 ‘}`

Now we have this value we can take further action, for example sending an e-mail based on its value (NULL or above a certain threshold).

Automatically monitoring the MySQL replication link state

Now that we’ve extracted the Seconds_Behind_Master value we can easily create script that reports when problems arise. By running this script periodically (maybe even every minute) from crontab you can get a very early warning when problems arise.
The replication monitor (Bash) script can be downloaded here.

Some important points to note about this monitoring script:

  • It assumes that the MySQL login credentials are stored in the user’s .my.cnf file that runs the script.
  • It assumes you are using the Bash shell (but should easily work with other shells as well).
  • It first checks whether replication is running at all (NULL value), then checks whether it’s delayed.
  • A slave is assumed to be delayed when it’s 1 minute (60 seconds) behind its master. You can easily adjust this value to your situation.
  • The script uses a temporary file to keep track whether a problem has been reported before (to prevent an e-mail flood). It deletes this temp file when the issue has been restored.
  • The script should be installed in crontab, preferably to run every minute.
  • See disclaimer below.

Disclaimer

This post just focuses on one simple variable: the amount of seconds a slave node is behind its master (or whether it’s running at all). However, it should be noted that all other kind of things could be wrong which we can’t detect with this variable. Some examples: we may be reading from the wrong master or a much needed database is excluded from replication. However, it’s still very useful to read the Seconds_Behind_Master variable: it reports by far the most usual problem (the link being down or delayed).

THE INFORMATION IN THIS POST AND THE MONITORING SCRIPT IS PROVIDED AS-IS. NO GUARANTEES WHATSOEVER ARE GIVEN THAT THIS WORKS PROPERLY OR WORKS AT ALL. USE AT YOUR OWN RISK.

Categories: MySQL.

Tags: , , ,

Comment Feed

2 Responses

  1. It’s hard to find your articles in google. I found
    it on 21 spot, you should build quality backlinks , it will help you
    to get more visitors. I know how to help you,
    just type in google – k2 seo tips and tricks



Some HTML is OK

or, reply to this post via trackback.

Continuing the Discussion

  1. [...] When you want to connect to your MySQL database externally you can’t really use this method. Examples are replication and external MySQL clients connecting to the database. You could use OpenSSH tunneling to tunnel the connection safely through SSH. SSH tunneling will be described in a future post. For more on replication see: Monitoring the MySQL master slave replication status. [...]