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.
you can use maatkit's mk-heartbeat
you can look at result of
run on sql slave but Seconds_Behind_Master is disturbingly inaccurate at times.
on master server i have simple cron job:
where repltest is:
on the slave i monitor value returned by:
local time on all servers is synchronized via ntp.
repl_test db contains:
if you run replication - i suggest you also set up mk-table-checksum to compare content of your sql servers from time to time.
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;
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.
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.
You should execute the query
A fairly good tool is
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
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:
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