How To Create Hot Backups of MySQL Databases with Percona XtraBackup on Ubuntu 14.04

A very common challenge encountered when working with active database systems is performing hot backups—that is, creating backups without stopping the database service or making it read-only. Simply copying the data files of an active database will often result in a copy of the database that is internally inconsistent, i.e. it will not be usable or it will be missing transactions that occurred during the copy. On the other hand, stopping the database for scheduled backups renders database-dependent portions of your application to become unavailable. Percona XtraBackup is an open source utility that can be used to circumvent this issue, and create consistent full or incremental backups of running MySQL, MariaDB, and Percona Server databases, also known as hot backups.

As opposed to the logical backups that utilities like mysqldump produce, XtraBackup creates physical backups of the database files—it makes a copy of the data files. Then it applies the transaction log (a.k.a. redo log) to the physical backups, to backfill any active transactions that did not finish during the creation of the backups, resulting in consistent backups of a running database. The resulting database backup can then be backed up to a remote location using rsync, a backup system like Bacula, or DigitalOcean backups.

This tutorial will show you how to perform a full hot backup of your MySQL or MariaDB databases using Percona XtraBackup on Ubuntu 14.04. The process of restoring the database from a backup is also covered. The CentOS 7 version of this guide can be found here.

To follow this tutorial, you must have the following:

• Superuser privileges on an Ubuntu 14.04 system
• A running MySQL or MariaDB database
• Access to the admin user (root) of your database

Also, to perform a hot backup of your database, your database system must be using the InnoDB storage engine. This is because XtraBackup relies on the transaction log that InnoDB maintains. If your databases are using the MyISAM storage engine, you can still use XtraBackup but the database will be locked for a short period towards the end of the backup.

mysql -u root -p

USE database_name;

SHOW TABLE STATUS\G;

Example Output:
...
*************************** 11. row ***************************
           Name: wp_users
         Engine: InnoDB
...

exit

The easiest way to install Percona XtraBackup is to use apt-get.

Add the Percona repository key with this command:

sudo apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A

Then add the Percona repository to your apt sources:

sudo sh -c "echo 'deb http://repo.percona.com/apt trusty main' > /etc/apt/sources.list.d/percona.list"
sudo sh -c "echo 'deb-src http://repo.percona.com/apt trusty main' >> /etc/apt/sources.list.d/percona.list"

Run this command to update your apt sources:

sudo apt-get update

Finally, you can run this command to install XtraBackup:

sudo apt-get install percona-xtrabackup

XtraBackup consists primarily of the XtraBackup program, and the innobackupex Perl script, which we will use to create our database backups.

Before using XtraBackup for the first time, we need to prepare system and MySQL user that XtraBackup will use. This section covers the initial preparation.

sudo gpasswd -a username mysql

sudo mkdir -p /data/backups
sudo chown -R username: /data

mysql -u root -p

CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY 'bkppassword';

GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'bkpuser'@'localhost';
FLUSH PRIVILEGES;

exit

This section covers the steps that are necessary to create a full hot backup of a MySQL database using XtraBackup. After ensuring that the database file permissions are correct, we will use XtraBackup to create a backup, then prepare it.

sudo chown -R mysql: /var/lib/mysql
sudo find /var/lib/mysql -type d -exec chmod 770 "{}" \;

If you added your user to the mysql group in the same session, you will need to login again for the group membership changes to take effect.

innobackupex --user=bkpuser  --password=bkppassword --no-timestamp /data/backups/new_backup

innobackupex output
innobackupex: Backup created in directory '/data/backups/new_backup'
150420 13:50:10  innobackupex: Connection to database server closed
150420 13:50:10  innobackupex: completed OK!

innobackupex --user=bkpuser  --password=bkppassword /data/backups

innobackupex output — no timestamp
innobackupex: Backup created in directory '/data/backups/2015-04-20_13-50-07'
150420 13:50:10  innobackupex: Connection to database server closed
150420 13:50:10  innobackupex: completed OK!

The last step in creating a hot backup with XtraBackup is to prepare it. This involves “replaying” the transaction log to apply any uncommitted transaction to the backup. Preparing the backup will make its data consistent, and usable for a restore.

Following our example, we will prepare the backup that was created in /data/backups/new_backup. Substitute this with the path to your actual backup:

innobackupex --apply-log /data/backups/new_backup

Again, you should see “innobackupex: completed OK!” as the last line of output.

Your database backup has been created and is ready to be used to restore your database. Also, if you have a file backup system, such as Bacula, this database backup should be included as part of your backup selection.

The next section will cover how to restore your database from the backup we just created.

Restoring a database with XtraBackup requires that the database is stopped, and that its datadir is empty.

Stop the MySQL service with this command:

sudo service mysql stop

Then move or delete the contents of the datadir (/var/lib/mysql). In our example, we’ll simply move it to a temporary location:

mkdir /tmp/mysql
mv /var/lib/mysql/* /tmp/mysql/

Now we can restore the database from our backup, “new_backup”:

innobackupex --copy-back /data/backups/new_backup

If it was successful, the last line of output should say “innobackupex: completed OK!”

The restored files in datadir will probably belong to the user you ran the restore process as. Change the ownership back to mysql, so MySQL can read and write the files:

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

Now we’re ready to start MySQL:

sudo service mysql start

That’s it! Your restored MySQL database should be up and running.

Now that you are able to create hot backups of your MySQL database using Percona XtraBackup, there are several things that you should consider setting up.

First of all, it is advisable to automate the process so you will have backups created according to a schedule. Second, you should make remote copies of the backups, in case your database server has problems, by using something like rsync, a network file backup system like Bacula, or DigitalOcean backups. After that, you will want to look into rotating your backups (deleting old backups on a schedule) and creating incremental backups (with XtraBackup) to save disk space.

Good luck!