Search code examples
mysqlreplication

MySQL replication monitoring

I have setup master-master replication on two servers 8 months ago.

Everything worked fine until now, I have restored it to operation but the thing is it took a while for us to notice the issue.

I have been thinking adding a cron job to check the two servers are contactable and that they have little or no difference in record count across all tables (12).

When considering how often to run it I was a bit concerned as I would like to keep it around 5 minutes but that might be a bit much perhaps.

Can someone provide an opinion about my approach and if possible suggestion to improve it.

Solution

  • You should try pt-heartbeat, which is part of Percona Toolkit.

    It updates a timestamp in a heartbeat table once per second, so you can check the timestamp in the heartbeat table of the replica to determine how far behind the master it is.