blog

How to Backup an Encrypted Database with Percona Server for MySQL 8.0

Bart Oles

Published December 3, 2019

Production interruptions are nearly guaranteed to happen at some point in time. We know it so we plan backups, create recovery standby databases, convert single instances into clusters.

Admitting the need for a proper recovery scenario, we must analyze the possible disaster timeline and failure scenarios and implement steps to bring your database up. Planned outage execution can help prepare, diagnose, and recover from the next one. To mitigate the impact of downtime, organizations need an appropriate recovery plan, which would include all factors required to bring service into life.

Backup Management is not as mild as just scheduling a backup job. There are many factors to consider, such as retention, storage, verification, and whether the backups you are taking are physical or logical and what is easy to overlook security.

Many organizations vary their approach to backups, trying to have a combination of server image backups (snapshots), logical and physical backups stored in multiple locations. It is to avoid any local or regional disasters that would wipe up our databases and backups stored in the same data center.

We want to make it secure. Data and backups should be encrypted. But there are many implications when both options are in place. In this article, we will take a look at backup procedures when we deal with encrypted databases.

Encryption-at-Rest for Percona Server for MySQL 8.0

Starting from MySQL 5.7.11, the community version of MySQL began support for InnoDB tablespace encryption. It is called Transparent Tablespace Encryption or referred to as Encryption-at-Rest.

The main difference compared to the enterprise version is the way the keys are stored – keys are not located in a secure vault, which is required for regulatory compliance. The same applies to Percona Server, starting version 5.7.11, it is possible to encrypt InnoDB tablespace. In the Percona Server 8.0, support for encrypting binary logs has been greatly extended. Version 8.0 added

(Per Percona 8.0 release doc):

Temporary File Encryption
InnoDB Undo Tablespace Encryption
InnoDB System Tablespace Encryption (InnoDB System Tablespace Encryption)
default_table_encryption =OFF/ON (General Tablespace Encryption)
table_encryption_privilege_check =OFF/ON (Verifying the Encryption Settings)
InnoDB redo log encryption (for master key encryption only) (Redo Log Encryption)
InnoDB merge file encryption (Verifying the Encryption Setting)
Percona Parallel doublewrite buffer encryption (InnoDB Tablespace Encryption)

For those interested in-migration from MySQL Enterprise version to Percona – It is also possible to integrate with Hashicorp Vault server via a keyring_vault plugin, matching the features available in Oracle’s MySQL Enterprise edition.

Data at rest encryption requires that a keyring plugin. There are two options here:

keyring_file – a flat-file with an encryption key
Keyring Vault plugin – a service

How to Enable Tablespace Encryption

To enable encryption start your database with the –early-plugin-load option:

either by hand:

$ mysqld --early-plugin-load="keyring_file=keyring_file.so"

or by modifying the configuration file:

[mysqld]

early-plugin-load=keyring_file.so

Starting Percona Server 8.0 two types of tablespaces can be encrypted. General tablespace and system tablespace. Sys tablespace is controlled via parameter innodb_sys_tablespace_encrypt. By default, the sys tablespace is not encrypted, and if you have one already, it’s not possible to convert it to encrypted state, a new instance must be created (start an instance with –bootstrap option).

General tablespace support encryption either of all tables in tablespace or none. It’s not possible to run encryption in mixed mode. In order to create ate tablespace with encryption use ENCRYPTION=’Y/N’ flag.

Example:

mysql> CREATE TABLESPACE severalnines ADD DATAFILE 'severalnines.ibd' ENCRYPTION='Y';

Backing up an Encrypted Database

When you add encrypted tablespaces it’s necessary to include keyring file in the xtrabackup command. To do it you must specify the path to a keyring file as the value of the –keyring-file-data option.

$ xtrabackup --backup --target-dir=/u01/mysql/data/backup/ --user=root --keyring-file-data=/u01/secure_location/keyring_file

Make sure to store the keyring file in a secure location. Also, make sure to always have a backup of the file. Xtrabackup will not copy the keyring file in the backup directory. To prepare the backup, you need to make a copy of the keyring file yourself.

Preparing the Backup

Once we have our backup file we should prepare it for the recovery. Here you also need to specify the keyring-file-data.

$ xtrabackup --prepare --target-dir=/u01/mysql/data/backup/ --keyring-file-data=/u01/secure_location/keyring_file

The backup is now prepared and can be restored with the –copy-back option. In the case that the keyring has been rotated, you will need to restore the keyring (which was used to take and prepare the backup).

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

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

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

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

Performing Incremental Backups

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

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

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

The /data/backups/inc1/ directory should now contain delta files, such as ibdata1.delta and test/table1.ibd.delta.

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

So far the process of backing up the database is similar to a regular backup, except for the flag where we specified location of keyring file.

Unfortunately, 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.

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

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

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

$ 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 last step is to restore it 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.

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= 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

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