Chapter 6 Working with Encrypted InnoDB Tables

MySQL Enterprise Backup supports encrypted InnoDB tablespaces. For details on how the MySQL server encrypts and decrypts InnoDB tables, see InnoDB Data-at-Rest Encryption—it explains concepts like master key and tablespace keys, which are important for understanding how MySQL Enterprise Backup works with encrypted InnoDB tablespaces.

When InnoDB tablespace encryption uses a centralized key management solution, the feature is referred to as “MySQL Enterprise Transparent Data Encryption (TDE).”

The following is a brief description on how encrypted InnoDB tables are handled by MySQL Enterprise Backup in backup, restore, and apply-log operations.

Backing up a database with encrypted InnoDB tables. The following is a typical command for backing up a database containing encrypted InnoDB tables:

$ mysqlbackup --user=root --password --backup-image=/home/admin/backups/my.mbi --backup-dir=/home/admin/backup-tmp \
    --encrypt-password="password" backup-to-image

During the backup operation, mysqlbackup copies the encrypted InnoDB tablespace files into the backup, and also performs the following actions:

For MySQL Enterprise Backup 4.1.0, MySQL Enterprise Backup 4.1.1 and later working with MySQL Enterprise Server 5.7.20 and earlier, or MySQL Enterprise Backup 4.1.1 and later working with MySQL Community Server 5.7:
- For online backups, mysqlbackup contacts the MySQL server to determine the keyring plugin the server is using, which, currently, is either one of keyring_file or keyring_okv (for offline backups, the --keyring option must be used to convey the same information to mysqlbackup). mysqlbackup also finds out from the server where to access the keyring (for offline backup, the --keyring_file_data or --keyring_okv_conf_dir option must be used to supply the same information). Once mysqlbackup has access to the keyring, it obtains the master key and uses it to decrypt the encrypted tablespace keys, which were used to encrypt the InnoDB tables on the server.
- Using the user password supplied with the option --encrypt-password, mysqlbackup reencrypts the tablespace keys. For each encrypted table, the reencrypted tablespace key, together with other information, is stored into a transfer file (with the .bkt extension), which is saved into the backup.
For MySQL Enterprise Backup 4.1.1 and later working with MySQL Enterprise Server 5.7.21 and later:
- MySQL Enterprise Backup always stores the master key for encryption in an encrypted file inside the backup, irrespective of the kind of keyring plugin the server uses.
- mysqlbackup contacts the MySQL server to determine the keyring plugin the server is using, which, currently, is one of keyring_encrypted_file, keyring_file, keyring_okv, or keyring_aws.
- If the server is using the keyring_encrypted_file plugin, the user must use the option --encrypt-password to supply to mysqlbackup the keyring file encryption password that has been set on the server with the keyring_encrypted_file_password option. mysqlbackup then copies over from the server the encrypted keyring data file, which contains the master key used to encrypt all the tablespace keys, into the meta folder in the backup. The encrypted tablespace files are also copied into the backup.
- If the server uses a keyring plugin other than keyring_encrypted_file, mysqlbackup accesses the keyring to obtain the master key and uses it to decrypt the encrypted tablespace keys, which were used to encrypt the InnoDB tables on the server. The master key is then put into a keyring data file encrypted with the user password supplied with the option --encrypt-password and saves the file, named keyring_kef, into the meta folder in the backup.

Note

Users who do not want to supply the password on the command line or in a defaults file may use the --encrypt-password option without specifying any value; mysqlbackup then asks the user to type in the password before the operation starts. This applies to all commands that use the --encrypt-password option.

An extract or image-to-backup-dir command for an image backup containing encrypted InnoDB tables does not require the --encrypt-password option.

Restoring a backup with encrypted InnoDB tables. The following is a typical command for restoring a single-file back up containing encrypted InnoDB tables:

$ mysqlbackup  --defaults-file=/usr/local/mysql/my.cnf  --backup-image=/home/admin/backups/my.mbi \
    --backup-dir=/home/admin/restore-tmp --encrypt-password="password" copy-back-and-apply-log

For MySQL Enterprise Backup 4.1.0, or MySQL Enterprise Backup 4.1.1 and later working with MySQL 5.7.20 and earlier: During the restore operation, mysqlbackup copies the encrypted InnoDB tablespace files onto the server. mysqlbackup also performs the following actions:

Using the user password supplied with the option --encrypt-password, which should be the same password used for backing up the database, mysqlbackup decrypts the tablespace keys, which were encrypted using the password when the backup was performed earlier.
If the --generate-new-master-key option is used, mysqlbackup generates a new master key and uses it to reencrypt the tablespace keys. To use the --generate-new-master-key option, the --keyring option, as well as the --keyring_file_data option (when --keyring=keyring_file) or --keyring_okv_conf_dir option (when --keyring=keyring_okv) must be specified, so mysqlbackup can access the keyring and add the new master key to it.
```
$ mysqlbackup  --defaults-file=/usr/local/mysql/my.cnf  --backup-image=/home/admin/backups/my.mbi \
  --backup-dir=/home/admin/restore-tmp --encrypt-password="password" \ 
  --generate-new-master-key --keyring=keyring_file --keyring-file-data=path-to-keyring-file \
    copy-back-and-apply-log
```
The keyring parameters should then be supplied to the restored server.
If the --generate-new-master-key is not used, mysqlbackup assumes that the same keyring used on the server when it was backed up continues to be valid and is available to the restored server.

For MySQL Enterprise Backup 4.1.1 and later working with MySQL 5.7.21 and later: The same password used for backing up the database must be supplied with the --encrypt-password option for a restore operation. During a restore, mysqlbackup copies the encrypted InnoDB tablespace files and the encrypted file containing the master keys (keyring_kef) onto the server. It also performs the following actions:

For a MySQL Enterprise Server: mysqlbackup restores the encrypted keyring data file to its proper location on the server. The restored server has to be started with keyring_encrypted_file plugin and with the options keyring_encrypted_file_data and keyring_encrypted_file_password (which should supply the server with the same password used with the --encrypt-password option during the restore).
For a MySQL Community Server: The keyring_file plugin is the only keyring plugin supported by the MySQL Community Server; therefore mysqlbackup uses the password supplied with the --encrypt-password option to decrypt keyring data file and then restores it to the proper location on the server for the keyring_file plugin to use.

For Incremental Backups. For a series of incremental backups, if a keyring plugin other than keyring_encrypted_file is being used on the server, users can provide a different value for --encrypt-password for any of the full or incremental backup in the backup sequence. However, the password used to make the specific full or incremental backup must be provided to restore that backup. When starting the server after restoring a series of incremental backups, the password used for the restore of the last incremental backup should be supplied to the server (except for a MySQL Community Server, which will start with the keyring_file plugin and does not require the keyring_encrypted_file_password option to start).

Advanced: Creating and Restoring a directory backup with encrypted InnoDB tables. The following is a typical command for creating a directory backup containing encrypted InnoDB tables:

$ mysqlbackup --user=root --password --backup-dir=/home/admin/backup \
    --encrypt-password="password" backup

The following is a typical command for preparing the backup with the apply-log command:

$ mysqlbackup --backup-dir=/home/admin/backup  --encrypt-password="password" apply-log

Notice that the user password must be supplied with the --encrypt-password option, as the tablespace keys must be decrypted before the log can be applied. The same requirement applies when you try to update a backup with an incremental backup using the apply-incremental-backup command:

$ mysqlbackup  --backup-dir=/home/admin/backup --incremental-backup-dir=/home/admin/backup-in \
    --encrypt-password="password" apply-incremental-backup

If you used different values for --encrypt-password for the full or incremental backups in the backup sequence, make sure you supply the very password you used to create an individual backup when you perform an apply-log or apply-incremental-backup operation with it.

Next, a copy-back command restores the prepared backup onto the server:

$ mysqlbackup  --defaults-file=/usr/local/mysql/my.cnf  --backup-dir=/home/admin/backup copy-back

Notice that the --encrypt-password option is not required for this step.

You can combine the two steps of apply-log and copy-back into one by running the copy-back-and-apply-log command, for which the --encrypt-password option is required:

$ mysqlbackup  --defaults-file=/usr/local/mysql/my.cnf  --backup-dir=/home/admin/backup \ 
  --encrypt-password="password" copy-back-and-apply-log

For MySQL Enterprise Backup 4.1.0, or MySQL Enterprise Backup 4.1.1 and later working with MySQL 5.7.20 and earlier: You can also use the --generate-new-master-key option, just like when you are restoring a single-file backup:

$ mysqlbackup  --defaults-file=/usr/local/mysql/my.cnf  --backup-dir=/home/admin/backup \ 
  --generate-new-master-key --keyring=keyring_file --keyring-file-data=path-to-keyring-file \
  --encrypt-password="password" copy-back-and-apply-log

Limitations. Certain limitations apply when MySQL Enterprise Backup works with encrypted InnoDB tables:

For MySQL 5.7.11 and earlier, backup for InnoDB tablespaces encrypted with “MySQL Enterprise Transparent Data Encryption (TDE)” is not supported by mysqlbackup. To perform a backup for those tables, upgrade the server to the latest MySQL 5.7 release, paying attention to any upgrade requirements explained in Changes in MySQL 5.7, especially the one regarding the --early-plugin-load option, and rotate the master key on the upgraded server using the ALTER INSTANCE ROTATE INNODB MASTER KEY statement. Proceed then with the backup process.
During a validate operation, if mysqlbackup encounters any encrypted InnoDB tables, it issues a warning and then skips over them.
For partial backups using transportable table spaces (that is, when the --use-tts option is used), encrypted InnoDB tables are never included in a backup. A warning is issued in the log file whenever an encrypted InnoDB table that matches the table selection criteria has been skipped over.
The --skip-unused-pages option has no effect on encrypted InnoDB tables during a backup (that is, empty pages for those tables are not skipped).
Offline backups of encrypted InnoDB tables are not supported by MySQL Enterprise Backup 4.1.1 and later when working with MySQL Enterprise Server 5.7.21 and later.