Python script to clean up binlogs based on number

I stumbled on a problem in MySQL that you can only rotate binlogs based on age. In certain cases it wasn’t sufficient. Scripts that I’ve found on the net (mysql doc, etc) were all in PHP/perl… So I took a bit of liberty and stole one idea from mysql’s doc comments and turned it into a piece of Python code. (uyes, ai luv python… hisssssssss).

#!/usr/bin/env python
#
# Since MySQL is unable to purge logs based on number of logs and does it only based on days
# I stole this idea from mysql.com. It was in PHP, I rewrote it in Python <3
#
#         vllazarenko@ebay.com
#
# Questions? Do mail or pass by.
#
#
 
import sys
import MySQLdb
 
""" If you want to smurf, smurf over here """
 
slaveHost="localhost"
slaveUser="root"
slavePass="rootpass"
slaveDb=""
 
masterHost="masterhost"
masterUser="root"
masterPass="rootpass"
masterDb=""
 
keepLogs = 5
 
 
""" Please don't smurf after this line, unless you're an experienced smurf """
 
try:
     connSlave = MySQLdb.connect(slaveHost,
        	                 slaveUser,
                	         slavePass,
                        	 slaveDb)
except MySQLdb.Error, e:
     print "SLAVE: Error %d: %s" % (e.args[0], e.args[1])
     sys.exit (1)
 
try:
	connMaster = MySQLdb.connect(masterHost,
        	                     masterUser,
                	             masterPass,
                        	     masterDb)
except MySQLdb.Error, e:
     print "MASTER: Error %d: %s" % (e.args[0], e.args[1])
     sys.exit (1)
 
cursSlave = connSlave.cursor(MySQLdb.cursors.DictCursor)
cursMaster = connMaster.cursor(MySQLdb.cursors.DictCursor)
 
cursSlave.execute("SHOW SLAVE STATUS")
row = cursSlave.fetchone()
 
if row["Slave_IO_Running"] != "Yes":
	print "Alert. Slave IO not running."
if row["Slave_SQL_Running"] != "Yes":
	print "Alert. Slave SQL not running." 
if row["Last_Error"] != "":
	print "Alert. Error on slave: %s" % row["Last_Error"]
 
masterLog = row["Master_Log_File"]
print "Current Master file on which Slave is acting: %s" % masterLog
 
cursMaster.execute("SHOW MASTER LOGS")
rows = cursMaster.fetchall()
 
if cursMaster.rowcount == 0:
	print "No master logs found."
	sys.exit(1)
 
lognames = list()
 
for row in rows:
	lognames.append(row["Log_name"])
 
marker = lognames.index(masterLog)
delMarker = marker - keepLogs;
 
if delMarker < 2:
	print "Not purging below 2 logs."
	sys.exit(1)
 
print "Going to purge logs up to %s." % lognames[delMarker]
 
delQuery = "PURGE MASTER LOGS TO '%s'" % lognames[delMarker]
# cursMaster.execute(delQuery)
 
print "Master binlogs were purged. The oldest log now available on the master is %s." % lognames[delMarker]
 
cursSlave.close()
connSlave.close()
cursMaster.close()
connMaster.close()

Leave a Reply