Monitor MySQL Replication

Question

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.

Answer 1

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.

Answer 2

pQd has it, checking 'show slave status' is the easiest way. Regarding Seconds_behind_master being inaccurate, I wanted to mention that the value is the difference in the timestamp for the statement being read out of the relay log by the slave SQL thread; it's not related to an estimate of how long it will take to catch up. For instance, a single long-running update that takes, say, an hour to run will cause the slave to appear up to an hour behind its master but once the statement completes it could very well only have 1 second of work left to do to catch up.

Also, you will want to grant 'REPLICATION CLIENT' to the user that you will be monitoring from in order to retrieve the slave status;

Answer 3

The obvious answer as others have said is to use some variation on SHOW SLAVE STATUS. I use the checker built into Nagios personally, but that is because I do all kinds of other monitoring through nagios already. There is a catch though, it is possible for SHOW SLAVE STATUS to show both processes running and yet for the slave to be hung. From what we can tell (because we had the problem and looked into it), the problem occurs when there are network burps of some duration that is too short to kill the slave outright but too long for it to recover properly. We came up with a work around where we look at the time stamp of the latest entry in a table that changes routinely and compare it between the master and slave then throw an alert if it is "too far" behind. Not perfect and it would only work in certain circumstances, but consider yourself warned.

Answer 4

You can refer this blog post which mentions all tools open-source and Commercial which shows http://blog.webyog.com/2012/11/20/how-to-monitor-mysql-replication/

Typically this blog includes tools like pt-heartbeat: Convenient tool to monitor slave lag in real time. pt-slave-restart: Watches and restarts Slave on error. pt-slave-find: Finds replication hierarchy of the slaves. pt-table-checksum: Checks if databases on the slaves are in sync with their master.

MySQL Enterprise Monitor: A “Virtual DBA Assistant” by Oracle is an agent based monitoring tool which has a neat web based GUI. ‘Replication’ tab which gives a topological view of all the Masters and their Slaves along with the output of SHOW SLAVE STATUS and SHOW MASTER STATUS.

MONyog-MySQL monitor and advisor: Which supports replication monitoring and managing include-‘Replication’ tab that give a topological view of all the Masters and their Slaves along with the SHOW SLAVE STATUS and SHOW MASTER STATUS.

Answer 5

You should execute the query SHOW SLAVE STATUS and make sure that both Slave_IO_Running and Slave_SQL_Running have a value of Yes. If not, the slave will NOT be able to recover automatically. If both are Yes then replication is still working, even though there may be a delay (Seconds_Behind_Master).

Answer 6

A fairly good tool is rep_mon, part of the MyCat suite, it's basically just a perl script in the style of pQd's third option, however it's easily configureable and well tested. Once configured you can run it as a quick test yourself, or schedule it in cron to send out emails if there's a problem.

When run, it basically just outputs either "OK", or what the error is. You can also have it alert you if the seconds-behind hits a certain threshold (set by you).

However, if it's just threshold monitoring you want, I'd suggest going for maatkit, it works by actually inserting and later querying using real SQL rather than the possibly inaccurate output of SHOW SLAVE STATUS.

Answer 7

Use

mysql_config_editor set --login-path=local --host=<< your slave >> --user=username --password

and

mysql_config_editor set --login-path=remote --host=<< your master >> --user=username --password

to set predefined login values to avoid Warning: Using a password on the command line interface can be insecure.

Use these to query both MySql servers:

1. Compare log position on master against Exec_Master_Log_Pos on slave:

   master_log=$(mysql --login-path=remote -e "show master status" | grep -v File | awk '{print $2}')

   slave_exec=$(mysql --login-path=local -e "show slave status \G" | grep Exec_Master_Log_Pos | awk '{print $2}')

   diff=$(( $master_log - $slave_exec ))

2. Check slave IO status to see if it's running:

   IO_Status=$(mysql --login-path=local -e "show slave status \G" | grep Slave_IO_Running | awk '{print $2}')

3. Check slave SQL status to see if it's ok or not:

   SQL_Status=$(mysql --login-path=local -e "show slave status \G" | grep "Slave_SQL_Running:" | awk '{print $2}')

You can then use the values of $diff, $SQL_Status, and $IO_Status to send warning emails if they didn't match certain values according to your preference

Answer 8

Here's how to do it with Zenoss: link text

Answer 9

You could use the check_mysql program from the Nagios Plugins project (https://github.com/nagios-plugins/nagios-plugins) like this:

/usr/lib/nagios/plugins/check_mysql -H <HOST> -u <MONITORING_USER> -p <PASSWORD> -S -w 60 -c 120

Where I think that depending on your use case you will need to update the values for the warning (-w) and critical (-c) seconds behind master thresholds.

Finally, note that the MONITORING_USER user only requires the REPLICATION CLIENT GRANT.

PS: You don't need a Nagios system to use the previous approach. You could use check_mysql alone.