Camil Blanaru
Camil Blanaru

Systems engineer with a passion for clouds. Based in Barcelona.

Camil Blanaru
Author

Linux Systems Engineer with a passion for clouds. Especially interested in automating the installation and management of enterprise infrastructure.

Share


Tags


avrt
Camil Blanaru

Hot Backup and Restore of MySQL with Percona XtraBackup

Camil BlanaruCamil Blanaru

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

Camil Blanaru

Linux Systems Engineer with a passion for clouds. Especially interested in automating the installation and management of enterprise infrastructure.

Comments