XtraBackup: Optimizing Your MySQL Backups

Introduction

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

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

Usage

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)

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

Backup (incremental)

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

From a Full backup

To restore from a full backup, the operation is done in three parts:

1. Use the apply-log argument:

1

innobackupex --apply-log directory_of_the_full_backup

1. Move the existing database to avoid any unwanted residue:

1

mv /var/lib/mysql/database_directory{,.old}

1. Run this command to restore (copy-back argument):

1

innobackupex --ibbackup=xtrabackup --copy-back directory_of_the_full_backup

From an incremental backup

To perform a restoration from an incremental backup, you again need several steps:

1. 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

1. 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

1. Prepare the final backup:

1

innobackupex-1.5.1 --apply-log directory_of_the_full_backup [--use-memory=1G] --user=root --password=xxxxx

1. 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

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/