InnoDB and XtraDB both use the same server system variables to configure support for data-at-rest encryption. You'll need to have an encryption plugin set up and loaded to use encryption, such as File Key Management or AWS Key Management, (see Encryption Key Management for more information).


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. At a minimum, you need to set innodb_encrypt_tables, innodb_encrypt_logs, innodb_encryption_threads, and innodb_encryption_rotate_key_age.

# vi /etc/my.cnf


# 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


The innodb_encryption_threads system variable controls the number of threads MariaDB utilizes for background key rotation and scrubbing. When you first set up encryption, you must set a value on this variable to ensure that MariaDB has threads available to encrypt any existing unencrypted tables. If not, you will need to manually encrypt the relevant tables using an ALTER TABLE statement.

MariaDB currently requires background threads to encrypt system tablespaces. Always set this variable to a value greater than 0 when using Encryption.

Key Rotation

MariaDB identifies encryption keys using a positive 32-bit integer. This makes it easier to identify the key you want to use for particular tables, through the ENCRYPTION_KEY_ID table option. You can set the default key using the innodb_default_encryption_key_id system variable.

When your encryption plugin provides the relevant support, you can automatically rotate and version your encryption keys. Using the innodb_encryption_rotation-iops system variable, you can set the number of I/O operations you want MariaDB to use for background key rotation. Using the innodb_encryption_rotate_key_age system variable, you can set the maximum age for an encryption key. Any table using a key older than this value is automatically re-encrypted in the background to use a more current key.

Disabling Key Rotation

Internally, MariaDB treats enabling or disabling encryption as key rotation. As such, you must have a non-zero value for the innodb_encryption_rotate_key_age system variable. This ensures that MariaDB employs the relevant resources to encrypt or decrypt existing tables. Otherwise, these tables remain in their previous state until you manually change them with an ALTER TABLE statement.

If you never plan to rotate your encryption keys. Start MariaDB with a non-zero value for the innodb_encryption_rotate_key_age system variable. Then check information_schema.INNODB_TABLESPACES_ENCRYPTION for any tables where the ROTATING_OR_FLUSHING column has a value of 1. For instance, you might use a query like this:


| Count |
|     2 |

When the count reaches 0, it means that all of the background threads have finished with the tablespaces. You can then set innodb_encryption_rotate_key_age to 0, disabling rotation.

Enabling Encryption

There are two variables that specifically enable encryption for InnoDB and XtraDB tables. The innodb_encrypt_log system variable allows you to encrypt log files. Using the innodb_encrypt_tables system variable, you can encrypt tables.

Encrypting tables has the following options:

OFFDisables table encryption
ONEnables table encryption, but allows unencrypted tables
FORCEEnables table encryption, but doesn't allow unencrypted tables

The FORCE option was introduced in version 10.1.4 of MariaDB. When set to FORCE, any CREATE TABLE or ALTER TABLE statement that sets the ENCRYPTED table option to NO, fails with an error.

It is the best practice to only enable table encryption when you also encrypt your log files. Enabling table encryption alone means that your unencrypted data remains available in the log files.

Using Encryption

Enabling encryption adds two table options to CREATE TABLE and ALTER TABLE statements. In order to use these options, you must have the innodb_file_per_table system variable set to ON, which is the default.

Table OptionValueDescription
ENCRYPTEDBooleanDefines whether to encrypt the table
ENCRYPTION_KEY_ID32-bit integerDefines the identifier for the encryption key to use

Using the ENCRYPTED table option you can explicitly enable or disable encryption for a table. Doing so 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.

Using the ENCRYPTION_KEY_ID table option allows you to define which encryption key you want to use. This is a poistive 32-bit integer which coresponds to the key identifier as defined in your key file.

For instance, you might create a table using a statement like this:

CREATE TABLE accounts (
   name VARCHAR(255),
   email VARCHAR(255))

In the event that you have an existing database and want to add data at rest encryption, you can do the same with an ALTER TABLE statement:

ALTER TABLE accounts

Enabling encryption for a table causes MariaDB to automatically encrypt the data, so anyone trying to access the data from outside of MariaDB client, will find encrypted files.

Using the ENCRYPTED table option, you can also disable encryption. For instance, imagine you have join publicData table that you don't want to encrypt:

ALTER TABLE publicData

When this statement executes, MariaDB decrypts the data in the table. The table will afterwards be unencrypted.

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.


Comments loading...