MySQL Enterprise Backup & Restore - Full and Incrimental

1. Create bash script 

#!/bin/bash

#creates and maintains MySQL Enterprise Backup (MEB) backups

#prints usage
usage()
{
    echo "$VERSION"
    echo "
Usage: `basename $0` [command] [MEB options]

Commands:
    full                                make full backup
    incremental                         make incremental backup
    incremental-with-redo-log-only      make incremental backup with redo log only
    verify-to-tape                      verify backup images, then copy to tape
    prepare                             prepare backups
    remove-old                          remove old backups
    "
}

# creates defaults values
initialize()
{
    VERSION=Kilo1.0
    MEBOPTIONS=
    CURDATE=`date +%y%m%d_%H%M%S`
   
    USER=YYYY
    PASS=XXXX
    SOCK=/var/run/mysqld/mysql.sock

              HOME=/data/

    #pre-defined defaults, can be overriden
    dMYSQLBACKUP="/usr/local/mysql/bin/mysqlbackup"
    dBACKUPDIR=$HOME/BACKUPDIR
    dINCREMENTALDIR=$HOME/INCREMENTALDIR
    dPREPAREDDIR=$HOME/PREPAREDDIR
    dLOGDIR=$HOME/log
    dBACKUPIMAGENAME=backup
    dOUTLOG=backup_output.log
    dERRORLOG=backup_error.log
    dVERIFYTIME=42
    dPREPARETIME=7
    dREMOVETIME=180
    #clean failed backups
    dCLEANUP=0
   
}

#parses options
parse_options()
{
    if [ -z "$1" ]
    then
        usage
        exit 1
    fi
    case $1 in
    full)                     COMMAND=do_full;;
    incremental)                 COMMAND=do_incremental;;
    incremental-with-redo-log-only)        COMMAND=do_incremental_with_redo_log_only;;
    verify-to-tape)             COMMAND=do_verify_to_tape;;
    prepare)                 COMMAND=do_prepare;;
    remove-old)                 COMMAND=do_remove_old;;
    *)                     usage; exit 1;;
    esac
    shift
    MEBOPTIONS=$@
   
    #setting environment variables
    if [ -z $MYSQLBACKUP ]
    then
        MYSQLBACKUP=`which mysqlbackup`
        if [ -z $MYSQLBACKUP ]
        then
            MYSQLBACKUP=$dMYSQLBACKUP
        fi
    fi
   
    if [ -z $BACKUPDIR ]
    then
        BACKUPDIR=${dBACKUPDIR}
    fi
   
    if [ -z $INCREMENTALDIR ]
    then
        INCREMENTALDIR=${dINCREMENTALDIR}
    fi
   
    if [ -z $PREPAREDDIR ]
    then
        PREPAREDDIR=$dPREPAREDDIR
    fi
   
    if [ -z $LOGDIR ]
    then
        LOGDIR=$dLOGDIR
    fi
   
    if [ -z $BACKUPIMAGENAME ]
    then
        BACKUPIMAGENAME=${dBACKUPIMAGENAME}.${CURDATE}.bki
    fi
   
    if [ -z $OUTLOG ]
    then
        OUTLOG=${dOUTLOG}.${CURDATE}
    fi
   
    if [ -z $ERRORLOG ]
    then
        ERRORLOG=${dERRORLOG}.${CURDATE}
    fi
   
    if [ -z $VERIFYTIME ]
    then
        VERIFYTIME=$dVERIFYTIME
    fi
   
    if [ -z $PREPARETIME ]
    then
        PREPARETIME=$dPREPARETIME
    fi
   
    if [ -z $REMOVETIME ]
    then
        REMOVETIME=$dREMOVETIME
    fi
   
    if [ -z $CLEANUP ]
    then
        CLEANUP=$dCLEANUP
    fi
   
}

#cleans up failed backups
cleanup()
{
    result=$?
    if [[ 0 -ne $result ]]
    then
        echo "Backup failed"
        if [[ 1 -eq $CLEANUP ]]
        then
        echo "Environment variable CLEANUP set, removing corrupted backup image"
            rm -f $1
        fi
        exit 1;
    else
        echo "Backup successful"
    fi
    if [[ 1 -eq $CLEANUP ]]
    then
        echo "Removing backup directory"
    fi
}

#makes full backup
do_full()
{
    $MYSQLBACKUP $MEBOPTIONS --socket=$SOCK --user=$USER --password=$PASS --backup-dir=$BACKUPDIR/${CURDATE} --backup-image=$BACKUPDIR/$BACKUPIMAGENAME backup-to-image >$LOGDIR/$OUTLOG 2>$LOGDIR/$ERRORLOG
    cleanup $BACKUPDIR/$BACKUPIMAGENAME
}

#makes incremental backup
do_incremental()
{
    $MYSQLBACKUP $MEBOPTIONS --socket=$SOCK --user=$USER --password=$PASS --incremental --incremental-base=history:last_backup --backup-dir=$INCREMENTALDIR/${CURDATE} --backup-image=$INCREMENTALDIR/$BACKUPIMAGENAME backup-to-image >$LOGDIR/$OUTLOG 2>$LOGDIR/$ERRORLOG
    cleanup $INCREMENTALDIR/$BACKUPIMAGENAME
}

#makes incremntal with redo long only backup
do_incremental_with_redo_log_only()
{
    $MYSQLBACKUP $MEBOPTIONS --socket=$SOCK --user=$USER --password=$PASS --incremental-with-redo-log-only --incremental-base=history:last_backup --backup-dir=$INCREMENTALDIR/${CURDATE} --backup-image=$INCREMENTALDIR/$BACKUPIMAGENAME backup-to-image >$LOGDIR/$OUTLOG 2>$LOGDIR/$ERRORLOG
    cleanup $INCREMENTALDIR/$BACKUPIMAGENAME
}

#verifies backup and writes to tape
do_verify_to_tape()
{
    fullbackup=(`find $BACKUPDIR -type f -atime +$VERIFYTIME`)
    incrementals=(`find $INCREMENTALDIR -type f -atime +$VERIFYTIME`)
    files=("${fullbackup[@]}" "${incrementals[@]}")
    for f in "${files[@]}"
    do
        $MYSQLBACKUP $MEBOPTIONS --socket=$SOCK --user=$USER --password=$PASS --backup-image=$f validate >$LOGDIR/$OUTLOG 2>$LOGDIR/$ERRORLOG
        result=$?
        if [[ 0 -eq $result ]]
        then
            echo "Here is supposed to be call which copies validated image to tape"
            #copy to tape here
            #since I don't know how customer is going to copy to tape leaving this part empty until I get explanation
        else
            echo "Here is supposed to be code which handles validation error"
            #also is not clear what to do with image which is not validated
        fi
       
    done
   
}

#prepares backup
do_prepare()
{
    fullbackup=(`find $BACKUPDIR -type f -atime +$PREPARETIME`)
    incrementals=(`find $INCREMENTALDIR -type f -atime +$PREPARETIME`)
    #for test only
    #fullbackup=(`ls --sort=t $BACKUPDIR/*bki`)
    #incrementals=(`ls --sort=t -r $INCREMENTALDIR/*bki`)
    #preparing full backup
    for f in "${fullbackup[@]}"
    do
        $MYSQLBACKUP $MEBOPTIONS --socket=$SOCK --user=$USER --password=$PASS --backup-image=$f --backup-dir=$PREPAREDDIR/${CURDATE} extract >>$LOGDIR/$OUTLOG 2>>$LOGDIR/$ERRORLOG
        result=$?
        if [[ 0 -eq $result ]]
        then
            $MYSQLBACKUP $MEBOPTIONS --socket=$SOCK --user=$USER --password=$PASS --backup-dir=$PREPAREDDIR/${CURDATE} apply-log >>$LOGDIR/$OUTLOG 2>>$LOGDIR/$ERRORLOG
            result=$?
            if [[ 0 -ne $result ]]
            then
                echo "212: Handle error"
                exit 1;
            fi
        else
            echo "216: Handle error"
            exit 1;
        fi
        break
    done
    i=0
    for f in "${incrementals[@]}"
    do
        $MYSQLBACKUP $MEBOPTIONS --socket=$SOCK --user=$USER --password=$PASS --backup-image=$f --backup-dir=$INCREMENTALDIR/${CURDATE}_${i} extract >>$LOGDIR/$OUTLOG 2>>$LOGDIR/$ERRORLOG
        result=$?
        if [[ 0 -eq $result ]]
        then
            $MYSQLBACKUP $MEBOPTIONS --socket=$SOCK --user=$USER --password=$PASS --backup-dir=$PREPAREDDIR/${CURDATE} --incremental-backup-dir=$INCREMENTALDIR/${CURDATE}_${i} apply-incremental-backup >>$LOGDIR/$OUTLOG 2>>$LOGDIR/$ERRORLOG
            result=$?
            if [[ 0 -ne $result ]]
            then
                echo "232: Handle error"
                exit 1;
            fi
        else
            echo "236: Handle error"
            exit 1;
        fi
        let "i+=1"
    done
}

#removes old backups
do_remove_old()
{
    find $BACKUPDIR -type f -ctime +$REMOVETIME -exec rm {} \;
    find $INCREMENTALDIR -type f -ctime +$REMOVETIME -exec rm {} \;
}

initialize
parse_options $@
$COMMAND

exit 0

Save it as autobackup.sh, then create cron script like in this example:

0 0 * * */3 autobackup.sh full
0 0 */2 * * autobackup.sh incremental
0 */8 * *   autobackup.sh incremental-with-redo-log-only
0 0 * * */6 autobackup.sh verify-to-tape
0 0 * * *   autobackup.sh prepare
0 0 0 */6 * autobackup.sh remove-old

This script will run full backup two times per week, incremental backup every second day, incremental backup with redo log only every 8 hours. Every week backup would be verified and copied to tape. Everyday it will be prepared and every 6 months old backup will be removed


2 . Restore Full and Incremantal 

There are two approaches to restore an incremental backup:

1.     Extract directly to datadir
2.     Extract and apply logs and incremental backups before copying back.

These two methods will be discussed in the following using the following backups and setup as an example:

1.     The last full backup was made a 3 AM on the day of the restore.
2.     Two incremental backups was made at 7 AM and 11 AM respectively.

 
The commands listed in the examples below are only meant as a basic example. The exact arguments and paths needed depends on your particular settings and setup.


Extract Directly to Datadir

The general steps to restore directly to datadir are:

    Restore and apply the log for the full backup.
    Important: the datadir must be empty before performing this step.
    
    For each incremental backup - in order - extract and apply to the full backup.
    Start MySQL.
    If necessary reply binary logs to perform a Point-in-time-Recovery.



Using the above example backups the restore process is:

    Restore the full backup and apply the log:

 mysqlbackup --datadir=/mysql/data ... --backup-image=/backups/full.mbi --backup-dir=/backups/full_restore copy-back-and-apply-log

    
    Important: the datadir must be empty before performing this step  You may need more options than just --datadir to specify the location of the restored files. Some examples of additional required options are: --innodb_data_home_dir, --innodb_log_group_home_dir, and --innodb_undo_directory. As an alternative you can use the --defaults-file=... option to load the MySQL configuration file for the instance.
    
    Extract and apply the incremental backup from 7 AM:

    mysqlbackup --datadir=/mysql/data --backup-image=/backups/incr_7am.mbi --backup_dir=/backups/incr_7am_restore --incremental copy-back-and-apply-log
    

    Extract and apply the incremental backup from 11 AM:

    mysqlbackup --datadir=/mysql/data --backup-image=/backups/incr_11am.mbi --backup_dir=/backups/incr_11am_restore --incremental copy-back-and-apply-log

    
    Start MySQL.
    If necessary reply binary logs to perform a Point-in-time-Recovery.


Extract and Apply Logs and Incremental Backup Before Copying Back

The general steps for a restore of this type are:

1.     Extract the full backup - this step applies only for single file backups.
2.     Apply the log for the full backup.

  For each incremental backup:

1.         Extract the backup - this step applies only for single file backups.
2.         Apply the incremental backup to the full backup.
3.     Copy back the combined backup.

Important: the datadir must be empty before performing this step. If necessary reply binary logs to perform a Point-in-time-Recovery.



The the above examples that gives the steps:

    Extract the full backup:

    mysqlbackup --backup-image=/backups/full.mbi --backup-dir=/backups/full_restore extract

    
    Apply the log to the full backup:

    mysqlbackup --backup-dir=/backups/full_restore apply-log

    
    Extract the incremental backup from 7 AM:

    mysqlbackup --backup-image=/backups/incr_7am.mbi --backup-dir=/backups/incr_7am_restore extract

    
    Apply the incremental backup from 7 AM to the full backup:
    mysqlbackup --incremental-backup-dir=/backups/incr_7am_restore --backup_dir=/backups/full_restore apply-incremental-backup
    
    Extract the incremental backup from 11 AM:

    mysqlbackup --backup-image=/backups/incr_11am.mbi --backup-dir=/backups/incr_11am_restore extract

    
    Apply the incremental backup from 11 AM to the full backup:

    mysqlbackup --incremental-backup-dir=/backups/incr_11am_restore --backup_dir=/backups/full_restore apply-incremental-backup

    
    Copy back the combined backup:

    mysqlbackup --datadir=mysql/data ... --backup-dir=/backups/full_restore copy-back

    
    Important: the datadir must be empty before performing this step.
    
    You may need more options than just --datadir to specify the location of the restored files. Some examples of additional required options are: --innodb_data_home_dir, --innodb_log_group_home_dir, and --innodb_undo_directory. As an alternative you can use the --defaults-file=... option to load the MySQL configuration file for the instance.
    
    Start MySQL.
    If necessary reply binary logs to perform a Point-in-time-Recovery.