Check/Optimize/Repair for MySQL

check_optimize.py

#!/usr/bin/enr python
import time
import MySQLdb
import pprint
from optparse import OptionParser

mysql_user = "<USER>"
mysql_host = "localhost"
mysql_pass = "<PASSWORD>"
mysql_db = "<DATABASE>"


pp = pprint.PrettyPrinter()
def init_parser():
	usage = "usage: %prog [options]"
	parser = OptionParser(usage=usage)
	parser.add_option("-c", "--check", help="Check tables only", action="store_true", dest="check", default=False)
	parser.add_option("-r", "--repair", help="Repair broken tables automatically", action="store_true", dest="repair", default=False)
	parser.add_option("-o", "--optimize", help="Optimize tables automatically", action="store_true", dest="optimize", default=False)
	parser.add_option("-i", "--interactive", help="Interactive mode, ask before changing stuff.", action="store_true", dest="interactive", default=False)

	(options,args) = parser.parse_args()
	return (options,args)

def check_optimize(quiet=False):
	fr_db = MySQLdb.connect(host=mysql_host, user=mysql_user, passwd=mysql_pass, db=mysql_db)
	
	fr_cursor = fr_db.cursor()
	fr_dcursor = MySQLdb.cursors.DictCursor(fr_db)
	table_query = "show tables"
	
	fr_cursor.execute(table_query)
	tables = fr_cursor.fetchall()
	optimize = []
	if quiet == False:
		print "Optimization needed:"
	for t in tables:
		fr_dcursor.execute("SHOW TABLE STATUS LIKE '%s'" % t[0])
		result = fr_dcursor.fetchall()
		if result[0]['Data_free'] > 0:
			if quiet == False:
				print result[0]['Name']
			optimize.append(result[0]['Name'])
	return optimize

def check_repair(quiet=False):
	fr_db = MySQLdb.connect(host=mysql_host, user=mysql_user, passwd=mysql_pass, db=mysql_db)
	
	fr_cursor = fr_db.cursor()
	fr_dcursor = MySQLdb.cursors.DictCursor(fr_db)
	table_query = "show tables"
	
	fr_cursor.execute(table_query)
	tables = fr_cursor.fetchall()
	
	repairs = []

	if quiet == False:
		print "Repairs needed:"
	for t in tables:
		fr_dcursor.execute("check table %s" % t[0])
		result = fr_dcursor.fetchall()
		if result[0]['Msg_text'] != "OK":
			if quiet == False:
				print result[0]['Table']
			repairs.append(result[0]['Table'])
	return repairs
	
def optimize(table):
	try:
		fr_db = MySQLdb.connect(host=mysql_host, user=mysql_user, passwd=mysql_pass, db=mysql_db)
		fr_dcursor = MySQLdb.cursors.DictCursor(fr_db)
		fr_dcursor.execute("OPTIMIZE TABLE %s"%table)
		status = fr_dcursor.fetchall()
	except MySQLdb.Error, e:
		print "Error %d: %s" % (e.args[0], e.args[1])

	if status[0]['Msg_text'] == "OK":
		return True
	else:
		print status[0]['Msg_text']
		print "Something's not quite right here, Status != OK"
	return False

def repair(table):
	fr_db = MySQLdb.connect(host=mysql_host, user=mysql_user, passwd=mysql_pass, db=mysql_db)
	fr_dcursor = MySQLdb.cursors.DictCursor(fr_db)
	fr_dcursor.execute("REPAIR TABLE %s"%table)
	status = fr_dcursor.fetchall()
        if status[0]['Msg_text'] == "OK":
                return True
        else:
                print "Something's not quite right here, Status != OK"
		print fr_dcursor.messages
        return False
		

if __name__ == "__main__":
	(options,args) = init_parser()
	if options.check:
		print "Checking Tables"
		check_optimize(quiet=False)
		check_repair(quiet=False)
	elif options.repair:
		print "Repairing broken tables"
		tables = check_repair(quiet=True)
		for t in tables:
			if options.interactive:
				print "About to repair %s, are you sure?!" % t
				answer = raw_input("[y/N]: ")
				if answer.lower() != "y":
					print "OK, nevermind"
				else:
					r = repair(t)
					if r:
						print "Done!"
					else:
						print "Err, dunno what happened there.."
			else:
				print "Repairing %s" % t
				r = repair(t)
				if r:
					print "Done!"
				else:
					print "Err, dunno what happened there.."
		print "All done!"
	elif options.optimize:
		print "Optimizing ugly tables"
		tables = check_optimize(quiet=True)
		for t in tables:
			if options.interactive:
				print "About to optimize %s, are you sure?" % t
				answer = raw_input("[y/N]: ")
				if answer.lower() != "y":
					print "OK, nevermind"
				else:
					r = optimize(t)
					if r:
						print "Done!"
					else:
						print "Err, dunno what happened there.."
			else:
				print "Optimizing %s" % t
				r = optimize(t)
				if r:
					print "Done!"
				else:
					print "Err, dunno what happened there.."
		print "All done!"

	else:
		print "Reticulating Splines..."
		time.sleep(0.5)