About Percona XtraBackup

Overview

The Percona XtraBackup tools provide a method for performing a hot backup of your MySQL database while the system is running. It is a free, online, open source, complete database backup solution for all versions of Percona Server for MySQL and MySQL. Percona XtraBackup performs online non-blocking, tightly compressed, highly secure full backups on transactional systems so that applications remain fully available during planned maintenance windows.

Percona XtraBackup is available at: https://www.percona.com/software/mysql-database/percona-xtrabackup

Zmanda Recovery Manager is a web-based backup management tool built around Percona XtraBackup. ZRM is a commercial tool that uses Percona XtraBackup for Non-Blocking Backups:

"ZRM provides support for non-blocking backups of MySQL using |Percona XtraBackup|. ZRM with |Percona XtraBackup| provides resource utilization management by providing throttling based on the number of IO operations per second. |Percona XtraBackup| based backups also allow for table level recovery even though the backup was done at the database level (needs the recovery database server to be |Percona Server| with XtraDB)."

ZRM can be found at: https://www.zmanda.com/zrm-mysql-enterprise.html

How Does Percona XtraBackup Work

Percona XtraBackup is based on InnoDB‘s crash-recovery functionality. It copies InnoDB data files, which results in data that is internally inconsistent; but then it performs crash recovery on the files to make them a consistent, usable database again.

This works because InnoDB maintains a redo log, also called the transaction log. This contains a record of every change to InnoDB data. When InnoDB starts, it inspects the data files and the transaction log, and performs two steps. It applies committed transaction log entries to the data files, and it performs an undo operation on any transactions that modified data but did not commit.

Percona XtraBackup works by remembering the log sequence number (LSN) when it starts, and then copying away the data files. It takes some time to do this, so if the files are changing, then they reflect the state of the database at different points in time. At the same time, Percona XtraBackup runs a background process that watches the transaction log files, and copies changes from it. Percona XtraBackup needs to do this continually because the transaction logs are written in a round-robin fashion, and can be reused after a while. Percona XtraBackup needs the transaction log records for every change to the data files since it began execution.

Percona XtraBackup will use Backup locks where available as a lightweight alternative to FLUSH TABLES WITH READ LOCK. This feature is available in Percona Server 5.6+. Percona XtraBackup uses this automatically to copy non-InnoDB data to avoid blocking DML queries that modify InnoDB tables. When backup locks are supported by the server, xtrabackup will first copy InnoDB data, run the LOCK TABLES FOR BACKUP and copy the MyISAM tables and .frm files. Once this is done, the backup of the files will begin. It will backup .frm, .MRG, .MYD, .MYI, .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV, .par, and .opt files.

After that xtrabackup will use LOCK BINLOG FOR BACKUP to block all operations that might change either binary log position or Exec_Master_Log_Pos or Exec_Gtid_Set (i.e. master binary log coordinates corresponding to the current SQL thread state on a replication slave) as reported by SHOW MASTER/SLAVE STATUS. xtrabackup will then finish copying the REDO log files and fetch the binary log coordinates. After this is completed xtrabackup will unlock the binary log and tables.

Finally, the binary log position will be printed to STDERR and xtrabackup will exit returning 0 if all went OK.

Preparing a backup

During the preparation phase, Percona XtraBackup performs crash recovery against the copied data files, using the copied transaction log file. After this is done, the database is ready to be restored and used.

The backed-up MyISAM and InnoDB tables will be eventually consistent with each other, because after the preparation (recovery) process, InnoDB‘s data is rolled forward to the point at which the backup completed, not rolled back to the point at which it started. This point in time matches where the FLUSH TABLES WITH READ LOCK was taken, so the MyISAM data and the prepared InnoDB data are in sync.

Restoring a backup

Percona XtraBackup will read from the my.cnf (or mysql.conf in the case for ST) file the variables datadir, innodb_data_home_dir, innodb_data_file_path, innodb_log_group_home_dir and check that the directories exist.

It will copy the MyISAM tables, indexes, etc. (.frm, .MRG, .MYD, .MYI, .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV, par and .opt files) first, InnoDB tables and indexes next and the log files at last. It will preserve file’s attributes when copying them, but you may have to change the files’ ownership to the appropriate user before starting the database server, as they will be owned by the user who created the backup.

Incremental Backup

Percona XtraBackup supports incremental backups, which means that it can copy only the data that has changed since the last backup.

You can perform many incremental backups between each full backup, so you can set up a backup process such as a full backup once a week and an incremental backup every day, or full backups every day and incremental backups every hour.

Incremental backups work because each InnoDB page contains a log sequence number, or LSN. The LSN is the system version number for the entire database. Each page’s LSN shows how recently it was changed.

An incremental backup copies each page whose LSN is newer than the previous incremental or full backup’s LSN. There are two algorithms in use to find the set of such pages to be copied. The first one, available with all the server types and versions, is to check the page LSN directly by reading all the data pages. The second one, available with Percona Server, is to enable the changed page tracking feature on the server, which will note the pages as they are being changed. This information will be then written out in a compact separate so-called bitmap file. The xtrabackup binary will use that file to read only the data pages it needs for the incremental backup, potentially saving many read requests.

Incremental backups do not actually compare the data files to the previous backup’s data files. In fact, you can use xtrabackup --incremental-lsn to perform an incremental backup without even having the previous backup, if you know its LSN. Incremental backups simply read the pages and compare their LSN to the last backup’s LSN. You still need a full backup to recover the incremental changes, however; without a full backup to act as a base, the incremental backups are useless.

Other options

Percona XtraBackup has built-in support for compressed backups and for encrypted backups. This features increases a lot the time for backup, prepare and restore phases and they are not covered in this article.

Technically Percona XtraBackup can create partial backups for individual partitions. This is possible because from the storage engine's perspective partitions are regular tables with specially formatted names. The only requirement for this feature is having the innodb_file_per_table option enabled in the server. This means that you may backup only some specific tables or databases. Restoring partial backups should be done by importing the tables. This feature is not covered in this article.

More information how to restore individual tables can be found at the Percona XtraBackup's Documentation page

Required Privileges

Percona XtraBackup needs to be able to connect to the database server and perform operations on the server and the data directory when creating a backup, when preparing the backup and when restoring it. In order to do so, there are privileges and permission requirements for its execution that must be fulfilled.

An SQL example of creating a database user with the minimum privileges required to full backups would be:

mysql> CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY 's3cret';
mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'bkpuser'@'localhost';
mysql> FLUSH PRIVILEGES;

More information can be found at the Percona XtraBackup's Documentation page.

Version Checking

Percona XtraBackup contains “version checking” functionality which is a feature that enables Percona software users to be notified of available software updates to improve your environment security and performance. Alongside this, the version check functionality also provides Percona with information relating to which software versions you are running, coupled with the environment confirmation which the software is running within.

Version Check was implemented in Percona Toolkit 2.1.4, and was enabled by default in version 2.2.1. Currently it is supported as a --[no]version-check option by Percona XtraBackup.

Although the version checking feature does not collect any personal information, you might prefer to disable this feature, either one time or permanently. To disable it one time, use --no-version-check option when invoking the Percona XtraBackup. This is not done in examples below neither in the attached bash scripts.

NFS Volumes

The xtrabackup tool requires no special configuration on most systems. However, the storage where the --target-dir is located must behave properly when fsync() is called. In particular, Percona developers have noticed that NFS volumes not mounted with the sync option might not really sync the data. As a result, if you back up to an NFS volume mounted with the async option, and then try to prepare the backup from a different server that also mounts that volume, the data might appear to be corrupt. You can use the sync mount option to avoid this problem.

Throttling Backups

Although xtrabackup does not block your database’s operation, any backup can add load to the system being backed up. On systems that do not have much spare I/O capacity, it might be helpful to throttle the rate at which xtrabackup reads and writes data. You can do this with the xtrabackup --throttle option. This option limits the number of chunks copied per second. The chunk size is 10 MB.

By default, there is no throttling, and xtrabackup reads and writes data as quickly as it can. If you set too strict of a limit on the IOPS, the backup might be so slow that it will never catch up with the transaction logs that InnoDB is writing, so the backup might never complete.

Percona XtraBackup versions and installations

Percona XtraBackup is available only for Linux distributions.

Percona provides repositories for yum (RPM packages for Red Hat, CentOS and Amazon Linux AMI) and apt (.deb packages for Ubuntu and Debian). This makes it easy to install and update XtraBackup and its dependencies through operating system’s package manager.

There is also available precompiled generic Linux archives which can be used on SuSE (SLES) and other distributions.

Percona XtraBackup is open source and the code is available on Github. You can compile and install it from source code. The instructions for compiling and installing from source code can be found at the Percona XtraBackup's Documentation page

Percona XtraBackup 2.4 is the latest version for MySQL 5.5, 5.6 and 5.7 servers.

Percona XtraBackup 8.0 for MySQL 8.0 servers. Percona XtraBackup 8.0 does not support making backups of databases created in versions prior to 8.0 of MySQL as the changes that MySQL 8.0 introduced in data dictionaries, redo log and undo log are incompatible with previous versions, it is currently impossible for Percona XtraBackup 8.0 to also support versions prior to 8.0.

Percona XtraBackup for SecureTransport

SecureTransport currently uses MySQL server 5.6 and you should use Percona XtraBackup 2.4.

For Axway Appliance 7.x which has SLES 11 SP4 use percona-xtrabackup-2.4.13-Linux-x86_64.libgcrypt11.tar.gz from Percona XtraBackup's Downloads page [Linux - Generic].

Full documentation for installation and use with all available parameters can be found at the Percona XtraBackup's Documentation page.

User manual in PDF can be downloaded from here.

Examples of usage with ST

Below you will find several example commands for creating, preparing and restoring full and incremental ST backups.

WARNING: In a Standard Cluster environment create a backup for each node separately and make sure you are restoring the correct backup for the correct server - the same node it was taken from. Restoring another node's backup will not work. Some configuration options are node specific and the database contains unique node ID in the ConfigurationOption table. If you restore the database on a wrong node ST will start but will not find the specific options for the current node (as they will be the other node's) and will not operate properly.

As a sort of precaution to help you avoid mistakes, the Bash scripts attached to this acticle were updated to store the server's hostname when creating the backup and print a warning during restore if the hostname is different.

However, you must not rely solely on the script's verification functions and you should be extra careful on which server the restore is executed.

Backup (full)

xtrabackup --defaults-file=/opt/Axway/SecureTransport/conf/mysql.conf --backup --user=root --password=tumbleweed --parallel=4 --target-dir=/root/backup/full

Backup (incremental)

xtrabackup --defaults-file=/opt/Axway/SecureTransport/conf/mysql.conf --backup --user=root --password=tumbleweed --parallel=4 --target-dir=/root/backup/inc1 --incremental-basedir=/root/backup/full

xtrabackup --defaults-file=/opt/Axway/SecureTransport/conf/mysql.conf --backup --user=root --password=tumbleweed --parallel=4 --target-dir=/root/backup/inc2 --incremental-basedir=/root/backup/inc1

Prepare backup (full)

cp -r /root/backup/full /root/backup/prep

xtrabackup --prepare --use-memory=2G --target-dir=/root/backup/prep

Prepare backup (incremental)

cp -r /root/backup/full /root/backup/prep

xtrabackup --prepare --apply-log-only --use-memory=2G --target-dir=/root/backup/prep

cp -r /root/backup/inc1 /root/backup/inc

xtrabackup --prepare --apply-log-only --use-memory=2G --target-dir=/root/backup/prep --incremental-dir=/root/backup/inc

rm -rf /root/backup/inc

cp -r /root/backup/inc2 /root/backup/inc

xtrabackup --prepare --use-memory=2G --target-dir=/root/backup/prep --incremental-dir=/root/backup/inc

Optional:

xtrabackup --prepare --use-memory=2G --target-dir=/root/backup/prep

Restore prepared backup

rm -r -f /opt/Axway/SecureTransport/var/db/mysql/data/

mkdir -p /opt/Axway/SecureTransport/var/db/mysql/data/

xtrabackup --defaults-file=/opt/Axway/SecureTransport/conf/mysql.conf --copy-back --target-dir=/root/backup/prep

Bash scripts

Attached you could find 3 scripts to automate the process for creating, preparing and restoring ST backups with Percona XtraBackup. You should modify the paths at the beginning of the scripts to reflect the real paths in your environment.

ST_percona_backup.sh

This script performs online (hot) backup of MySQL on a running ST. It was designed to create one backup per day. If you run it more then once in the same day after the first successful backup the script will not do anything until next day. It creates one full and 6 incremental backups to cover the whole week. Schedule the script at least 2 times in crontab. The script will create a directory structure in the following way:

|- YYYY-MM-DD
|--- full
|--- inc1
|--- inc2
|--- inc3
|--- inc4
|--- inc5
|--- inc6

ST_percona_prepare.sh

This script prepares a created backups for use. It takes full backup and merge increments. The result is MySQL data dir ready for use. The script has one mandatory parameter - a date for which you want to prepare the database. It searches for a full backup and locates the appropriate subdirectory with either full or incremental backup. If the directory contains valid data it will attempt to prepare the database starting from full backup and merging all increments until the selected one. This operation can be done on another machine. For a huge database with a lot of modifications it is intensive CPU and IO processing taking significant amount of time. You can prepare the database at any time before restore manually. You can schedule a prepare phase everyday after a successful backup to have a ready for restore recent database copy. Examples how to run the script:

ST_percona_prepare.sh `date`

This gets the current date and can be used in crontab.

ST_percona_prepare.sh 2019-05-20

This is the simple form of date in the way the script uses it.

ST_percona_prepare.sh "Wed May 22 06:07:33 GMT 2019"

Any date format that produces a valid date can be used with the script.

ST_percona_restore.sh

This script just copies the prepared database to the original location where ST works with it. The script can stop the ST services before restoring the database. The restoration operation requires an ST outage. The script creates a backup of the existing database from a destination directory and then removes its content before the restoration. If the restoration fails it will copy back the original database from the backup it created earlier. The script does not start the ST services after restoration had completed. Instead of using this script you could manually use the rsync or cp or mv commands to restore the database. Here is an example with rsync command:

rsync -avrP /root/backup/prep /opt/Axway/SecureTransport/var/db/mysql/data/

You should check that the restored files have the correct ownership and permissions.

Note that the datadir must be empty before restoring the backup. Also it’s important to note that the MySQL server needs to be shut down before restore is performed. You cannot restore to a datadir of a running MySQL instance (except when importing a partial backup).

Final notes

Index of files created by Percona XtraBackup can be found at the Percona XtraBackup's Documentation page.

After restoring a MySQL database with Percona XtraBackup you will find some extra files in datadir like xtrabackup_info and xtrabackup_master_key_id. This indicates that Percona XtraBackup was used to restore the database. The file xtrabackup_info contains useful information about the backup used for restoring the database.

Happy backup-ing! Enjoy!

Evgeni Evangelov