Encrypting Data for InnoDB / XtraDB
Contents
MariaDB can encrypt data in tables that use the InnoDB and XtraDB storage engines.
For encryption with the Aria storage engine, see Encrypting Data for Aria.
Basic Configuration
In order to enable encryption for tables using the InnoDB and XtraDB storage engines, there are several server system variables that you need to set and configure. Most users will want to set innodb_encrypt_tables
, innodb_encrypt_logs
, innodb_encryption_threads
, and innodb_encryption_rotate_key_age
. InnoDB and XtraDB both use the same server system variables to configure support for data-at-rest encryption
Users of data-at-rest encryption will also need to have an encryption key management plugin configured. Some examples are File Key Management Plugin and AWS Key Management Plugin.
# vi /etc/my.cnf [mysqld] ... # File Key Management plugin_load_add = file_key_management file_key_management_filename = /etc/mysql/keys.enc file_key_management_filekey = FILE:/etc/mysql/.key file_key_management_encryption_algorithm = aes_cbc # InnoDB/XtraDB Encryption innodb_encrypt_tables = ON innodb_encrypt_log = ON innodb_encryption_threads = 8 innodb_encryption_rotate_key_age = 5
Background Encryption Threads
The innodb_encryption_threads
system variable controls the number of threads that the InnoDB storage engine utilizes for its encryption-related background operations, which includes encrypting/decrypting InnoDB pages after key rotations or configuration changes and scrubbing InnoDB data to permanently delete it.
Background Operations
The encryption/decryption operations that are performed by the background encryption threads are:
- When a new version of an encryption key is rotated, then the background encryption threads will re-encrypt pages that were previously encrypted with the older version of that encryption key.
- When the configured value of
innodb_encrypt_tables
changes toON
orFORCE
, then the background encryption threads will encrypt InnoDB's system tablespace and currently unencrypted file-per-table tablespaces for InnoDB tables that haveENCRYPTED=DEFAULT
set as a table option. - When the configured value of
innodb_encrypt_tables
changes toOFF
, then the background encryption threads will decrypt InnoDB's system tablespace and currently encrypted file-per-table tablespaces for InnoDB tables that haveENCRYPTED=DEFAULT
set as a table option.
The latter two points mean that, whenever you change the configured value of innodb_encrypt_tables
, then you must ensure that innodb_encryption_threads
is set to a non-zero value, so that InnoDB can perform any necessary encryption or decryption operations in the background. innodb_encryption_rotate_key_age
must also be set to a non-zero value for the initial encryption/decryption operations to happen in the background. See disabling key rotations for more information about that.
The encryption/decryption operations that are not performed by the background encryption threads are:
- When a file-per-table tablespace for an InnoDB table is manually encrypted by setting
ENCRYPTED=YES
table option with anALTER TABLE
statement, then the background encryption threads do not encrypt the table in the background. The table is encrypted by the server thread for the client connection that executed the statement. - Likewise, when a file-per-table tablespace for an InnoDB table is manually decrypted by setting
ENCRYPTED=NO
table option with anALTER TABLE
statement, then the background encryption threads do not decrypt the table in the background. The table is decrypted by the server thread for the client connection that executed the statement.
These two points mean that encryption changes can still be made to InnoDB's file-per-table tablespaces with ALTER TABLE
, even when innodb_encryption_threads=0
and/or innodb_encryption_rotate_key_age=0
are set.
Manual encryption changes cannot be made to InnoDB's system tablespace. This means that whenever you change the configured value of innodb_encrypt_tables
, you must also set innodb_encryption_threads
to a non-zero value and innodb_encryption_rotate_key_age=1
to ensure that the system tablespace is properly encrypted or decrypted by the background encryption threads.
The innodb_encryption_rotation_iops
system variable can be used to configure how many I/O operations you want to allow for InnoDB's background encryption operations.
Checking the Status of Background Operations
The status of background encryption operations can be checked by querying the information_schema.INNODB_TABLESPACES_ENCRYPTION
table. If the background encryption threads are working on a tablespace, then the output will show ROTATING_OR_FLUSHING=1
for that tablespace.
For example, if you'd like to see which tablespaces the background encryption threads are currently working on, then you could execute:
SELECT SPACE, NAME FROM information_schema.INNODB_TABLESPACES_ENCRYPTION WHERE ROTATING_OR_FLUSHING=1;
Or if you'd like to see how many tablespaces the background encryption threads are currently working on, then you could execute:
SELECT COUNT(*) AS 'Count' FROM information_schema.INNODB_TABLESPACES_ENCRYPTION WHERE ROTATING_OR_FLUSHING=1;
Encryption Keys
MariaDB identifies encryption keys using a positive 32-bit integer.
The default encryption key to be used by InnoDB to encrypt tablespaces is configured by setting the innodb_default_encryption_key_id
system variable. A specific key can be used to encrypt a specific InnoDB table by setting the ENCRYPTION_KEY_ID
table option for the table. This is only supported for InnoDB tables that have their own file-per-table tablespaces. InnoDB tables in the system tablespace will always be encrypted by the encryption key set by innodb_default_encryption_key_id
.
InnoDB uses the encryption key with ID 1 to encrypt the InnoDB redo log.
Key Rotation
When your encryption key management plugin provides the relevant support, you can automatically rotate and version your encryption keys. For example, the AWS key management plugin supports key rotation, but the file key management plugin does not.
When an encryption key is rotated and a new version of the key is created, then InnoDB's background encryption threads will re-encrypt any InnoDB pages that are encrypted with the old version of the key. The maximum age for an encryption key can be configured by setting the innodb_encryption_rotate_key_age
system variable. Any InnoDB page encrypted with a key version older than this value is automatically re-encrypted in the background to use a more current key version. When innodb_encryption_rotate_key_age
is set to a non-zero value, the background encryption threads are constantly checking pages to determine if any pages are encrypted with a key version that is too old. These constant key version checks can lead to high CPU usage in some cases.
Disabling Background Key Rotation Operations
Background key rotation operations can be disabled by setting innodb_encryption_rotate_key_age=0
. This can be useful if the constant key version checks performed by InnoDB's background encryption threads are leading to excessive CPU usage. This is especially useful when using key management plugins such as file key management plugin that do not support key rotation. See MDEV-14180 about that.
However, there are important things that need to be considered when disabling background key rotation operations. For example, InnoDB internally treats background encryption/decryption operations that happen when the value of innodb_encrypt_tables
changes as a form of a background key rotation operation. Therefore, if you want to disable key rotation checks, then you need to ensure that any pending background/encryption operations are done by checking the status of these operations. Otherwise, you can end up with unencrypted tables that you intended to be encrypted, or vice-versa. See MDEV-14398 about that.
Enabling Encryption
There are two variables that relate to enabling encryption for InnoDB and XtraDB data. The innodb_encrypt_tables
system variable allows you to encrypt InnoDB's system tablespace and InnoDB's file-per-table tablespaces. The innodb_encrypt_log
system variable allows you to encrypt InnoDB's redo log.
There are also two table options that relate to enabling encryption for InnoDB and XtraDB data. These are the ENCRYPTED
and ENCRYPTION_KEY_ID
table options.
If you would like to use encryption, then it is the best practice to encrypt both InnoDB tablespace files and InnoDB redo log files. Otherwise, if you only encrypted the tablespace files, then your data would remain unencrypted in the redo log files.
Enabling Encryption for Automatically Encrypted Tablespaces
The innodb_encrypt_tables
system variable has the following possible values:
Option | Description |
---|---|
OFF | Disables table encryption. |
ON | Enables table encryption, but allows unencrypted tables to be created. |
FORCE | Enables table encryption, and doesn't allow unencrypted tables to be created. |
The FORCE
option was introduced in version 10.1.4 of MariaDB. When set to FORCE
, any CREATE TABLE
or ALTER TABLE
statement that attempts to sets ENCRYPTED=NO
for an InnoDB table will fail with an error.
When innodb_encrypt_tables
is set to ON
for FORCE
, then you must ensure that innodb_encryption_threads
is set to a non-zero value, so that InnoDB can perform any necessary encryption operations in the background. See background operations for more information about that. innodb_encryption_rotate_key_age
must also be set to a non-zero value for the initial encryption operations to happen in the background. See disabling key rotations for more information about that.
Enabling Encryption for Manually Encrypted Tablespaces
Two table options that are relevant to encryption can be used with CREATE TABLE
and ALTER TABLE
statements. These options only apply to InnoDB tables that have their own InnoDB's file-per-table tablespaces, meaning that tables that were created with innodb_file_per_table=ON
set.
Table Option | Value | Description |
---|---|---|
ENCRYPTED | Boolean | Defines whether to encrypt the table |
ENCRYPTION_KEY_ID | 32-bit integer | Defines the identifier for the encryption key to use |
You can manually enable or disable encryption for a table by using the ENCRYPTED
table option. If you only need to protect a subset of InnoDB tables with encryption, then it can be a good idea to manually encrypt each table that needs the extra protection, rather than encrypting all InnoDB tables globally with innodb_encrypt_tables
. This allows you to balance security with speed, as it means the encryption and decryption performance overhead only applies to those tables that require the additional security.
You can also manually specify a encryption key for a table by using the ENCRYPTION_KEY_ID
table option. This allows you to use different encryption keys for different tables. If not specified, then the table will be encrypted with the key identified by the innodb_default_encryption_key_id
system variable.
For instance, you might create a table using a statement like this:
CREATE TABLE my_db.my_encrypted_table ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255), email VARCHAR(255)) ENGINE = InnoDB ENCRYPTED = YES ENCRYPTION_KEY_ID = 3;
In the event that you have an existing table and you want to manually enable encryption for that table, then you can do the same with an ALTER TABLE
statement:
ALTER TABLE my_db.my_encrypted_table ENCRYPTED=YES ENCRYPTION_KEY_ID=3;
Enabling Encryption for Redo Log
We can use the following process to safely enable encryption of the InnoDB redo log:
1.) Stop the server.
2.) Set innodb_encrypt_log=ON
in the MariaDB configuration file.
3.) Start the server.
Disabling Encryption
Disabling encryption is a little more complicated than turning off the relevant system variables. There are a couple different pieces to this.
Disabling Encryption for Automatically Encrypted Tablespaces
For InnoDB tablespaces that have ENCRYPTED=DEFAULT
set as a table option, special care needs to be taken to ensure that the background encryption threads are able to decrypt he tablespaces before the encryption keys are removed. Otherwise, the tablespace remains encrypted and becomes inaccessible once you've removed the keys. We can use the following process to safely disable encryption for these tablespaces.
First, set innodb_encrypt_tables=OFF
:
SET GLOBAL innodb_encrypt_tables = OFF;
Then, set innodb_encryption_threads
to a non-zero value:
SET GLOBAL innodb_encryption_threads = 4;
Then, set innodb_encryption_rotate_key_age=1
:
SET GLOBAL innodb_encryption_rotate_key_age = 1;
With these options set, any InnoDB tablespaces that have ENCRYPTED=DEFAULT
set as a table option should get decrypted in the background by InnoDB's background encryption threads. You can determine when these background operations are complete by checking the status of these operations.
For example, when the following query returns an empty result set, you know that all of your InnoDB tablespaces are unencrypted.
SELECT * FROM information_schema.INNODB_TABLESPACES_ENCRYPTION WHERE ENCRYPTION_SCHEME != 0 OR ROTATING_OR_FLUSHING != 0 \G
Disabling Encryption for Manually Encrypted Tablespaces
For InnoDB tablespaces that have ENCRYPTED=YES
set as a table option, you will need to issue ALTER TABLE
statements for each table to set ENCRYPTED=NO
before the encryption keys are removed. Otherwise, the tablespace remains encrypted and becomes inaccessible once you've removed the keys. We can use the following process to safely disable encryption for these tablespaces.
First, we can query information_schema.TABLES
table and filter on the CREATE_OPTIONS
column to find the InnoDB tablespaces that have ENCRYPTED=YES
set:
SELECT TABLE_SCHEMA AS "Database", TABLE_NAME AS "Table" FROM information_schema.TABLES WHERE ENGINE='InnoDB' AND CREATE_OPTIONS LIKE '%`ENCRYPTED`=YES%';
Then, for each table in the result-set, you need to issue an ALTER TABLE
statement that setsENCRYPTED=NO
:
ALTER TABLE my_db.my_encrypted_table ENCRYPTED=NO;
Disabling Encryption for Redo Log
We can use the following process to safely disable encryption of the InnoDB redo log:
1.) If innodb_fast_shutdown=2
is set, then set it to 1:
SET GLOBAL innodb_fast_shutdown = 1;
2.) Stop the server.
3.) Set innodb_encrypt_log=OFF
in the MariaDB configuration file.
4.) Delete the existing redo logs.
5.) Start the server.
Known Problems
Wrong create options
In the event that the innodb_file_per_table
system variable is set to OFF
or if you attempt to encrypt the table with a key that is not found, these statements fail, returning the error:
Error: 140 Wrong create options.
Tablespaces Created on MySQL 5.1.47 or Earlier
MariaDB's data-at-rest encryption implementation re-used previously unused fields in InnoDB's buffer pool pages to identify the encryption key version and the post-encryption checksum. Prior to MySQL 5.1.48, these unused fields were not initialized in memory due to performance concerns. These fields still had zero values most of the time, but since they were not explicitly initialized, that means that these fields could have occasionally had non-zero values that could have been written into InnoDB's tablespace files. If MariaDB were to encounter an unencrypted page from a tablespace file that was created on an early version of MySQL that also had non-zero values in these fields, then it would mistakenly think that the page was encrypted. This logic was changed in MariaDB 10.1.38, MariaDB 10.2.20, MariaDB 10.3.12 due to fixing MDEV-12112.
Seeing Which Tables are Encrypted
The Information Schema INNODB_TABLESPACES_ENCRYPTION table gives encryption information about InnoDB tables, including which tables are encrypted.
For example:
SELECT * FROM information_schema.innodb_tablespaces_encryption\G *************************** 1. row *************************** SPACE: 9 NAME: test/t3 ENCRYPTION_SCHEME: 1 KEYSERVER_REQUESTS: 1 MIN_KEY_VERSION: 1 CURRENT_KEY_VERSION: 1 KEY_ROTATION_PAGE_NUMBER: NULL KEY_ROTATION_MAX_PAGE_NUMBER: NULL CURRENT_KEY_ID: 1 ROTATING_OR_FLUSHING: 0 ... *************************** 3. row *************************** SPACE: 11 NAME: test/t5 ENCRYPTION_SCHEME: 0 KEYSERVER_REQUESTS: 0 MIN_KEY_VERSION: 0 CURRENT_KEY_VERSION: 1 KEY_ROTATION_PAGE_NUMBER: NULL KEY_ROTATION_MAX_PAGE_NUMBER: NULL CURRENT_KEY_ID: 1 ROTATING_OR_FLUSHING: 0 *************************** 4. row *************************** SPACE: 12 NAME: test/t6 ENCRYPTION_SCHEME: 1 KEYSERVER_REQUESTS: 1 MIN_KEY_VERSION: 1 CURRENT_KEY_VERSION: 1 KEY_ROTATION_PAGE_NUMBER: NULL KEY_ROTATION_MAX_PAGE_NUMBER: NULL CURRENT_KEY_ID: 3 ROTATING_OR_FLUSHING: 0