MySQL incremental backup/restore script

#!/bin/bash

# Author: Andrey Miroshnichenko <zentavr.trafford.com.ua>
# Created for: Silicom Internet SL                   2012, 2013
#              ICSW Group: http://www.icsw.com/en/
# Created for: Trafford Group                        2012, 2013
# Created for: Localcircles India                    2013
#
# Introducion:
# The script does incremental backup using Percona's xtrabackup utilities.
# You can download them from this site:
# http://www.percona.com/downloads/XtraBackup/LATEST/
# Yum repository: http://www.percona.com/doc/percona-server/5.5/installation/yum_repo.html
# APT repository: http://www.percona.com/doc/percona-server/5.5/installation/apt_repo.html
#
# FreeBSD users can find the percona xtrabackup in the ports (seems like rather old version):
# [root@cassiopeia ~]# cd /usr/ports/
# [root@cassiopeia /usr/ports]# make search key=xtrabackup
# Port:   xtrabackup-1.1
# Path:   /usr/ports/databases/xtrabackup
# Info:   OpenSource version of InnoDB backup with support of Percona extensions
# Maint:  [email protected]
# B-deps: gettext-0.18_1 gmake-3.81_4 libiconv-1.13.1_1
# R-deps:
# WWW:    http://www.percona.com/docs/wiki/percona-xtrabackup:start

trap bashtrap INT
function bashtrap() {
    echo "CTRL+C Detected !..."
    exit 1
}

function usage() {
    cat << EOF
usage: $0 options

The script does incremental MySQL backup.

OPTIONS:
    -h, --help
        Show this message

    -j, --job <job>
        What job to do: "backup" or "restore"?
        Default is "backup".

    -t, --type <type>
        Backup type: "full" or "incremental".
        Default is "full".

    -d, --database <database>
        Database name

    -l, --lsndir <directory>
        LSN (Log Seek Number) Directory Name.
        Default is "/tmp"

    -T, --threads <threads>
        How many threads to use?
        Default is: "amount of CPUs + 1"

    --throttle <IOPS>
        How many IOPS to produce

    --ionice-class <ionice_class>
        ionice (1) class to set up during the operation (pass -c ionice_class to ionice):
        0: none, 1: realtime, 2: best-effort, 3: idle

    --ionice-classdata <ionice_class>
        ionice (1) classdata to set up during the operation (pass -n ionice_class to ionice):
        class data (0-7, lower being higher prio)

    -L, --log <logfile>
        Path to the logfile.
        Default is "/tmp/logfile.log".

    -D, --path <path>
        Where to store/pick the backup results.
        Default is "Current Directory"

    -r, --dest <path>
        Where to place the retored MySQL data
        Default is "Current Directory"

    -x, --xtrabin <xtrabackup_binary>
        Sets up the Xtrabackup binary

    -s, --stream <streamtype>
        Sets what the stream to use: "tar" or "xbstream"
        Default is "xbstream"

    -n, --do-not-delete
        Do not delete the packed files during restore

    -e, --export
        Do an export of InnoDB tablespaces to separate .exp files.
        Only for Percona Server.
        Review this: http://www.percona.com/doc/percona-xtrabackup/innobackupex/importing_exporting_tables_ibk.html
        Set up this into your my.cnf:
            innodb_file_per_table=1               # For any version of Percona Server
            innodb_expand_import=1                # For Percona Sevrer to version 5.5.10-20.1
            innodb_import_table_from_xtrabackup=1 # For Percona Sevrer from version 5.5.10-20.1 and after
        Importing tables:
        To import a table to other server, first create a new table with the same structure as the one that will be imported at that server:
        OTHERSERVER|mysql> CREATE TABLE mytable (...) ENGINE=InnoDB;
        then discard its tablespace:
        OTHERSERVER|mysql> ALTER TABLE mydatabase.mytable DISCARD TABLESPACE;
        After this, copy mytable.ibd and mytable.exp files to database’s home, and import its tablespace:
        OTHERSERVER|mysql> ALTER TABLE mydatabase.mytable IMPORT TABLESPACE;
        Once this is executed, data in the imported table will be available.


EOF
}

function checkpipe() {
    STATUS=$1
    SOFT1=$2
    SOFT2=$3
    if [ "$STATUS" != "0 0" ]; then
        echo "The pipe of $SOFT1 and $SOFT2 was finished unseccussfully: $STATUS"
        exit 1
    else
        echo "The pipe operation was completed successfully"
    fi
}

function checkexit() {
    STATUS=$1
    SOFT=$2
    if [ "$STATUS" != "0" ]; then
        echo "The $SOFT was finished unseccussfully: $STATUS"
        exit 1
    else
        echo "The $SOFT operation was completed successfully"
    fi
}

# Extracting the options
# TODO: (Low) If this fails, parse only the short options with bash built-in getopt
#       http://linuxwell.com/2011/07/14/getopt-in-bash/
ARGS=$(getopt -o "hj:t:d:l:T:L:D:r:x:nes:" -l "help,job:,type:,database:,lsndir:,threads:,log:,path:,dest:,xtrabin:,do-not-delete,export,stream:,throttle:,ionice-class:,ionice-classdata:" -n "MyXtraBackup" -- "$@")
if [ $? -ne 0 ]; then
    echo "Bad argument to getopt was given"
    exit 1
fi

eval set -- "$ARGS"

#
# Set up the defaults :)
#
# Job type: backup or restore
JOB="backup"

# Backup type: "full" or "incremental"
TYPE="full"

# Path to where LSN position will be stored
LSNDIR="/tmp"

# The amount of the threads to use
THREADS=$(( $(grep -c processor /proc/cpuinfo) + 1 ))

# Where to store the logfile
LOGFILE="/tmp/logfile.log"

# Where to put the dump
BACKUPRES=$(pwd)

# Where to restore the data
DEST=$(pwd)

# Type of the stream
STREAMTYPE="xbstream"

# Location of the utilities
INNOBACKUPEX=$(which innobackupex)
LZOP=$(which lzop)
XBSTREAM=$(which xbstream)
TAR=$(which tar)
MYSQLDUMP=$(which mysqldump)
MYSQL=$(which mysql)
IONICE=$(which ionice)

OPTS=""
EXPORT=""
LZOP_OPTS="-dcfU"
IONICE_CMD=""
IONICE_CLASS=""
IONICE_CLASSDATA=""

# Extract the options
while true; do
    case "$1" in
        -h|--help)
            usage
            exit;;

        -j|--job)
            if [ -n "$2" ];then
                JOB="$2"
            else
                echo "No job type was specified (-j | --job)."
                exit 1;
            fi
            shift 2;;

        -t|--type)
            if [ -n "$2" ];then
                TYPE="$2"
            else
                echo "No backup type was specified (-t | --type)."
                exit 1;
            fi
            shift 2;;

        -d|--database)
            if [ -n "$2" ];then
                DATABASE="$2"
            else
                echo "No database was specified (-d|--database)."
                exit 1;
            fi
            shift 2;;

        -l|--lsndir)
            if [ -n "$2" ];then
                LSNDIR="$2"
            else
                echo "No LSN Directory was specified (-l|--lsndir)."
                exit 1;
            fi
            shift 2;;

        -T|--threads)
            if [ -n "$2" ];then
                THREADS="$2" # TODO: (Low) Check, if the parameter is an integer?
            else
                echo "No amount of the threads was specified (-T | --threads)."
                exit 1;
            fi
            shift 2;;

        -L|--log)
            if [ -n "$2" ];then
                LOGFILE="$2" # TODO: (Low) Check, if the basename of logfile exists??
            else
                echo "No logfile was given (-L | --log)."
                exit 1;
            fi
            shift 2;;

        -D|--path)
            if [ -n "$2" ];then
                BACKUPRES="$2"
            else
                echo "No directory for backup results was specified (-D | --path)."
                exit 1;
            fi
            shift 2;;

        -r|--dest)
            if [ -n "$2" ];then
                DEST="$2"
            else
                echo "No directory where to place restored data was specified (-r | --dest)."
                exit 1;
            fi
            shift 2;;

        -x|--xtrabin)
            if [ -n "$2" ];then
                OPTS="$OPTS --ibbackup=$2"
            else
                echo "No xtrabackup name was specified (-x | --xtrabin)."
                exit 1;
            fi
            shift 2;;

        --throttle)
            if [ -n "$2" ];then
                OPTS="$OPTS --throttle=$2"
            else
                echo "No throttle value was specified (--throttle)."
                exit 1;
            fi
            shift 2;;

        --ionice-class)
            if [ -n "$2" ];then
                IONICE_CLASS="-c$2"
            else
                echo "No ionice class value was specified (--ionice-class)."
                exit 1;
            fi
            shift 2;;

        --ionice-classdata)
            if [ -n "$2" ];then
                IONICE_CLASSDATA="-n$2"
            else
                echo "No ionice class data value was specified (--ionice-classdata)."
                exit 1;
            fi
            shift 2;;

        -s|--stream)
            if [ -n "$2" ];then
                STREAMTYPE="$2"
            else
                echo "No streamtype name was specified (-s | --stream)."
                exit 1;
            fi
            shift 2;;

        -n|--do-not-delete)
            LZOP_OPTS="-dcf"
            shift
        ;;

        -e|--export)
            EXPORT="--export"
            shift
        ;;

        --)
            shift
            break;;
    esac
done

# Remove trailing slashes from the end
BACKUPRES=$(echo "$BACKUPRES" | sed 's/\/*$//')
LSNDIR=$(echo "$LSNDIR" | sed 's/\/*$//')
DEST=$(echo "$DEST" | sed 's/\/*$//')

case "${STREAMTYPE}" in
    "tar")
        # Filename Suffix
        echo "Using tar as a streamer"
        FILENAMESUFFIX=".tar.lzo"
        STREAMER_BIN=$TAR
    ;;
    "xbstream")
        # Filename Suffix
        echo "Using xbstream as a streamer"
        FILENAMESUFFIX=".xbs.lzo"
        STREAMER_BIN=$XBSTREAM
    ;;
    *)
        echo "Unknown type of backup was specified: ${STREAMTYPE}"
        exit 1
    ;;

esac

# Determine for whether we need to use ionice(1)
if [[ -n "$IONICE_CLASS" ]] || [[ -n "$IONICE_CLASSDATA" ]]; then
    if [[ -n "$IONICE" ]] && [[ -x "$IONICE" ]]; then
        IONICE_CMD="$IONICE $IONICE_CLASS $IONICE_CLASSDATA -- "
    else
        echo "No ionice(1) executable was found or it has no executable flag"
    fi
fi

# The names will look like this:
# DATABASE-20120810_221058-full-169:12587456.xbs.lzo
# DATABASE-20120811_221055-incremental-169:12599569.xbs.lzo

# What needs to be done?
case "$JOB" in
    "backup")
        echo "Doing a backup."
        # Check, if the database was specified?
        if [ -z "$DATABASE" ]; then
            echo "Database was not specified"
            exit 1
        fi

        # Checking if the destination path exists
        if [ ! -d $BACKUPRES ]; then
            echo "Creating $BACKUPRES"
            mkdir -p "$BACKUPRES"
            if [ $? -ne 0 ]; then
                echo "Creation of $BACKUPRES was failed"
                exit 1
            fi
        fi

        if [ -z "$INNOBACKUPEX" ]; then
            echo "No innobackupex was found in $PATH"
            exit 1
        fi

        if [ -z "$MYSQL" ]; then
            echo "No mysql was found in $PATH"
            exit 1
        fi

        if [ -z "$MYSQLDUMP" ]; then
            echo "No mysqldump was found in $PATH"
            exit 1
        fi

        if [ -z "$LZOP" ]; then
            echo "No lzop archiver was found in $PATH"
            exit 1
        fi

        # Inventing a name for the file
        FILENAME=$DATABASE"-"$(date "+%Y%m%d_%H%M%S")

        TMPNAME=$(mktemp --tmpdir="$BACKUPRES")
        # What type of the backup do we have?
        case "$TYPE" in
            "full")
                echo "Doing a full backup to $TMPNAME"

                if [ "$STREAMTYPE" == "xbstream" ]; then
                    echo "...xbstream"
                    $IONICE_CMD $INNOBACKUPEX --include=$DATABASE".*" --parallel=$THREADS --extra-lsndir="$LSNDIR" --stream=$STREAMTYPE --no-timestamp $OPTS "$BACKUPRES" 2>>"$LOGFILE" | $LZOP -c > $TMPNAME
                    # Status of the pipe
                    checkpipe "${PIPESTATUS[*]}" "innobackupex ($INNOBACKUPEX)" "lzop:($LZOP)"
                else
                    echo "...tar: Doing a backup"
                    rm -rf --preserve-root "$BACKUPRES"/tmp
                    $IONICE_CMD $INNOBACKUPEX --include=$DATABASE".*" --parallel=$THREADS --extra-lsndir="$LSNDIR" --no-timestamp $OPTS "$BACKUPRES"/tmp 2>>"$LOGFILE"
                    echo "...tar: Packing"
                    ( cd "$BACKUPRES"/tmp; $TAR -cf - . | $LZOP -c > $TMPNAME )
                    # TODO: Check for pipe status here
                    echo "Pipe status is: ${PIPESTATUS[*]}"
                    echo "...tar: Removing an old data"
                    rm -rf --preserve-root "$BACKUPRES"/tmp
                fi
                ;;
            "incremental")
                echo "Doing an incremental backup to $TMPNAME"
                # Check if we have full backup (check LSN, the file could be moved away to another server)?
                TOLSN=$(grep "to_lsn" "$LSNDIR/xtrabackup_checkpoints" | awk -F'=' '{print $2}' | sed 's/^[ \t]*//;s/[ \t]*$//')
                if [ -z "$TOLSN" ]; then
                    echo "Cannot find LSN position of the previous backup in $LSNDIR"
                    exit 1
                fi

                if [ "$STREAMTYPE" == "xbstream" ]; then
                    echo "...xbstream"
                    $IONICE_CMD $INNOBACKUPEX --include=$DATABASE".*" --parallel=$THREADS --incremental --extra-lsndir="$LSNDIR" --incremental-lsn=$TOLSN --stream=$STREAMTYPE $OPTS "$BACKUPRES" 2>>"$LOGFILE" | $LZOP -c > $TMPNAME
                    # Status of the pipe
                    STATUS=${PIPESTATUS[*]}
                    if [ "$STATUS" != "0 0" ]; then
                        echo "The pipe of innobackupex ($INNOBACKUPEX) and lzop:($LZOP) was finished unseccussfully: $STATUS"
                        exit 1
                    else
                        echo "The pipe operation was completed successfully"
                    fi
                else
                    echo "Removing ${BACKUPRES}/tmp"
                    rm -rf --preserve-root "$BACKUPRES"/tmp
                    echo "...tar: Doing a backup"
                    echo $IONICE_CMD $INNOBACKUPEX --include=$DATABASE".*" --parallel=$THREADS --incremental --extra-lsndir="$LSNDIR" --no-timestamp --incremental-lsn=$TOLSN $OPTS "$BACKUPRES"/tmp
                    $IONICE_CMD $INNOBACKUPEX --include=$DATABASE".*" --parallel=$THREADS --incremental --extra-lsndir="$LSNDIR" --no-timestamp --incremental-lsn=$TOLSN $OPTS "$BACKUPRES"/tmp 2>>"$LOGFILE"
                    echo "...tar: Packing"
                    ( cd "$BACKUPRES"/tmp; $TAR -cf - . | $LZOP -c > $TMPNAME )
                    echo "...tar: Removing an old data"
                    rm -rf --preserve-root "$BACKUPRES"/tmp
                fi
                ;;
            *)
                echo "A wrong backup type was specified ($TYPE)"
                exit 1
                ;;
        esac

        # Checking what we have in the logs
        LOGISOK=$(tail -1 "$LOGFILE" | grep -c 'completed OK!')
        if [ $LOGISOK -eq 1 ]; then
            tail -1 "$LOGFILE"
        else
            echo '##### ALERT #####'
            echo 'We don\'t have \'completed OK!\' status in the end of a logfile, so probably an error happened'
            echo '##### ALERT #####'
        fi

        echo "Renaming $TMPNAME"
        # Fetch the latest LSN number
        FROMLSN=$(grep "from_lsn" "$LSNDIR/xtrabackup_checkpoints" | awk -F'=' '{print $2}' | sed 's/^[ \t]*//;s/[ \t]*$//')
        TOLSN=$(grep "to_lsn" "$LSNDIR/xtrabackup_checkpoints" | awk -F'=' '{print $2}' | sed 's/^[ \t]*//;s/[ \t]*$//')
        mv -f $TMPNAME $BACKUPRES"/"$FILENAME"-"$TYPE"-"$FROMLSN"-"$TOLSN""$FILENAMESUFFIX
        echo "$TYPE backup is done. The result is in "$BACKUPRES"/"$FILENAME"-"$TYPE"-"$FROMLSN"-"$TOLSN""$FILENAMESUFFIX
        # Storing table definitions
        echo "Dumping table definitions"
        mkdir -p $BACKUPRES/definitions
        for TABLE in $(echo "show tables" | $MYSQL --defaults-extra-file=/root/.my.cnf -B -N "$DATABASE"); do
            echo "Dumping table definition for ${DATABASE}.${TABLE}"
            $IONICE_CMD $MYSQLDUMP --defaults-extra-file=/root/.my.cnf -q -Q --no-data "$DATABASE" "$TABLE" > $BACKUPRES/definitions/${TABLE}.sql 2>> "$LOGFILE"
        done
        echo "Doing a backup of $DATABASE routies"
        $IONICE_CMD $MYSQLDUMP --defaults-extra-file=/root/.my.cnf -q -Q --routines --no-create-info --no-data --no-create-db --skip-opt "$DATABASE" > $BACKUPRES/definitions/__routines.sql 2>> "$LOGFILE"
        echo "Archiving the definitions"
        ( cd $BACKUPRES/definitions; $TAR -cf - . | $LZOP -c > $BACKUPRES/definitions-$FILENAME.tar.lzo )
        rm -rf --preserve-root $BACKUPRES/definitions
        echo "Done"
        ;;

    "restore")
        echo "Restoring $DATABASE" | tee "${LOGFILE}"

        if [ -z "$XBSTREAM" ]; then
            echo "No xbstream (the part of percona's xtrabackup was found in $PATH" | tee -a "${LOGFILE}"
            exit 1
        fi

        # Review what files of full backups do we have (we take the latest one...)
        FULLBACKUP=$(find "$BACKUPRES" -type f -name "$DATABASE-*-full-*$FILENAMESUFFIX" -exec basename {} \; | sort -r | head -1)
        if [ -z "$FULLBACKUP" ]; then
            echo "No full backup was found in $BACKUPRES for database $DATABASE" | tee -a "${LOGFILE}"
            exit 1
        else
            echo "Considering $FULLBACKUP to be as a full backup for $DATABASE" | tee -a "${LOGFILE}"
        fi

        # Searching for the incremental backups (We must !!!! apply the increments one by one to the base backup in the right order)
        echo "Searching for the incremental backups" | tee -a "${LOGFILE}"
        # We need to search files recursively. The age the files should be younger then the age of a full backup
        PREV_FILE=$FULLBACKUP
        while true; do
            NEXT_LSN=$(basename $PREV_FILE $FILENAMESUFFIX | cut -f5 -d-) # 479:2100086046
            NEXT_FILE="$DATABASE-*-incremental-$NEXT_LSN-*$FILENAMESUFFIX"
            echo "   Searching for $NEXT_FILE" | tee -a "${LOGFILE}"
            #INC=$(find "$BACKUPRES" -type f -name "$NEXT_FILE" -newer "$BACKUPRES/$PREV_FILE" -exec basename {} \; | sort -r | head -1)
            INC=$(find "$BACKUPRES" -type f -name "$NEXT_FILE" -exec basename {} \; | sort -r | head -1)
            if [ -z "$INC" ]; then
                echo "    No next incremental found" | tee -a "${LOGFILE}"
                break
            fi
            echo "     Found $INC" | tee -a "${LOGFILE}"
            # Adding the file to the list
            INCREMENTALS="$INCREMENTALS $INC"
            PREV_FILE=$INC
        done

        FULLRESTORE=$DEST/full
        # Restore a full backup
        mkdir -p $FULLRESTORE
        echo "Unpacking a full backup to $FULLRESTORE" | tee -a "${LOGFILE}"
        $LZOP $LZOP_OPTS $BACKUPRES/$FULLBACKUP | $STREAMER_BIN -x --directory=$FULLRESTORE
        # Status of the pipe
        checkpipe "${PIPESTATUS[*]}" "lzop:($LZOP)" "$STREAMER_BIN" | tee -a "${LOGFILE}"

        # Replay the commited transactions on full (base) backup
        echo "Replaying the commited transactions on full (base) backup" | tee -a "${LOGFILE}"
        $IONICE_CMD $INNOBACKUPEX --apply-log --redo-only $FULLRESTORE --use-memory=1G $OPTS $EXPORT 2>>"$LOGFILE"
        checkexit $? "$INNOBACKUPEX" | tee -a "${LOGFILE}"

        echo | tee -a "${LOGFILE}"
        echo | tee -a "${LOGFILE}"
        echo | tee -a "${LOGFILE}"
        echo "====================================================================================================================" | tee -a "${LOGFILE}"
        echo | tee -a "${LOGFILE}"
        echo | tee -a "${LOGFILE}"
        echo | tee -a "${LOGFILE}"

        # Processing the increments
        for INC in $INCREMENTALS; do
            echo "Processing $INC" | tee -a "${LOGFILE}"
            INCRESTORE=$DEST/$(basename $INC $FILENAMESUFFIX)
            mkdir -p $INCRESTORE
            echo "Unpacking an incremental backup to $INCRESTORE" | tee -a "${LOGFILE}"
            $LZOP $LZOP_OPTS $BACKUPRES/$INC | $STREAMER_BIN -x --directory=$INCRESTORE
            # Status of the pipe
            checkpipe "${PIPESTATUS[*]}" "lzop:($LZOP)" "$STREAMER_BIN" | tee -a "${LOGFILE}"

            echo "Applying incremental backup log to a full backup" | tee -a "${LOGFILE}"
            $IONICE_CMD $INNOBACKUPEX --apply-log --redo-only $FULLRESTORE --incremental-dir=$INCRESTORE --use-memory=1G $OPTS $EXPORT 2>>"$LOGFILE"
            checkexit $? "$INNOBACKUPEX" | tee -a "${LOGFILE}"

            echo "Removing the folder whith incremental deltas" | tee -a "${LOGFILE}"
            rm -rf $INCRESTORE
            echo | tee -a "${LOGFILE}"
            echo | tee -a "${LOGFILE}"
            echo | tee -a "${LOGFILE}"
            echo "====================================================================================================================" | tee -a "${LOGFILE}"
            echo | tee -a "${LOGFILE}"
            echo | tee -a "${LOGFILE}"
            echo | tee -a "${LOGFILE}"
        done

        # At last - prepare the baclup for usage
        echo "Preparing again the full backup (base + incrementals) once again to rollback the pending transactions" | tee -a "${LOGFILE}"
        $IONICE_CMD $INNOBACKUPEX --apply-log $FULLRESTORE --use-memory=1G $OPTS $EXPORT 2>>"$LOGFILE"
        checkexit $? "$INNOBACKUPEX" | tee -a "${LOGFILE}"

        echo | tee -a "${LOGFILE}"
        echo | tee -a "${LOGFILE}"
        echo | tee -a "${LOGFILE}"
        echo "====================================================================================================================" | tee -a "${LOGFILE}"
        echo | tee -a "${LOGFILE}"
        echo | tee -a "${LOGFILE}"
        echo | tee -a "${LOGFILE}"

        echo "The restore is done. The result is in $FULLRESTORE folder" | tee -a "${LOGFILE}"
        ;;
    *)
        echo "Wrong job-type was specified"
        exit 1
        ;;
esac

exit 0