Please help us update it! We pay for new and updated articles.
See Vultr's Request for Article (RFA) System for details and include RFA# 439 in your request.
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.
I assume that you have deployed a One-Click WordPress Vultr server instance from scratch and have logged in as root, using SSH.
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
1) Create a new user. Replace
sysuser with your own username.
2) Set the password for your new user. Replace
sysuser with your own username.
3) Grant root permissions to your new user.
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.
4) Switch to your new user account.
Then, use the new user's credentials to log in from your terminal window.
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.
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:
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.
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.
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
First, you need to add user
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
sudo chown -R sysuser:sysuser /dbbackup
Log out to put these changes into effect.
Then, log in with
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,
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
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.
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 (
to_lsn). You can use those numbers to clarify your database restore strategy. Look at the examples below.
xtrabackup_checkpoints file of the first full backup, I have:
backup_type = full-backuped from_lsn = 0 to_lsn = 2932478
xtrabackup_checkpoints file of the first incremental backup, I have:
backup_type =incremental from_lsn = 2932478 to_lsn = 2970177
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
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.
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.