Purges binary unneeded binary logs from a MySQL master.

mysql_bin_purge.py

#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""MySQL Binary Log Purger

This script handles purging unneeded binary logs from a MySQL master. It
will run on python 2.5+ and requires the oursql library.

In order to configure this script, you need to have a user with the same
username and password on the master and all slaves. The port is assumed
to be the same amongst the master and all slaves, and it is assumed
that the user can login to the slaves from the master server.

As a safety feature, configure the expected number of slaves. If the
number of connected slaves is different then the expected, the script
will exit, and will not purge any logs. This will prevent purging a log
that a currently disconnected slave might need.
"""
from __future__ import with_statement
import sys

import oursql

__author__ = 'Donald von Stufft'
__license__ = 'BSD'
__version__ = '1.0'
__maintainer__ = 'Donald von Stufft'
__email__ = 'donald.stufft@gmail.com'
__status__ = 'Production'


# Database Credentials
USER = 'DATABASE_USER'
PASSWORD = 'DATABASE_PASSWORD'
HOST = 'localhost'
PORT = 3306

# Configuration
EXPECTED_NUMBER_OF_SLAVES = 1

# - Do not edit Below this line - #
slaves = []
slave_logs = {}
purge_log = None

conn = oursql.connect(HOST, USER, PASSWORD, port=PORT)

with conn.cursor(oursql.DictCursor) as cursor:
    cursor.execute('SHOW PROCESSLIST', plain_query=True)
    for process in cursor:
        if process['Command'] == 'Binlog Dump':
            slaves.append(process['Host'].split(':', 1)[0])

if len(slaves) == EXPECTED_NUMBER_OF_SLAVES:
    for slave in slaves:
        slave_conn = oursql.connect(slave, USER, PASSWORD, port=PORT)
        with slave_conn.cursor(oursql.DictCursor) as cursor:
            cursor.execute('SHOW SLAVE STATUS', plain_query=True)
            result = cursor.fetchone()
            if not result is None:
                slave_logs[slave] = result['Relay_Master_Log_File']
            else:
                e = '%s does not return a result for SHOW SLAVE STATUS'
                print >> sys.stderr, e % slave
else:
    e = 'The number of slaves connected to the master is different then' + \
            'expected (%d).'
    sys.exit(e % EXPECTED_NUMBER_OF_SLAVES)

with conn.cursor(oursql.DictCursor) as cursor:
    cursor.execute('SHOW BINARY LOGS', plain_query=True)
    for log in cursor:
        purge_log = log['Log_name']
        if log['Log_name'] in slave_logs.values():
            break

if not purge_log is None:
    print 'Purging binary logs up to but not including %s' % purge_log
else:
    print 'No Logs to Purge.'
    sys.exit(0)

with conn.cursor() as cursor:
    cursor.execute("PURGE BINARY LOGS TO '%s'" % purge_log)
    print 'Binary logs have been purged.'