Encrypting Data for InnoDB / XtraDB

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:

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

[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_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.

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 (
   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 database and want to add data at rest encryption, you can do the same with an ALTER TABLE statement:

ALTER TABLE accounts
ENCRYPTED = YES
ENCRYPTION_KEY_ID = 3

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
ENCRYPTED = NO

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:

innodb_default_encryption_key_id

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.

innodb_encryption_rotation_iops

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.

innodb_encryption_rotate_key_age

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.

innodb_encryption_threads

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