reg88.com

fine...I'll blog too

How to Monitor MySQL Replication

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.

1
mysql> GRANT REPLICATION CLIENT ON \*.\* TO ‘rep-client’@’192.168.1.101′ IDENTIFIED BY ‘\***\***\*****’;

Step3: Test your newly created account’s permissions. Logon to the Slave server and run the following commands Master and the Slave:

1
2
mysql -urep-client -p\***\***\***** -h192.168.1.101 -e “show master statusG;”
mysql -urep-client -p\***\***\***** -h192.168.1.102 -e “show slave statusG;”

Step4: Create the following script

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
vi /root/scripts/MySQL\_Replication\_Check
#!/bin/bash

#MASTER Variables  
M_Position=\`mysql -urep-client -\***\***\***\***\****\* -h192.168.1.101 -e “show master statusG;” | grep Position | sed -e ‘s/[^:]\*://’\`  
M_File=\`mysql -urep-client -\***\***\***\***\****\* -h192.168.1.101 -e “show master statusG;” | grep File | sed -e ‘s/[^:]\*://’\`

#SLAVE Variables  
S\_Exec\_Master\_Log\_Pos=\`mysql -urep-client -\***\***\***\***\****\* -h192.168.1.102 -e “show slave statusG;” | grep Exec\_Master\_Log_Pos | sed -e ‘s/[^:]\*://’\`  
S\_Master\_Log\_File=\`mysql -urep-client -\***\***\***\***\****\* -h192.168.1.102 -e “show slave statusG;” | grep Master\_Log\_File | sed -e ‘/Relay\_Master\_Log\_File/d’ | sed -e ‘s/[^:]\*://’\`  
S\_Slave\_IO\_State=\`mysql -urep-client -\***\***\***\***\****\* -h192.168.1.102 -e “show slave statusG;” | grep Slave\_IO_State | sed -e ‘s/[^:]\*://’\`  
S\_Seconds\_Behind\_Master=\`mysql -urep-client -\***\***\***\***\****\* -h192.168.1.102 -e “show slave statusG;” | grep Seconds\_Behind_Master | sed -e ‘s/[^:]\*://’\`  
S\_Slave\_IO\_StateString=\`echo $S\_Slave\_IO\_State\`
S\_Slave\_IO_StateOK=”Waiting for master to send event”

#Replication Overall Status. Send SNMPTrap to Monitoring  
echo “”
if [ "$S\_Slave\_IO\_StateString" == "$S\_Slave\_IO\_StateOK" ] && [ $M\_Position -eq $S\_Exec\_Master\_Log\_Pos ] && [ $M\_File == $S\_Master\_Log\_File ] && [ $S\_Seconds\_Behind\_Master -lt 5 ]
then
echo “Replication is running fine….”
snmptrap -v 1 -c ROpublic 192.168.1.99 ” ” ” ” ” 1.11.12.13.14.15111 s “Replication OK”
else
echo “Replication BROKEN”
snmptrap -v 1 -c ROpublic 192.168.1.99 ” ” ” ” ” 1.11.12.13.14.15111 s “Replication Broken”
fi

#Replication State Status  
echo “”
echo “Slave IO State: ” $S\_Slave\_IO_StateString
if [ "$S\_Slave\_IO\_StateString" == "$S\_Slave\_IO\_StateOK" ]
then
echo “Replication OK”
else
echo “Replication STOPPED!”
fi

#Replication Log Files Status  
echo “”
echo “Master Log File: ” $M_File
echo “Slave Log File: ” $S\_Master\_Log_File
if [ $M\_File == $S\_Master\_Log\_File ]
then
echo “Log Files: OK”
else
echo “Log Files DO NOT Match!”
fi

#Replication Log Position Status  
echo “”
echo “Master Position: ” $M_Position
echo “Slave Position: ” $S\_Exec\_Master\_Log\_Pos
if [ $M\_Position -eq $S\_Exec\_Master\_Log_Pos ]
then
echo “Position: OK”
else
echo “Slave is Behind!”
fi

#Slave Seconds Behind Master  
echo “”
echo “Slave Seconds Behind Master: ” $S\_Seconds\_Behind_Master
if [ $S\_Seconds\_Behind_Master -lt 5 ]
then
echo “Acceptable: YES”
else
echo “Acceptable: NO, Slave is Way Behind!”
fi

Step5: Create a cronjob to run your new script every 60 seconds. You can adjust this if you like.. All depends on how busy your database is.

1
2
3
crontab -e
\# MySQL Replication Check
\* \* \* \* * /root/scripts/MySQL\_Replication\_Check

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
– Devices
– Select your Probe
– Add Sensor
– Select or search for “SNMP Trap Receiver” and complete the following:
OID value: 1.11.12.13.14.15111
Sender: 192.168.1.102
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.
On Redhat:

1
yum install net-snmp-utils