This article applies to PRTG Network Monitor 12 or later

Monitoring MySQL Data Replication

This can be achieved using a custom script on your Linux server. The script will check if the replication was successful, then returning the respective result on an http web page.

Please make sure this script can be run by calling a certain URL. It should return a web page showing numeric status results in the format, for example, like this: [3][0][0][0]

Once this URL works, use it in combination with PRTG's HTTP Content Sensor, in order to integrate the replication check in your existing monitoring. With each sensor scan the script the URL will be called, thus the script will run, check for successful replication, and return the simple web page shown above.

Example Script: Python

In the following we provide a sample Python script that comes with absolutely no warranty! We provide it for your information to illustrate the general concept. You will have to adapt the script to fit your own needs.

# -*- coding: utf-8 -*-
"""
    paessler.utils.system.service
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    :Authors:   Michael Zeidler 
    :Organization: Paessler AG
    :Copyright: 2012 Paessler AG
"""

# Imports
# -------
import re
import shlex
import logging
import subprocess

from django.db import connections
from itertools import izip


# Constants
# ---------
logger = logging.getLogger('paessler')

LIMIT_OUTOF_SYNC_SECONDS    = 60*5 # as the monitoring check is executed every 5 minutes
MYSQL_SHOW_SLAVE_STATUS     = 'SHOW SLAVE STATUS'
MYSQL_SHOW_MASTER_STATUS    = 'SHOW MASTER STATUS'


def get_mysql_master_replication_status():
    cursor = connections['replication_master'].cursor()
    cursor.execute(MYSQL_SHOW_MASTER_STATUS)
    row = cursor.fetchone()
    col_names = [desc[0] for desc in cursor.description]
    return dict(izip(col_names, row))
    
def get_mysql_slave_replication_status():
    cursor = connections['replication_slave'].cursor()
    cursor.execute(MYSQL_SHOW_SLAVE_STATUS)
    row = cursor.fetchone()
    col_names = [desc[0] for desc in cursor.description]
    return dict(izip(col_names, row))


class MySQLDatabaseReplicationValidation(object):    

    def validate(self):
        """Validates the slave, master and slave-master"""
        errors = {}
        master_res, master_errors = self.validate_master()
        slave_res, slave_errors = self.validate_slave()
        errors.update(master_errors)
        errors.update(slave_errors)

        if master_res and slave_res:
            master_position     = master_res['Position']
            master_binlog_file  = master_res['File']
            slave_master_log_file = slave_res['Master_Log_File']
            slave_read_master_log_position    = slave_res['Read_Master_Log_Pos']
            
            if master_binlog_file != slave_master_log_file:
                errors.update({'MASTER_SLAVE_ERROR': "master_log_file '%s' != slave_log_file: '%s'" % (master_binlog_file, slave_master_log_file)})
        return errors

    def validate_master(self):
        errors = {}
        try:
            res = get_mysql_master_replication_status()
        except Exception, e:
            errors = {'MASTER_CONNECTION_ERROR': str(e)}
            return None, errors
        position            = res['Position']
        binlog_do_db        = res['Binlog_Do_DB']
        binlog_file         = res['File']
        binlog_ignore_db    = res['Binlog_Ignore_DB']
        return res, errors

    def validate_slave(self):
        errors = {}
        try:
            res = get_mysql_slave_replication_status()
        except Exception, e:
            errors = {'SLAVE_CONNECTION_ERROR': str(e)}
            return None, errors
        slave_io_state              = res['Slave_IO_State']
        slave_io_running            = res['Slave_IO_Running']
        slave_sql_running           = res['Slave_SQL_Running']

        master_log_file             = res['Master_Log_File']
        read_master_log_position    = res['Read_Master_Log_Pos']

        last_errno                  = res['Last_Errno']
        last_error                  = res['Last_Error']
        last_io_errno               = res['Last_IO_Errno']
        last_io_error               = res['Last_IO_Error']
        last_sql_errno              = res['Last_SQL_Errno']
        last_sql_error              = res['Last_SQL_Error']

        seconds_behind_master       = res['Seconds_Behind_Master']

        if seconds_behind_master > LIMIT_OUTOF_SYNC_SECONDS:
            errors.update({'SLAVE_OUT_OF_SYNC_LIMIT_EXCEEDED':seconds_behind_master})
        if slave_io_running != 'Yes':
            errors.update({'SLAVE_IO_RUNNING': False})
        if slave_sql_running != 'Yes':
            errors.update({'SLAVE_IO_RUNNING': False})
        if last_errno > 0:
            errors.update({'SLAVE_ERROR': "%d : %s" % (last_errno, last_error)})
        return res, errors