mon monitor plugin to test mysql services

pymysql.monitor.py

#!/usr/bin/env python

from __future__ import with_statement
import contextlib
import logging
import MySQLdb
import optparse
import os
import sys

def monitor(host, port, options):
    result = True
    try:
        logging.debug("Connecting to %s:%d..." % (host, port))
        kwargs = {'host': host, 'port': port}
        if options.user:
            kwargs['user'] = options.user
        if options.passwd:
            kwargs['passwd'] = options.passwd
        if options.defaults_file:
            kwargs['read_default_file'] = options.defaults_file
        with contextlib.closing(MySQLdb.connect(**kwargs)) as client:
            if options.execute:
                result = do_execute(client, options) and result
            if options.master:
                result = do_master(client, options) and result
            if options.slave:
                result = do_slave(client, options) and result
    except Exception, error:
        logging.error(repr(error))
        result = False
    return result

def do_execute(client, options):
    result = True
    logging.debug("Executing query on %s..." % (client.get_host_info(),))
    with contextlib.closing(client.cursor()) as cursor:
        cursor.execute(options.execute)
        all = cursor.fetchall()
        logging.debug("%s: Got %d answers." % (client.get_host_info(), len(all)))
        if len(all) == 0:
            logging.warn("%s: Response is empty." % (client.get_host_info(),))
            result = False
    return result

def do_master(client, options):
    result = True
    logging.debug("Querying master status on %s..." % (client.get_host_info(),))
    with contextlib.closing(client.cursor()) as cursor:
        cursor.execute("SHOW MASTER STATUS;")
        keys = [ name.lower() for (name, type_code, display_size, internal_size, precision, scale, null_ok) in cursor.description ] # PEP 249
        values = cursor.fetchone()
        status = dict(zip(keys, values))
    logging.debug("%s: File => %s, Position => %d" % (client.get_host_info(), status.get('file', 'file'), status.get('position', 0)))
    if len(status) == 0:
        logging.warn("%s: Master status is empty." % (client.get_host_info(),))
        result = False
    return result

def do_slave(client, options):
    result = True
    logging.debug("Querying slave status on %s..." % (client.get_host_info(),))
    with contextlib.closing(client.cursor()) as cursor:
        cursor.execute("SHOW SLAVE STATUS;")
        keys = [ name.lower() for (name, type_code, display_size, internal_size, precision, scale, null_ok) in cursor.description ] # PEP 249
        values = cursor.fetchone()
        status = dict(zip(keys, values))
    slave_io_running = str(status.get('slave_io_running', 'no')).lower()
    slave_sql_running = str(status.get('slave_sql_running', 'no')).lower()
    logging.debug("%s: Slave_IO_Running => %s, Slave_SQL_Running => %s" % (client.get_host_info(), slave_io_running, slave_sql_running))
    if slave_io_running != 'yes' or slave_sql_running != 'yes':
        logging.warn("%s: Slave threads are not running." % (client.get_host_info(),))
        result = False
    last_errno = status.get('last_errno', 0)
    last_io_errno = status.get('last_io_errno', 0)
    last_sql_errno = status.get('last_sql_errno', 0)
    logging.debug("%s: Last_Errno => %d, Last_Error => %s" % (client.get_host_info(), last_errno, status.get('last_error', '')))
    logging.debug("%s: Last_IO_Errno => %d, Last_IO_Error => %s" % (client.get_host_info(), last_io_errno, status.get('last_io_error', '')))
    logging.debug("%s: Last_SQL_Errno => %d, Last_SQL_Error => %s" % (client.get_host_info(), last_sql_errno, status.get('last_sql_error', '')))
    if 0 < last_errno or 0 < last_io_errno or 0 < last_sql_errno:
        logging.warn("%s: Slave errors are reported." % (client.get_host_info(),))
        result = False
    return result

def main(args):
    parser = optparse.OptionParser("usage %prog [OPTIONS]", add_help_option=False)
    parser.add_option('-P', '--password', default=None, dest='passwd', help='password')
    parser.add_option('--defaults-file', default=None, dest='defaults_file', help='defaults_file')
    parser.add_option('-e', '--execute', default=None, dest='execute', help='query to execute')
    parser.add_option('--help', action='help', help='show this message and exit')
    parser.add_option('-m', '--master', default=False, action='store_true', dest='master', help='test master status')
    parser.add_option('-p', '--port', type='int', default=[], action='append', dest='ports', help='MySQL port')
    parser.add_option('-s', '--slave', default=False, action='store_true', dest='slave', help='test slave status')
    parser.add_option('-t', '--timeout', type='int', default=None, dest='timeout', help='timeout')
    parser.add_option('-u', '--user', default=None, dest='user', help='user')
    parser.add_option('-v', '--verbose', default=False, action='store_true', dest='verbose', help='show verbose messages')
    (options, args) = parser.parse_args(args)

    if options.verbose:
        logging.basicConfig(level=logging.DEBUG)

    hosts = args[1:]
    ports = options.ports if 0 < len(options.ports) else [ 3306 ]

    for (host, port) in [ (host, port) for host in hosts for port in ports ]:
        if not monitor(host, port, options):
            sys.exit(1)

if __name__ == "__main__":
    main(sys.argv)

# vim:set ft=python :