Tutorial

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

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

Introduction

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.

Prerequisites

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.

Check Storage Engine

If you are unsure of which storage engine your databases use, you can look it up through a variety of methods. One way is to use the MySQL console to select the database in question, then output the status of each table.

First, enter the MySQL console:

  1. mysql -u root -p

Then enter your MySQL root password.

At the MySQL prompt, select the database that you want to check. Be sure to substitute your own database name here:

  1. USE database_name;

Then print its table statuses:

  1. SHOW TABLE STATUS\G;

The engine should be indicated for each row in the database:

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

Once you are done, leave the console:

  1. exit

Let’s install Percona XtraBackup.

Install Percona XtraBackup

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

Add the Percona repository key with this command:

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

Then add the Percona repository to your apt sources:

  1. sudo sh -c "echo 'deb http://repo.percona.com/apt trusty main' > /etc/apt/sources.list.d/percona.list"
  2. 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:

  1. sudo apt-get update

Finally, you can run this command to install XtraBackup:

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

First Time Preparations

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.

System User

Unless you plan on using the system root user, you must perform some basic preparations to ensure that XtraBackup can be executed properly. We will assume that you are logged in as the user that will run XtraBackup, and that it has superuser privileges.

Add your system user to the “mysql” group (substitute in your actual username):

  1. sudo gpasswd -a username mysql

While we’re at it, let’s create the directory that will be used for storing the backups that XtraBackup creates:

  1. sudo mkdir -p /data/backups
  2. sudo chown -R username: /data

The chown command ensures that the user will be able to write to the backups directory.

MySQL User

XtraBackup requires a MySQL user that it will use when creating backups. Let’s create one now.

Enter the MySQL console with this command:

  1. mysql -u root -p

Supply the MySQL root password.

At the MySQL prompt, create a new MySQL user and assign it a password. In this example, the user is called “bkpuser” and the password is “bkppassword”. Change both of these to something secure:

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

Next, grant the new MySQL user reload, lock, and replication privileges to all of the databases:

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

These are the minimum required privileges that XtraBackup needs to create full backups of databases.

When you are finished, exit the MySQL console:

  1. exit

Now we’re ready to create a full backup of our databases.

Perform Full Hot Backup

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.

Update Datadir Permissions

On Ubuntu 14.04, MySQL’s data files are stored in /var/lib/mysql, which is sometimes referred to as a datadir. By default, access to the datadir is restricted to the mysql user. XtraBackup requires access to this directory to create its backups, so let’s run a few commands to ensure that the system user we set up earlier—as a member of the mysql group—has the proper permissions:

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

These commands ensure that all of the directories in the datadir are accessible to the mysql group, and should be run prior to each backup.

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.

Create Backup

Now we’re ready to create the backup. With the MySQL database running, use the innobackupex utility to do so. Run this command after updating the user and password to match your MySQL user’s login:

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

This will create a backup of the database at the location specified, /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!

Alternatively, you may omit the --no-timestamp to have XtraBackup create a backup directory based on the current timestamp, like so:

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

This will create a backup of the database in an automatically generated subdirectory, like so:

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!

Either method that you decide on should output “innobackupex: completed OK!” on the last line of its output. A successful backup will result in a copy of the database datadir, which must be prepared before it can be used.

Prepare Backup

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:

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

Perform Backup Restoration

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

Stop the MySQL service with this command:

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

  1. mkdir /tmp/mysql
  2. mv /var/lib/mysql/* /tmp/mysql/

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

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

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

Now we’re ready to start MySQL:

  1. sudo service mysql start

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

Conclusion

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!

Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.

Learn more about us


Tutorial Series: How To Use Bacula on Ubuntu 14.04

This series will show you how implement file backups of your Ubuntu 14.04 servers using Bacula, the popular open-source backup software suite.

About the authors

Still looking for an answer?

Ask a questionSearch for more help

Was this helpful?
 
10 Comments


This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

As of October 13, 2016, the Percona repository key has changed from the key mentioned in this tutorial.

apt-key adv --keyserver keys.gnupg.net --recv-keys 8507EFA5 or
apt-key adv --keyserver keyserver.ubuntu.com --recv-keys 8507EFA5

Reference: https://www.percona.com/blog/2016/10/13/new-signing-key-for-percona-debian-and-ubuntu-packages/

Is it possible to back up without any blocking? Because the output appears as messages:

Executing UNLOCK TABLES All tables unlocked Backup created

But in the xtrabackup_info file it says:

lock_time = 0

I should use the option –no-lock?

On ubuntu 16.04 and a newer database version higher than 5.7 you will need : to install this package instead of the official one in https://www.percona.com/doc/percona-xtrabackup/2.3/installation/apt_repo.html

apt-get install percona-xtrabackup-24

also, PROCESS privilege is needed or you will get

Error: failed to execute query SHOW ENGINE INNODB STATUS: Access denied; you need (at least one of) the PROCESS privilege(s) for this operation

the workaround is replacing this line with the one in the tutorial GRANT RELOAD, LOCK TABLES,PROCESS, REPLICATION CLIENT ON . TO ‘username’@‘localhost’;

Dear Mitchell,

OS - 14.04 ubuntu

I have configured xtradbbackup by seeing this article. But when I tried to perform backup operation below is the error which I got.

160524 11:16:46 version_check Connecting to MySQL server with DSN ‘dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/var/run/mysqld/mysqld.sock’ as ‘sateam’ (using password: YES). Failed to connect to MySQL server: DBI connect(‘;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/var/run/mysqld/mysqld.sock’,‘sateam’,…) failed: Access denied for user ‘sateam’@‘localhost’ (using password: YES) at - line 1314. 160524 11:16:46 Connecting to MySQL server host: localhost, user: sateam, password: set, port: 3306, socket: /var/run/mysqld/mysqld.sock Failed to connect to MySQL server: Access denied for user ‘sateam’@‘localhost’ (using password: YES).

I have installed the below things as well but still it is not working .

ii libdbd-mysql-perl 4.025-1 amd64 Perl5 database interface to the MySQL database ii libdbi-perl 1.630-1 amd64 Perl Database Interface (DBI)

Can you help me out on this ?

Thank you in advance

Dear Mitchel, this is a great sample. I have tried to follow it step by step according your suggestion, but everything works fine until the step when i have to restore it. I’ve copied to tmp folder my data-dir, and then ran the following command: innobackupex --copy-back /data/backups/new_backup Then ran the following command: sudo chown -R mysql: /var/lib/mysql (By the way i tried to change the upside command by chown -R root: /var/lib/mysql) and : sudo service mysql start after the last command I’ve recieved an error like this: Job for mysql.service failed. See ‘systemctl status mysql.service’ and ‘journalctl -xn’ for details. As i wasnt able to restore it, what i did was to move again the folder I sent to my tmp folder to data-dir and finally could start the service again. Could you tell me if I’m doing something wrong please?

If the keyserver times out like so:

gpg: keyserver timed out
gpg: keyserver receive failed: keyserver error

This will save you: http://serverfault.com/questions/252355/installing-percona-server-error-keyserver

Hello, Mithcell!
Thanks for the article! I have done all of this, but after innobackupex --user=xxxxx --password=xxxxx /data/backups I see this -bash: !r: event not found

I keep getting this error??

150818 02:05:07 innobackupex: Connecting to MySQL server with DSN ‘dbi:mysql:;mysql_read_default_group=xtrabackup’ as ‘backupUserforme’ (using password: YES). innobackupex: Error: Failed to connect to MySQL server: DBI connect(‘;mysql_read_default_group=xtrabackup’,‘backupUserforme’,…) failed: Access denied for user ‘backupUserforme’@‘localhost’ (using password: YES) at /usr/bin/innobackupex line 2945.

Thank you Mitchell.

Hi Mitchell,

Nice article!

I’m using MariaDB with Aria table storage engine. Would my db still be locked up at the end of the backup?

Try DigitalOcean for free

Click below to sign up and get $200 of credit to try our products over 60 days!

Sign up

Join the Tech Talk
Success! Thank you! Please check your email for further details.

Please complete your information!

Get our biweekly newsletter

Sign up for Infrastructure as a Newsletter.

Hollie's Hub for Good

Working on improving health and education, reducing inequality, and spurring economic growth? We'd like to help.

Become a contributor

Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.

Welcome to the developer cloud

DigitalOcean makes it simple to launch in the cloud and scale up as you grow — whether you're running one virtual machine or ten thousand.

Learn more
DigitalOcean Cloud Control Panel