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
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:
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:
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.
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
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
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:
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
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
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.
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
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
In case of keyring_vault, it will look like this:
$ xtrabackup --copy-back --target-dir=/data/backup --datadir=/data/mysql