Check replication MySQL master-master via Zabbix

After setting up a master-master replication of MySQL, the next logical step is to set up verification of this system. Reading tutorials on the Internet, it became clear that such replication is a popular affair, and it flies two or three times. Therefore, I decided to insert a check whether replication is still working or something has happened and everything has flown down.
The first few Google queries showed that it is impossible to believe “SHOW SLAVE STATUS \ G”: Stuck is good, but it often lies a lot. A little thought, I came to the following decision:

* A database is created on two servers, a label in it in which both servers are written to the crown. Replication should automatically transfer data from one server to another.
* Writes a script that is called on the crown to update the data.
* A script is written that reads the tablet and watches when the data from the “other” server was last updated. If everything is good, it writes 1, if not - 0 (this status is easier to check with Zabbiks)
* Fits your UserParameter into the zabbiks agent config. Added triggers in zabbiks
*Coffee

Training

On both servers we create a database and tables:

create database repl_check;
use repl_check;
create table repl_status(s_id int, server_time TIMESTAMP);
insert into repl_status values(1,NOW());

Do not forget to include this database in the replication, you need to add the line to /etc/my.cnf
binlog-do-db=repl_check

Restart both mysql servers.
')

We give signs of life

Each server has its own config, and a unique server_id. Initially I tried to use the global @@ server_id, which is defined in my.cnf for replication, but the binary log transmits the command completely, and each server executes it for a different server_id, (Ie, the first one updates the line where server_id = 1, and the second one where server_id = 2), and I decided not to suffer, but simply to enter unique server_id for each server.
max_seconds_diff is the maximum difference to which the server thinks everything is fine. In principle, there may be a small value, but I decided not to fall into fanaticism. If there is a real problem, I will find out in 3 minutes anyway.

config.php
<?php
$username="root";
$password="";
$dbname="repl_check";
$server_id=1;
$max_seconds_diff = 130;
?>

repl_update.php
<?php
include('config.php');

$sql_conn = mysql_connect("localhost",$username,$password);
if ($sql_conn == NULL)
die("failed connecting");
mysql_select_db($dbname);

$sql_query = "update repl_status set server_time=NOW() where s_id like " . intval($server_id);
mysql_query($sql_query,$sql_conn);

mysql_close($sql_conn);
?>

We add it to kroner. Now every minute both servers will update the database, each line with its own server_id
crontab -e
* * * * * php /home/myuser/scripts/check_repl/repl_update.php
Everything, the plate is updated every minute.

Recognize signs of life.

check_repl.php
<?php
include('config.php');

$sql_conn = mysql_connect("localhost",$username,$password);
if ($sql_conn == NULL)
die("failed connecting");
mysql_select_db($dbname);

$time_result = mysql_query("select time_to_sec(timediff(NOW(), max(server_time))) as serv_time_diff from repl_status where s_id != " . intval($server_id));
$result_row = mysql_fetch_array($time_result, MYSQL_ASSOC);
$seconds_diff = intval($result_row["serv_time_diff"]);

mysql_free_result($time_result);
mysql_close($sql_conn);

if ($seconds_diff > $max_seconds_diff)
print "0"; //bad
else
print "1"; //good!
?>

For convenience, I have added a script next:

check_repl.sh
#!/bin/sh
php /home/myuser/scripts/check_repl/check_repl.php

To check, on one of the servers, you need to start “slave stop”, wait 2-3 minutes and run check_repl.sh. In this case, we should get 0. If we start “slave start”, in a minute the status should equalize.

Zabbiks

This line should be added to /etc/zabbix/zabbix_agentd.conf (or where the config is located)
UserParameter=mysql_repl.check,/home/myuser/scripts/check_repl/check_repl.sh

This means that if zabbix (or zabbix_get) requests an item with the key mysql_repl.check, then our script will start and we will get its value (0 or 1)

After this, you need to restart the agent of zabbiks and you can add item and trigger to zabbiks. In Item, in the Show value field, set the Service state (in this case, 0 is down, 1 up)
mysql_repl.check
And the trigger on it: {MyHost: mysql_repl.check.last (0)} <1

Do not forget that item and trigger must be added to both hosts. Where replication will fly - there will be a trigger.

Everything.
I wish high availability to your mysql servers.