Learn about InnoDB encryption for data at rest. This section details how to encrypt InnoDB tablespaces, ensuring strong data security and compliance for your mission-critical applications.
MariaDB supports data-at-rest encryption for tables using the InnoDB storage engines. When enabled, the server encrypts data when it writes it to and decrypts data when it reads it from the file system. You can configure InnoDB encryption to automatically have all new InnoDB tables automatically encrypted, or specify encrypt per table.
For encrypting data with the Aria storage engine, see Encrypting Data for Aria.
Using data-at-rest encryption requires that you first configure an Encryption Key Management plugin, such as the file_key_management or aws_key_management plugins. MariaDB uses this plugin to store, retrieve and manage the various keys it uses when encrypting data to and decrypting data from the file system.
Once you have the plugin configured, you need to set a few additional system variables to enable encryption on InnoDB tables, including innodb_encrypt_tables, innodb_encrypt_log, innodb_encryption_threads, innodb_encrypt_temporary_tables and innodb_encryption_rotate_key_age.
[mariadb]
...
# File Key Management
plugin_load_add = file_key_management
file_key_management_filename = /etc/mysql/encryption/keyfile.enc
file_key_management_filekey = FILE:/etc/mysql/encryption/keyfile.key
file_key_management_encryption_algorithm = AES_CTR
# InnoDB Encryption
innodb_encrypt_tables = ON
innodb_encrypt_temporary_tables = ON
innodb_encrypt_log = ON
innodb_encryption_threads = 4
innodb_encryption_rotate_key_age = 1For more information on system variables for encryption and other features, see the InnoDB system variables page.
To create encrypted tables, specify the table options ENCRYPTED=YES and ENCRYPTION_KEY_ID= with a corresponding key id;
CREATE TABLE t (i INT PRIMARY KEY) ENGINE=InnoDB ENCRYPTED=YES ENCRYPTION_KEY_ID=2;When using data-at-rest encryption with the InnoDB storage engine, it is not necessary that you encrypt every table in your database. You can check which tables are encrypted and which are not by querying the INNODB_TABLESPACES_ENCRYPTION table in the Information Schema. This table provides information on which tablespaces are encrypted, which encryption key each tablespace is encrypted with, and whether the background encryption threads are currently working on the tablespace. Since the system tablespace can also contain tables, it can be helpful to join the INNODB_TABLESPACES_ENCRYPTION table with the INNODB_SYS_TABLES table to find out the encryption status of each specific table, rather than each tablespace. For example:
SELECT st.SPACE, st.NAME, te.ENCRYPTION_SCHEME, te.ROTATING_OR_FLUSHING
FROM information_schema.INNODB_TABLESPACES_ENCRYPTION te
JOIN information_schema.INNODB_SYS_TABLES st
ON te.SPACE = st.SPACE \G
*************************** 1. row ***************************
SPACE: 0
NAME: SYS_DATAFILES
ENCRYPTION_SCHEME: 1
ROTATING_OR_FLUSHING: 0
*************************** 2. row ***************************
SPACE: 0
NAME: SYS_FOREIGN
ENCRYPTION_SCHEME: 1
ROTATING_OR_FLUSHING: 0
*************************** 3. row ***************************
SPACE: 0
NAME: SYS_FOREIGN_COLS
ENCRYPTION_SCHEME: 1
ROTATING_OR_FLUSHING: 0
*************************** 4. row ***************************
SPACE: 0
NAME: SYS_TABLESPACES
ENCRYPTION_SCHEME: 1
ROTATING_OR_FLUSHING: 0
*************************** 5. row ***************************
SPACE: 0
NAME: SYS_VIRTUAL
ENCRYPTION_SCHEME: 1
ROTATING_OR_FLUSHING: 0
*************************** 6. row ***************************
SPACE: 0
NAME: db1/default_encrypted_tab1
ENCRYPTION_SCHEME: 1
ROTATING_OR_FLUSHING: 0
*************************** 7. row ***************************
SPACE: 416
NAME: db1/default_encrypted_tab2
ENCRYPTION_SCHEME: 1
ROTATING_OR_FLUSHING: 0
*************************** 8. row ***************************
SPACE: 402
NAME: db1/tab
ENCRYPTION_SCHEME: 1
ROTATING_OR_FLUSHING: 0
*************************** 9. row ***************************
SPACE: 185
NAME: db1/tab1
ENCRYPTION_SCHEME: 1
ROTATING_OR_FLUSHING: 0
*************************** 10. row ***************************
SPACE: 184
NAME: db1/tab2
ENCRYPTION_SCHEME: 1
ROTATING_OR_FLUSHING: 0
*************************** 11. row ***************************
SPACE: 414
NAME: db1/testgb2
ENCRYPTION_SCHEME: 1
ROTATING_OR_FLUSHING: 0
*************************** 12. row ***************************
SPACE: 4
NAME: mysql/gtid_slave_pos
ENCRYPTION_SCHEME: 1
ROTATING_OR_FLUSHING: 0
*************************** 13. row ***************************
SPACE: 2
NAME: mysql/innodb_index_stats
ENCRYPTION_SCHEME: 1
ROTATING_OR_FLUSHING: 0
*************************** 14. row ***************************
SPACE: 1
NAME: mysql/innodb_table_stats
ENCRYPTION_SCHEME: 1
ROTATING_OR_FLUSHING: 0
*************************** 15. row ***************************
SPACE: 3
NAME: mysql/transaction_registry
ENCRYPTION_SCHEME: 1
ROTATING_OR_FLUSHING: 0
15 rows in set (0.000 sec)Using data-at-rest encryption with InnoDB, the innodb_encrypt_tables system variable only encrypts the InnoDB tablespaces. In order to also encrypt the InnoDB Redo Logs, you also need to set the innodb_encrypt_log system variable.
Beginning in MariaDB 10.4, where the encryption key management plugin supports key rotation the InnoDB Redo Log can also rotate encryption keys. In previous releases, the Redo Log can only use the first encryption key.
This page is licensed: CC BY-SA / Gnu FDL
The process involved in safely disabling encryption for your InnoDB tables is a little more complicated than that of enabling encryption. Turning off the relevant system variables doesn't decrypt the tables. If you turn it off and remove the encryption key management plugin, it'll render the encrypted data inaccessible.
In order to safely disable encryption, you first need to decrypt the tablespaces and the Redo Log, then turn off the system variables. The specifics of this process depends on whether you are using automatic or manual encryption of the InnoDB tablespaces.
When an InnoDB tablespace has the ENCRYPTED table option set to DEFAULT and the innodb_encrypt_tables system variable is set to ON or FORCE, the tablespace's encryption is automatically managed by the background encryption threads. When you want to disable encryption for these tablespaces, you must ensure that the background encryption threads decrypt the tablespaces before removing the encryption keys. Otherwise, the tablespace remains encrypted and becomes inaccessible once you've removed the keys.
To safely decrypt the tablespaces, first, set the innodb_encrypt_tables system variable to OFF:
SET GLOBAL innodb_encrypt_tables = OFF;Next, set the innodb_encryption_threads system variable to a non-zero value:
SET GLOBAL innodb_encryption_threads = 4;Then, set the innodb_encryption_rotate_key_age system variable to 1:
SET GLOBAL innodb_encryption_rotate_key_age = 1;Once set, any InnoDB tablespaces that have the ENCRYPTED table option set to DEFAULT will be decrypted in the background by the InnoDB background encryption threads.
You can check the status of the decryption process using the INNODB_TABLESPACES_ENCRYPTION table in the information_schema database.
SELECT COUNT(*) AS "Number of Encrypted Tablespaces"
FROM information_schema.INNODB_TABLESPACES_ENCRYPTION
WHERE ENCRYPTION_SCHEME != 0
OR ROTATING_OR_FLUSHING != 0;This query shows the number of InnoDB tablespaces that currently using background encryption threads. Once the count reaches 0, then all of your InnoDB tablespaces are unencrypted. Be sure to also remove encryption on the Redo Log and the Aria storage engine before removing the encryption key management settings from your configuration file.
In the case of manually encrypted InnoDB tablespaces, (that is, those where the ENCRYPTED table option is set to YES), you must issue an ALTER TABLE statement to decrypt each tablespace before removing the encryption keys. Otherwise, the tablespace remains encrypted and becomes inaccessible without the keys.
First, query the Information Schema TABLES table to find the encrypted tables. This can be done with a WHERE clause filtering the CREATE_OPTIONS column.
SELECT TABLE_SCHEMA AS "Database", TABLE_NAME AS "Table"
FROM information_schema.TABLES
WHERE ENGINE='InnoDB'
AND CREATE_OPTIONS LIKE '%`ENCRYPTED`=YES%';For each table in the result-set, issue an ALTER TABLE statement, setting the ENCRYPTED table option to NO.
SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID
FROM information_schema.INNODB_TABLESPACES_ENCRYPTION
WHERE NAME='db1/tab1';
+----------+-------------------+----------------+
| NAME | ENCRYPTION_SCHEME | CURRENT_KEY_ID |
+----------+-------------------+----------------+
| db1/tab1 | 1 | 100 |
+----------+-------------------+----------------+
ALTER TABLE tab1
ENCRYPTED=NO;
SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID
FROM information_schema.INNODB_TABLESPACES_ENCRYPTION
WHERE NAME='db1/tab1';
+----------+-------------------+----------------+
| NAME | ENCRYPTION_SCHEME | CURRENT_KEY_ID |
+----------+-------------------+----------------+
| db1/tab1 | 0 | 100 |
+----------+-------------------+----------------+Once you have removed encryption from all the tables, your InnoDB deployment is unencrypted. Be sure to also remove encryption from the Redo Log as well as Aria and any other storage engines that support encryption before removing the encryption key management settings from your configuration file.
InnoDB does not permit manual encryption changes to tables in the system tablespace using ALTER TABLE. Encryption of the system tablespace can only be configured by setting the value of the innodb_encrypt_tables system variable. This means that when you want to encrypt or decrypt the system tablespace, you must also set a non-zero value for the innodb_encryption_threads system variable, and you must also set the innodb_encryption_rotate_key_age system variable to 1 to ensure that the system tablespace is properly encrypted or decrypted by the background threads. See MDEV-14398 for more information.
The innodb_encrypt_temporary_tables system variable controls the configuration of encryption for the temporary tablespace. To disable it, remove the system variable from your server's option file, and then restart the server.
InnoDB uses the Redo Log in crash recovery. By default, these events are written to file in an unencrypted state. In removing data-at-rest encryption for InnoDB, be sure to also disable encryption for the Redo Log before removing encryption key settings. Otherwise the Redo Log can become inaccessible without the encryption keys.
First, set the innodb_encrypt_log system variable to OFF in a server option group in an option file. Once this is done, restart the MariaDB Server. When the Server comes back online, it begins writing unencrypted data to the Redo Log.
After the server has been successfully restarted with encryption disabled, you may remove the key management and encryption plugin that had been used. If you try to disable encryption for the Redo Log and remove the plugin in a single step, InnoDB will be unable to decrypt the log in order to remove the encryption.
This page is licensed: CC BY-SA / Gnu FDL
InnoDB performs some encryption and decryption operations with background encryption threads. The innodb_encryption_threads system variable controls the number of threads that the storage engine uses for encryption-related background operations, including encrypting and decrypting pages after key rotations or configuration changes, and scrubbing data to permanently delete it.
InnoDB performs the following encryption and decryption operations using background encryption threads:
When rotating encryption keys, InnoDB's background encryption threads re-encrypt pages that use key versions older than innodb_encryption_rotate_key_age to the new key version.
When changing the innodb_encrypt_tables system variable to FORCE, InnoDB's background encryption threads encrypt the system tablespace and any file-per-table tablespaces that have the ENCRYPTED table option set to DEFAULT.
When changing the innodb_encrypt_tables system variable to OFF, InnoDB's background encryption threads decrypt the system tablespace and any file-per-table tablespacs that have the ENCRYPTED table option set to DEFAULT.
The innodb_encryption_rotation_iops system variable can be used to configure how many I/O operations you want to allow for the operations performed by InnoDB's background encryption threads.
Whenever you change the value on the innodb_encrypt_tables system variable, InnoDB's background encryption threads perform the necessary encryption or decryption operations. Because of this, you must have a non-zero value set for the innodb_encryption_threads system variable. InnoDB also considers these operations to be key rotations internally. Because of this, you must have a non-zero value set for the innodb_encryption_rotate_key_age system variable. For more information, see disabling key rotations.
InnoDB performs the following encryption and decryption operations without using background encryption threads:
When a file-per-table tablespaces and using ALTER TABLE to manually set the ENCRYPTED table option to YES, InnoDB does not use background threads to encrypt the tablespaces.
Similarly, when using file-per-table tablespaces and using ALTER TABLE to manually set the ENCRYPTED table option to NO, InnoDB does not use background threads to decrypt the tablespaces.
In these cases, InnoDB performs the encryption or decryption operation using the server thread for the client
connection that executes the statement. This means that you can update encryption on file-per-table tablespaces with an ALTER TABLE statement, even when the innodb_encryption_threads and/or the innodb_rotate_key_age system variables are set to 0.
InnoDB does not permit manual encryption changes to tables in the system tablespace using ALTER TABLE. Encryption of the system tablespace can only be configured by setting the value of the innodb_encrypt_tables system variable. This means that when you want to encrypt or decrypt the system tablespace, you must also set a non-zero value for the innodb_encryption_threads system variable, and you must also set the innodb_system_rotate_key_age system variable to 1 to ensure that the system tablespace is properly encrypted or decrypted by the background threads. See MDEV-14398 for more information.
InnoDB records the status of background encryption operations in the INNODB_TABLESPACES_ENCRYPTION table in the information_schema database.
For example, to see which InnoDB tablespaces are currently being decrypted or encrypted on by background encryption, you can check which InnoDB tablespaces have the ROTATING_OR_FLUSHING column set to 1:
SELECT SPACE, NAME
FROM information_schema.INNODB_TABLESPACES_ENCRYPTION
WHERE ROTATING_OR_FLUSHING = 1;And to see how many InnoDB tablespaces are currently being decrypted or encrypted by background encryption threads, you can call the COUNT() aggregate function.
SELECT COUNT(*) AS 'encrypting'
FROM information_schema.INNODB_TABLESPACES_ENCRYPTION
WHERE ROTATING_OR_FLUSHING = 1;And to see how many InnoDB tablespaces are currently being decrypted or encrypted by background encryption threads, while comparing that to the total number of InnoDB tablespaces and the total number of encrypted InnoDB tablespaces, you can join the table with the INNODB_SYS_TABLESPACES table in the information_schema database:
/* information_schema.INNODB_TABLESPACES_ENCRYPTION does not always have rows for all tablespaces,
so let's join it with information_schema.INNODB_SYS_TABLESPACES */
WITH tablespace_ids AS (
SELECT SPACE
FROM information_schema.INNODB_SYS_TABLESPACES ist
UNION
/* information_schema.INNODB_SYS_TABLESPACES doesn't have a row for the system tablespace (MDEV-20802) */
SELECT 0 AS SPACE
)
SELECT NOW() AS 'time',
'tablespaces', COUNT(*) AS 'tablespaces',
'encrypted', SUM(IF(ite.ENCRYPTION_SCHEME IS NOT NULL, ite.ENCRYPTION_SCHEME, 0)) AS 'encrypted',
'encrypting', SUM(IF(ite.ROTATING_OR_FLUSHING IS NOT NULL, ite.ROTATING_OR_FLUSHING, 0)) AS 'encrypting'
FROM tablespace_ids
LEFT JOIN information_schema.INNODB_TABLESPACES_ENCRYPTION ite
ON tablespace_ids.SPACE = ite.SPACEThis page is licensed: CC BY-SA / Gnu FDL
In order to enable data-at-rest encryption for tables using the InnoDB storage engines, you first need to configure the Server to use an Encryption Key Management plugin. Once this is done, you can enable encryption by setting the innodb_encrypt_tables system variable to encrypt the InnoDB system and file tablespaces and setting the innodb_encrypt_log system variable to encrypt the InnoDB Redo Log.
Setting these system variables enables the encryption feature for InnoDB tables on your server. To use the feature, you need to use the ENCRYPTION_KEY_ID table option to set what encryption key you want to use and set the ENCRYPTED table option to enable encryption.
When encrypting any InnoDB tables, the best practice is also enable encryption for the Redo Log. If you have encrypted InnoDB tables and have not encrypted the Redo Log, data written to an encrypted table may be found unencrypted in the Redo Log.
The innodb_encrypt_tables system variable controls the configuration of automatic encryption of InnoDB tables. It has the following possible values:
OFF
Disables table encryption.
ON
Enables table encryption, but allows unencrypted tables to be created.
FORCE
Enables table encryption, and doesn't allow unencrypted tables to be created. Added in MariaDB 10.1.4.
When innodb_encrypt_tables is set to ON, InnoDB tables are automatically encrypted by default. For example, the following statements create an encrypted table and confirm that it is encrypted:
SET GLOBAL innodb_encryption_threads=4;
SET GLOBAL innodb_encrypt_tables=ON;
SET SESSION innodb_default_encryption_key_id=100;
CREATE TABLE tab1 (
id INT PRIMARY KEY,
str VARCHAR(50)
);
SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID
FROM information_schema.INNODB_TABLESPACES_ENCRYPTION
WHERE NAME='db1/tab1';
+----------+-------------------+----------------+
| NAME | ENCRYPTION_SCHEME | CURRENT_KEY_ID |
+----------+-------------------+----------------+
| db1/tab1 | 1 | 100 |
+----------+-------------------+----------------+When innodb_encrypt_tables is set to ON, an unencrypted InnoDB table can be created by setting the ENCRYPTED table option to NO for the table. For example, the following statements create an unencrypted table and confirm that it is not encrypted:
SET GLOBAL innodb_encryption_threads=4;
SET GLOBAL innodb_encrypt_tables=ON;
SET SESSION innodb_default_encryption_key_id=100;
CREATE TABLE tab1 (
id INT PRIMARY KEY,
str VARCHAR(50)
) ENCRYPTED=NO;
SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID
FROM information_schema.INNODB_TABLESPACES_ENCRYPTION
WHERE NAME='db1/tab1';
+----------+-------------------+----------------+
| NAME | ENCRYPTION_SCHEME | CURRENT_KEY_ID |
+----------+-------------------+----------------+
| db1/tab1 | 0 | 100 |
+----------+-------------------+----------------+When innodb_encrypt_tables is set to FORCE, InnoDB tables are automatically encrypted by default, and unencrypted InnoDB tables can not be created. In this scenario, if you set the ENCRYPTED table option to NO for a table, then you will encounter an error. For example:
SET GLOBAL innodb_encryption_threads=4;
SET GLOBAL innodb_encrypt_tables='FORCE';
SET SESSION innodb_default_encryption_key_id=100;
CREATE TABLE tab1 (
id INT PRIMARY KEY,
str VARCHAR(50)
) ENCRYPTED=NO;
ERROR 1005 (HY000): Can't create table `db1`.`tab1` (errno: 140 "Wrong create options")
SHOW WARNINGS;
+---------+------+----------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------------------------------+
| Warning | 140 | InnoDB: ENCRYPTED=NO implies ENCRYPTION_KEY_ID=1 |
| Warning | 140 | InnoDB: ENCRYPTED=NO cannot be used with innodb_encrypt_tables=FORCE |
| Error | 1005 | Can't create table `db1`.`tab1` (errno: 140 "Wrong create options") |
| Warning | 1030 | Got error 140 "Wrong create options" from storage engine InnoDB |
+---------+------+----------------------------------------------------------------------+
4 rows in set (0.00 sec)When innodb_encrypt_tables is set to ON or 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.
If you do not want to automatically encrypt every InnoDB table, then it is possible to manually enable encryption for just the subset of InnoDB tables that you would like to encrypt. MariaDB provides the ENCRYPTED and ENCRYPTION_KEY_ID table options that can be used to manually enable encryption for specific InnoDB tables. These table options can be used with CREATE TABLE and ALTER TABLE statements. These table options can only be used with 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.
ENCRYPTED
Boolean
Defines whether to encrypt the table
ENCRYPTION_KEY_ID
32-bit integer
Defines 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.
If a manually encrypted InnoDB table contains a FULLTEXT INDEX, then the internal table for the full-text index will not also be manually encrypted. To encrypt internal tables for InnoDB full-text indexes, you must enable automatic InnoDB encryption by setting innodb_encrypt_tables to ON or FORCE.
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. For example, you might create a table using a statement like this:
CREATE TABLE tab1 (
id INT PRIMARY KEY,
str VARCHAR(50)
) ENCRYPTED=YES ENCRYPTION_KEY_ID=100;
SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID
FROM information_schema.INNODB_TABLESPACES_ENCRYPTION
WHERE NAME='db1/tab1';
+----------+-------------------+----------------+
| NAME | ENCRYPTION_SCHEME | CURRENT_KEY_ID |
+----------+-------------------+----------------+
| db1/tab1 | 1 | 100 |
+----------+-------------------+----------------+If the ENCRYPTION_KEY_ID table option is not specified, then the table will be encrypted with the key identified by the innodb_default_encryption_key_id system variable. For example, you might create a table using a statement like this:
SET SESSION innodb_default_encryption_key_id=100;
CREATE TABLE tab1 (
id INT PRIMARY KEY,
str VARCHAR(50)
) ENCRYPTED=YES;
SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID
FROM information_schema.INNODB_TABLESPACES_ENCRYPTION
WHERE NAME='db1/tab1';
+----------+-------------------+----------------+
| NAME | ENCRYPTION_SCHEME | CURRENT_KEY_ID |
+----------+-------------------+----------------+
| db1/tab1 | 1 | 100 |
+----------+-------------------+----------------+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. For example:
CREATE TABLE tab1 (
id INT PRIMARY KEY,
str VARCHAR(50)
) ENCRYPTED=NO;
SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID
FROM information_schema.INNODB_TABLESPACES_ENCRYPTION
WHERE NAME='db1/tab1';
+----------+-------------------+----------------+
| NAME | ENCRYPTION_SCHEME | CURRENT_KEY_ID |
+----------+-------------------+----------------+
| db1/tab1 | 0 | 100 |
+----------+-------------------+----------------+
ALTER TABLE tab1
ENCRYPTED=YES ENCRYPTION_KEY_ID=100;
SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID
FROM information_schema.INNODB_TABLESPACES_ENCRYPTION
WHERE NAME='db1/tab1';
+----------+-------------------+----------------+
| NAME | ENCRYPTION_SCHEME | CURRENT_KEY_ID |
+----------+-------------------+----------------+
| db1/tab1 | 1 | 100 |
+----------+-------------------+----------------+InnoDB does not permit manual encryption changes to tables in the system tablespace using ALTER TABLE. Encryption of the system tablespace can only be configured by setting the value of the innodb_encrypt_tables system variable. This means that when you want to encrypt or decrypt the system tablespace, you must also set a non-zero value for the innodb_encryption_threads system variable, and you must also set the innodb_system_rotate_key_age system variable to 1 to ensure that the system tablespace is properly encrypted or decrypted by the background threads. See MDEV-14398 for more information.
The innodb_encrypt_temporary_tables system variable controls the configuration of encryption for the temporary tablespace. It has the following possible values:
OFF
Disables temporary table encryption.
ON
Enables temporary table encryption.
This system variable can be specified as a command-line argument to mysqld or it can be specified in a relevant server option group in an option file. For example:
[mariadb]
...
innodb_encrypt_temporary_tables=ONInnoDB uses the Redo Log in crash recovery. By default, these events are written to file in an unencrypted state. In configuring MariaDB for data-at-rest encryption, ensure that you also enable encryption for the Redo Log.
To encrypt the Redo Log, first stop the server process. Then, set the innodb_encrypt_log to ON in a relevant server option group in an option file. For example:
[mariadb]
...
innodb_encrypt_log = ONThen, start MariaDB. When the server starts back up, it checks to recover InnoDB in the event of a crash. Once it is back online, it begins writing encrypted data to the Redo Log.
In MariaDB 10.3 and before, InnoDB does not support key rotation for the Redo Log. Key rotation for the Redo Log is supported in MariaDB 10.4 and later. See InnoDB Encryption Keys: Key Rotation for more information.
This page is licensed: CC BY-SA / Gnu FDL
InnoDB uses encryption key management plugins to support the use of multiple encryption keys.
Each encryption key has a 32-bit integer that serves as a key identifier.
The default key is set using the innodb_default_encryption_key_id system variable.
Encryption keys can also be specified with the ENCRYPTION_KEY_ID table option for tables that use file-per-table tablespaces.
InnoDB encrypts the temporary tablespace using the encryption key with the ID 1.
InnoDB encrypts the Redo Log using the encryption key with the ID 1.
With tables that use manually enabled encryption, one way to set the specific encryption key for the table is to use the ENCRYPTION_KEY_ID table option. For example:
CREATE TABLE tab1 (
id INT PRIMARY KEY,
str VARCHAR(50)
) ENCRYPTED=YES ENCRYPTION_KEY_ID=100;
SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID
FROM information_schema.INNODB_TABLESPACES_ENCRYPTION
WHERE NAME='db1/tab1';
+----------+-------------------+----------------+
| NAME | ENCRYPTION_SCHEME | CURRENT_KEY_ID |
+----------+-------------------+----------------+
| db1/tab1 | 1 | 100 |
+----------+-------------------+----------------+If the ENCRYPTION_KEY_ID table option is not set for a table that uses manually enabled encryption, then it will inherit the value from the innodb_default_encryption_key_id system variable. For example:
SET SESSION innodb_default_encryption_key_id=100;
CREATE TABLE tab1 (
id INT PRIMARY KEY,
str VARCHAR(50)
) ENCRYPTED=YES;
SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID
FROM information_schema.INNODB_TABLESPACES_ENCRYPTION
WHERE NAME='db1/tab1';
+----------+-------------------+----------------+
| NAME | ENCRYPTION_SCHEME | CURRENT_KEY_ID |
+----------+-------------------+----------------+
| db1/tab1 | 1 | 100 |
+----------+-------------------+----------------+With tables that use automatically enabled encryption, one way to set the specific encryption key for the table is to use the innodb_default_encryption_key_id system variable. For example:
SET GLOBAL innodb_encryption_threads=4;
SET GLOBAL innodb_encrypt_tables=ON;
SET SESSION innodb_default_encryption_key_id=100;
CREATE TABLE tab1 (
id INT PRIMARY KEY,
str VARCHAR(50)
);
SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID
FROM information_schema.INNODB_TABLESPACES_ENCRYPTION
WHERE NAME='db1/tab1';
+----------+-------------------+----------------+
| NAME | ENCRYPTION_SCHEME | CURRENT_KEY_ID |
+----------+-------------------+----------------+
| db1/tab1 | 1 | 100 |
+----------+-------------------+----------------+InnoDB tables that are part of the system tablespace can only be encrypted using the encryption key set by the innodb_default_encryption_key_id system variable.
If the table is in a file-per-table tablespace, and if innodb_encrypt_tables is set to ON or FORCE, and if innodb_encryption_threads is set to a value greater than 0, then you can also set the specific encryption key for the table by using the ENCRYPTION_KEY_ID table option. For example:
SET GLOBAL innodb_encryption_threads=4;
SET GLOBAL innodb_encrypt_tables=ON;
CREATE TABLE tab1 (
id INT PRIMARY KEY,
str VARCHAR(50)
) ENCRYPTION_KEY_ID=100;
SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID
-> FROM information_schema.INNODB_TABLESPACES_ENCRYPTION
-> WHERE NAME='db1/tab1';
+----------+-------------------+----------------+
| NAME | ENCRYPTION_SCHEME | CURRENT_KEY_ID |
+----------+-------------------+----------------+
| db1/tab1 | 1 | 100 |
+----------+-------------------+----------------+However, if innodb_encrypt_tables is set to OFF or if innodb_encryption_threads is set to 0, then this will not work. See InnoDB Encryption Troubleshooting: Setting Encryption Key ID For an Unencrypted Table for more information.
Some key management and encryption plugins allow you to automatically rotate and version your encryption keys. If a plugin support key rotation, and if it rotates the encryption keys, then InnoDB's background encryption threads can re-encrypt InnoDB pages that use the old key version with the new key version.
You can set the maximum age for an encryption key using the innodb_encryption_rotate_key_age system variable. When this variable is set to a non-zero value, background encryption threads constantly check pages to determine if any page is encrypted with a key version that's too old. When the key version is too old, any page encrypted with the older version of the key is automatically re-encrypted in the background to use a more current version of the key. Bear in mind, this constant checking can sometimes result in high CPU usage.
Key rotation for the InnoDB Redo Log is only supported in MariaDB 10.4.0 and later. For more information, see MDEV-12041.
In order for key rotation to work, both the backend key management service (KMS) and the corresponding key management and encryption plugin have to support key rotation. See Encryption Key Management: Support for Key Rotation in Encryption Plugins to determine which plugins currently support key rotation.
In the event that you encounter issues with background key encryption, you can disable it by setting the innodb_encryption_rotate_key_age system variable to 0. You may find this useful when the constant key version checks lead to excessive CPU usage. It's also useful in cases where your encryption key management plugin does not support key rotation, (such as with the file_key_management plugin). For more information, see MDEV-14180.
There are, however, issues that can arise when the background key rotation is disabled.
Prior to MariaDB 10.2.24, MariaDB 10.3.15, and MariaDB 10.4.5, when you update the value on the innodb_encrypt_tables system variable InnoDB internally treats the subsequent background operations to encrypt and decrypt tablespaces as background key rotations. See MDEV-14398 for more information.
In older versions of MariaDB, if you have recently changed the value of the innodb_encrypt_tables system variable, then you must ensure that any pending background encryption or decryption operations are complete before disabling key rotation. You can check the status of background encryption operations by querying the INNODB_TABLESPACES_ENCRYPTION table in the information_schema database.
See InnoDB Background Encryption Threads: Checking the Status of Background Operations for some example queries.
Otherwise, in older versions of MariaDB, if you disable key rotation while there are background encryption threads at work, it may result in unencrypted tables that you want encrypted or vice versa.
For more information, see MDEV-14398.
This page is licensed: CC BY-SA / Gnu FDL
With InnoDB tables using encryption, there are several cases where a CREATE TABLE or ALTER TABLE statement can throw Error 1005, due to the InnoDB error 140, Wrong create options. For instance,
CREATE TABLE `test`.`table1` ( `id` INT(4) PRIMARY KEY , `name` VARCHAR(50));
ERROR 1005 (HY000): Can't create table `test`.`table1` (errno: 140 "Wrong create options")When this occurs, you can usually get more information about the cause of the error by following it with a SHOW WARNINGS statement.
This error is known to occur in the following cases:
Encrypting a table by setting the ENCRYPTED table option to YES when the innodb_file_per_table is set to OFF.In this case, SHOW WARNINGS would return the following:
SHOW WARNINGS;
+---------+------+---------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------------------+
| Warning | 140 | InnoDB: ENCRYPTED requires innodb_file_per_table |
| Error | 1005 | Can't create table `db1`.`tab3` (errno: 140 "Wrong create options") |
| Warning | 1030 | Got error 140 "Wrong create options" from storage engine InnoDB |
+---------+------+---------------------------------------------------------------------+
3 rows in set (0.00 sec)Encrypting a table by setting the ENCRYPTED table option to YES, and the innodb_default_encryption_key_id system variable or the ENCRYPTION_KEY_ID table option refers to a non-existent key identifier. In this case, SHOW WARNINGS would return the following:
SHOW WARNINGS;
+---------+------+---------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------------------+
| Warning | 140 | InnoDB: ENCRYPTION_KEY_ID 500 not available |
| Error | 1005 | Can't create table `db1`.`tab3` (errno: 140 "Wrong create options") |
| Warning | 1030 | Got error 140 "Wrong create options" from storage engine InnoDB |
+---------+------+---------------------------------------------------------------------+
3 rows in set (0.00 sec)In some versions, this could happen while creating a table with the ENCRYPTED table option set to DEFAULT while the innodb_encrypt_tables system variable is set to OFF, and the innodb_default_encryption_key_id system variable or the ENCRYPTION_KEY_ID table option are not set to 1. In this case, SHOW WARNINGS would return the following:
SHOW WARNINGS;
+---------+------+---------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------------------+
| Warning | 140 | InnoDB: innodb_encrypt_tables=OFF only allows ENCRYPTION_KEY_ID=1 |
| Error | 1005 | Can't create table `db1`.`tab3` (errno: 140 "Wrong create options") |
| Warning | 1030 | Got error 140 "Wrong create options" from storage engine InnoDB |
+---------+------+---------------------------------------------------------------------+
3 rows in set (0.00 sec)Starting in MariaDB 10.1.39, MariaDB 10.2.23, and MariaDB 10.3.14, creating a table with the ENCRYPTED table option set to DEFAULT while the innodb_encrypt_tables system variable is set to OFF, and the innodb_default_encryption_key_id system variable or the ENCRYPTION_KEY_ID table option are not set to 1 will no longer fail, and it will no longer throw a warning.
For more information, see MDEV-18601.
If you set the ENCRYPTION_KEY_ID table option for a table that is unencrypted because the innodb_encrypt_tables system variable is set to OFF and the ENCRYPTED table option set to DEFAULT, then this encryption key ID will be saved in the table's .frm file, but the encryption key will not be saved to the table's .ibd file.
As a side effect, with the current encryption design, if the innodb_encrypt_tables system variable is later set to ON, and InnoDB goes to encrypt the table, then the InnoDB background encryption threads will not read this encryption key ID from the .frm file. Instead, the threads may encrypt the table with the encryption key with ID 1, which is internally considered the default encryption key when no key is specified. For example:
SET GLOBAL innodb_encrypt_tables=OFF;
CREATE TABLE tab1 (
id INT PRIMARY KEY,
str VARCHAR(50)
) ENCRYPTION_KEY_ID=100;
SET GLOBAL innodb_encrypt_tables=ON;
SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID
FROM information_schema.INNODB_TABLESPACES_ENCRYPTION
WHERE NAME='db1/tab1';
+----------+-------------------+----------------+
| NAME | ENCRYPTION_SCHEME | CURRENT_KEY_ID |
+----------+-------------------+----------------+
| db1/tab1 | 1 | 1 |
+----------+-------------------+----------------+A similar problem is that, if you set the ENCRYPTION_KEY_ID table option for a table that is unencrypted because the ENCRYPTED table option is set to NO, then this encryption key ID will be saved in the table's .frm file, but the encryption key will not be saved to the table's .ibd file.
Recent versions of MariaDB will throw warnings in the case where the ENCRYPTED table option is set to NO, but they will allow the operation to succeed. For example:
CREATE TABLE tab1 (
id INT PRIMARY KEY,
str VARCHAR(50)
) ENCRYPTED=NO ENCRYPTION_KEY_ID=100;
Query OK, 0 rows affected, 1 warning (0.01 sec)
SHOW WARNINGS;
+---------+------+--------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------+
| Warning | 140 | InnoDB: ENCRYPTED=NO implies ENCRYPTION_KEY_ID=1 |
+---------+------+--------------------------------------------------+
1 row in set (0.00 sec)However, in this case, if you change the ENCRYPTED table option to YES or DEFAULT with ALTER TABLE, then it will actually use the proper key. For example:
SET GLOBAL innodb_encrypt_tables=ON;
ALTER TABLE tab1 ENCRYPTED=DEFAULT;
SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID
FROM information_schema.INNODB_TABLESPACES_ENCRYPTION
WHERE NAME = 'db1/tab1';
+----------+-------------------+----------------+
| NAME | ENCRYPTION_SCHEME | CURRENT_KEY_ID |
+----------+-------------------+----------------+
| db1/tab1 | 1 | 100 |
+----------+-------------------+----------------+For more information, see MDEV-17230, MDEV-18601, and MDEV-19086.
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.
The fix for MDEV-12112 that was included in MariaDB 10.1.38, MariaDB 10.2.20, and MariaDB 10.3.12 changed the way that MariaDB distinguishes between encrypted and unencrypted pages, so that it is less likely to mistake an unencrypted page for an encrypted page.
In MariaDB 10.4.3 and later, if innodb_checksum_algorithm is set to full_crc32 or strict_full_crc32, and if the table does not use ROW_FORMAT=COMPRESSED, then data files will be guaranteed to be zero-initialized.
For more information, see MDEV-18097.
MariaDB 10.4.3 introduces support for encrypting spatial indexes. To enable, set the innodb_checksum_algorithm to full_crc32 or to strict_full_crc32. Note that MariaDB only encrypts spatial indexes when the ROW_FORMAT table option is not set to COMPRESSED.
In older versions of MariaDB, spatial index encryption is unsupported. Tables that contain spatial indexes store them unencrypted.
For more information, see MDEV-12026.
This page is licensed: CC BY-SA / Gnu FDL