XtraBackup: Optimizing Your MySQL Backups
Software version | 2.1.2-611.wheezy |
Operating System | Debian 7 |
Website | XtraBackup Website |
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:
Create this file to add the repository:
Update and install:
Usage
It can be useful to store the credentials in a file if you want to back up locally:
You can also use a file like ~/.my.cnf.
Backup (full)
To make a full 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
:
Info
With the --apply-log option, it's impossible to make incremental backups with XtraBackup
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:
Restoration
From a Full backup
To restore from a full backup, the operation is done in three parts:
- Use the apply-log argument:
- Move the existing database to avoid any unwanted residue:
- Run this command to restore (copy-back argument):
From an incremental backup
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:
- Apply the incremental backups, in order:
- Prepare the final backup:
- Restore the final 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:
On the master:
Apply the logs on the slave: