Mysql utility: purge master binlogs after as soon as slave has received them

mysql-master-binlog-purge

#!/bin/bash
#
# This utility is thought to be run on a Mysql Slave and enables you to safely
# purge (delete) binary logs by ensuring that any files which are in use or
# required by the slave in a replication topology are not deleted.
# This is achieved by checking which binary logs have been read on the slave.
# This determines the minimal set of binary log files that can be purged.
#
# Inspired by mysqlbinlogpurge that cannot run on centos 6
# https://dev.mysql.com/doc/mysql-utilities/1.6/en/mysqlbinlogpurge.html
#
# PAY ATTENTION: the only mysql topology supported is with 1 master and
# 1 slave: DON'T use this script if you have more than one slave!
#
# Tested on mysql 5.5.x
#
# SETUP
# -----
#
# 1. download bash library from https://github.com/lorello/lib.sh in
#    /usr/local/lib/bash/lib.sh
# 2. download this script in /usr/local/bin on the SLAVE MySQL server
# 3. ensure that slave host can execute ssh commands on master without
#    interactive login (setup ssh key-based login, man ssh-copy-id)
# 4. ensure that mysql command on the MASTER MySQL Server can be run
#    without specifying credentials (put them in ~/.my.cnf)
#

set -e

. $(dirname $(readlink -f $0))/../lib/bash/lib.sh || exit

# uncomment next line if you schedule this script in crontab:
# the script will be silent unless some error happens
# LOG_LEVEL=$LOGLEVEL_ERROR



# get a value from slave status
function getSlaveStatusInfo()
{
        [ "x$1" == "x" ] && return 1
        NAME=$1
        VALUE=$(mysql -e 'show slave status\G' | grep -i $NAME | awk '{ print $2 }')
        echo $VALUE
}

function mysql.sshExec()
{
        HOST="$1"
        COMMAND="$2"
        ssh $HOST "mysql -e \"$COMMAND\""
        if [ $? -eq 0 ]; then
                log "Successfully run remote command on '$HOST': '$COMMAND'"
        else
                log_error "ERROR running '$COMMAND' on '$HOST'"
        fi
}

function ssh.remoteExec()
{
        HOST="$1"
        shift
        COMMAND="$*"
        ssh $HOST "$COMMAND"
        if [ $? -gt 0 ]; then
                log_error "ERROR running '$COMMAND' on '$HOST'"
        fi
}



# get some status infos about slave and master
MASTER_ACTIVE_BINLOG_FILE=$(getSlaveStatusInfo 'Relay_Master_Log_File')
SLAVE_IO_THREAD_STATUS=$(getSlaveStatusInfo 'Slave_IO_Running')
SLAVE_SQL_THREAD_STATUS=$(getSlaveStatusInfo 'Slave_SQL_Running')
SLAVE_LAG=$(getSlaveStatusInfo 'Seconds_Behind_Master')
MASTER_HOST=$(getSlaveStatusInfo 'Master_Host')
MASTER_USER=$(getSlaveStatusInfo 'Master_User')
MASTER_PORT=$(getSlaveStatusInfo 'Master_Port')

MASTER_BINLOG_SPACE_USAGE_BEFORE=$(ssh.remoteExec $MASTER_HOST 'du -hc /var/log/mysql/mysql-bin*| tail -1' | cut -d$'\t' -f1)
log "Connection to master: $MASTER_USER@$MASTER_HOST:$MASTER_PORT"
log "Master active binlog file: $MASTER_ACTIVE_BINLOG_FILE"
log "Disk space used by binlogs on Master: $MASTER_BINLOG_SPACE_USAGE_BEFORE"
log "Slave IO Thread running? $SLAVE_IO_THREAD_STATUS"
log "Slave SQL Thread running? $SLAVE_SQL_THREAD_STATUS"
log "Slave LAG (seconds behind master): $SLAVE_LAG"


mysql.sshExec $MASTER_HOST "PURGE BINARY LOGS TO '$MASTER_ACTIVE_BINLOG_FILE';"

MASTER_BINLOG_SPACE_USAGE_AFTER=$(ssh.remoteExec $MASTER_HOST 'du -hc /var/log/mysql/mysql-bin*| tail -1' | cut -d$'\t' -f1)
log "Disk space used by binlogs on Master: $MASTER_BINLOG_SPACE_USAGE_AFTER"