InnoDB / XtraDB Encryption Keys

InnoDB uses encryption key management plugins to support the use of multiple encryption keys.

Encryption Keys

Each encryption key has a 32-bit integer that serves as a key identifier. The default key is set using the innodb_default_encryption_key_id system variable.

SET GLOBAL innodb_encryption_threads=4;
SET GLOBAL innodb_encrypt_tables=ON;
SET GLOBAL innodb_default_encryption_key_id=3;
CREATE TABLE t (i int) ENGINE=InnoDB;

Keys with Manually Encrypted Tablespaces

With tables that use manually enabled encryption, you can set the specific key you want to use with the ENCRYPTION_KEY_ID table option.

CREATE TABLE t (i int) ENGINE=InnoDB ENCRYPTED=YES ENCRYPTION_KEY_ID=3;

Bear in mind, this feature is only available with file-per-table tablespaces.

Keys with Automatically Encrypted Tablespaces

With tables that use automatically enabled encryption, you can not change the encryption key through the ENCRYPTION_KEY_ID table option. InnoDB encrypts these tables using the encryption key set by the innodb_default_encryption_key_id system variable only.

Attempting to set an encryption key on these tables raises InnoDB Error 140. For more information, see InnoDB Encryption Troubleshooting and MDEV-17230.

Keys with Other Tablespaces

InnoDB tables that are part of the system tablespace are automatically encrypted and use the encryption key set by the innodb_default_encryption_key_id system variable.

InnoDB encrypts the Redo Log using the encrypt key with the ID 1.

Key Rotation

Some encryption key management plugins allow you to automatically rotate and version your encryption keys. This feature is available with the AWS Key Management plugin, but not the file_key_management plugin.

When the plugin rotates the encryption keys, InnoDB runs background encryption threads to re-encrypt InnoDB pages using the old key.

You can set the maximum age for an encryption key using the innodb_encryption_rotate_key_age system variable. When this variable is set to a non-zero value, background encryption threads constantly check pages to determine if any page is encrypted with a key version that's too old. When the key expires, any page encrypted with the older version of the key automatically re-encrypts in the background to use a more current version of the key. Bear in mind, this constant checking can sometimes result in high CPU usage.

Key rotation for the InnoDB Redo Log is only supported in MariaDB 10.4.0 and later. For more information, see MDEV-12041.

Disabling Background Key Rotation Operations

In the event that you encounter issues with background key encryption, you can disable it by setting the innodb_encryption_rotate_key_age system variable to 0. You may find this useful when the constant key version checks lead to excessive CPU usage. It's also useful in cases where your encryption key management plugin does not support key rotation, (such as with the file_key_management plugin). For more information, see MDEV-14180.

There are, however, issues that can arise when the background key rotation is disabled.

Pending Encryption Operations

When you update the value on the innodb_encrypt_tables system variable InnoDB internally treats the subsequent background operations to encrypt and decrypt tablespaces as background key rotations.

Ensure that any pending background encryption or decryption operations are complete before disabling key rotation. You can check the status of background encryption operations by querying the Information Schema,

SELECT COUNT(*) AS 'Count' 
FROM information_schema.INNODB_TABLESPACES_ENCRYPTION
WHERE ROTATING_OR_FLUSHING = 1;

In the event that you disable key rotation while there are background encryption threads at work, it may result in unencrypted tables that you want encrypted or vice versa.

For more information, see MDEV-14398.