Remote Binlog Back-up. Quite similar feature is available since 5.6

#!/bin/bash

#
#       |||||||||||
#	-----------
#	  []   []
#	     |
#	   //|\\
#
#    The Binlog Patrol

# @auth: pb@press-service.com.pl
# @background:
# Why do not use rotate?
# 1. Rotate does not turn off the instance.
# 2. Rotate does not know:
#	- which file are to be archive;
#	- the slaves are alive and up to date;
#	- no guarantee that after change of the configuration it includes the current file.
# 2.1 Rotate can:
#	- compress files
#	- move file to specified file into a given localization
# @scenario
# 	1. [done] Get connected slaves
#	2. [done] Check conditions:
#	  2.1 [done] Check maximal delay of the slave;
#	  2.2 [done] Check whether the number of slaves is correct;
#	3. [done] Get actual file 
#	4. [done] Iterate files.
#	5. [done] Bzip the file
#	6. Purge binary logs cause binary logs are purge conform to my.cnf policy (expire_logs_days)
#	7. [done] mv bzipped file to backup
#	8. [done] remove old files (on backup storage)
#	9. [done] Stop the server in case there is no space enough

HOST="$1";
PORT="$2";
# 3 on db1 (db5 returned)
SLAVE_EXPECTED_NO=3
SLAVE_CHECKED_NO=0
SLAVE_CHECKED=""
SLAVE_LAG_GLOBAL=0
LOGBIN_NAME="inforia"
LOGBIN_DIR="/storage_local/mysql/logbin"
# no trailing slash
LOGBIN_DIR_BCK="/backup_nfs/logbin/"
LOGBIN_PATTERN="$LOGBIN_NAME.[0-9]\{1,\}$"
LOGBIN_FILE_NO_MIN=2 # leave the minimal nr of file
LOGBIN_CURRENT_FILE=""

THIS_MASTER_SLAVES='' # Containes master's SHOW SLAVE HOSTS
THIS_MASTER_SLAVES_ERR='' # Contains any error

SCRIPT_FILE="$0"
SCRIPT_FILE_LOG="${SCRIPT_FILE%.*}.log"
SCRIPT_FILE_LOG_MAX_SIZE=100000

CURRENT_TIMESTAMP=$(date '+%s')
LOGBIN_TIMESTAMP_LIMIT=0
TIMESTAMP_TO_ADD=3600 # one hour
MIN_VOLUME_CAPACITY_TO_RESTART_MB=200

function compare_int {
    if [ $1 -gt $2 ]; then
	return 1;
    else
	return 0;
    fi 
}


function strstr ( )
{
  echo $1 | grep --quiet $2
}

function write_to_log {
    local INFO=$1
    local TIME=$(date '+%Y.%m.%d %H:%M');
    echo "[$TIME] $INFO" >> $SCRIPT_FILE_LOG
}
            
function write_to_syslog
{
    logger -p daemon.error -t BINLOG_ROTATE "$1";
}


# cut the file where the scripts logs
function rotate_log
{

    local FILE_LOG="$1"
    local FILE_EXT="${FILE_LOG##*.}"
    local FILE_SIZE=$(stat -c"%s" $FILE_LOG)

    if [ ! "$FILE_EXT" = "log" ]; then
	echo "I cannot truncate file which has another ext than log ($FILE_LOG)";
	return 1
    fi

    if [ "$FILE_SIZE" -gt "$SCRIPT_FILE_LOG_MAX_SIZE" ]; then
	date > "$FILE_LOG"
	echo "File truncatated by script owner" >> "$FILE_LOG"
    fi
    return 0
}

if [ -z $HOST ]; then
    /bin/echo "[Error] no host given (first argument)" > /dev/stderr;
    exit 1;
fi
if [ -z $PORT ]; then
    PORT='3306';
fi

PRINT_RESULT=0;
RESULT="";
if [ "$3" = "print" ]; then
    PRINT_RESULT=1;
fi

SCRIPT_NAME=`basename ${0%.*}`;
STANDARD_IFS=$IFS

TMP_ERROR_DIR="/tmp/";
DIR_REPORT="/var/log/overseer/";
ERR_FILE="$DIR_REPORT""Mysql_"$HOST".Binlog";
# LAG_ERR_FILE="$DIR_REPORT""Mysql_"$HOST".ReplicationLag";

MYSQL_HOMEDIR="/usr/local/services/mysql/bin/";
MYSQL_CMD="mysql";

MY_PASSWD="SWavuwUqupR8___";
MY_USER="overseer.sys";


rotate_log "$SCRIPT_FILE_LOG"

rm -v "$TMP_ERROR_DIR$SCRIPT_NAME$HOST"
# 1
THIS_MASTER_SLAVES=$($MYSQL_HOMEDIR$MYSQL_CMD --connect_timeout=1 -u$MY_USER --password=$MY_PASSWD -h $HOST --port=$PORT -e "SHOW SLAVE HOSTS\G" 2>$TMP_ERROR_DIR$SCRIPT_NAME$HOST);
if [ -f "$TMP_ERROR_DIR$SCRIPT_NAME$HOST" ]; then
    THIS_MASTER_SLAVES_ERR=$(/bin/cat < $TMP_ERROR_DIR$SCRIPT_NAME$HOST);
fi

if [ -n "$SLAVE_ERR" ]; then
	/bin/echo $SLAVE_ERR  > $ERR_FILE;
	exit 1;
fi


if [ "$SLAVE_EXPECTED_NO" -gt "0" ]; then

    PORT=""
    HOST=""
    IFS=$'\n'
    THIS_MASTER_SLAVES=$(echo "$THIS_MASTER_SLAVES" | sed 's/\*/#/g')
    for item in ${THIS_MASTER_SLAVES}
    do
    # echo "$item"
    if ( strstr "$item" "Host"); then
	HOST=$(/bin/echo "$item"  | /bin/grep -o -i -e '\(Host:[[:space:]]\)\([0-9\.]\{3,\}\)' | /bin/awk -F " "  '{print $2 }');
	# '
    fi
    if ( strstr "$item" "Port"); then
	PORT=$(/bin/echo "$item"  | /bin/grep -o -i -e '\(Port:[[:space:]]\)\([0-9]\{3,\}\)' | /bin/awk -F " "  '{print $2 }');
	# '
    fi
    
    if [ -n "$PORT" ]; then
	rm "$TMP_ERROR_DIR$SCRIPT_NAME$HOST"
	SLAVE_INFO=$($MYSQL_HOMEDIR$MYSQL_CMD --connect_timeout=1 -u$MY_USER --password=$MY_PASSWD -h $HOST --port=$PORT -e "show slave status\G" 2>$TMP_ERROR_DIR$SCRIPT_NAME$HOST); 
	SLAVE_INFO=$(echo "$SLAVE_INFO" | sed 's/\*/#/g')
	# echo "$SLAVE_INFO"
	SLAVE_IO=$(/bin/echo $SLAVE_INFO  | /bin/grep -o -i -e '\(Slave_IO_Running:[[:space:]]\)\([A-Za-z]\{3\}\)' | /bin/awk -F " "  '{print $2 }'); 
	# highlight '
	SLAVE_SQL=$(/bin/echo $SLAVE_INFO | /bin/grep -o -i -e '\(Slave_SQL_Running:[[:space:]]\)\([A-Za-z]\{3\}\)' | /bin/awk -F " "  '{print $2 }'); 
	# highlight '
	if [ -f "$TMP_ERROR_DIR$SCRIPT_NAME$HOST" ]; then
	    SLAVE_ERR=$(/bin/cat < $TMP_ERROR_DIR$SCRIPT_NAME$HOST);
	fi
	if [ -n "$SLAVE_ERR" ]; then
	    write_to_log "Errors while asking about slaves"
	    /bin/echo $SLAVE_ERR  > $ERR_FILE;
	    exit 1;
	fi
	if  [ "$SLAVE_IO" = "Yes" ] && [ "$SLAVE_SQL" = "Yes" ]; then 
	    SLAVE_LAG=$(/bin/echo $SLAVE_INFO  | /bin/grep -o -i -e '\(Seconds_Behind_Master:[[:space:]]\)\([0-9]\{0,10\}\)' | /bin/awk -F " "  '{print $2 }');
	    # '
	    SLAVE_LAG=$((0 +  $SLAVE_LAG));
	    write_to_log "$HOST io: $SLAVE_IO, sql: $SLAVE_SQL, lag: $SLAVE_LAG"
	    if [ "$SLAVE_LAG" -gt "$SLAVE_LAG_GLOBAL" ]; then
		SLAVE_LAG_GLOBAL=$SLAVE_LAG
	    fi
	else
	    INFO="$HOST io: $SLAVE_IO, sql: $SLAVE_SQL, lag: $SLAVE_LAG"
	    write_to_log "$INFO"
	    /bin/echo "$INFO"  > $ERR_FILE;
	    exit 1
	fi
	SLAVE_CHECKED="$SLAVE_CHECKED $HOST" 
	HOST=""
	PORT=""
	let SLAVE_CHECKED_NO++
    fi
    done
else
    write_to_log "I omit slave checking cause SLAVE_EXPECTED_NO=$SLAVE_EXPECTED_NO"
fi
IFS=$STANDARD_IFS

if [ ! "$SLAVE_CHECKED_NO" -eq "$SLAVE_EXPECTED_NO" ]; then
    echo "Some slaves are down (SLAVE_EXPECTED: $SLAVE_EXPECTED_NO, is $SLAVE_CHECKED_NO). CHECKED: $SLAVE_CHECKED" > $ERR_FILE
    exit 1
fi

LOGBIN_TIMESTAMP_LIMIT=$(($CURRENT_TIMESTAMP - $SLAVE_LAG_GLOBAL - $TIMESTAMP_TO_ADD))

# echo "now $CURRENT_TIMESTAMP"
# echo "max lag $SLAVE_LAG_GLOBAL"
# echo "limit: $LOGBIN_TIMESTAMP_LIMIT"
# exit 0
# echo "$LOGBIN_PATTERN"

# #3

LOGBIN_CURRENT_FILE=`tail -n 1 $LOGBIN_DIR/$LOGBIN_NAME.index`
# Check if it exists
CURRENT_EXISTS=`echo $LOGBIN_CURRENT_FILE | grep -c "$LOGBIN_DIR/$LOGBIN_PATTERN"`
if [ ! "$CURRENT_EXISTS" = "1" ]; then
    echo "Current binlog file was not detected. I expected file: $LOGBIN_CURRENT_FILE. However CURRENT_EXISTS=$CURRENT_EXISTS" > $ERR_FILE
    exit 1
fi
LOGBIN_CURRENT_FILE=$(basename $LOGBIN_CURRENT_FILE)

for binlog_file in "$LOGBIN_DIR"/*; do
    proceed=`echo "$binlog_file" | grep -c "$LOGBIN_PATTERN"`
    if [ "$proceed" -eq "1" ]; then
	FILE_TO_PROCEED="$binlog_file"
	FILE_TO_PROCEED_BASENAME=$(basename $binlog_file)
	if [ "$FILE_TO_PROCEED_BASENAME" = "$LOGBIN_CURRENT_FILE" ];then
	    write_to_log  "I omit current file $FILE_TO_PROCEED_BASENAME"
	    break;
	fi
	FILE_TIMESTAMP=`stat -c"%Z" "$FILE_TO_PROCEED"`
	FILE_TIMESTAMP=$(($FILE_TIMESTAMP + 0))
	compare_int $LOGBIN_TIMESTAMP_LIMIT $FILE_TIMESTAMP
	IS_GT=$?
	if [ "$IS_GT" -eq "0" ]; then
	    write_to_log "File is too fresh: $FILE_TO_PROCEED_BASENAME"
	    write_to_log "$IS_GT : $LOGBIN_TIMESTAMP_LIMIT :  $FILE_TIMESTAMP"
	    break;
	fi
	write_to_log "bzip2 $binlog_file"
	bzip2  "$binlog_file"
	BZIP_RESULT=$?
	if [ "$BZIP_RESULT" -gt "0" ];then
	    echo "Bzip failed (with status $BZIP_RESULT): $LOGBIN_CURRENT_FILE" > $ERR_FILE
	    exit 1
	fi
	write_to_log "mv $binlog_file.bz2 $LOGBIN_DIR_BCK"
	mv "$binlog_file.bz2" "$LOGBIN_DIR_BCK"
	MV_RESULT=$?
	if [ "$MV_RESULT" -gt "0" ];then
	    echo "mv failed (with status $MV_RESULT): $LOGBIN_CURRENT_FILE" > $ERR_FILE
	    exit 1
	fi
    fi
done

# remove old files
find "$LOGBIN_DIR_BCK" -type f -mtime +31 -exec rm -v '{}' \;
AVAILABLE_STORAGE_SPACE=$(df -P "$LOGBIN_DIR" | tail -n 1  | awk -F " " '{print $4}')
if [ -z "$AVAILABLE_STORAGE_SPACE" ]; then
    COMM="Left space for devide information error"
    echo $COMM > $ERR_FILE
    write_to_log $COMM
    exit 1
fi

AVAILABLE_STORAGE_SPACE=$(($AVAILABLE_STORAGE_SPACE/1024))
write_to_log "Available space $AVAILABLE_STORAGE_SPACE Mb"
if [ "$AVAILABLE_STORAGE_SPACE" -lt "$MIN_VOLUME_CAPACITY_TO_RESTART_MB" ]; then
    write_to_syslog "Script $0 is to stop mysql server"
    /etc/init.d/mysql.server stop
fi

# echo "$ERR_FILE"
# cat "$ERR_FILE"
exit 0;