elenchos-check-mymaria-replication 1.1.1

Élenchos: Check MySQL and MariaDB Replication

Élenchos: Check MySQL and MariaDB Replication

A Élenchos command for checking the replication of a MySQL or MariaDB instance.

Installation and Configuration

Primary Instance

On the primary instance create a heartbeat by executing the following SQL commands.

create schema heartbeat;

use heartbeat;

create table heartbeat
(
  heartbeat datetime not null
) engine = InnoDB;

insert into heartbeat(heartbeat)
values (now());

create definer=root@localhost event heartbeat
on schedule every 1 minute starts '2016-02-07 09:22:34'
on completion preserve enable
comment 'Heartbeat for replication monitoring.'
do
update heartbeat
set heartbeat = now();

Using this heartbeat is more reliable than Seconds_Behind_Master as provided by the show replica status SQL command which is NULL when the SQL thread is not running.

Replica Host

On the host where the replication instance is running, install Élenchos by executing the following commands.

cd /opt

mkdir elenchos
cd elenchos

python -m venv .venv
mkdir bin
ln -s ../.venv/bin/elenchos bin/elenchos

. .venv/bin/activate
pip install elenchos-check-mymaria-replication
/opt/elenchos/bin/elenchos gather-commands

Create the configuration file /etc/nagios/replication.cfg:

[nagios]
name           = MariaDB Replication <hostname>
max_lag        = 60
warning        = 15000
critical       = 20000
timestamp_path = timestamp.txt

[database]
supplement = credentials.cfg

Create the file /etc/nagios/credentials.cfg for storing the credentials of the replication monitoring user:

[database]
host     = localhost
port     = 3306
user     = rep_monitor
password = secret
database = heartbeat

Set the proper mode and ownership of /etc/nagios/credentials.cfg by executing the following commands.

chmod 400  /etc/nagios/credentials.cfg
chown nrpe.nrpe /etc/nagios/credentials.cfg

Create the configuration file /etc/nrpe.d/check_mysql_replication.cfg for nrpe:

command[check_mysql_replication]=/opt/elenchos/bin/elenchos check:mariadb-replication /etc/nagios/replication.cfg

Create the user for monitoring the replication by executing the following SQL statements.

create user `rep_monitor`@`localhost` identified by password('secret') with max_user_connections 1;
grant binlog monitor, slave monitor on *.* to `rep_monitor`@`localhost`;
grant select on heartbeat.heartbeat to rep_monitor@localhost;

Finally, restart the nrpe daemon.

systemctl reload nrpe

License

This project is licensed under the terms of the MIT license.