Skip to content
Starting November 2023 Percona XtraBackup 2.4 has reached EOL status. If you have 5.7 databases, we encourage you to upgrade to 8.0 and then install Percona XtraBackup 8.0. Learn more

logo
Percona XtraBackup
Encrypted InnoDB Tablespace Backups
Initializing search
    percona/pxb-docs
    percona/pxb-docs
    • Home
      • About Percona XtraBackup
      • How Percona XtraBackup Works
      • Understand version numbers
      • Installing Percona XtraBackup 2.4
      • Installing Percona XtraBackup on Debian and Ubuntu
      • Installing Percona XtraBackup on Red Hat Enterprise Linux and CentOS
      • Installing Percona XtraBackup from a Binary Tarball
      • Compiling and Installing from Source Code
      • Running Percona XtraBackup in a Docker container
      • Connection and Privileges Needed
      • Configuring xtrabackup
      • The Backup Cycle - Full Backups
      • Incremental Backup
      • Compressed Backup
      • Encrypted Backup
      • Percona XtraBackup User Manual
      • Throttling Backups
      • Lockless binary log information
      • Encrypted InnoDB Tablespace Backups
        • Making a Backup Using keyring_file Plugin
          • Creating Backup
          • Preparing Backup
        • Making Backup Using keyring_vault Plugin
          • Creating Backup
          • Preparing the Backup
        • Incremental Encrypted InnoDB Tablespace Backups with keyring_file
          • Creating an Incremental Backup
          • Preparing the Incremental Backups
        • Restoring backup when keyring is not available
          • Creating the Backup with a Passphrase
          • Preparing the Backup with a Passphrase
          • Restoring the Backup with a Generated Key
        • Making the Backup with a Stored Transition Key
          • Backup
          • Prepare
          • keyring_file variant
          • keyring_vault variant
          • Copy-back
          • keyring_file variant
          • keyring_vault variant
      • lock-ddl-per-table Option Improvements
      • How-tos and Recipes
        • Release notes index
        • Percona XtraBackup 2.4.29 (2023-12-18)
        • Percona XtraBackup 2.4.28 (2023-04-04)
        • Percona XtraBackup 2.4.27 (2022-12-06)
        • Percona XtraBackup 2.4.26
        • Percona XtraBackup 2.4.25
        • Percona XtraBackup 2.4.24
        • Percona XtraBackup 2.4.23
        • Percona XtraBackup 2.4.22
        • Percona XtraBackup 2.4.21
        • Percona XtraBackup 2.4.20
        • Percona XtraBackup 2.4.19
        • Percona XtraBackup 2.4.18
        • Percona XtraBackup 2.4.17
        • Percona XtraBackup 2.4.16
        • Percona XtraBackup 2.4.15
        • Percona XtraBackup 2.4.14
        • Percona XtraBackup 2.4.13
        • Percona XtraBackup 2.4.12
        • Percona XtraBackup 2.4.11
        • Percona XtraBackup 2.4.10
        • Percona XtraBackup 2.4.9
        • Percona XtraBackup 2.4.8
        • Percona XtraBackup 2.4.7-2
        • Percona XtraBackup 2.4.7
        • Percona XtraBackup 2.4.6
        • Percona XtraBackup 2.4.5
        • Percona XtraBackup 2.4.4
        • Percona XtraBackup 2.4.3
        • Percona XtraBackup 2.4.2
        • Percona XtraBackup 2.4.1
      • The xtrabackup Option Reference
      • The innobackupex Option Reference
      • The xbcloud Binary
      • Exponential Backoff
      • Using the xbcloud binary with Microsoft Azure Cloud Storage
      • The xbcrypt binary
      • The xbstream binary
      • Known issues and limitations
      • Frequently Asked Questions
      • Glossary
      • Index of files created by Percona XtraBackup
      • Trademark policy
      • Copyright and licensing information
      • Version Checking

    • Making a Backup Using keyring_file Plugin
      • Creating Backup
      • Preparing Backup
    • Making Backup Using keyring_vault Plugin
      • Creating Backup
      • Preparing the Backup
    • Incremental Encrypted InnoDB Tablespace Backups with keyring_file
      • Creating an Incremental Backup
      • Preparing the Incremental Backups
    • Restoring backup when keyring is not available
      • Creating the Backup with a Passphrase
      • Preparing the Backup with a Passphrase
      • Restoring the Backup with a Generated Key
    • Making the Backup with a Stored Transition Key
      • Backup
      • Prepare
      • keyring_file variant
      • keyring_vault variant
      • Copy-back
      • keyring_file variant
      • keyring_vault variant

    Encrypted InnoDB Tablespace Backups¶

    As of MySQL 5.7.11, InnoDB supports data encryption for InnoDB tables stored in file-per-table tablespaces. This feature provides at-rest encryption for physical tablespace data files.

    For authenticated user or application to access encrypted tablespace, InnoDB will use master encryption key to decrypt the tablespace key. The master encryption key is stored in a keyring. Two keyring plugins supported by xtrabackup are keyring_file and keyring_vault. These plugins are installed into the plugin directory.

    Making a Backup Using keyring_file Plugin¶

    Support for encrypted InnoDB tablespace backups with keyring_file has been implemented in Percona XtraBackup 2.4.2 by implementing xtrabackup --keyring-file-data option (and also xtrabackup --server-id option, needed for MySQL prior to 5.7.13). These options are only recognized by xtrabackup binary i.e., innobackupex will not be able to backup and prepare encrypted tablespaces.

    Creating Backup¶

    In order to backup and prepare database containing encrypted InnoDB tablespaces, you must specify the path to keyring file by using the xtrabackup --keyring-file-data option.

    $ xtrabackup --backup --target-dir=/data/backup/ --user=root \
    --keyring-file-data=/var/lib/mysql-keyring/keyring
    

    With MySQL prior to 5.7.13, use xtrabackup --server-id in the backup creation command:

    $ xtrabackup --backup --target-dir=/data/backup/ --user=root \
    --keyring-file-data=/var/lib/mysql-keyring/keyring --server-id=1
    

    After xtrabackup is finished taking the backup you should see the following message:

    xtrabackup: Transaction log of lsn (5696709) to (5696718) was copied.
    160401 10:25:51 completed OK!
    

    Warning

    xtrabackup will not copy keyring file into the backup directory. In order to be prepare the backup, you must make a copy of keyring file yourself.

    Preparing Backup¶

    In order to prepare the backup you’ll need to specify the keyring-file-data (server-id is stored in backup-my.cnf file, so it can be omitted when preparing the backup, regardless of the MySQL version used).

    $ xtrabackup --prepare --target-dir=/data/backup \
    --keyring-file-data=/var/lib/mysql-keyring/keyring
    

    After xtrabackup is finished preparing the backup you should see the following message:

    InnoDB: Shutdown completed; log sequence number 5697064
    160401 10:34:28 completed OK!
    

    Backup is now prepared and can be restored with xtrabackup –copy-back option. In case the keyring has been rotated you’ll need to restore the keyring which was used to take and prepare the backup.

    Making Backup Using keyring_vault Plugin¶

    Support for encrypted InnoDB tablespace backups with keyring_vault has been implemented in Percona XtraBackup 2.4.11. Keyring vault plugin settings are described here.

    Creating Backup¶

    The following command creates a backup in the /data/backup directory:

    $ xtrabackup --backup --target-dir=/data/backup --user=root
    

    After xtrabackup completes taking the backup you should see the following message:

    xtrabackup: Transaction log of lsn (5696709) to (5696718) was copied.
    160401 10:25:51 completed OK!
    

    Preparing the Backup¶

    In order to prepare the backup xtrabackup will need an access to the keyring. Since xtrabackup doesn’t talk to MySQL server and doesn’t read default my.cnf configuration file during prepare, user will need to specify keyring settings via the command line:

    $ xtrabackup --prepare --target-dir=/data/backup \
    --keyring-vault-config=/etc/vault.cnf
    

    See also

    Data at Rest Encryption for Percona Server [keyring vault plugin settings] (https://www.percona.com/doc/percona-server/LATEST/management/data_at_rest_encryption.html#keyring-vault-plugin).

    After xtrabackup completes preparing the backup you should see the following message:

    InnoDB: Shutdown completed; log sequence number 5697064
    160401 10:34:28 completed OK!
    

    The backup is now prepared and can be restored with xtrabackup --copy-back option:

    $ xtrabackup --copy-back --target-dir=/data/backup --datadir=/data/mysql
    

    Incremental Encrypted InnoDB Tablespace Backups with keyring_file¶

    The process of taking incremental backups with InnoDB tablespace encryption is similar to taking the Incremental Backups with unencrypted tablespace.

    Creating an Incremental Backup¶

    To make an incremental backup, begin with a full backup. The xtrabackup binary writes a file called xtrabackup_checkpoints into the backup’s target directory. This file contains a line showing the to_lsn, which is the database’s LSN at the end of the backup. First you need to create a full backup with the following command:

    $ xtrabackup --backup --target-dir=/data/backups/base \
    --keyring-file-data=/var/lib/mysql-keyring/keyring
    

    Warning

    xtrabackup will not copy keyring file into the backup directory. In order to be prepare the backup, you must make a copy of keyring file yourself. If you try to restore the backup after the keyring has been changed you’ll see errors like ERROR 3185 (HY000): Can't find master key from keyring, please check keyring plugin is loaded. when trying to access encrypted table.

    If you look at the xtrabackup_checkpoints file, you should see some contents similar to the following:

    backup_type = full-backuped
    from_lsn = 0
    to_lsn = 7666625
    last_lsn = 7666634
    compact = 0
    recover_binlog_info = 1
    

    Now that you have a full backup, you can make an incremental backup based on it. Use a command such as the following:

    $ xtrabackup --backup --target-dir=/data/backups/inc1 \
    --incremental-basedir=/data/backups/base \
    --keyring-file-data=/var/lib/mysql-keyring/keyring
    

    Warning

    xtrabackup will not copy keyring file into the backup directory. In order to be prepare the backup, you must make a copy of keyring file yourself. If the keyring hasn’t been rotated you can use the same as the one you’ve backed-up with the base backup. If the keyring has been rotated you’ll need to back it up otherwise you won’t be able to prepare the backup.

    The /data/backups/inc1/ directory should now contain delta files, such as ibdata1.delta and test/table1.ibd.delta. These represent the changes since the LSN 7666625. If you examine the xtrabackup_checkpoints file in this directory, you should see something similar to the following:

    backup_type = incremental
    from_lsn = 7666625
    to_lsn = 8873920
    last_lsn = 8873929
    compact = 0
    recover_binlog_info = 1
    

    The meaning should be self-evident. It’s now possible to use this directory as the base for yet another incremental backup:

    $ xtrabackup --backup --target-dir=/data/backups/inc2 \
    --incremental-basedir=/data/backups/inc1 \
    --keyring-file-data=/var/lib/mysql-keyring/keyring
    

    Preparing the Incremental Backups¶

    The xtrabackup --prepare step for incremental backups is not the same as for normal backups. In normal backups, two types of operations are performed to make the database consistent: committed transactions are replayed from the log file against the data files, and uncommitted transactions are rolled back. You must skip the rollback of uncommitted transactions when preparing a backup, because transactions that were uncommitted at the time of your backup may be in progress, and it’s likely that they will be committed in the next incremental backup. You should use the xtrabackup --apply-log-only option to prevent the rollback phase.

    Warning

    If you do not use the xtrabackup --apply-log-only option to prevent the rollback phase, then your incremental backups will be useless. After transactions have been rolled back, further incremental backups cannot be applied.

    Beginning with the full backup you created, you can prepare it, and then apply the incremental differences to it. Recall that you have the following backups:

    /data/backups/base
    /data/backups/inc1
    /data/backups/inc2
    

    To prepare the base backup, you need to run xtrabackup --prepare as usual, but prevent the rollback phase:

    $ xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base \
    --keyring-file-data=/var/lib/mysql-keyring/keyring
    

    The output should end with some text such as the following:

    InnoDB: Shutdown completed; log sequence number 7666643
    InnoDB: Number of pools: 1
    160401 12:31:11 completed OK!
    

    To apply the first incremental backup to the full backup, you should use the following command:

    $ xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base \
    --incremental-dir=/data/backups/inc1 \
    --keyring-file-data=/var/lib/mysql-keyring/keyring
    

    Warning

    Backup should be prepared with the keyring that was used when backup was being taken. This means that if the keyring has been rotated between the base and incremental backup that you’ll need to use the keyring that was in use when the first incremental backup has been taken.

    Preparing the second incremental backup is a similar process: apply the deltas to the (modified) base backup, and you will roll its data forward in time to the point of the second incremental backup:

    $ xtrabackup --prepare --target-dir=/data/backups/base \
    --incremental-dir=/data/backups/inc2 \
    --keyring-file-data=/var/lib/mysql-keyring/keyring
    

    Note

    xtrabackup --apply-log-only should be used when merging each incremental backup except the last one. That’s why the previous line doesn’t contain xtrabackup --apply-log-only. Even if the xtrabackup --apply-log-only were used on the last step, backup would still be consistent but in that case server would perform the rollback phase.

    The backup is now prepared and can be restored with xtrabackup --copy-back. In case the keyring has been rotated you’ll need to restore the keyring which was used to take and prepare the backup.

    Restoring backup when keyring is not available¶

    While described restore method works, it requires an access to the same keyring which server is using. It may not be possible if backup is prepared on different server or at the much later time, when keys in the keyring have been purged, or in case of malfunction when keyring vault server is not available at all.

    A xtrabackup --transition-key should be used to make it possible for xtrabackup to process the backup without access to the keyring vault server. In this case xtrabackup will derive AES encryption key from specified passphrase and will use it to encrypt tablespace keys of tablespaces being backed up.

    Creating the Backup with a Passphrase¶

    The following example illustrates how a backup can be created in this case:

    $ xtrabackup --backup --user=root -p --target-dir=/data/backup \
    --transition-key=MySecretKey
    

    If xtrabackup --transition-key is specified without a value, xtrabackup will ask for it.

    Note

    xtrabackup --transition-key scrapes the supplied value so that it does not appear in the ps command output.

    Preparing the Backup with a Passphrase¶

    The same passphrase should be specified for the prepare command:

    $ xtrabackup --prepare --target-dir=/data/backup \
    --transition-key=MySecretKey
    

    There is no keyring-vault or keyring-file here, because xtrabackup does not talk to the keyring in this case.

    Restoring the Backup with a Generated Key¶

    When restoring a backup you will need to generate new master key. Here is the example for keyring_file:

    $ xtrabackup --copy-back --target-dir=/data/backup --datadir=/data/mysql \
    --transition-key=MySecetKey --generate-new-master-key \
    --keyring-file-data=/var/lib/mysql-keyring/keyring
    

    In case of keyring_vault it will look like this:

    $ xtrabackup --copy-back --target-dir=/data/backup --datadir=/data/mysql \
    --transition-key=MySecetKey --generate-new-master-key \
    --keyring-vault-config=/etc/vault.cnf
    

    xtrabackup will generate new master key, store it into target keyring vault server and re-encrypt tablespace keys using this key.

    Making the Backup with a Stored Transition Key¶

    Finally, there is an option to store transition key in the keyring. In this case xtrabackup will need access to the same keyring file or vault server during prepare and copy-back, but does not depend on whether the server keys have been purged.

    The three stages of the backup process are the following:

    Backup¶

    $ xtrabackup --backup --user=root -p --target-dir=/data/backup \
    --generate-transition-key
    

    Prepare¶

    keyring_file variant¶

    $ xtrabackup --prepare --target-dir=/data/backup \
    --keyring-file-data=/var/lib/mysql-keyring/keyring
    

    keyring_vault variant¶

    $ xtrabackup --prepare --target-dir=/data/backup \
    --keyring-vault-config=/etc/vault.cnf
    

    Copy-back¶

    keyring_file variant¶

    $ xtrabackup --copy-back --target-dir=/data/backup --datadir=/data/mysql \
    --generate-new-master-key --keyring-file-data=/var/lib/mysql-keyring/keyring
    

    keyring_vault variant¶

    $ xtrabackup --copy-back --target-dir=/data/backup --datadir=/data/mysql \
    --generate-new-master-key --keyring-vault-config=/etc/vault.cnf
    

    Contact us

    For free technical help, visit the Percona Community Forum.

    To report bugs or submit feature requests, open a JIRA ticket.

    For paid support and managed or consulting services , contact Percona Sales.

    2022-12-06
    Percona LLC and/or its affiliates, © 2024 Cookie Preferences
    Made with Material for MkDocs