Script to Monitor MySQL Replication

Question

G'Day,

I am looking for a bash script (using Perl - As that is already installed on the machine and I don't want to have to install Ruby, Python etc. just for this requirement) that will perform some simple mySQL replication monitoring of a remote Master & Slave. The script will be able to:

• Connect to a remote mySQL master and a remote mySQL slave (cron job is running on a different machine)
• Using the output of SHOW SLAVE STATUS; and SHOW MASTER STATUS be able to email notify:

1) A general problem with replication ie. Not running, slave threads stopped etc. 2) Whether the slave is behind in transactions (Ideally this would be configurable)

• Email should be configurable for delivery details as well as a cooling off period. So if the cron is run every minute emails for the same error kind are only sent after the cooling off period. So instead of 10 emails for the replication not running you get 1

I have looked online for a solution, but have been unable to find anything suitable.

So if anyone can point me in the right direction I would appreciate it.

• Justin Ethier: It sounds like you have a very specific solution in mind - have you considered writing your own script?
• Gilles 'SO- stop being evil': While your question is on-topic here, there may be more expertise on other Stack Exchange where I think it is also appropriate: Server Fault and Database administration. If you want to migrate your question (you don't have to), use the flag link and ask a moderator to do it (do not repost it).
• Mannoj: Your script logic is perfectly fine, don't go for any opensource monitoring for this. Try having your monitoring as precise under your control, and have them customized whenever you need them to be.

Answer 1

The nagios monitoring scripts for mysql should be a good start.

If you want to monitor your database with this kind of email alerts, you should really try to setup nagios or some similar software as this is exactly the purpose of a monitoring software.

Answer 2

We have a little site to monitor this kind of MySql Replication. Actually, I monitor and admin like 40 MySql databases between servers and end user machines, all of them replicating like crazy daily =P

The site is http://tinyurl.com/4fmu88l

(Because of my boss I preferred to make a tinyurl, sorry hehe)

It's written in PHP. I could share some code if you want, but I don't have mailers. I prefer r-u-on.com for that stuff. But it's quite easy to mail any error.

Good luck!

• Lucky Murari: Awesome can you please share the code?
• a coder: some code would be dope. @altuzar's script is still working here in Dec 2023.