Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

4 Answers 4

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

share|improve this answer

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

share|improve this answer

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.

share|improve this answer
up vote 0 down vote accepted

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.