I have spent some time looking for a solution to monitor MySQL Replication with no lock. So I decided to figure something out myself rather than wasting more time or end up paying Oracle. Turns out the setup is actually very simple and can be easily setup with few lines of shell script and a monitoring tool that can produce alerts and graphs via SNMP Traps (PRTG in my case).
What do you need? Note: Make sure to update the below addresses
MySQL Master: 192.168.1.101
MySQL Slave: 192.168.1.102
SNMP Tools (snmptrap specifically)
Monitoring Server (I use PRTG): 192.168.1.116
Your shell script
A Replication client user must be created on both Master and Slave servers.
Where do you configure things?
You can run the script/cronjob from either Master or the Slave. I configured the job on the Slave server keep the load off the Master.
Step1: Configure Replication. I’m not going to cover replication here. Plenty of articles available on how to configure replication
Step2: Create the Replication Client user on Master and Slave. Run the following command on both servers. Make sure to grant REPLICATION CLIENT privilege only.
Note: replace the “*****” with your password for the new account.
Step6: Configure your monitoring to receive SNMPTraps from your slave (or where you are running your cronjob). The following is meant to be used with PRTG. You can configure other monitoring tools to received SNMP Traps as well.
– Logon to the Web Interface
– Select your Probe
– Add Sensor
– Select or search for “SNMP Trap Receiver” and complete the following:
OID value: 220.127.116.11.14.15111
Set sensor to ‘warning’/Message must include: Replication OK
Set sensor to ‘warning’/Message must not include: Replication Broken
Scanning Interval: 60 seconds
Step7: Make sure you have SNMP Tools installed.