public

Scripts to create and restore full and incremental backups (for all databases on server) using innobackupex from Percona.

  • Download Gist
innobackupex-restore.sh
Shell
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118
#!/bin/sh
#
# Script to prepare and restore full and incremental backups created with innobackupex-runner.
#
# This script is provided as-is; no liability can be accepted for use.
#
 
INNOBACKUPEX=innobackupex-1.5.1
INNOBACKUPEXFULL=/usr/bin/$INNOBACKUPEX
TMPFILE="/tmp/innobackupex-restore.$$.tmp"
MYCNF=/etc/mysql/my.cnf
BACKUPDIR=/backup # Backups base directory
FULLBACKUPDIR=$BACKUPDIR/full # Full backups directory
INCRBACKUPDIR=$BACKUPDIR/incr # Incremental backups directory
MEMORY=1024M # Amount of memory to use when preparing the backup
 
#############################################################################
# Display error message and exit
#############################################################################
error()
{
echo "$1" 1>&2
exit 1
}
 
#############################################################################
# Check for errors in innobackupex output
#############################################################################
check_innobackupex_error()
{
if [ -z "`tail -1 $TMPFILE | grep 'completed OK!'`" ] ; then
echo "$INNOBACKUPEX failed:"; echo
echo "---------- ERROR OUTPUT from $INNOBACKUPEX ----------"
cat $TMPFILE
rm -f $TMPFILE
exit 1
fi
}
 
# Check options before proceeding
if [ ! -x $INNOBACKUPEXFULL ]; then
error "$INNOBACKUPEXFULL does not exist."
fi
 
if [ ! -d $BACKUPDIR ]; then
error "Backup destination folder: $BACKUPDIR does not exist."
fi
 
if [ $# != 1 ] ; then
error "Usage: $0 /absolute/path/to/backup/to/restore"
fi
 
if [ ! -d $1 ]; then
error "Backup to restore: $1 does not exist."
fi
 
# Some info output
echo "----------------------------"
echo
echo "$0: MySQL backup script"
echo "started: `date`"
echo
 
PARENT_DIR=`dirname $1`
 
if [ $PARENT_DIR = $FULLBACKUPDIR ]; then
FULLBACKUP=$1
echo "Restore `basename $FULLBACKUP`"
echo
else
if [ `dirname $PARENT_DIR` = $INCRBACKUPDIR ]; then
INCR=`basename $1`
FULL=`basename $PARENT_DIR`
FULLBACKUP=$FULLBACKUPDIR/$FULL
if [ ! -d $FULLBACKUP ]; then
error "Full backup: $FULLBACKUP does not exist."
fi
echo "Restore $FULL up to incremental $INCR"
echo
echo "Replay committed transactions on full backup"
$INNOBACKUPEXFULL --defaults-file=$MYCNF --apply-log --redo-only --use-memory=$MEMORY $FULLBACKUP > $TMPFILE 2>&1
check_innobackupex_error
# Apply incrementals to base backup
for i in `find $PARENT_DIR -mindepth 1 -maxdepth 1 -type d -printf "%P\n" | sort -n`; do
echo "Applying $i to full ..."
$INNOBACKUPEXFULL --defaults-file=$MYCNF --apply-log --redo-only --use-memory=$MEMORY $FULLBACKUP --incremental-dir=$PARENT_DIR/$i > $TMPFILE 2>&1
check_innobackupex_error
if [ $INCR = $i ]; then
break # break. we are restoring up to this incremental.
fi
done
else
error "unknown backup type"
fi
fi
 
echo "Preparing ..."
$INNOBACKUPEXFULL --defaults-file=$MYCNF --apply-log --use-memory=$MEMORY $FULLBACKUP > $TMPFILE 2>&1
check_innobackupex_error
 
echo
echo "Restoring ..."
$INNOBACKUPEXFULL --defaults-file=$MYCNF --copy-back $FULLBACKUP > $TMPFILE 2>&1
check_innobackupex_error
 
rm -f $TMPFILE
echo "Backup restored successfully. You are able to start mysql now."
echo "Verify files ownership in mysql data dir."
echo "Run 'chown -R mysql:mysql /path/to/data/dir' if necessary."
echo
echo "completed: `date`"
exit 0
innobackupex-runner.sh
Shell
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117
#!/bin/sh
#
# Script to create full and incremental backups (for all databases on server) using innobackupex from Percona.
# http://www.percona.com/doc/percona-xtrabackup/innobackupex/innobackupex_script.html
#
# Every time it runs will generate an incremental backup except for the first time (full backup).
# FULLBACKUPLIFE variable will define your full backups schedule.
#
# (C)2010 Owen Carter @ Mirabeau BV
# This script is provided as-is; no liability can be accepted for use.
# You are free to modify and reproduce so long as this attribution is preserved.
#
 
INNOBACKUPEX=innobackupex-1.5.1
INNOBACKUPEXFULL=/usr/bin/$INNOBACKUPEX
USEROPTIONS="--user=root --password=XXXXXX"
TMPFILE="/tmp/innobackupex-runner.$$.tmp"
MYCNF=/etc/mysql/my.cnf
MYSQL=/usr/bin/mysql
MYSQLADMIN=/usr/bin/mysqladmin
BACKUPDIR=/backup # Backups base directory
FULLBACKUPDIR=$BACKUPDIR/full # Full backups directory
INCRBACKUPDIR=$BACKUPDIR/incr # Incremental backups directory
FULLBACKUPLIFE=86400 # Lifetime of the latest full backup in seconds
KEEP=1 # Number of full backups (and its incrementals) to keep
 
# Grab start time
STARTED_AT=`date +%s`
 
#############################################################################
# Display error message and exit
#############################################################################
error()
{
echo "$1" 1>&2
exit 1
}
 
# Check options before proceeding
if [ ! -x $INNOBACKUPEXFULL ]; then
error "$INNOBACKUPEXFULL does not exist."
fi
 
if [ ! -d $BACKUPDIR ]; then
error "Backup destination folder: $BACKUPDIR does not exist."
fi
 
if [ -z "`$MYSQLADMIN $USEROPTIONS status | grep 'Uptime'`" ] ; then
error "HALTED: MySQL does not appear to be running."
fi
 
if ! `echo 'exit' | $MYSQL -s $USEROPTIONS` ; then
error "HALTED: Supplied mysql username or password appears to be incorrect (not copied here for security, see script)."
fi
 
# Some info output
echo "----------------------------"
echo
echo "$0: MySQL backup script"
echo "started: `date`"
echo
 
# Create full and incr backup directories if they not exist.
mkdir -p $FULLBACKUPDIR
mkdir -p $INCRBACKUPDIR
 
# Find latest full backup
LATEST_FULL=`find $FULLBACKUPDIR -mindepth 1 -maxdepth 1 -type d -printf "%P\n" | sort -nr | head -1`
 
# Get latest backup last modification time
LATEST_FULL_CREATED_AT=`stat -c %Y $FULLBACKUPDIR/$LATEST_FULL`
 
# Run an incremental backup if latest full is still valid. Otherwise, run a new full one.
if [ "$LATEST_FULL" -a `expr $LATEST_FULL_CREATED_AT + $FULLBACKUPLIFE + 5` -ge $STARTED_AT ] ; then
# Create incremental backups dir if not exists.
TMPINCRDIR=$INCRBACKUPDIR/$LATEST_FULL
mkdir -p $TMPINCRDIR
# Find latest incremental backup.
LATEST_INCR=`find $TMPINCRDIR -mindepth 1 -maxdepth 1 -type d | sort -nr | head -1`
# If this is the first incremental, use the full as base. Otherwise, use the latest incremental as base.
if [ ! $LATEST_INCR ] ; then
INCRBASEDIR=$FULLBACKUPDIR/$LATEST_FULL
else
INCRBASEDIR=$LATEST_INCR
fi
echo "Running new incremental backup using $INCRBASEDIR as base."
$INNOBACKUPEXFULL --defaults-file=$MYCNF $USEROPTIONS --incremental $TMPINCRDIR --incremental-basedir $INCRBASEDIR > $TMPFILE 2>&1
else
echo "Running new full backup."
$INNOBACKUPEXFULL --defaults-file=$MYCNF $USEROPTIONS $FULLBACKUPDIR > $TMPFILE 2>&1
fi
 
if [ -z "`tail -1 $TMPFILE | grep 'completed OK!'`" ] ; then
echo "$INNOBACKUPEX failed:"; echo
echo "---------- ERROR OUTPUT from $INNOBACKUPEX ----------"
cat $TMPFILE
rm -f $TMPFILE
exit 1
fi
 
THISBACKUP=`awk -- "/Backup created in directory/ { split( \\\$0, p, \"'\" ) ; print p[2] }" $TMPFILE`
rm -f $TMPFILE
 
echo "Databases backed up successfully to: $THISBACKUP"
echo
 
# Cleanup
echo "Cleanup. Keeping only $KEEP full backups and its incrementals."
AGE=$(($FULLBACKUPLIFE * $KEEP / 60))
find $FULLBACKUPDIR -maxdepth 1 -type d -mmin +$AGE -execdir echo "removing: "$FULLBACKUPDIR/{} \; -execdir rm -rf $FULLBACKUPDIR/{} \; -execdir echo "removing: "$INCRBACKUPDIR/{} \; -execdir rm -rf $INCRBACKUPDIR/{} \;
 
echo
echo "completed: `date`"
exit 0

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.