linux – Page 4 – Wise Geek

Monitoring MySQL Replication with Munin and mk-heartbeat

We use Munin and MySQL Replication from our production systems for hot standby purposes. But how to know if replication breaks?

Using components of the excellent MySQL Toolkit, I wrote a Munin plug-in to monitor replication delay and alert with an email if the delay gets above a certain level. This requires running mk-heartbeat as a daemon.
Here’s the plugin code:

#!/bin/bash
## Plug-in to Monitor MySQL Replication Delay.
#
#$Author: imran $
#$Date: 2008-09-01 09:29:21 $
#$RCSfile: mysql_rep_delay,v $
#$Revision: 1.1 $
case $1 in
        config)
                cat <<'EOM'
graph_title MySQL Replication Delay
graph_vlabel seconds
mysql_rep_delay.label delay
mysql_rep_delay.warning 60
mysql_rep_delay.critical 3600
graph_category mysql
EOM
                exit 0;;
esac
echo -n "mysql_rep_delay.value "
mk-heartbeat -D test --check -h localhost

In Munin, you will eventually get graphs of your replication delay like this:

Master-Master Replication in Active-Passive mode

Yup, as the post title says I am currently undertaking a project to provide a “hot spare” web application server using MySQL replication.

The solution will use the hot spare (or “passive” server) for scale-out of certain classes of read-writes from the “active” server. Replication is being done from the “active” to “passive” and vice-versa. The IP address tied to the domain name of the web server will fail-over to the hot spare in the event of a disaster.

The failover will use the Heartbeat package. It’s all kinda fun and the Heartbeat stuff is new to me so it is a new thing to learn.

What’s best is that I have end-to-end responsibility for this project. This means that I designed the solution and will implement it and maintain it. More on how I get on soon.