PatchLog

This is not a tutorial about how to set up mysql replication. You can find all the details about how to set up mysql replication in the official mysql documentation. This is just a script that can be used to monitor a MySQL replication setup. A MySQL replication setup consists of a master server and a slave server. On the slave server there are two threads that run continuously, one is the I/O thread that fetches changes that occurred on the master server and one is the SQL thread that tries to run the queries that were executed on the master server.

Any one of those two threads can fail. The I/O thread can fail because a connection timeout, or because the master server has blocked and went down, and the SQL thread can fail because some query that was executed on the master server could not be executed on the slave server. If any of those threads fail the replication will be interrupted and as a server administrator you would want to know when this happens so that you can fix it as soon as possible and resume the replication.

If you just want to see the status of the replication threads on the slave server you can use any mysql client to connect to the server and log in with a user that has "replication client" privileges (root user has them by default but you can add them to any user ) and execute this:

 
SHOW slave status\\G;

This will show you information about the slave server like, the slave threads state ( running or not ), last error and replication position.
But doing manually this each time you want to know if the replication status is not efficient, so here comes my script that does it automatically and notifies you only if thee is a problem. This script will run on the slave server every 30 minutes or whatever you want to set it in cron and will check the slave status. In case any if the two threads is not running the script will send an email message to one or two email addresses.
The script is attached to this post but it requires some configuration before you can run it or set it up in cron.
you will have to modify the first lines of the script to specify what user name and password to use when connecting to the MySQL. server. This user must has "replication client" privileges. It is recommended that you add a user that only has this privilege and use it in this script. You can do that by logging in the MySQL server as root and execute:

 
GRANT replication client to replc@localhost \\
identified BY "your_password";

You can use the method presented in a previous post to generate a random password for the "replc" user.
One other configuration you need to set is the email address(es) where the script will send the notifications and you can also customize the subject and body of the messages.
The script is released under GPL and you can get it from here:
Replication monitor

This entry was posted on Thursday, February 8th, 2007 at 9:31 am and is filed under databases, shell. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.