MySQL Enterprise Backup supports encrypted InnoDB tablespaces. For details on how the MySQL server encrypts and decrypts InnoDB tables, see Tablespace 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.
For MySQL Enterprise Backup to backup encrypted InnoDB tables, the following must be true:
MySQL Enterprise Backup is not run by the operating system's
MySQL Enterprise Backup is run by an operating system user that has write permission for the keyring file, if the
keyring_awsplugin is used on the server.
When the database uses encrypted InnoDB tables, 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. 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:
mysqlbackup contacts the MySQL server to determine the keyring plugin the server is using, which, currently, is either one of
If the server is using the
keyring_encrypted_fileplugin, the user must use the option
--encrypt-passwordto supply to mysqlbackup the keyring file encryption password that has been set on the server with the
--keyring_encrypted_file_passwordoption. 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
metafolder 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, named
keyring_kefand saved in the
metafolder in the backup; the file is encrypted with the user password supplied with the option
Users who do not want to supply the password on the command line
or in a default 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
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
The same password used for backing up the database must be supplied
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_fileplugin and with the options
--keyring_encrypted_file_password(which should supply the server with the same password used with the
--encrypt-passwordoption during the restore).
For a MySQL Community Server: The
keyring_fileplugin is the only keyring plugin supported by the MySQL Community Server; therefore mysqlbackup uses the password supplied with the
--encrypt-passwordoption to decrypt keyring data file and then restores it to the proper location on the server for the
keyring_fileplugin to use.
For Incremental Backups.
For a series of incremental backups, if a keyring plugin other
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
keyring_file plugin and does not
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
$ 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 and then the tables must be decrypted before the log
can be applied. The same requirement applies when you try to update
an encrypted backup with an encrypted incremental backup using the
$ 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
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
option is not required for this step.
$ mysqlbackup --defaults-file=/usr/local/mysql/my.cnf --backup-dir=/home/admin/backup \ --encrypt-password="password" copy-back-and-apply-log
validateoperation, 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-ttsoption 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.
--skip-unused-pagesoption has no effect on encrypted InnoDB tables during a backup (that is, empty pages for those tables are not skipped).