Being a MySQL DBA , one faces a common issue in replication environment -> Disk space issue on master, since the number of binary logs have increased. Now, one of the solution to this would be using expire_logs_days parameter in your mysql config file. But what if, the slave is lagging by few hours or if the slave is broken since few days and the binary logs are removed due to the parameter set. Whenever the salve comes up, it will go bonkers, knowing that the binary log where it last stopped no more exists.
I faced this issue a couple of time until I decided to automate it using a script. Herewith I am attaching a python script which can run regularly in cron.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125
#!/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, e.args) 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 == "Binlog Dump": if ':' in result1: # Output is seen in the form of hosname:port number. sslave = string.split(result1,':') # Seperating port number from hostname. slave1[cntr] = sslave 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: # Output of log-bin gives the full path name icluding the filename. ts = string.split(ldirop,'/') # 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 # Check the 11th variable in the array for percentage of size used. num = dfhintop + dfhintop if dfhintop == "%": # 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, e.args) sys.exit(1) cursor = db.cursor() cursor.execute("show slave status" ) result = cursor.fetchone() slvlog[i] = result 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, e.args) 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)
- Checks the slaves connected to the master (I have limit it to 3 for now.)
- Checks the last binary log file which is being used by the slave.
- All the binary logs until the last bin log used by slave are purged.
- If a slave is not connected, purging is aborted, so that important bin logs are not purged.
Do run this script on a test machine before using it on any production system. It is alwasy advisable to to take BACKUP of data before running this script.
- Before executing emerge -av mysql-python numeric (on Gentoo)
- Before executing apt-get install python-mysqldb and apt-get install python-numeric (on Ubuntu)
- Modify the username, password, database name as needed.
- Be sure to give the user (as you define), super, select privileges to master and slave.
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.