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

innobackupex-restore.sh

#!/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

#!/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

I got a huge stream of "InnoDB: Warning: difficult to find free blocks" errors in the tmpfile because of this and canceled before it ended (after dumping these errors for 30 minutes):

--use-memory=1024 (line 73)

Changed to:
--use-memory=1024M

Works good with that instead :-)

TY

Hello,

There is a bug in above script as it don't delete old DB dumps after taking next full backup.

Old Line for deleting old backups:
find $FULLBACKUPDIR -maxdepth 1 -type d -mmin +$AGE -execdir echo "removing: "$FULLBACKUPDIR/{} ; -execdir rm -rf $FULLBACKUPDIR/{} ; -execdir echo "removing: "$INCRBACKUPDIR/{} ; -execdir rm -rf $INCRBACKUPDIR/{} ;

Kindly suggest.

Regards
Tapan Thapa

Dear guys. i have an issue exactly in innobackupex-restore.sh.
innobackupex-runner.sh is working like a charm. and i can prepare backup directly with innobackupex from terminal and can recovery ofcourse directly with innobackupex.
But when i want to apply(prepare) with innobackupex-restore.sh following error is raising:

./innobackupex-restore.sh: MySQL backup script
started: Thu May 23 18:16:20 AZST 2013

unknown backup type

How i can solve this? What may cause this?

Developed backup and restore scripts in Python 3...need feedback:

https://github.com/ShahriyarR/mysql_physical_backuper

https://github.com/ShahriyarR/mysql_physical_backup_prepare

Hey thank you for the script.Works like a charm.However,my requirement is little different.I want to keep 1 full backup and 7 of its incrementals (1 each day for a week) before a new full backup is taken.Can you please guide.

Just to update on my two year old comment/issue, here is the solution to delete the old backed up directory.
find $FULLBACKUPDIR -maxdepth 1 -type d -mmin +$AGE -execdir echo "removing: "$FULLBACKUPDIR/{} ; -execdir rm -rf {} ; -execdir echo "removing: "$INCRBACKUPDIR/{} ; -execdir rm -rf {} ;

Don't know if its still in use or if there is a more up to date version but what @htmsl-techsupport said is true, mtime looks for changes in the last DAY's n_24 so the age calculation, if looking for day should be
AGE=$(($FULLBACKUPLIFE * $KEEP / 60 / 60 / 24))
This will get the number of full days out of the keep fullbackuplife, either that or change fullbackuplife to minutes, ie a FULLBACKUPLIFE=60 would work for a day of fulls.
*edit_
so I take that back, I didn't read the right line in the man page, ignore my ignorance

How can restore backup without doing Original data directory 'mysql' empty.

Today, 07:08 AM
Hello All,

I used restore DB backup script : https://gist.github.com/DamianCaruso/931358?signup=true

As I am new to Xtrabackup I am facing some difficulties on how to restore an incremental backup. In more details:

Full backup:
/home/navman/../data/backups/full/2015-12-23_23-52-31/

Incremental backup:
/home/navman/../data/backups/incr/2015-12-23_23-52-31/2015-12-23_23-52-41/
/home/navman/../data/backups/incr/2015-12-23_23-52-31/2015-12-24_22-00-03/
/home/navman/../data/backups/incr/2015-12-23_23-52-31/2015-12-25_22-00-02/

After preparing my incremental backups

I am facing below error. In MySQL I have multiple databases running in MySQL. So without stop MySQL and without doing Original data directory '/var/lib/mysql' empty. How can restore db backup for single database?

innobackupex-1.5.1 failed:

---------- ERROR OUTPUT from innobackupex-1.5.1 ----------

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

Get the latest version of Percona XtraBackup, documentation, and help resources:
http://www.percona.com/xb/ubuntu

IMPORTANT: Please check that the copy-back run completes successfully.
At the end of a successful copy-back run innobackupex-1.5.1
prints "completed OK!".

innobackupex-1.5.1: Error: Original data directory '/var/lib/mysql' is not empty! at /usr/b

Hi while restoring i am getting Usage: innobackupex-restore.sh /absolute/path/to/backup/to/restore
my conf is & folder backup is there with full & incr folder inside of it and my restore.sh file is in tmp folder can anyone help

INNOBACKUPEX=innobackupex
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

Thanks

In restore script, do we need to provide any argument?

When I execute it, I get following error:

/opt/redcappi/db_restore.sh: error: not found

/opt/redcappi/db_restore.sh: MySQL backup script
started: Sun Apr 10 08:38:24 UTC 2016

/opt/redcappi/db_restore.sh: 66: [: =: unexpected operator
dirname: missing operand
Try 'dirname --help' for more information.
/opt/redcappi/db_restore.sh: 72: [: =: unexpected operator
/opt/redcappi/db_restore.sh: 99: /opt/redcappi/db_restore.sh: error: not found
Preparing ...
innobackupex failed:

---------- ERROR OUTPUT from innobackupex ----------
160410 08:38:24 innobackupex: Missing argument