mysql / munin

MySQL slave monitoring wihth Munin

We have a couple of MySQL slave servers running debian here at Skroutz and I was searching for a way to monitor the service’s status. I run munin agents on all servers so I wrote a small python script to graph the slave’s status.

The script uses the debian-sys-maint user to connect to the database so it needs to be configured to be run as root from Munin.

#!/usr/bin/env python
# GPLv2
# Configuration
#   [mysql_*]
#       user root
#       env.mysqlopts "mysql options here"
#
#
#%# family=auto
#%# capabilities=autoconf

import sys,os

def autoconfigure():

    try:
        import MySQLdb
        cnfpath = os.environ['mysqlopts'].split('=')[-1]
        conn = MySQLdb.connect(read_default_file=cnfpath)
        cur = conn.cursor(MySQLdb.cursors.DictCursor)
        cur.execute("SHOW SLAVE STATUS")
        if cur.rowcount == 0:
            print "no (Slave not configured?)"
            cur.close()
            conn.close()
            sys.exit(0)

        cur.close()
        conn.close()
    except ImportError:
        print "no (MySQLdb module missing)"
        sys.exit(0)
    except MySQLdb.OperationalError,e:
        print "no (%d, %s)" % (e.args[0],e.args[1])
        sys.exit(0)


    print "yes"

def config():

    print "graph_title Seconds behind Master"
    print "graph_vlabel seconds"
    print "graph_args -l 0"
    print "secs_behind.label seconds behind"
    print "sec_behind.draw LINE3"
    print "graph_category mysql"
    print "secs_behind.warning 60"
    print "secs_behind.critical 90"

def seconds_behind_master():
    import  MySQLdb
    cnfpath = os.environ['mysqlopts'].split('=')[-1]
    conn = MySQLdb.connect(read_default_file=cnfpath)
    cur = conn.cursor(MySQLdb.cursors.DictCursor)
    cur.execute("SHOW SLAVE STATUS")
    row = cur.fetchone()
    print "secs_behind.value %s" % (row['Seconds_Behind_Master'])
    cur.close()
    conn.close()

if __name__ == "__main__":

    if len(sys.argv) == 2:
        if sys.argv[1] == "config":
            config()
        elif sys.argv[1] == "autoconf":
            autoconfigure()
        else:
            seconds_behind_master()