Encrypting Data for InnoDB / XtraDB
Contents
MariaDB can encrypt data in tables that use the InnoDB and XtraDB storage engines.
For encryption with the Aria storage engine, see Encrypting Data for Aria.
Basic Configuration
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. Most users will want to set innodb_encrypt_tables
, innodb_encrypt_logs
, innodb_encryption_threads
, and innodb_encryption_rotate_key_age
. InnoDB and XtraDB both use the same server system variables to configure support for data-at-rest encryption
Users of data-at-rest encryption will also need to have a key management and encryption plugin configured. Some examples are File Key Management Plugin and AWS Key Management Plugin.
[mariadb] ... # 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 = 4 innodb_encryption_rotate_key_age = 1
Limitations
Wrong create options
There are several cases where a CREATE TABLE
or ALTER TABLE
statement that involves encryption on an InnoDB table can throw the following error:
Error: 140 Wrong create options.
You can usually execute SHOW WARNINGS
to get more details about the cause of the error. For example:
create table `table1` ( `id` int(4) primary key , `name` varchar(50)); ERROR 1005 (HY000): Can't create table `dba_test`.`table1` (errno: 140 "Wrong create options") show warnings; +---------+------+----------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------------------------------------+ | Warning | 140 | InnoDB: innodb_encrypt_tables=OFF only allows ENCRYPTION_KEY_ID=1 | | Error | 1005 | Can't create table `dba_test`.`table1` (errno: 140 "Wrong create options") | | Warning | 1030 | Got error 140 "Wrong create options" from storage engine InnoDB | +---------+------+----------------------------------------------------------------------------+ 3 rows in set (0.00 sec)
This error is known to occur in at least the following cases:
- You attempted to encrypt a table with
ENCRYPTED=YES
set as a table option andinnodb_file_per_table=OFF
is set. - You attempted to encrypt a table with the
ENCRYPTION_KEY_ID
table option set to a key that cannot be found. - You attempted to set the
ENCRYPTION_KEY_ID
table option for a table, but theENCRYPTED=YES
table option was not also set for that table. See MDEV-17230 about that. - You attempted to encrypt a table with
ENCRYPTED=YES
set as a table option whileinnodb_encrypt_tables=OFF
was set, andinnodb_default_encryption_key_id
or theENCRYPTION_KEY_ID
table option was not set to1
.
Tablespaces Created on MySQL 5.1.47 or Earlier
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.
See MDEV-18097 about plans for permanently fixing this issue.
Seeing Which Tables are Encrypted
The Information Schema INNODB_TABLESPACES_ENCRYPTION table gives encryption information about InnoDB tables, including which tables are encrypted.
For example:
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