mystatus.py

#!/usr/bin/env python
# coding: utf-8

"""mystatus - Casual MySQL status check
"""

import os
import sys
import re
from time import sleep, strftime
from ConfigParser import SafeConfigParser
from optparse import OptionParser

try:
    import MySQLdb  # MySQL-python
except ImportError:
    try:
        import pymysql as MySQLdb  # PyMySQL
    except ImportError:
        print "Please install MySQLdb or PyMySQL"
        sys.exit(1)



def connect(conf='~/.my.cnf', section='DEFAULT'):
    """
    connect to MySQL from conf file.
    """
    parser = SafeConfigParser()
    parser.read([os.path.expanduser(conf)])

    host = parser.get(section, 'host')
    user = parser.get(section, 'user')
    password = parser.get(section, 'password')
    if parser.has_option(section, 'port'):
        return MySQLdb.connect(host=host, user=user, passwd=password, port=port)
    else:
        return MySQLdb.connect(host=host, user=user, passwd=password)


_status_for_check = "Queries Slow_queries Com_select Com_delete Com_insert Com_update".split()
_cmd = "show global status"

def get_status(con):
    con.query(_cmd)
    return dict((row['Variable_name'], int(row['Value']))
                    for row in con.store_result().fetch_row(maxrows=200, how=1)
                        if row['Variable_name'] in _status_for_check)

def build_option_parser():
    parser = OptionParser()
    parser.add_option(
            '-c', '--config',
            help="read MySQL configuration from. (default: '~/.my.cnf'",
            default='~/.my.cnf'
            )
    parser.add_option(
            '-s', '--section',
            help="read MySQL configuration from this section. (default: '[DEFAULT]')",
            default="DEFAULT"
            )
    parser.add_option(
            '-i', '--interval',
            help="Interval of executing 'show global status' [sec] (default: 10.0)",
            type="float", default=10.0
            )
    return parser


def main():
    parser = build_option_parser()
    opts, args = parser.parse_args()

    try:
        con = connect(opts.config, opts.section)
    except Exception, e:
        parser.error(e)

    prev = get_status(con)

    while True:
        sleep(opts.interval)
        print strftime('== %Y-%m-%d %H:%M:%S')
        for name,value in get_status(con).iteritems():
            print "%12s: %4d" % (name, value-prev[name])
            prev[name] = value

if __name__ == '__main__':
    main()