Features

• Full Backup (Mysqldump)
• Incremental Backup (Bin-log)
• Local Backup & Remote(AWS S3) Upload

Sample Environments

• MySQL bin log prefix : mysql-bin
• BINLOG_PATH=/home/mysql/data
• BACKUP_ROOT=/home/backup/mysql
• S3_PATH=s3://system-backup/{SyatemName}/mysql

#!/bin/bash
#----------------------------------------
# MySQL Backup Script
# Revision 20160531
# Copyright (c) Aiden Kim
# http://www.daemon.pe.kr
#----------------------------------------

ARCHIVE_PERIOD=60
BINLOG_PATH=/home/mysql/data
BASE_PATH=/home/backup/mysql
SYSNAME=$(echo $HOSTNAME | tr "[:lower:]" "[:upper:]")
S3_PATH=s3://system-backup/${SYSNAME}/mysql
STATUS_PATH=${BASE_PATH}/mysql-backup-status

## prepare path
if ! [ -d ${BASE_PATH} ]; then
  /bin/mkdir -p ${BASE_PATH}
fi

# Full backup
if [[ "$1" == "full" ]]; then

    echo "MySQL Full Backup started `date`..."

    # prepare directory
    TSTR=$(date +"%Y%m%d_%H%M%S")
    BACKUP_PATH=${BASE_PATH}/${TSTR}
    if ! [[ -d ${BACKUP_PATH} ]]; then
        /bin/mkdir -p ${BACKUP_PATH}
    fi
    /bin/rm -rf ${BACKUP_PATH}/*

    # get old full backup information
    if [[ -r ${STATUS_PATH} ]]; then
        OLD_BACKUP_PATH=`/bin/cat ${STATUS_PATH} | /bin/sed -n '1p'`
        OLD_START_BINLOG=`/bin/cat ${STATUS_PATH} | /bin/sed -n '2p'`
    fi

    # mysqldump
    /usr/local/mysql/bin/mysqldump --all-databases --routines --events --hex-blob --single-transaction --flush-logs --master-data=2 --include-master-host-port > ${BACKUP_PATH}/full_backup-${TSTR}.sql
    START_BINLOG=`/bin/sed -rn "1,50 s/^.+ MASTER_LOG_FILE\='(mysql-bin\.[0-9]+)'.+$/\1/p"  ${BACKUP_PATH}/full_backup-${TSTR}.sql | /bin/sed -n '1p'`
    echo "${BACKUP_PATH}" > ${STATUS_PATH}
    echo "${START_BINLOG}" >> ${STATUS_PATH}
    /bin/gzip ${BACKUP_PATH}/full_backup-${TSTR}.sql

    # finalize previous full backup data
    if [[ -n ${OLD_BACKUP_PATH} ]] && [[ -n ${OLD_START_BINLOG} ]] && [[ -d ${OLD_BACKUP_PATH} ]]; then
        for FILE in `ls ${BINLOG_PATH}/mysql-bin.?????? | sort -g`
        do
            if [[ "${FILE}" < "${BINLOG_PATH}/${START_BINLOG}" ]]; then
                if [[ "${FILE}" == "${BINLOG_PATH}/${OLD_START_BINLOG}" ]] || [[ "${FILE}" > "${BINLOG_PATH}/${OLD_START_BINLOG}" ]]; then
                    /bin/cp -afpu ${FILE} ${OLD_BACKUP_PATH}/
                fi
            fi
        done
    fi

# Incremental backup
else

    echo "MySQL Incremental Backup started..."

    # get previous full backup data
    if ! [[ -r ${STATUS_PATH} ]]; then
        echo "Could not find full backup status file."
        exit 1
    fi
    BACKUP_PATH=`/bin/cat ${STATUS_PATH} | /bin/sed -n '1p'`
    START_BINLOG=`/bin/cat ${STATUS_PATH} | /bin/sed -n '2p'`
    TSTR=`/bin/echo ${BACKUP_PATH} | sed -rn "s/^.+\/(20[0-9]{6}_[0-9]{6})$/\1/p"`
    if ! [[ -d ${BACKUP_PATH} ]]; then
        echo "Could not find last full backup path."
        exit 2
    fi
    if ! [[ -d ${BINLOG_PATH} ]]; then
        echo "Could not find mysql bin-log path."
        exit 2
    fi

    # flush logs & copy bin-logs
    /usr/local/mysql/bin/mysqladmin flush-logs
    BINLOG_CURR=`ls -d ${BINLOG_PATH}/mysql-bin.?????? | sed 's/^.*\.//' | sort -g | tail -n 1`
    for FILE in `ls ${BINLOG_PATH}/mysql-bin.?????? | sort -g`
    do
        if [[ "${BINLOG_PATH}/mysql-bin.${BINLOG_CURR}" != "${FILE}" ]]; then
            if [[ "${FILE}" == "${BINLOG_PATH}/${START_BINLOG}" ]] || [[ "${FILE}" > "${BINLOG_PATH}/${START_BINLOG}" ]]; then
                /bin/cp -afpu ${FILE} ${BACKUP_PATH}/
            fi
        fi
    done

fi

# sync to s3 bucket
/usr/bin/aws s3 sync ${BACKUP_PATH}/ ${S3_PATH}/${TSTR}/

# remove old backups
oldDirs=($(/bin/find ${BASE_PATH} -type d -mtime +${ARCHIVE_PERIOD}))
oldDirLen=${#oldDirs[@]}
for (( i=0; i<${oldDirLen}; i++ ));
do
    /bin/rm -rf ${oldDirs[$i]}
done

exit 0

Executing

# Full Backup
./backup.mysql.sh full

# Incremental Backup
./backup.mysql.sh