8

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?

Improve this question

2 Answers 2

Reset to default
9

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.

Improve this answer
7
  • 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
    Commented May 8, 2012 at 14:03
  • another thing I run this command show slave status; but the details is not readable properly.
    – newbie14
    Commented May 8, 2012 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
    Commented May 8, 2012 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
    Commented May 9, 2012 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
    Commented May 9, 2012 at 18:34
1

  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

Improve this answer
2
  • 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
    Commented May 7, 2012 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
    Commented May 7, 2012 at 20:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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