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).

Enabling Encryption

There are two system variables used to enable encryption for tables using the InnoDB or XtraDB storage engines. innodb_encrypt_tables controls whether to encrypt tables and innodb_encrypt_log controls whether to encrypt log files.

The innodb_encrypt_log variable is set with a boolean value. innodb_encrypt_tables takes one of three 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.

Best practice is to only enable table encryption when you also encrypt your log files. Enabling table encryption alone means that your unencrypted data can be found in log files.

With these variables and some form of an encryption plugin configured, you can begin to encrypt your tables.

# 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_encrypton = aes_cbc

# InnoDB/XtraDB Encryption
innodb_encrypt_tables = ON
innodb_encrypt_log = ON

Once you've made the relevant changes to the configuration file, (that is, my.cnf or my.ini) restart the MariaDB server to enable encryption.

Using Encyrption

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.

System Variables for Encryption

In fine tuning MariaDB Encryption to meet your needs, you might consider the following system variables:


Whenever you create an encrypted table, you need to define the key that you want to use in the encryption process. Keys are selected using key identifiers as defined the key file.

In cases where you're encrypted many tables, you may find it conventinet to set a default key for encryption. That way, you can encrypt tables without choosing a key identifier.


Key rotation is the process of re-encrypted tables to use a new key. Some encryption plugins support this feature, while others (like the File Key Management plugin) do not.

Using this system variable, you can define the number of I/O operations per second you want MariaDB to use for background key rotation.


Key rotation is the process of re-encrypted tables to use a new key. Some encryption plugins support this feature, while others (like the File Key Management plugin) do not.

Using this system variable, you can define how many key versions to allow a page to fall behind. When a page reaches this many versions behind, MariaDB automatically re-encrypts the page in the background.


Using this system variable, you can define the number of threads to use when performing background key rotation and scrubbing.


Comments loading...