Archive

Archive for October, 2011

Python script to clean up binlogs based on number

October 25th, 2011 No comments

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()
Tags:

Just me…

October 17th, 2011 No comments

360903_460s_v1

Tags:

Music…

October 17th, 2011 No comments

Tags:

Want an iPhone 4G upgraded to 4S? No problem.

October 5th, 2011 No comments

Tags:

Delete cookies

October 4th, 2011 No comments

 

 

 

 

 

 

Tags:

My life in a nutshell

October 2nd, 2011 No comments

Tags: