Purge Mysql binary logs (Python recipe) by Umang Gopani

ActiveState Code (http://code.activestate.com/recipes/577004/)

#!/usr/bin/python
#Name: Umang Gopani
#Purpose: Purging Binary logs from Master DBs.
#Disclaimer:Obviously, you use this document at your own risk. I am not responsible for any damage or injury caused by your use of this document, or #caused by errors  and/or omissions in this document. If that's not acceptable to you, you may not use this document. By using this document you are #accepting this disclaimer.

# Before executing emerge -av mysql-python numeric  (on Gentoo)
# Before executing apt-get install python-mysqldb  and apt-get install python-numeric (on Ubuntu)

import sys
import string
import array
import commands         # API for running unix commands
import MySQLdb          # API for accessing to Mysql database
from Numeric import *

# Database credentials
user = 'username'
password = 'mypassword'
database = 'mysql'

# Function returning the hostnames of slaves connected to the master DB.
def slave_host():
        try:
                db = MySQLdb.connect (host = "localhost",
                                        user = user,
                                        passwd = password,
                                        db = database)
        except MySQLdb.Error, e:
                print "Error %d: %s" % (e.args[0], e.args[1])
                sys.exit(1)
        cursor = db.cursor()
        cursor.execute("show processlist" )     # Get the output of show processlist to find the slave hostname.For connected slave one can find the command column as "Binlog Dump".
        numrows = int(cursor.rowcount)
        cntr = 0
        slave1 = list([0, 0, 0])                # Initialising the slave1 variable for stopring the hostnames for slaves.RIght now it is limited to 3 slaves.
        for x in range(0,numrows):
                result1 = cursor.fetchone()
                if result1[4] == "Binlog Dump":
                        if ':' in result1[2]:           # Output is seen in the form of hosname:port number.
                                sslave = string.split(result1[2],':')           # Seperating port number from hostname.
                                slave1[cntr] = sslave[0]
                        cntr = cntr + 1
                        return slave1                           # Returning the name of the slave hostnames.
                else:
                        return 0


# This section gives the output as the fullpath of the directory in which the bin-logs reside on the Master.The information is extraceted form the mysql config file.
ldir = "cat /etc/mysql/my.cnf|grep log-bin"
ldiro = commands.getoutput(ldir)
ldirop = ldiro.rsplit()
if '/' in ldirop[2]:            # Output of log-bin gives the full path name icluding the filename.
        ts = string.split(ldirop[2],'/')        # Seperating the filename, since it is not desired to check the disk space.
tsrem = ts[0:len(ts) -1]
jtsrem = string.join(tsrem,"/")         # Joining the string with "/" to give the full path of the directory.


# This section takes the full pathname form the previous section and checks the size of that partition using df -h
dfh = 'df -h %s' % jtsrem
dfho = commands.getoutput(dfh)
dfhop = dfho.rsplit()
dfhintop =  dfhop[11]           # Check the 11th variable in the array for percentage of size used.
num = dfhintop[0] + dfhintop[1]
if dfhintop[1] == "%":          # If the output is in 1 digit  i.e is less than 10%, then purgning is not required.
        print "Less than 10% of disk space being used.Aborting purgin of logs."
        sys.exit(0)
size = string.atoi(num)

# This section check for the latest master bin log file being used by the slave and gives it as the output.
if  slave_host() != 0:
        uhost = slave_host()            # Save the returned value  from the function slave_host into a variable.
#       print "Slave host name : %s" % uhost
        slvlog = list([0, 0, 0])
        for i in range(0,len(uhost)):
                if uhost[i] != 0:       # To keep a check on the number of slaves.If there is only 1 slave get the name and stop the loop.
                        rhost = uhost[i]
                        srhost = str(rhost)
                        try:
                                db = MySQLdb.connect (host="%s" % (srhost[0:]),
                                        user = user,
                                        passwd = password,
                                        db = database)
                        except MySQLdb.Error, e:
                                print "Error %d: %s" % (e.args[0], e.args[1])
                                sys.exit(1)
                        cursor = db.cursor()
                        cursor.execute("show slave status" )
                        result = cursor.fetchone()
                        slvlog[i] = result[9]
else:
        print "No slave connected to this master.Aborting purgin of logs.Will try again in next run."
        sys.exit(0)
flog = slvlog[i]        # Store the result into a variable. This is useful if there is only 1 slave to the master and the next loop is not followed.

# If the slaves are more than 1, compare which is the latest file being used by any slave.This is to avoid purgning a file being used by any other slave.
if i > 1:
        for x in range(0,i):
                if slvlog[x] != 0:
                        if slvlog[x] != slvlog[x+1]:
                                if slvlog[x] > slvlog[x+1]:
                                        flog = slvlog[x]
                                else:
                                        flog = slvlog[x+1]

final = str(flog)       # The final name of the file upto which teh logs needs to be purged have been stored in "final " variable.

# This is the section which will use the outputs form all the previous section and purge the binary logs if the disk space used is greater than 50%.
if size > 50:
        try:
                db = MySQLdb.connect (host="localhost",
                        user = user,
                        passwd = password,
                        db = database)
        except MySQLdb.Error, e:
                print "Error %d: %s" % (e.args[0], e.args[1])
                sys.exit(1)
        cursor = db.cursor()
        sql = """ purge master logs to '%s' """ % (final[0:])   # Mysql command to purge binary logs from master.
#       print "Output is : %s" % sql
        cursor.execute(sql)
        print "Binary logs from master have been purged upto %s." % final
        sys.exit(0)
else:
        print "Less than 50% of disk space bein used.Aborting purging of logs."
        sys.exit(0)