Percona XtraBackup is a hot backup utility that suits all versions of MySQL and Percona Server for MySQL. The utility is completely free and open source. This post should provide you some more insight of how it actually works and what it does.
Percona XtraBackup Installation
Before installing the tool, do note that in certain versions of MariaDB the effectiveness of the tool might vary:
- In MariaDB 10.0 and before, Percona XtraBackup 2.3 is supported;
- In MariaDB 10.1, Percona XtraBackup is supported, but only if:
- InnoDB tables are not compressed (in other words, if InnoDB does not use page compression);
- InnoDB does not use data-at-rest encryption;
- The InnoDB page size in bytes is left at 16k (the default value).
- In MariaDB 10.2, Percona XtraBackup is supported, but also only if the above three scenarios are true. Users should also note that backups taken using XtraBackup may fail to recover some transactions due to a bug that was fixed in MariaDB 10.2.2. MariaDB 10.2.19 and Percona XtraBackup 2.4 may not be a good combination either – you might see the following error:
InnoDB: Unsupported redo log format. The redo log was created with MariaDB 10.2.19. Please follow the instructions at http://dev.mysql.com/doc/refman/5.7/en/upgrading-downgrading.html
- In MariaDB 10.3 and later versions, Percona XtraBackup is not supported – this issue is known, but there is no certainty that it will be fixed. In this case, you should use Mariabackup instead.
How does Percona XtraBackup work?
In a nutshell, Percona XtraBackup copies the InnoDB data files, then performs crash recovery on them. When the tool starts, it remembers the Log Sequence Number which is an integer that acts as a version to the REDO (ib_logfile*) log entries (in essence, everytime data is changed, LSN is incremented). At the same time, Percona XtraBackup continually monitors the transaction log files and copies changes from them – the transaction log records are used to monitor changes to the data files since the tool is first executed.
Percona Server 5.6 and higher is also able to use backup locks as an alternative to FLUSH TABLES WITH READ LOCK, which acquires a global read lock. When backup locks are supported by the server, Percona XtraBackup will perform the following steps:
- The InnoDB data will be copied;
- Percona XtraBackup will execute the LOCK TABLES FOR BACKUP query and copy the MyISAM tables and the corresponding .frm files (they are used to define the format of a table used by MySQL);
- The backup process will begin – backed up files include .frm, .MRG, .MRD, .MYI, .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV, .par and .opt files. Each of those files are used for different things – .frm files are used to define the format of a table used by MySQL, .MRG files are MySQL merge files, .MYI files contain MyISAM table index data, .TRG files are trigger parameter files, .par files are the partition definition files, .opt files are database configuration files etc.
After the above steps are completed, all operations that alter the binary log position will be blocked – a LOCK BINLOG FOR BACKUP query will be run. Percona XtraBackup will then finish copying the REDO log files and fetch the binary log coordinates, then unlock the binary log and tables and exit returning 0.
When restoring a backup, Percona XtraBackup will take a look at the datadir, innodb_data_home_dir, innodb_data_file_path, and innodb_log_group_home_dir variables from my.cnf and verify that the directories exist. It will copy MyISAM data (tables, indexes, etc.) first, InnoDB tables and indexes next, and the log files at last.
Using Percona XtraBackup
To create a backup using Percona XtraBackup, run xtrabackup with the –backup option:
$ xtrabackup --backup --target-dir=/backups/
This will store the backup in the backups directory. During the backup process, you should see a lot of output that tells you in what phase the tool is at currently – that’s normal. When the backup completes, you should see a message that says completed OK!
After the backup has completed, your backups directory should contain a backup-my.cnf file, the ibdata1 file, a few XtraBackup files (xtrabackup_*), and some other files. Keep in mind that it is safe to pause xtrabackup at any time – it does not modify the database.
To restore the backup, you first need to prepare it before restoring it because the data files are not point-in-time consistent until they are prepared: if you try running InnoDB at this stage, the engine will stop working to avoid working on damaged data. To prepare the backup, run:
$ xtrabackup --prepare --target-dir=/backups/
To copy the backup to the datadir of the server, run:
$ xtrabackup --copy-back --target-dir=/backups/
To move the backup to the datadir of the server, replace –copy-back with –move-back. Do note that the MySQL server needs to be shut down before restore operations are performed, the datadir must be empty when restoring the backup too.
Also keep in mind that XtraBackup will only work correctly if it is able to connect to the database server and perform operations on the server and the data directory, so consider checking the privileges and permission requirements too. To grant a MySQL user privileges you can use the following query:
GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO ‘bkpuser’@’localhost’;
Follow it up with FLUSH PRIVILEGES; and you should be good to go.More information on Percona’s XtraBackup can be found at its documentation page.
Percona XtraBackup is an open-source hot backup utility for MySQL-based servers. Some of the benefits that the tool provides include quick and reliable backups, automatic backup verification, and non-blocking backups for databases running the InnoDB and XtraDB storage engines. To learn more, take a look at the Percona’s documentation of the tool.