# Monitoring replication on mysql

I have managed to setup master and slave replication.

It is working fine. What are the possibilities that it might go down?

Is there any alerting tool to monitor that?

Another thing: Can I run a separate db in my replication db which I just run for testing purposes?

-

Replication can break or misbehave in all sorts of fun and exciting ways. You need to monitor for three things:

# 1. Replication is running and has not stopped due to error

To monitor whether or not replication is running is simply a matter of programmatically checking SHOW SLAVE STATUS and looking at the values for Slave_IO_Running and Slave_SQL_Running. Both should be "yes". pmp-check-mysql-replication-running from the Percona Monitoring Plugins for Nagios is written for this task.

# 2. Replication is performing well (slave lag behind master is within an acceptable range)

You need to make sure that the slave has not lagged behind the master by too far. "Too far" is determined by what your application can tolerate and by how many binary logs you keep on the master server. Because replication on the slave is single-threaded, slaves can easily get lagged behind. SHOW SLAVE STATUS has the Seconds_Behind_Master value, but is not a reliable indicator of actual lag, and frequently will jump around. In order to accurately measure replication lag, you need an external application to insert a timestamp into a table periodically. You can then measure that value from the slave and compare it against the current time to get actual replication delay. pt-heartbeat is a daemon that will insert a heartbeat into a table on your server. You can then alert on that value with pmp-check-mysql-replication-delay to make sure it is within your specified parameters.

# 3. The data on the servers is in sync.

There are many ways that a master and slave can get out of sync so that the data differs. You need to detect those differences and correct them periodically because a small difference can, over time, turn into a very large difference, especially with statement-based replication. This is no small task, and pt-table-checksum is designed to calculate these differences. Run this weekly. pmp-check-pt-table-checksum is a Nagios plugin to alert when the slave has data discrepancies relative to the master. To actually fix the differences, use pt-table-sync.

pt-table-checksum has been recently rewritten and is pretty easy to use. pt-table-sync has a lot of options and can be confusing. Read the documentation for these thoroughly as you can really shoot yourself in the foot if you aren't careful. Here is a webinar about these tools.

Another thing: Can I run a separate db in my replication db which I just run for testing purposes?

There is nothing preventing you from modifying (or supplementing) the data on the slave, though generally I would recommend against it. Best practice is to have the slave be read_only=1. However, real life tends to trump best practices and often slaves are used as reporting servers. My suggestion would be to make very clear access privileges for those using the slave for data modification and to have all additional tables in a separate schema.

-
ok I want to test with the first tool your gave me here percona.com/doc/percona-monitoring-plugins/nagios/…. The problem it does not have any installation guide here? Must I be running any prerequisite is it? –  newbie14 May 8 '12 at 14:03
another thing I run this command show slave status; but the details is not readable properly. –  newbie14 May 8 '12 at 14:48
The plugins are built for an existing Nagios installation. If you don't have Nagios, they don't serve much of a purpose. –  Aaron Brown May 8 '12 at 19:09
the problem with these web-based application are very vunerable. I recently had an incident of hacking via the phpMyAdmin so what could be some other option? –  newbie14 May 9 '12 at 15:08
What are you worried about with Nagios, possibly the most widely used and robust monitoring application in the world? Nagios doesn't talk to MySQL directly like phpMyAdmin. Always make sure to put a .htaccess file in front of it and run it over https. –  Aaron Brown May 9 '12 at 18:34
1. Replication can 'go down' from several reasons, the main one is that the slave will get a sql error while performing one of the commands that were executed on the master (e.g. updating a row that exist on the master but doesnt exist on the slave), another issue can be different variables setting between master and slave such as max_allowed_packet. All in all, replication is a solid feature.

2. I use Server Density to monitor the replication (among other parameters on the server), they can monitor if the replication is running, seconds behind slave, and lots of other parameters on the server (cpu, memory). They have a very clear web app, iphone app and can send push notification when thing goes south and the best thing is that integration with them takes 5 min.

3. As for the separate db, I didn't understood what you trying to achieve there

Hope this helps,

R

-
what else variable must be same for both replication and master to avoid any problem? 2. Beside server density any other tools? 3. Say my master db is called dbV1 and replication is also dbV1 and on the replication can I have another db running named dbV1_Test. –  newbie14 May 7 '12 at 17:04
1. the variable i've mentioned was the one I had issues with, dont know what others are potential trouble makers. 2. you can use nagios, but I think its an over kill, serverdensity is easy and full of nice features 3. yes –  Ran May 7 '12 at 20:00

What are the possibilities that it might go down?

1. Network problem between Master and Slave server
2. Failed statements on Slave, which succeeded on Master though.
3. Data goes out of synchrony,

For example, if you update the slave outside of the slave thread

These are possible reasons that your replication may go down

Is there any alerting tool to monitor that?

Yes .. there is a tool called MONyog-MySQL Monitor and Advisor which monitors Replication Slave and alerts in-case of failure immediately as an Email OR SNMP trap.

This is quite useful if you are replicating your production server and for some reasons (possibly mentioned above) has caused replication to failed you get an alert then you can take proper action to make sure that Master and Slave are in sync again.

MONyog has separate Replication tab as well which shows all Master and Slave servers in your environment.

You can prefer pt-table-checksum as well which can be configured with NagiOS as mentioned by @Aaron but it could be a cumbersome process.

-
will monyog work well on linux because I am on centos environment? –  newbie14 Jul 11 '12 at 15:34
Yes . it works On linux 64 and 32 bit environment as RPM and TAR package –  Mahesh Patil Jul 12 '12 at 3:32
is there any open source option of that sort too? –  newbie14 Jul 14 '12 at 3:12
Nope it's not open-source, you can try percona-utilities for that. –  Mahesh Patil Jul 18 '12 at 6:15