Encrypting Data for InnoDB / XtraDB

You are viewing an old version of this article. View the current version here.

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:

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 an ALTER 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 an ALTER 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:

OptionDescription
OFFDisables table encryption.
ONEnables table encryption, but allows unencrypted tables to be created.
FORCEEnables 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 OptionValueDescription
ENCRYPTEDBooleanDefines whether to encrypt the table
ENCRYPTION_KEY_ID32-bit integerDefines 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

See Also

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.