python-mysql-repl-monitor

#!/usr/bin/python
"""
python-bottle based MySQL replication monitor.
Configuration can be specified via [server.*] and [bottle] directivesin /etc/rack_replmon/monitor.conf. The monitor will parse any entry thatbegins with ^server as directives for a server to scan

Valid [server.*] directives:
host, port, user, passwd

Valid [bottle] directives:
host, port

If any server fails to meet the following replication requirements,
an error is returned:

- Slave lag > 60s
- Slave_IO_Thread not running
- Slave_SQL_Thread not running

If all tests succeed, the first line will match ^OK:
If any tests fail, the first line will match ^BAD:
"""

from bottle import route, run, template
import MySQLdb as mdb
import ConfigParser
import sys

class Config(object):

    def __init__(self):
        self.settings = ConfigParser.ConfigParser()
        self.settings.read("/etc/rack_replmon/monitor.conf")

    def set_config(self, section):
        option_dict = {}

        if section in self.settings.sections():
            options = self.settings.options(section)
            for option in options:
                option_dict[option] = self.settings.get(section, option)

        return option_dict


def test_slave(dsn):
    dbh = None

    try:
        dbh = mdb.connect(host = dsn["host"], port = int(dsn["port"]), user = dsn["user"], passwd = dsn["passwd"]);
        cur = dbh.cursor(mdb.cursors.DictCursor)

        cur.execute("SHOW SLAVE STATUS")
        slave_status = cur.fetchall()

        if slave_status[0]["Seconds_Behind_Master"] <= 60 and slave_status[0]["Slave_IO_Running"] == "Yes" and slave_status[0]["Slave_SQL_Running"] == "Yes":
            return (1, "+ Checks for server %s:%s completed successfully" % (dsn["host"], dsn["port"]))

        else:
            fail_print = " Relay coordinates: %s %d\n Master coordinates: %s %d\n IO Error: %d -- %s\n SQL Error: %d -- %s" % (slave_status[0]["Relay_Log_File"], slave_status[0]["Relay_Log_Pos"], slave_status[0]["Relay_Master_Log_File"], slave_status[0]["Exec_Master_Log_Pos"], slave_status[0]["Last_IO_Errno"], slave_status[0]["Last_IO_Error"], slave_status[0]["Last_SQL_Errno"], slave_status[0]["Last_SQL_Error"])
            return (0, "x Replication Error: Checks failed for serv %s:%s\n%s" % (dsn["host"], dsn["port"], fail_print))

    except mdb.Error, e:
        return (0, "x Connection Error %d: %s" % (e.args[0],e.args[1]))

    finally:
        if dbh:
            dbh.close()


def enumerate_slaves(config):
    test_success = 1
    test_status_list = []


    server_list = filter(lambda server_match : 'server' in server_match, config.settings.sections())
    for server in server_list:
        dsn = {"host" : "localhost", "port" : 3306, "user" : "root", "passwd" : ""}
        dsn.update(config.set_config(server))

        (test_result, status_string) = test_slave(dsn)
        if test_result == 0:
            test_success = 0
        test_status_list.append(status_string)

    if test_success == 1:
        test_status_list.insert(0, "OK: All checks completed successfully\n")
    else:
        test_status_list.insert(0, "BAD: At least one check failed\n")

    return '\n'.join(test_status_list) + '\n'


config = Config()

@route('/')
def index(slave_status = enumerate_slaves(config)):
    return template('{{slave_status}}', slave_status = slave_status)

bottle_server = {"host" : "localhost", "port" : 8080}
bottle_server.update(config.set_config("bottle"))
run(host = bottle_server["host"], port = bottle_server["port"])