Introduction link XtraBackup is an open-source solution for optimizing your MySQL backups. It’s much faster than mysqldump since it works directly with files rather than SQL queries.
In this article, we’ll see how to use it. It’s important to know that XtraBackup is particularly effective with the InnoDB engine.
Installation link Installing it on Debian is very simple. First, we’ll add the repository:
1
apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A
Create this file to add the repository:
1
2
3
# /etc/apt/sources.list.d/percona.list
deb http://repo.percona.com/apt VERSION main
deb-src http://repo.percona.com/apt VERSION main
Update and install:
1
2
aptitude update
aptitude install xtrabackup
It can be useful to store the credentials in a file if you want to back up locally:
1
2
3
4
# /etc/mysql/conf.d/xtrabackup.cnf
[ xtrabackup]
user = <user>
password = <password>
You can also use a file like ~/.my.cnf.
Backup (full) link To make a full backup:
1
innobackupex --user= xxxxx --password= xxxx --databases= database_name directory_to_store_backup
The backup will be stored in a folder named after its creation timestamp (example: 2011-09-19_09-32-19).
It’s possible to add the ‘–apply-log’ option, which will save more information to allow a simple restore by copying the folder to /var/lib/mysql
:
1
innobackupex --apply-log --user= xxxxx --password= xxxx --databases= database_name directory_to_store_backup
info
With the –apply-log option, it’s impossible to make incremental backups with XtraBackup
Backup (incremental) link A full backup is required to be able to make an incremental backup. Once you have one, use this command to generate an incremental backup:
1
innobackupex --incremental directory_to_store_backup --incremental-basedir= directory_containing_the_full --user= root --password= xxxxx
Restoration link From a Full backup link To restore from a full backup, the operation is done in three parts:
Use the apply-log argument: 1
innobackupex --apply-log directory_of_the_full_backup
Move the existing database to avoid any unwanted residue: 1
mv /var/lib/mysql/database_directory{ ,.old}
Run this command to restore (copy-back argument): 1
innobackupex --ibbackup= xtrabackup --copy-back directory_of_the_full_backup
From an incremental backup link To perform a restoration from an incremental backup, you again need several steps:
Prepare the full backup. Here, use-memory is optional; it just speeds up the process: 1
innobackupex --apply-log --redo-only directory_of_the_full_backup [ --use-memory= 1G] --user= root --password= xxxxx
Apply the incremental backups, in order: 1
innobackupex --apply-log directory_of_the_full_backup --incremental-dir= directory_of_the_incremental_backup [ --use-memory= 1G] --user= root --password= xxxxx
Prepare the final backup: 1
innobackupex-1.5.1 --apply-log directory_of_the_full_backup [ --use-memory= 1G] --user= root --password= xxxxx
Restore the final backup: 1
2
mv /var/lib/mysql/database_directory{ ,.old}
innobackupex --ibbackup= xtrabackup --copy-back directory_of_the_full_backup
Backup and restore from the slave link Here is a solution when you do not have enough space on the local master to store backups. Simply use Netcat to grab the backup directly from the slave:
1
2
mkdir /tmp/backups/
nc -l -p 8080 | tar xfi - -C /tmp/backups/
On the master:
1
innobackupex --stream= tar /tmp/ --slave-info | nc sql-slave 8080
Apply the logs on the slave:
1
innobackupex --apply-log --ibbackup= xtrabackup /tmp/backups/
Last updated
27 Dec 2013, 08:43 +0200 . history