Zabbix detecting MySQL Database master-Slave synchronization

in the high-concurrency Web site architecture, MySQL database master-slave synchronization is indispensable, but often occur due to network reasons or operation errors, MySQL master and slave often appear out of sync, then how to monitor MySQL master-slave synchronization, also become a test site normal operation of the important link.

MySQL synchronization function by 3 threads (master on 1, slave on 2) to achieve, simply say: Master send log one, slave receive log one, slave run log one.

First, let's explain some of the important parameters in show slave status:

Slave_io_running: I/o Whether the thread was started and successfully connected to the primary server. slave_sql_running: SQL  Whether the thread is started. seconds_behind_master: This field is an indication of how much "backward" the subordinate server is. When the subordinate sql thread is running (processing the update), this field is the number of seconds elapsed since the time stamp of the most recent event executed by this thread on the primary server. When this thread is subordinate to the server i/o thread catches up and goes into idle state, waiting to come from i/o more events for threads, this field is zero. In summary, this field measures the subordinate server sql threads and subordinate servers i/ o The time gap between threads, in seconds.  

So how do you monitor whether the server is running properly?

1. Create an account to monitor MySQL

--p password mysql>* * 'Zabbix '@' localhost ';      

2. Write a monitoring script

This script is mainly used to obtain MySQL master-slave synchronization information;

Let's execute a command first.

Mysql-u zabbix-e ' show slave status\g '

We select in the output information

Slave_IO_Running:YesSlave_SQL_Running:Yes

These two to monitor, I tested, when the operation of the data is abnormal, slave_sql_running will become no, when the execution of Slave stop, two will become no;

The script content is as follows

#!/bin/bash--' show slave status\g '|  -"Slave_io_running| Slave_sql_running "|" {print $} '|  -Yes          

I wrote only one command, and here is a detailed explanation of its meaning;

First use Zabbix this user to get all the state of slave, then grep out the two states, then output the second column, and finally look at a few yes states, normally there are two yes states.

3 . Adding monitoring items to the Zabbix Agent

Add the following line at the end of the file

userparameter=mysql.replication,/home/zabbix/mysql-replication.sh

In the string following the equal sign, the comma is preceded by key, followed by the executed script (the script does not forget to execute the permission)

After adding it, restart the agent program.

4. Add monitoring items to server side

Zabbix_get-s 192.168.0.34-k "Mysql.replication" 2

The 192.168.0.34 here is my agent's IP, if the master-slave replication is normal, will return 2 (representing two states are yes), now that the server has been able to get the state from the agent, and now in the management interface to add monitoring items:

Configuration --Templates

First create a template, the main function of this template is to monitor the status of MySQL master-slave replication;

Add Trigger

When the returned value is less than 2 o'clock, indicating that there is only 1 Yes or 0 Yes, this indicates that MySQL master-slave synchronization is abnormal, that is, generate an alarm;

Get the latest data

As can be seen here, the latest monitoring data has been obtained, indicating the success of monitoring added;

Zabbix detecting MySQL Database master-Slave synchronization