Percona XtraBackup is an open-source hot backup utility for MySQL – based servers that doesn’t lock your database during the backup.

It can easily be installed using official repositories on Debian/Ubuntu or RedHat/CentOS.

Please check the official documentation.

Installing Percona XtraBackup 2.4

Since I’m using Debian, I will provide a quick installation for Debian/Ubuntu.

Installing Percona XtraBackup from Percona apt repository

Fetch the repository packages from Percona web:

$ wget https://repo.percona.com/apt/percona-release_0.1-3.$(lsb_release -sc)_all.deb

Install the downloaded package with dpkg. To do that, run the following commands as root or with sudo:

$ sudo dpkg -i percona-release_0.1-3.$(lsb_release -sc)_all.deb

Update the local cache:

$ sudo apt-get update

Install the package:

$ sudo apt-get install percona-xtrabackup-24

Backup

A quick example of how to backup the InnoDB data and log files located in /var/lib/mysql/ to /data/backups/mysql/:

$ xtrabackup --backup --target-dir=/data/backups/mysql/

However, for regular backups, depending on you requirements, you may want to compress and/or encrypt your backups or stream them to `STDOUT` where you can pipe them to **gzip** or across the network to another servers.

Here is an example of a daily script that create, compress and encrypt a full MySQL backup to a local directory.

If you want smaller backups, gzip can additionally compress a 1.3 GB xbstream file into a ~700 MB gzip archive. This can be done after, or piped directly to gzip. I remember having some issues with piping to gzip, so I prefer adding a extra step when I need smaller backups. Just uncomment the line.

#!/bin/sh
#daily backup
BACKUP_DATE=`date +%Y%m%d`
BACKUP_DATETIME=`date +%Y%m%d-%H%M`
echo "Backup Started (Your Project Name)" "$BACKUP_DATE" >> /var/log/backuplog
mkdir -p /backup/automatic/daily/"$BACKUP_DATE"
cd /backup/automatic/daily/"$BACKUP_DATE"/
innobackupex --stream=xbstream --compress --user=root --password="yourpassword" --encrypt=AES256 --encrypt-key="somerandoomkey" /backup/automatic/daily/"$BACKUP_DATE" > /backup/automatic/daily/"$BACKUP_DATE"/"$BACKUP_DATETIME"_full-backup.xbstream
#gzip "$BACKUP_DATETIME"_full-backup.xbstream
echo Backup Completed `date` >> /var/log/backuplog

Restore

Some important notes on the restoring process

When you compress and/or encrypt the backups in xbstream format you have to use the xbstream tool first to extract all the files, then using innobackupex to decrypt and/or uncompress the files.

Personally I had a situation when the backup it was only compressed and then extracted using $ xbstream -x < /backup/automatic/daily/timestamp_full-backup.xbstream

Doing this will result in many .qp files that are compressed and will generate errors when you try to restore the backup:

xtrabackup: Warning: cannot open ./xtrabackup_logfile. will try to find.

innobackupex: File ‘ibdata1’ not found (Errcode: 2 – No such file or directory)

InnoDB: Operating system error number 2 in a file operation.

InnoDB: The error means the system cannot find the path specified.

[01] error: cannot open file ibdata1

[01] Error: copy_file() failed.

Looking thru forums I saw people making scripts just to search and extract these files. Anyway this can be solved using innobackupex --decompress /restore_dir

Now, according to the backup script above, this is the full restoration procedure:

Stop MySQL service

$ sudo service mysql stop

Remove or move the datastore , usually `/var/lib/mysql`

$ sudo rm -rf /var/lib/mysql/*

Extract the xbstream file to a directory

$ cd /restore_dir

$ xbstream -x < /backup/data/timestamp.xbstream

Install qpress

$ sudo apt-get install qpress

Uncompress and decrypt the files inside `/restore_dir`

$ innobackupex --decompress --decrypt=AES256 --encrypt-key="somerandoomkey" /restore_dir

Prepare the backup

$ xtrabackup --prepare --target-dir=/restore_dir

Apply log

$ innobackupex --apply-log /restore_dir

Restore MySQL data

$ innobackupex --copy-back /restore_dir

Restore permissions

$ sudo chown -R mysql:mysql /var/lib/mysql

Start MySQL

$ sudo service mysql start

Done