Encrypted InnoDB tablespace backups Encrypted InnoDB tablespace backups¶
Starting from MySQL 5.7, 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 an authenticated user or application to access an encrypted tablespace,
InnoDB will use the master encryption key to decrypt the tablespace key. The
master encryption key is stored in a keyring. xtrabackup supports two keyring
plugins: keyring_file, and keyring_vault. These plugins are installed
into the plugin directory.
Making a backup¶
Using keyring_file plugin¶
In order to backup and prepare a database containing encrypted InnoDB
tablespaces, specify the path to a keyring file as the value of the
--keyring-file-data option.
$ xtrabackup --backup --target-dir=/data/backup/ --user=root \
--keyring-file-data=/var/lib/mysql-keyring/keyring
Note
for use with MySQL prior to 5.7.13, the --server-id option
is added to the backup creation command, making the previous example look like
the following:
$ 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 the keyring file into the backup directory. In order to prepare the backup, you must make a copy of the keyring file yourself.
Preparing the Backup
In order to prepare the backup you need to specify the keyring-file-data
(server-id is stored in the 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!
The backup is now prepared and can be restored with the --copy-back
option. In case the keyring has been rotated, you need to restore the keyring
which was used to take and prepare the backup.
Using keyring_vault plugin¶
The 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
Command like
$ xtrabackup --backup --target-dir=/data/backup --user=root
will create a backup in the /data/backup directory.
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!
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 the default
my.cnf configuration file during prepare, the user will need to specify
keyring settings via the command line:
$ xtrabackup --prepare --target-dir=/data/backup \
--keyring-vault-config=/etc/vault.cnf
Note
Please look here for a description of keyring vault plugin settings.
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!
The backup is now prepared and can be restored with the --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 the keyring file into the backup directory. In order to
prepare the backup, you must make a copy of the 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
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 the keyring file into the backup directory. In order to prepare the backup, you must make a copy of the 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 Incremental Backups
The --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 --apply-log-only option to prevent the
rollback phase.
Warning
If you do not use the --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 --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
The 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
--apply-log-only should be used when merging all
incrementals except the last one. That’s why the previous line doesn’t contain
the --apply-log-only option. Even if the --apply-log-only
was 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 --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.
Restoring a Backup When Keyring Is not Available¶
While the described restore method works, it requires an access to the same keyring that the server is using. It may not be possible if the backup is prepared on a different server or at a much later time, when keys in the keyring are purged, or, in the case of a malfunction, when the keyring vault server is not available at all.
The --transition-key=<passphrase> option should be used to make it possible
for xtrabackup to process the backup without access to the keyring vault
server. In this case, xtrabackup derives the AES encryption key from the
specified passphrase and will use it to encrypt tablespace keys of tablespaces
that are being backed up.
Creating a Backup with a Passphrase
The following example illustrates how the backup can be created in this case:
$ xtrabackup --backup --user=root -p --target-dir=/data/backup \
--transition-key=MySecetKey
If --transition-key is specified without a value, xtrabackup will ask for
it.
Note
xtrabackup scrapes --transition-key so that its value is not visible 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
There is no --keyring-vault... or --keyring-file... options 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 a 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 a new master key, store it in the target keyring vault server and re-encrypt the tablespace keys using this key.
Making the Backup with a Stored Transition Key
Finally, there is an option to store a transition key in the keyring. In this case, xtrabackup will need to access the same keyring file or vault server during prepare and copy-back but does not depend on whether the server keys have been purged.
In this scenario, the three stages of the backup process look as follows.
Backup
$ xtrabackup --backup --user=root -p --target-dir=/data/backup \ --generate-transition-key .. warning:: This usage of the :option:`--generate-transition-key` option is only applicable if |Percona XtraBackup| is used with |Percona Server| version lower than 8.0.15-6. For |Percona Server| versions higher than 8.0.15-6, :option:`--generate-transition-key` should not be used for making full backups with the `keyring_file` plugin.
Prepare
keyring_filevariant:$ xtrabackup --prepare --target-dir=/data/backup \ --keyring-file-data=/var/lib/mysql-keyring/keyring
keyring_vaultvariant:$ xtrabackup --prepare --target-dir=/data/backup \ --keyring-vault-config=/etc/vault.cnf
Copy-back
keyring_filevariant:$ xtrabackup --copy-back --target-dir=/data/backup --datadir=/data/mysql \ --generate-new-master-key --keyring-file-data=/var/lib/mysql-keyring/keyring
keyring_vaultvariant:$ 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 professional services, contact Percona Sales.