how to monitor mysql replication slave?

Question

With great efforts finally I was able to setup Mysql replication (both on windows) from one host to other as simple one way asynchrounous master-slave replication. Now purpose of this replication is just to have upto date backup, and we can switch to slave server at the time of crash.

1. How do I monitor slave status, can it email me if it could not contact host, or more number of failures occur.
2. How to make sure replication is in sync and we are not loosing any data.

If there is any automated solution then great, otherwise if I have to write some tool by myself, I am still open to the idea.

Answer 1

Nagios has several MySQL replication monitors. These should do the trick.

Answer 2

I'm really happy with the maatkit tools.

For Monitoring the Slave lag, i use mk-heartbeat. Its output is processed by a simple nagios plugin. So the alerting stuff is handled by nagios via SMS, Mail, ...

To make sure the data is the same on all replication nodes, i use mk-table-checksum. I have a small script which runs mk-table-checksum every day and checksums 1/7 of all my data on master and slaves and then verifies the checksums. So in every week, the whole data should have been checksummed. I'm currently working on a small wrapper arround this script to get the results back into nagios again. For mk-table-checksum i recommend to dig into the arguments --arg-table, --replicate, --modulo, --offset, ...

I also have another small script, which daily counts the number of rows in every table and then calculates the chunk-size and stores it in the table given at --arg-table ... so things should be kind of automagic ...

HTH

Answer 3

Not aware of anything off the shelf - but it'd be very simple to write something which fires from a cron job, say every 10 minutes and....

writes to a dedicated table in the master, and checks the time
sends an alert (or triggers automatic failover) if the live database is not available
alternatively 
sleeps for X minutes
reads the timestamp on the replicated record
sends an email if they're out of sync by more than a defined threshold

If you've not already got one, you should think about getting a proper monitoring setup - see this thread and integrating your solution with that.

HTH

C.

Answer 4

I made a scheduled program that queries "SHOW SLAVE STATUS" and I notify myself in case of any error.

Answer 5

There's also nagios plugin mysql_slavestatus available here https://www.claudiokuenzler.com/monitoring-plugins/check_mysql_slavestatus.php#.WRKpt8akLIV It works really well to monitor mysql slave sql_io_running, sql_thread_running and seconds_behind_master