#!/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)