Hot Backups with Percona XtraBackup on the One-Click WordPress App

Introduction

Percona XtraBackup is a free MySQL-based program used for making hot backups. It is also open-sourced. With Percona XtraBackup, you can make hot backups of running MySQL, MariaDB, or Percona Server databases without stopping your database service or making it read-only. This is a business critical feature for lots of online businesses.

For databases using InnoDB, XtraDB, and HailDB storage engines, Percona XtraBackup can perform non-blocking backups. For databases using MyISAM, Merge, and Archive storage engines, Percona XtraBackup can also perform backups by briefly pausing writes at the end of the backup procedure.

In this article, I will show you how to install and use Percona XtraBackup to perform full and incremental hot backups on a Vultr server based on the One-Click WordPress application. We will perform a full backup and two incremental backups, and then restore the database to the state of each of the three backups accordingly.

Prerequisites

I assume that you have deployed a One-Click WordPress Vultr server instance from scratch and have logged in as root, using SSH.

Step 1: Create a non-root system user

For security purposes, a recommended practice is to create another user account with root permissions, then use it to log in and perform your daily operations on the system. You can still execute almost all of the superuser commands with the sudo command.

1. Create a new user. Replace sysuser with your own username.

   useradd sysuser

2. Set the password for your new user. Replace sysuser with your own username.

   passwd sysuser

3. Grant root permissions to your new user.

   visudo

Find the paragraph below.

## Allow root to run any commands anywhere
root     ALL=(ALL)     ALL

Add a row directly beneath this paragraph, replace sysuser with your own username.

sysuser     ALL=(ALL)     ALL

Save and quit.

:wq

4. Switch to your new user account.

   logout

Then, use the new user's credentials to log in from your terminal window.

Step 2: Check the storage engine

By default, the MySQL root login is saved on the VPS in /root/.my.cnf. Display the password in your terminal with the following command.

sudo cat /root/.my.cnf

Use the credential displayed on the screen to log into the MySQL console.

mysql -u root -p

In the MySQL shell, run the following.

SHOW DATABASES;

All the MySQL databases will have been displayed on the screen. The database named like wp5273512 is the WordPress database that we want to backup. In the following command, replace wp5273512 with your own one:

USE wp5273512;

Check the storage engine for each table:

SHOW TABLE STATUS\G

You will find that all of the tables in your WordPress MySQL database are using the InnoDB storage engine which is perfect for performing hot backups with Percona XtraBackup.

For any other MySQL databases using MyISAM storage engine, we can still backup them with Percona XtraBackup by briefly pausing writes.

Step 3: Create a database user for backup

Still in the MySQL shell, use the following commands to create a dedicated database user for backup. Remember to replace the database username xbuser and the password xbpasswd with your own ones:

CREATE USER 'xbuser'@'localhost' IDENTIFIED BY 'xbpasswd';
GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT, CREATE TABLESPACE, PROCESS, SUPER, CREATE, INSERT, SELECT ON *.* TO 'xbuser'@'localhost';
FLUSH PRIVILEGES;
EXIT;

The privileges granted above are necessary for full Percona XtraBackup functionality. You can remove some of them for less functionality and better security. For more details, see the Percona XtraBackup offical website.

Step 4: Install Percona XtraBackup

You can install Percona XtraBackup from Percona's RPM repository quite easily:

sudo yum install -y http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
sudo yum install -y percona-xtrabackup

Step 5: Create backup storage directories

First, you need to add user sysuser to mysql group. Replace sysuser with your own username.

sudo gpasswd -a sysuser mysql

Create a directory to store full backups.

sudo mkdir -p /dbbackup/full/

Create another directory to store incremental backups.

sudo mkdir -p /dbbackup/inc/

Change the owner of these directories to user sysuser and group sysuser.

sudo chown -R sysuser:sysuser /dbbackup

Log out to put these changes into effect.

logout

Then, log in with sysuser again.

Step 6: Create the first full backup

XtraBackup consists primarily of the XtraBackup program and the innobackupex perl script. Usually, you can use the innobackupex perl script to perform various operating procedures for convenience.

Input the following command to create the first full backup. Remember to replace the database username xbuser, the database user password xbpasswd, and the full backup directory /dbbackup/full/ with your own ones.

sudo innobackupex --user=xbuser  --password=xbpasswd /dbbackup/full/

With this command executed correctly, you will see the confirmation message "innobackupex: completed OK!" at the last line of the output.

All of the newly-created files of this full backup will be stored in a time-stamped directory under /dbbackup/full/. For example, /dbbackup/full/2015-05-22_05-45-54.

Step 7: Create the following incremental backups

Input the following command to create the first incremental backup. Substitute variables in the command accordingly.

sudo innobackupex --user=xbuser  --password=xbpasswd --incremental --incremental-basedir=/dbbackup/full/2015-05-22_05-45-54 /dbbackup/inc/

Again, you will see "innobackupex: completed OK!" at the end of the output when the command executes successfully. The backup files will be stored in a time-stamped directory under /dbbackup/inc/.

Input the following command to create the second incremental backup. Replace variables in the command accordingly.

sudo innobackupex --user=xbuser  --password=xbpasswd --incremental --incremental-basedir=/dbbackup/inc/2015-05-22_05-48-12 /dbbackup/inc/

Upon success, you will see the "innobackupex: completed OK!" message again. Check the /dbbackup/inc/ folder again to see the backup files.

Step 8: Prepare backup files for restoring database

All of the database backup files need to be prepared before they can be used to restore the database.

Note: Before you perform the prepare and restore procedures, you'd better keep a copy of the whole backup directory (such as /dbbackup/) at another place in case any damage to backup the files is caused by mistake.

In each backup directory, there is a file named xtrabackup_checkpoints which contains the backup type and the beginning and end log sequence numbers (from_lsn and to_lsn). You can use those numbers to clarify your database restore strategy. Look at the examples below.

In the xtrabackup_checkpoints file of the first full backup, I have:

backup_type = full-backuped
from_lsn = 0
to_lsn = 2932478

In the xtrabackup_checkpoints file of the first incremental backup, I have:

backup_type =incremental
from_lsn = 2932478
to_lsn = 2970177

In the xtrabackup_checkpoints file of the second incremental backup, I have:

backup_type = incremental
from_lsn = 2970177
to_lsn = 3004672

In brief, you should tackle each backup with the increasing order of lsn. If the lsn sequence is incomplete or disordered, you may lose data.

Note: The following commands involve three directories, replace them with your own ones.

To restore the database to the state of the first full backup, you need to prepare the backup files with the following command:

sudo innobackupex --apply-log /dbbackup/full/2015-05-22_05-45-54

To restore the database to the state of the first incremental backup, you need to prepare the backup files with the following commands:

sudo innobackupex --apply-log --redo-only /dbbackup/full/2015-05-22_05-45-54
sudo innobackupex --apply-log /dbbackup/full/2015-05-22_05-45-54 --incremental-dir=/dbbackup/inc/2015-05-22_05-48-12
sudo innobackupex --apply-log /dbbackup/full/2015-05-22_05-45-54

To restore the database to the state of the second incremental backup, you need to prepare the backup files with the following commands:

sudo innobackupex --apply-log --redo-only /dbbackup/full/2015-05-22_05-45-54
sudo innobackupex --apply-log --redo-only /dbbackup/full/2015-05-22_05-45-54 --incremental-dir=/dbbackup/inc/2015-05-22_05-48-12
sudo innobackupex --apply-log /dbbackup/full/2015-05-22_05-45-54 --incremental-dir=/dbbackup/inc/2015-05-22_05-51-32
sudo innobackupex --apply-log /dbbackup/full/2015-05-22_05-45-54

Notes:

For incremental backups, You should use the --redo-only option on all but the last incremental backup. Nevertheless, using this option on the last incremental backup is still harmless to the consistency of your data - it will only cause some delay due to the database rollback.

The last command of each incremental scenario is optional but recommended, because it will accelerate the restore.

After preparation, the changes recorded in the incremental backup files will be appended to the prepared base full backup files, so you should always use the prepared full backup files to restore your database, no matter if you choose a full backup or an incremental backup.

Step 9: Restore database

Before you can restore your database, you need to stop the database service.

sudo service mysqld stop

You also need to empty the database directory. You can move the current database files to another place for precaution.

sudo mkdir /currentdb
sudo mv /var/lib/mysql/* /currentdb

Restore your database with the prepared "full backup" files.

sudo innobackupex --copy-back /dbbackup/full/2015-05-22_05-45-54

Because the restore procedure will modify the owner of the database directory, you need to change it back to mysql:mysql to make it operational.

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

Restart the database service.

sudo service mysqld start

That's it. At this point, you can visit your WordPress site to verify that the restore process was successful.