For this example, the following software versions were in use:

  1. Ubuntu v20.04.3
  2. MySQL server v5.7.35
  3. Percona XtraBackup v2.4.24

It is important to note that Percona XtraBackup 8.0 is not compatible with MySQL version 5.7 and was introduced for use with MySQL version 8.0. As of the writing of this document, there does not exist a Percona XtraBackup version compatible with the most recent version of MySQL (8.0.28); see for more details.

This document shows a step-by-step example of setting up and restoring incremental backups using Percona XtraBackup on MySQL 5.7.

This document will use “$” to denote a command used in the operating system terminal and “>” to denote any commands used in the MySQL client command line.


The necessary permissions to perform a Percona XtraBackup are easily found on the Percona website: and are as follows (for this example all os permissions were taken care of using root privileges):

Permission ListExamplePermissions Used for Example
OSREAD, WRITE, EXECUTE at filesystem level for the MySQL datadir$sudo xtrabackup$sudo -i
(root user)
Table 1: Minimum permissions necessary for Percona XtraBackup. Note: $ is used to denote the os command line whereas > is used to denote the MySQL client command line.


Full backup:

An incremental backup must first begin with a full backup. The command is called like: “$xtrabackup –backup –user=bkupusr –password –target-dir=/home/user/fullBackupDestination,” where target-dir is the destination for the full backup. In this example the target directory will be /home/bkupusr/xtra/full. If the target directory does not already exist it will be created (Figures 2 and 3).

Percona XtraBackup Example Image1
Figure 1: A full backup taken using Percona XtraBackup. Note this example does not use the options –user or –password because a login-path has been setup using the program mysql_config_editor.

Figure 1 shows the full backup being taken but without the options –user or –password as the login credentials have been set up using $mysql_config_editor. A tutorial on this topic can be found here:

Percona XtraBackup Example Image2
Figure 2: Backup taken when target-dir doesn’t already exist.
Percona XtraBackup Example Image3
Figure 3: A new directory has been created as the specified target-dir path.

Incremental Backup:

After the initial full backup is set up incremental backups are next. The command for an incremental backup is very similar to that of a full, but an additional directory is required. Now the target-dir will be where the new incremental files go and the option “–incremental-basedir” is the location of the most recent backup taken (in this case the full backup); Figure 4 shows a first incremental backup taken from the previous full backup.

Percona XtraBackup Example Image4
Figure 4: The first incremental backup. The full backup is used as the incremental-basedir and a new destination is selected for which target-dir the incremental backup will go to.

For the next incremental the command is the same, but now the target-dir will specify the location of the next incremental backup, and incremental-basedir will specify the most recent incremental backup as shown in figure 5.

Percona XtraBackup Example Image5
Figure 5: A second incremental backup taken using the first incremental backup as the incremental-basedir.

Now there exist three backups (Figures 6-8): one full and two incrementals.

Percona XtraBackup Example Image6
Figure 6: Directory contents of the full backup.
Percona XtraBackup Example Image7
Figure 7: Directory contents for the first incremental backup.
Percona XtraBackup Example Image8
Figure 8: Directory contents for the second incremental backup.

For additional incremental backups continue on in this fashion moving the previous target-dir to the current incremental-basedir.


The final step is to restore the backups. There is some setup before the restore can actually be executed:

  1. MySQL service must be stopped
  2. MySQL data directory must be empty
Percona XtraBackup Example Image9
Figure 9: Restore Prep step 1 – stop mysql server.
Percona XtraBackup Example Image10
Figure 10: Restore prep step 2 – clear out the MySQL data directory.

A full backup restore is relatively simple:

  1. The backup is first prepared:
    • $xtrabackup –prepare –target-dir=/home/usr/full_bkup_directory
      • note the –prepare option.
  2. The full backup is restored to the empty data directory:
    • $xtrabackup –copy-back –target-dir=/home/usr/full_bkup_directory
      • –copy-back is the restore command
  3. Permissions must be checked/set on the newly restored backups:
    • $Chown mysql:mysql mysql
      • This is applied to all files recursively in the data directory
  4. Resume MySQL service:
    • $service mysql start
Percona XtraBackup Example Image11
Figure 11: Prepare the full backup for restore.
Percona XtraBackup Example Image12
Figure 12: The actual full backup restore.
Percona XtraBackup Example Image14
Figure 13: Change the permissions after the restore
Percona XtraBackup Example Image13
Figure 14: Resume MySQL service.

For an incremental restore the setup and copy-back steps are the same, but the prepare step is different; the additional parameter –apply-log-only must be included alongside the –prepare statement except on the final incremental backup.

The prepare steps for incremental backups are:

  1. Prepare full
    • $xtrabackup –prepare –apply-log-only –target-dir=/data/backups/full
  2. Apply first incremental backup to full backup
    • $xtrabackup –prepare –apply-log-only –target-dir=/data/backups/full –incremental-dir=/data/backups/inc1
  3. Apply second incremental backup to full backup
    • $xtrabackup –prepare –apply-log-only –target-dir=/data/backups/full –incremental-dir=/data/backups/inc2
Percona XtraBackup Example Image15
Figure 15: Preparing the full backup for incremental restore.
Percona XtraBackup Example Image16
Figure 16: Preparing the first incremental backup for restore.
Percona XtraBackup Example Image17
Figure 17: Preparing the second incremental backup for restore.

Again, the copy back steps will be the same as with the full backup (using the only the prepared full backup). After the copy back step the restore is complete.

Share This