# É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.
```sql
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.
```shell
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`:
```shell
[nagios]
name = MariaDB Replication <hostname>
max_lag = 60
warning = 15000
critical = 20000
timestamp_path = timestamp.txt
[database]
host = localhost
database = heartbeat
port = 3306
charset = utf8mb4
collation = utf8mb4_general_ci
supplement = credentials.cfg
```
The values of `host`, `port`, `charset`, and `collation` in the `database` section are the defaults and can be omitted.
Create the file `/etc/nagios/credentials.cfg` for storing the credentials of the replication monitoring user:
```ini
[database]
user = rep_monitor
password = secret
```
Set the proper mode and ownership of `/etc/nagios/credentials.cfg` by executing the following commands.
```shell
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.
```sql
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.
```shell
systemctl reload nrpe
```
License
-------
This project is licensed under the terms of the MIT license.
Raw data
{
"_id": null,
"home_page": "https://github.com/NagiosElenchos/check-mymaria-replication",
"name": "elenchos-check-mymaria-replication",
"maintainer": null,
"docs_url": null,
"requires_python": "<4.0.0,>=3.10.0",
"maintainer_email": null,
"keywords": "\u00c9lenchos, Elenchos, MySQL, MariaDB, Replication",
"author": "Set Based IT Consultancy",
"author_email": "info@setbased.nl",
"download_url": "https://files.pythonhosted.org/packages/15/c9/5e820976e385f3f22ff486bc8c431f7f4ca5ba43f8305feecffadc93a9cc/elenchos_check_mymaria_replication-1.2.0.tar.gz",
"platform": null,
"description": "# \u00c9lenchos: Check MySQL and MariaDB Replication\n\nA \u00c9lenchos command for checking the replication of a MySQL or MariaDB instance.\n\n## Installation and Configuration\n\n### Primary Instance\n\nOn the primary instance create a heartbeat by executing the following SQL commands.\n\n```sql\ncreate schema heartbeat;\n\nuse heartbeat;\n\ncreate table heartbeat\n(\n heartbeat datetime not null\n) engine = InnoDB;\n\ninsert into heartbeat(heartbeat)\nvalues (now());\n\ncreate definer=root@localhost event heartbeat\non schedule every 1 minute starts '2016-02-07 09:22:34'\non completion preserve enable\ncomment 'Heartbeat for replication monitoring.'\ndo\nupdate heartbeat\nset heartbeat = now();\n```\n\nUsing this heartbeat is more reliable than `Seconds_Behind_Master` as provided by the `show replica status` SQL command\nwhich is `NULL` when the SQL thread is not running.\n\n#### Replica Host\n\nOn the host where the replication instance is running, install \u00c9lenchos by executing the following commands.\n\n```shell\ncd /opt\n\nmkdir elenchos\ncd elenchos\n\npython -m venv .venv\nmkdir bin\nln -s ../.venv/bin/elenchos bin/elenchos\n\n. .venv/bin/activate\npip install elenchos-check-mymaria-replication\n/opt/elenchos/bin/elenchos gather-commands\n```\n\nCreate the configuration file `/etc/nagios/replication.cfg`:\n\n```shell\n[nagios]\nname = MariaDB Replication <hostname>\nmax_lag = 60\nwarning = 15000\ncritical = 20000\ntimestamp_path = timestamp.txt\n\n[database]\nhost = localhost\ndatabase = heartbeat\nport = 3306\ncharset = utf8mb4\ncollation = utf8mb4_general_ci\nsupplement = credentials.cfg\n```\n\nThe values of `host`, `port`, `charset`, and `collation` in the `database` section are the defaults and can be omitted.\n\nCreate the file `/etc/nagios/credentials.cfg` for storing the credentials of the replication monitoring user:\n\n```ini\n[database]\nuser = rep_monitor\npassword = secret\n```\n\nSet the proper mode and ownership of `/etc/nagios/credentials.cfg` by executing the following commands.\n\n```shell\nchmod 400 /etc/nagios/credentials.cfg\nchown nrpe.nrpe /etc/nagios/credentials.cfg\n```\n\nCreate the configuration file `/etc/nrpe.d/check_mysql_replication.cfg` for `nrpe`:\n\n```\ncommand[check_mysql_replication]=/opt/elenchos/bin/elenchos check:mariadb-replication /etc/nagios/replication.cfg\n```\n\nCreate the user for monitoring the replication by executing the following SQL statements.\n\n```sql\ncreate user `rep_monitor`@`localhost` identified by password('secret') with max_user_connections 1;\ngrant binlog monitor, slave monitor on *.* to `rep_monitor`@`localhost`;\ngrant select on heartbeat.heartbeat to rep_monitor@localhost;\n```\n\nFinally, restart the `nrpe` daemon.\n\n```shell\nsystemctl reload nrpe\n```\n\nLicense\n-------\n\nThis project is licensed under the terms of the MIT license.\n",
"bugtrack_url": null,
"license": "MIT",
"summary": "\u00c9lenchos: Check MySQL and MariaDB Replication",
"version": "1.2.0",
"project_urls": {
"Documentation": "https://github.com/NagiosElenchos/check-mymaria-replication",
"Homepage": "https://github.com/NagiosElenchos/check-mymaria-replication",
"Repository": "https://github.com/NagiosElenchos/check-mymaria-replication"
},
"split_keywords": [
"\u00e9lenchos",
" elenchos",
" mysql",
" mariadb",
" replication"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "df79a9648be2b57e6cc00da4be1b4451f5951fd68c396bd84fe1e2cc8fa66512",
"md5": "4ed50eab1c2e279afc83c73c003f86aa",
"sha256": "a44fb2e8a6a5ee3a5c67969a73b15e49dd38b3aa936d23a4b9bffc97a58fb487"
},
"downloads": -1,
"filename": "elenchos_check_mymaria_replication-1.2.0-py3-none-any.whl",
"has_sig": false,
"md5_digest": "4ed50eab1c2e279afc83c73c003f86aa",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": "<4.0.0,>=3.10.0",
"size": 7291,
"upload_time": "2024-09-20T10:54:44",
"upload_time_iso_8601": "2024-09-20T10:54:44.489301Z",
"url": "https://files.pythonhosted.org/packages/df/79/a9648be2b57e6cc00da4be1b4451f5951fd68c396bd84fe1e2cc8fa66512/elenchos_check_mymaria_replication-1.2.0-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "15c95e820976e385f3f22ff486bc8c431f7f4ca5ba43f8305feecffadc93a9cc",
"md5": "09097b031393c4af67dcf3de34dd091d",
"sha256": "bc3e379e638a054a32fa3b795d1a3f27b377edfe27662c1d16dbc63ba2702408"
},
"downloads": -1,
"filename": "elenchos_check_mymaria_replication-1.2.0.tar.gz",
"has_sig": false,
"md5_digest": "09097b031393c4af67dcf3de34dd091d",
"packagetype": "sdist",
"python_version": "source",
"requires_python": "<4.0.0,>=3.10.0",
"size": 5504,
"upload_time": "2024-09-20T10:54:46",
"upload_time_iso_8601": "2024-09-20T10:54:46.134755Z",
"url": "https://files.pythonhosted.org/packages/15/c9/5e820976e385f3f22ff486bc8c431f7f4ca5ba43f8305feecffadc93a9cc/elenchos_check_mymaria_replication-1.2.0.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2024-09-20 10:54:46",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "NagiosElenchos",
"github_project": "check-mymaria-replication",
"travis_ci": false,
"coveralls": false,
"github_actions": false,
"lcname": "elenchos-check-mymaria-replication"
}
JSON
