Starting with release 4.0.2, 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 Oracle Key Vault (OKV) for encryption key management, 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="encryptpass" backup-to-image
During the backup operation, mysqlbackup copies the encrypted InnoDB tablespace files into the backup, and also performs the following actions:
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
orkeyring_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
(users who do not want to supply the password on the command line or in a default file may use the option without specifying any value; mysqlbackup then asks the user to type in the password before the operation starts), 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.
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 single-file 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="encryptpass" copy-back-and-apply-log
During the restore operation, mysqlbackup copies the encrypted InnoDB tablespace files onto the server, and 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 (users who do not want to supply the password on the command line or in a default file may use the option without specifying any value; mysqlbackup then asks the user to type in the password before the operation starts), 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="encryptpass" \ --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.
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="encryptpass" backup
The following is a typical command for preparing the backup with the
apply-log
command:
$ mysqlbackup --backup-dir=/home/admin/backup --encrypt-password="encryptpass" apply-log
Notice that the user password must be supplied with the
--encrypt-password
option (users
who do not want to supply the password on the command line or in a
default file may use the option without specifying any value;
mysqlbackup then asks the user to type in the
password before the operation starts), 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="encryptpass" apply-incremental-backup
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="encryptpass" copy-back-and-apply-log
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="encryptpass" 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).