I am not a DBA, but I am interested in knowing how replication monitoring is done on MySQL severs.
In order to know if a slave is in sync with its master, one would typically check Seconds_Behind_Master, Slave_SQL_Running, Slave_IO_Running.
There are tools available like mk-table-checksum to verify replication integrity.
My question is, how else would a DBA like to monitor replication?
Once I have asked the similar question there was ultimate Answer. Please have a look at it.
Then you will be clear with the
Seconds_Behind_Master
The Seconds_Behind_Master is not a good Parameter for monitoring the replication.
Try to use mk-heartbeat monitoring script. I use that and it's really reliable.
For documentation of mk-heartbeat, click here.
UPDATE
Try MONyog Web based application which has separate Replication tab and pretty easy to use, with lots of features
This will let you know Status of all slaves and Master on Replication tab for example: Replication topology, Master detail, Slave detail in a tabular format. Also, using Monitors page, you can get alert if seconds_behind_master lags behind certain threshold value.
You can also use the MySQL Community Replication Monitor
The community driven MySQL replication monitoring tools is a collection of scripts in SQL and other languages, to achieve the same result that others can get with expensive applications.
I also recommend (as mentioned in first reply) to monitor data consistency across all servers binded to your master, becouse for variety of reasons sometimes replicas are not 100% copies.
Some nice PHP script, that checks data integrity can be found here: https://github.com/KredytyChwilowki/MySQLReplicaIntegrityCheck
It is an open source tool, very similar to "mk-table-checksum" but with one huge difference: it can check consistency on all binary log configuration modes: “statement”, “row” and “mixed”.
