Chapter 6 Working with Encrypted InnoDB Tables

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 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.  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 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 (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 Affecting Upgrades to 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).