Welcome, guest | Sign In | My Account | Store | Cart

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.

Python, 125 lines
  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[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)

Features :

  • 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.

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.

3 comments

Gabriel Genellina 14 years, 3 months ago  # | flag

I could not determine where Numeric is used (if used at all), and the import line isn't particularly helpful... A dependency with Numeric may be problematic because it is a rather old package.

Also, the array module seems not to be required either.

And the string module has been deprecated eons ago (split and join have been str methods since 2.1 at least, and int() replaced string.atoi around the same time...)

Michael Grünewald 14 years, 3 months ago  # | flag

In addition to what Gabriel Genellina said, this all does not look very pythonic at all. For example, take the following block:

ldir = "cat /etc/mysql/my.cnf|grep log-bin"
ldiro = commands.getoutput(ldir)
ldirop = ldiro.rsplit()
if '/' in ldirop[2]:
        ts = string.split(ldirop[2],'/')
tsrem = ts[0:len(ts) -1]
jtsrem = string.join(tsrem,"/")
  • Why are you using rsplit instead of split?
  • Why do you use the string module sometimes?
  • ts[0:len(ts) -1] is the same as ts[:-1]

This should do the same as yours:

ldir = "cat /etc/mysql/my.cnf|grep log-bin"
ldiro = commands.getoutput(ldir)
ldirop = ldiro.split()
if '/' in ldirop[2]:
        ts = ldirop[2].split('/')
jtsrem = "/".join(ts[:-1])

There are many other points in the rest of the code. For example list([0, 0, 0]) does the same thing as [0, 0, 0] and range(0,numrows) can be written shorter as range(numrows). Python has a very powerful slicing, so you can remove concatenations num = dfhintop[0] + dfhintop[1] in favor of num = dfhintop[0:2].

By using the power of Python, you really could increase the readability of the program.

Umang Gopani (author) 14 years, 3 months ago  # | flag

Thanks for reviewing this code. I have had my first hands on python with this code. WIll be using your suggestions in this code.