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
# 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
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.
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:
SELECT COUNT(*) AS 'Count' FROM information_schema.INNODB_TABLESPACES_ENCRYPTION WHERE ROTATING_OR_FLUSHING=1; +-------+ | 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.
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:
|Disables table encryption.|
|Enables table encryption, but allows unencrypted tables to be created.|
|Enables table encryption, and doesn't allow unencrypted tables to be created.|
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.
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.
|Boolean||Defines whether to encrypt the table|
|32-bit integer||Defines the identifier for the encryption key to use|
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.
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.
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.
Seeing Which Tables are Encrypted
The Information Schema INNODB_TABLESPACES_ENCRYPTION table gives encryption information about InnoDB tables, including which tables are encrypted.
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