Zabbix detects the master-slave synchronization of Mysql database

Zabbix detects the master-slave synchronization of Mysql database

In the highly concurrent website architecture, master-slave synchronization of MySQL database is indispensable, but it often occurs due to network reasons or operational errors, MySQL master-slave synchronization often occurs, so how to monitor MySQL master-slave synchronization has become an important part of testing the normal operation of the website.

The MySQL synchronization function is implemented by three threads (1 on master and 2 on slave). To put it simply: a log is sent by master, a log is received by slave, and a log is run by slave.

Slave_io_Running:yes

Slave_SQL_Running: yes

When these two are yes at the same time, it means that the master and slave are synchronized, and one of them becomes no. Synchronization fails.

We need to write a script to check the status of these two parameters

Cat mysql-replication.sh

#! / bin/bash

Mysql-uroot

-paired colors color show slave status\ G' | grep-E "Slave_IO_Running | Slave_SQL_Running" | awk'{print $2}'| grep-c Yes

The following error occurred while testing the script

Warning: Using a password on the command line interface can be insecure.

two

At this time, the data accepted by the detector is an error and the data cannot be accepted by the detector.

You can add mysql and password to my.cnf [mysqld], and we can also give the database an account without a password.

Mysql-uroot-p password

Mysql > grant replication client on *. * to 'zabbix'@'localhost'

The test changes the script to

#! / bin/bash

Mysql-uzabbix-e 'show slave status\ G' | grep-E "Slave_IO_Running | Slave_SQL_Running" | awk' {print $2}'| grep-c Yes

The test script is shown as follows

two

When 1 or 0 is displayed, there is a problem with synchronization.

Add monitoring items to zabbix agent

Vim / usr/local/zabbix-3.0.4/etc/zabbix_agentd.conf

UserParameter=mysql.replication,/usr/local/zabbix-3.0.4/bin/mysql-replication.sh

In the string after the equal sign, the comma is preceded by Key and followed by the executed script (don't forget to execute the script)

Restart zabbix agent

Do a test in zabbix server to see if you can accept the data.

[root@zabbix bin] #. / zabbix_get-s 192.168.2.44-k "mysql.replication"

two

Indicates success

Add monitoring items and triggers to the monitoring interface

When the returned value is less than 2, there is only 1 YES or 0 YES, which means that the master-slave synchronization of MYSQL is abnormal, that is, an alarm is generated.

At this point, stop slave is executed from the library

Received an email

Start salve start slave

Receive a reply email

Increase the success of master-slave monitoring