From Superk
#!/usr/bin/env python
"""
This simple script is designed to extract the System Variables from MySQL that
report it's current running status and then write those values back to another
DB as a record. These records over time can be used to track trends of the MySQL
server's health. While the cost of performing the query/write from/to the MySQL
server may bias the results some (ie, add additional load to the server that
wouldn't otherwise be there), it should be exteremly minimal (we're only talking
two queries).
"""
import MySQLdb, MySQLdb.cursors
class sql:
""" Class to handle SQL methods """
def dbConnect(self, host, user, passwd, db):
""" Create a DB connection """
db = MySQLdb.connect(host=host, user=user, passwd=passwd, db=db)
return db.cursor(cursorclass=MySQLdb.cursors.DictCursor)
def getStats(self, cur):
""" Get the server stats """
cur.execute("SHOW STATUS")
return cur.fetchall()
def updateDB(self, qs, cur):
""" Add stats to the DB """
cur.execute(qs)
class query:
""" SQL Update methods """
def buildQuery(self, res):
""" Build the query string """
qs = "INSERT INTO running_stats SET timestamp=NOW(), "
for i in res[:-1]:
qs += i['Variable_name'] + '="' + i['Value'] + '", '
qs += res[-1]['Variable_name'] + '="' + res[-1]['Value'] + '"'
return qs
def main():
""" Main program """
sqldb = sql()
q = query()
cursor = sqldb.dbConnect("server.someplace.com", "dbuser", "secret_pass", "mysql_stats")
rows = sqldb.getStats(cursor)
sqldb.updateDB(q.buildQuery(rows), cursor)
if __name__ == "__main__":
main()
