Mysql – Monitor MySQL Replication

monitoringMySQLmysql-replication

What is the best practice for monitoring a slave to make sure that it is

a) Still running
b) Not too far being the master

I would like to alert by email if it is behind, happy to write a script or two to hook into command line applications.

Best Answer

1

you can use maatkit's mk-heartbeat

2

you can look at result of

show slave status;

run on sql slave but Seconds_Behind_Master is disturbingly inaccurate at times.

3

you can hack your own solution, similar to mine - i use it both for nagios monitoring and for feeding munin charts showing 'seconds behind master'.

on master server i have simple cron job:

* * * * * root /usr/local/bin/repltest

where repltest is:

#!/bin/bash
start=`date +%s`
d=0
while [ $d -lt 60 ] ; do
        echo "update repl_test set t= NOW(); " |mysql --defaults-file=/etc/mysql/debian.cnf repl_test
        sleep 3
        d=$(( `date +%s` - $start ))
done

on the slave i monitor value returned by:

echo "select UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(t) from repl_test" |  mysql --defaults-file=/etc/mysql/debian.cnf -N repl_test

local time on all servers is synchronized via ntp.

repl_test db contains:

CREATE TABLE IF NOT EXISTS `repl_test` (`t` datetime NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `repl_test` (`t`) VALUES(NOW());

if you run replication - i suggest you also set up mk-table-checksum to compare content of your sql servers from time to time.

Related Topic