MySQL (with InnoDB) backup for simple servers.

Well; the MySQL tarts seem to be just as bad as ever; making backup really, really hard, and being careful never to really explain how to do it safely. But very keen, no doubt, to maintain their salaries and/or sell you a product to do a backup. As they say “Obscurity is Job Security”.

Took some reading to put this together; the ‘classic’ mysqldump method does not hang together very well now that most MySQL installs use InnoDB by default. You basically run a real risk of failing to produce consistent up-to-date backups (as noted here). Big ‘pro’ MySQL sites and admins normally either use a slave server, or filesystem snapshots to do the backup. But this is not very handy if you are constrained by resources from doing this; fortunately a helpful person posted a link to a specific InnoDB backup tool here).

Our situation is that we have an internal DB server (MySQL on Linux) which handles the databases used in our various intranet sites. This data is quite important, but there is not a lot of it (a few hundred megs at most), and fairly low load on the server. We do not have a lot of resource available; so slave servers or commercial tools are a no-go. Uptime is important too, we do not want to lock the databases while backing up. I initially contemplated converting the filesystem to ZFS or some other snapshotable filesystem, but rejected that as a long-winded and risky option.What I really needed is a tool that does a ‘hot’ backup of all our databases with no serious downtime, and handles InnoDB properly. Backing up the whole server is preferable, it is highly unlikely we would need to restore just one part of a Db, the most likely failure scenario for us is loss of an entire database, or the whole server.

That leads to the Percona XtraBackup tool. Which in turn provides a utility called ‘innobackupex‘; which turns out to be exactly what I needed in my company. It installed perfectly on our RedHat 5.4 server using the rpm on their site.

But, for what I want to do, I need to do two passes, running the tool once to make the actual backup, and a second time to apply outstanding logs in the backup. By applying the logs immediately after the backup  it means that the backups become ‘self-standing’ and do not need to be processed before they can be used (the alternative is to backup the initial dump, and only apply logs when restoring, fast, but it makes the restore process more complex, and assumes this tool is already available on the restore system.

So; what with one thing and another, I ended up writing the following script:

• This is intended to be run several times daily from a cron job; with it’s output being redirected to a logfile.
• It removes old backups according to their age.
• It produces backup directories that can simply be dropped into a MySQL install in place of the old storage area.
• If the recovery of just one database is required (probably from a specific date/time) I will recover the whole backup to a separate machine (which is easy; just recover the whole backup folder in the new machines mysql data directory and start mysqld). And then dump the specific database/tables I want via mysqldump, and recover them into the active server.

The principle requirement for it is that you have already installed the percona-xtrabackup package from their website, they have binary packages for quite a few architectures available here.

For RedHat EL5 (5.4, 64bit in this example) I installed the Percona tool as root in the following manner:

[root@db1] # wget http://www.percona.com/percona-builds/xtrabackup/XtraBackup-1.0/RPM/rhel5/xtrabackup-1.0-56.rhel5.x86_64.rpm
[root@db1] # rpm -ivh xtrabackup-1.0-56.rhel5.x86_64.rpm

I then created a location for the backups at /data/backup/mysql, and for the script at /data/backup/scripts (/data is a separate data partition on our database server; and the /data/backup tree is backed-up by a remote-server-synchronisation backup solution.) I then put the following script in as /data/backup/scripts/innobackupex-runner.sh.
Since the script has a fully privileged MySQL account in it I re-protected the directory and script to 700 so that it is only readable by the root account itself (chmod -R 700 /data/backup/scripts). Finally I added a cron job for the backups via a new cron configuration file at /etc/cron.d/mysql-backup.cron; with the contents:

0 7,13,19 * * * root /data/backup/scripts/innobackupex-runner.sh >> /var/log/mysql-backup.log

I created an initial logfile, and reprotected it to be only root readable.

[root@db1]# touch /var/log/mysql-backup.log
[root@db1]# chmod 600 /var/log/mysql-backup.log

The logfile produced is rotated by the standard logrotate system; I added a new config to this as /etc/logrotate.d/mysql-backup :

/var/log/mysql-backup.log {
 weekly
 rotate 5
 compress
 notifempty
 create 0600 root root
}

The actual Script I use is as follows:

• This script was written for a RedHat5.4 system; it’s pretty portable, but some bits (like the way it checks that MySQL is running) may need converting if you are not on a RH-like OS (Fedora and CentOS users should be fine..)
• Check the defaults at the beginning of the file; you need to specify a user account with access to all the databases.
• I’m sure it can be improved.

#!/bin/sh
#
# Script to run innobackupex script (for all databases on server), check for success, and apply logs to backups.
#

# (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=<OURUSER> --password=<OURUSERSPASSWORD>"
BACKUPDIR=/data/backup/mysql
TMPFILE="/tmp/innobackupex-runner.$$.tmp"

# Age of oldest retained backups in minutes.
AGE=10500

# Some info output

echo "----------------------------"
echo
echo "innobackupex-runner.sh: MySQL backup script"
echo "started: `date`"
echo

# Check options before proceeding

if [ ! -x $INNOBACKUPEXFULL ]; then
 error
 echo "$INNOBACKUPEXFULL does not exist."; echo
 exit 1
fi

if [ ! -d $BACKUPDIR ]; then
 error
 echo "Backup destination folder: $BACKUPDIR does not exist."; echo
 exit 1
fi

if [ -z "`/sbin/service mysqld status | grep 'mysqld (.*) is running'`" ] ; then
 echo "HALTED: mysqld does not appear to be running."; echo
 exit 1
fi

if ! `echo 'exit' | /usr/bin/mysql -s $USEROPTIONS` ; then
 echo "HALTED: Supplied mysql username or password appears to be incorrect (not copied here for security, see script)"; echo
 exit 1
fi

# Now run the command to produce the backup; capture it's output.

echo "Check completed OK; running $INNOBACKUPEX command."

$INNOBACKUPEXFULL $USEROPTIONS $BACKUPDIR > $TMPFILE 2>&1

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
echo "Now applying logs to the backuped databases"

# Run the command to apply the logfiles to the backup directory.
$INNOBACKUPEXFULL --apply-log $THISBACKUP > $TMPFILE 2>&1

if [ -z "`tail -1 $TMPFILE | grep 'completed OK!'`" ] ; then
 echo "$INNOBACKUPEX --apply-log failed:"; echo
 echo "---------- ERROR OUTPUT from $INNOBACKUPEX --apply-log ----------"
 cat $TMPFILE
 rm -f $TMPFILE
 exit 1
fi

echo "Logs applied to backuped databases"
echo

# Cleanup

echo "Cleaning up old backups (older than $AGE minutes) and temporary files"
rm -f $TMPFILE

cd /tmp ; find $BACKUPDIR -maxdepth 1 -type d -mmin +$AGE -exec echo "removing: "{} \; -exec rm -rf {} \;

echo
echo "completed: `date`"
exit 0