Instantly share code, notes, and snippets.

Embed
What would you like to do?
Scripts to create and restore full and incremental backups (for all databases on server) using innobackupex from Percona.
#!/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
#!/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
@markstreich

This comment has been minimized.

Copy link

markstreich commented Nov 23, 2011

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

@htmsl-techsupport

This comment has been minimized.

Copy link

htmsl-techsupport commented Aug 18, 2012

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

@ShahriyarR

This comment has been minimized.

Copy link

ShahriyarR commented May 23, 2013

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?

@ShahriyarR

This comment has been minimized.

Copy link

ShahriyarR commented Dec 4, 2013

@siddharth456

This comment has been minimized.

Copy link

siddharth456 commented Jan 13, 2014

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.

@tapankumar

This comment has been minimized.

Copy link

tapankumar commented Jul 2, 2014

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 {} ;

@emerham

This comment has been minimized.

Copy link

emerham commented Jul 24, 2015

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

@anandvastrad

This comment has been minimized.

Copy link

anandvastrad commented Jan 1, 2016

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

@dws-avishek-singh

This comment has been minimized.

Copy link

dws-avishek-singh commented Jan 7, 2016

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

@pravinjha

This comment has been minimized.

Copy link

pravinjha commented Apr 10, 2016

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment