reptile

Reptile is an easy to use utility that will monitor your MySQL replication, so you can forget about it and focus on the good stuff.

STATUS DISCLAIMER:

I’m not sure if anybody else uses this. I use it to monitor production MySQL database replication.

I am planning to simplify this significantly, but I won’t likely get to them soon. I MIGHT fix a patch you submit. Drop me a line, and I’ll answer any questions you have.

NCS 3/2011

The –diff check can cause problems on large tables, as it does a lot of SELECT COUNT(*) FROM..‘s .

REQUIREMENTS:

A mysql replication setup.

SYNOPSIS

Put a config file at /etc/reptile.yml, /etc/reptile/reptile.yml, ./reptile.yml, or specify one on the command line.

[nick@s34 ~]$ replication_status -h
Usage: replication_status [path_to_config_file]
    -h, --help                       Displays this help info
        --status                     Displays the slave status
        --diff                       Checks the row count difference between master and slave
        --report                     Prints a report
        --heartbeat                  Checks the heartbeat timestamp difference between master and slave
        --stop-slaves                Stops all slaves
        --start-slaves               Starts all slaves
    -l, --log-level [LEVEL]          Specify log level (debug,info,warn,error,fatal)

[nick@s34 ~]$ replication_status --status
  * a_database slave is up and running
  * b_database slave is up and running
  * c_database slave is up and running

Super simple crontab setup

[nick@s34 ~]$ sudo crontab -l
Password:
MAILTO="[email protected]"
# Use the '-l error' arg to only spit out errors, which will be mailed by crontab
*/15 * * * * replication_status -l error
# Or, specify certain checks
# */15 * * * * replication_status -l error --status

Setup SQL

Run this SQL to setup the DBs/perms for Reptile. Using different users, since each user requires significantly different permissions.

GRANT REPLICATION SLAVE, REPLICATION CLIENT, SUPER ON *.* TO 'repl'@"localhost" IDENTIFIED BY 'repl_user_pass';
GRANT REPLICATION SLAVE, REPLICATION CLIENT, SUPER ON *.* TO 'repl'@"MONITOR_HOST_IP" IDENTIFIED BY 'repl_user_pass';

GRANT SELECT, REPLICATION CLIENT ON *.* TO 'ro_user'@"localhost" IDENTIFIED BY 'ro_user_pass';
GRANT SELECT, REPLICATION CLIENT ON *.* TO 'ro_user'@"MONITOR_HOST_IP" IDENTIFIED BY 'ro_user_pass';

CREATE DATABASE replication_monitor;

GRANT SELECT, INSERT, UPDATE, ALTER ON replication_monitor.* TO 'heartbeat_user'@"localhost" IDENTIFIED BY 'heartbeat_user_pass';
GRANT SELECT, INSERT, UPDATE, ALTER ON replication_monitor.* TO 'heartbeat_user'@"MONITOR_HOST_IP" IDENTIFIED BY 'heartbeat_user_pass';

CREATE TABLE replication_monitor.heartbeats (
  unix_time INTEGER NOT NULL,
  db_time TIMESTAMP NOT NULL,
  INDEX time_idx(unix_time)
)

INSTALL:

sudo gem install reptile