InnoDB uses encryption key management plugins to support the use of multiple 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.
Encryption keys can also be specified with the ENCRYPTION_KEY_ID table option for tables that use file-per-table tablespaces.
InnoDB encrypts the temporary tablespace using the encryption key with the ID 1.
InnoDB encrypts the using the encryption key with the ID 1.
With tables that use enabled encryption, one way to set the specific encryption key for the table is to use the table option. For example:
If the table option is not set for a table that uses enabled encryption, then it will inherit the value from the system variable. For example:
With tables that use enabled encryption, one way to set the specific encryption key for the table is to use the system variable. For example:
InnoDB tables that are part of the tablespace can only be encrypted using the encryption key set by the system variable.
If the table is in a tablespace, and if is set to ON or FORCE, and if is set to a value greater than 0, then you can also set the specific encryption key for the table by using the table option. For example:
However, if is set to OFF or if is set to 0, then this will not work. See for more information.
Some allow you to automatically rotate and version your encryption keys. If a plugin support key rotation, and if it rotates the encryption keys, then InnoDB's can re-encrypt InnoDB pages that use the old key version with the new key version.
You can set the maximum age for an encryption key using the 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 version is too old, any page encrypted with the older version of the key is automatically re-encrypted 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 is only supported in and later. For more information, see .
In order for key rotation to work, both the backend key management service (KMS) and the corresponding have to support key rotation. See to determine which plugins currently support key rotation.
In the event that you encounter issues with background key encryption, you can disable it by setting the 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 plugin). For more information, see .
There are, however, issues that can arise when the background key rotation is disabled.
When updating the value on the system variable, InnoDB internally treats the subsequent to encrypt and decrypt tablespaces as background key rotations. See for more information.
You can check the status of background encryption operations by querying the table in the database.
See for some example queries.
For more information, see .
This page is licensed: CC BY-SA / Gnu FDL
CREATE TABLE tab1 (
id INT PRIMARY KEY,
str VARCHAR(50)
) ENCRYPTED=YES ENCRYPTION_KEY_ID=100;
SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID
FROM information_schema.INNODB_TABLESPACES_ENCRYPTION
WHERE NAME='db1/tab1';
+----------+-------------------+----------------+
| NAME | ENCRYPTION_SCHEME | CURRENT_KEY_ID |
+----------+-------------------+----------------+
| db1/tab1 | 1 | 100 |
+----------+-------------------+----------------+SET SESSION innodb_default_encryption_key_id=100;
CREATE TABLE tab1 (
id INT PRIMARY KEY,
str VARCHAR(50)
) ENCRYPTED=YES;
SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID
FROM information_schema.INNODB_TABLESPACES_ENCRYPTION
WHERE NAME='db1/tab1';
+----------+-------------------+----------------+
| NAME | ENCRYPTION_SCHEME | CURRENT_KEY_ID |
+----------+-------------------+----------------+
| db1/tab1 | 1 | 100 |
+----------+-------------------+----------------+SET GLOBAL innodb_encryption_threads=4;
SET GLOBAL innodb_encrypt_tables=ON;
SET SESSION innodb_default_encryption_key_id=100;
CREATE TABLE tab1 (
id INT PRIMARY KEY,
str VARCHAR(50)
);
SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID
FROM information_schema.INNODB_TABLESPACES_ENCRYPTION
WHERE NAME='db1/tab1';
+----------+-------------------+----------------+
| NAME | ENCRYPTION_SCHEME | CURRENT_KEY_ID |
+----------+-------------------+----------------+
| db1/tab1 | 1 | 100 |
+----------+-------------------+----------------+SET GLOBAL innodb_encryption_threads=4;
SET GLOBAL innodb_encrypt_tables=ON;
CREATE TABLE tab1 (
id INT PRIMARY KEY,
str VARCHAR(50)
) ENCRYPTION_KEY_ID=100;
SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID
-> FROM information_schema.INNODB_TABLESPACES_ENCRYPTION
-> WHERE NAME='db1/tab1';
+----------+-------------------+----------------+
| NAME | ENCRYPTION_SCHEME | CURRENT_KEY_ID |
+----------+-------------------+----------------+
| db1/tab1 | 1 | 100 |
+----------+-------------------+----------------+