Data at Rest Encryption¶

System Variables¶

variable innodb_default_encryption_key_id¶

Version Info:	5.7.23-24 – Implemented
Command Line:	--innodb-default-encryption-key-id
Dynamic:	Yes
Scope:	Session
Variable Type:	Numeric
Default Value:	0

The ID of the default encryption key. By default, this variable contains 0 to encrypt new tables with the latest version of the key percona_innodb-0.

To change the default value use the following syntax:

mysql> SET innodb_default_encryption_key_id = NEW_ID

Here, NEW_ID is an unsigned 32-bit integer.

System variables¶

variable innodb_sys_tablespace_encrypt¶

Version Info:	5.7.23-24 – Implemented
Command Line:	--innodb-sys-tablespace-encrypt
Dynamic:	No
Scope:	Global
Variable Type:	Boolean
Default Value:	OFF

Enables the encryption of the InnoDB System tablespace. It is essential that the server is started with the --bootstrap option.

variable innodb_parallel_dblwr_encrypt¶

Version Info:	5.7.23-24 – Implemented
Command Line:	--innodb-parallel-dblwr-encrypt
Dynamic:	Yes
Scope:	Global
Variable Type:	Boolean
Default Value:	OFF

Enables the encryption of the parallel doublewrite buffer. For encryption, uses the key of the tablespace where the parallel doublewrite buffer is used.

Usage¶

General tablespace encryption is enabled by the following syntax extension:

mysql> CREATE TABLESPACE tablespace_name ... ENCRYPTION='Y'

Attempts to create or to move tables, including partitioned ones, to a general tablespace with an incompatible encryption setting are diagnosed and aborted.

As you cannot move tables between encrypted and unencrypted tablespaces, you will need to create another table, add it to a specific tablespace and run INSERT INTO SELECT from the table you want to move from, and then you will get encrypted or decrypted table with your desired content.

Example¶

To create an encrypted tablespace run:

mysql> CREATE TABLESPACE foo ADD DATAFILE 'foo.ibd' ENCRYPTION='Y';

To add an encrypted table to that table space run:

mysql> CREATE TABLE t1 (a INT, b TEXT) TABLESPACE foo ENCRYPTION="Y";

Trying to add unencrypted table to this table space will result in an error:

mysql> CREATE TABLE t3 (a INT, b TEXT) TABLESPACE foo ENCRYPTION="N";
ERROR 1478 (HY000): InnoDB: Tablespace `foo` can contain only an ENCRYPTED tables.

Checking¶

If there is a general tablespace which doesn’t include tables yet, sometimes user needs to find out whether it is encrypted or not (this task is easier for single tablespaces since you can check table info).

A flag field in the INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES has bit number 13 set if tablespace is encrypted. This bit can be ckecked with flag & 8192 expression in the following way:

>SELECT space, name, flag, (flag & 8192) != 0 AS encrypted FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES WHERE name in ('foo', 'test/t2', 'bar', 'noencrypt');
+-------+-----------+-------+-----------+
| space | name      | flag  | encrypted |
+-------+-----------+-------+-----------+
|    29 | foo       | 10240 |      8192 |
|    30 | test/t2   |  8225 |      8192 |
|    31 | bar       | 10240 |      8192 |
|    32 | noencrypt |  2048 |         0 |
+-------+-----------+-------+-----------+
4 rows in set (0.01 sec)

System Variables¶

variable innodb_temp_tablespace_encrypt¶

Version Info:	5.7.21-21 – Implemented
Command Line:	--innodb-temp-tablespace-encrypt
Dynamic:	Yes
Scope:	Global
Variable Type:	Boolean
Default Value:	Off

When this option is turned on, server starts to encrypt temporary tablespace and temporary InnoDB file-per-table tablespaces. The option does not force encryption of temporary tables which are currently opened, and it doesn’t rebuild system temporary tablespace to encrypt data which are already written.

Since temporary tablespace is created fresh at each server startup, it will not contain unencrypted data if this option specified as server argument.

Turning this option off at runtime makes server to create all subsequent temporary file-per-table tablespaces unencrypted, but does not turn off encryption of system temporary tablespace.

This feature is considered BETA quality.

variable innodb_encrypt_tables¶

Version Info:	5.7.21-21 – Implemented
Command Line:	--innodb-encrypt-tables
Dynamic:	Yes
Scope:	Global
Variable Type:	Text
Default Value:	OFF

The implementation of the behavior controlled by this variable is considered BETA quality.

This variable was ported from MariaDB and then extended to support key rotation. This variable has the following possible values:

ON

New tables are created encrypted. You can create an unencrypted table by using the ENCRYPTION=NO clause to the CREATE TABLE or ALTER TABLE statement.

OFF

By default, newly created tables are not encrypted. Add the ENCRYPTION=YES clause in the CREATE TABLE or ALTER TABLE statement to create an encrypted table.

FORCE

New tables are created encrypted with the master key. Passing ENCRYPTION=NO to CREATE TABLE or ALTER TABLE will result in an error and the table will not be created or altered.

If you alter a table which was created without encryption, note that it will not be encrypted unless you use the ENCRYPTION clause explicitly.

KEYRING_ON

New tables are created encrypted with the keyring as the default encryption. You may specify a numeric key identifier and use a specific percona-innodb- key from the keyring instead of the default key:

mysql> CREATE TABLE ... ENCRYPTION=’KEYRING’ ENCRYPTION_KEY_ID=NEW_ID

NEW_ID is an unsigned 32-bit integer that refers to the numerical part of the percona_innodb- key. When you assign a numerical identifer in the ENCRYPTION_KEY_ID clause, the server uses the latest version of the corresponding key. For example, the clause ENCRYPTION_KEY_ID=2 refers to the latest version of the percona_innodb-2 key from the keyring.

In case the percona-innodb- key with the requested ID does not exist in the keyring, Percona Server will create it with version 1. If a new percona-innodb- key cannot be created with the requested ID, the whole CREATE TABLE statement fails

FORCE_KEYRING

New tables are created encrypted and keyring encryption is enforced.

ONLINE_TO_KEYRING

All tables created or altered without the ENCRYPTION=NO clause are encrypted with the latest version of the default encryption key. If a table being altered is already encrypted with the master key, the table is recreated encrypted with the latest version of the default encryption key.

ONLINE_TO_KEYRING_FORCE

It is only possible to apply the keyring encryption when creating or altering tables.

variable innodb_online_encryption_threads¶

Version Info:	5.7.23-24 – Implemented
Command Line:	--innodb-online-encryption-threads
Dynamic:	Yes
Scope:	Global
Variable Type:	Numeric
Default Value:	1

This variable works in combination with the innodb_encrypt_tables variable set to ONLINE_TO_KEYRING. This variable configures the number of threads for background encryption. For the online encryption to work, this variable must contain a value greater than zero.

variable innodb_online_encryption_rotate_key_age¶

Version Info:	5.7.23-24 – Implemented
Command Line:	--innodb-online-encryption-rotate-key-age
Dynamic:	Yes
Scope:	Global
Variable Type:	Numeric
Default Value:	1

By using this variable, you can re-encrypt the table encrypted using KEYRING. The value of this variable determines how frequently the encrypted tables should be encrypted again. If it is set to 1, the encrypted table is re-encrypted on each key rotation. If it is set to 2, the table is encrypted on every other key rotation.

variable innodb_encrypt_online_alter_logs¶

Version Info:	5.7.21-21 – Implemented
Command Line:	--innodb-encrypt-online-alter-logs
Dynamic:	Yes
Scope:	Global
Variable Type:	Boolean
Default Value:	OFF

This variable simultaneously turns on the encryption of files used by InnoDB for full text search using parallel sorting, building indexes using merge sort, and online DDL logs created by InnoDB for online DDL.

System variables¶

variable innodb_undo_log_encrypt¶

Version Info:	5.7.23-24 – Implemented
Command Line:	--innodb-undo-log-encrypt
Dynamic:	Yes
Scope:	Global
Variable Type:	Boolean
Default Value:	Off

Enables the encryption of InnoDB Undo tablespaces

System Variables¶

variable encrypt_binlog¶

Version Info:	5.7.20-19 – Implemented
Command Line:	--encrypt-binlog
Dynamic:	No
Scope:	Global
Variable Type:	Boolean
Default Value:	OFF

The variable turns on binary and relay logs encryption.

System variables¶

variable innodb_redo_log_encrypt¶

Version Info:	5.7.23-24 – Implemented
Command Line:	--innodb-redo-log-encrypt
Dynamic:	Yes
Scope:	Global
Variable Type:	Text
Default Value:	OFF

Enables the encryption of the redo log.

variable innodb_scrub_log¶

Version Info:	5.7.23-24 – Implemented
Command Line:	--innodb-scrub-log
Dynamic:	Yes
Scope:	Global
Variable Type:	Boolean
Default Value:	OFF

Specifies if data scrubbing should be automatically applied to the redo log.

variable innodb_scrub_log_speed¶

Version Info:	5.7.23-24 – Implemented
Command Line:	--innodb-scrub-log-speed
Dynamic:	Yes
Scope:	Global
Variable Type:	Text
Default Value:

Specifies the velocity of data scrubbing (writing dummy redo log records) in bytes per second.

System Variables¶

variable encrypt-tmp-files¶

Version Info:	5.7.22-22 – Implemented
Command Line:	--encrypt-tmp-files
Dynamic:	No
Scope:	Global
Variable Type:	Boolean
Default Value:	OFF

The option turns on encryption of temporary files created by Percona Server.

Installation¶

The safest way to load the plugin is to do it on the server startup by using –early-plugin-load option option:

$ mysqld --early-plugin-load="keyring_vault=keyring_vault.so" \
--loose-keyring_vault_config="/home/mysql/keyring_vault.conf"

It should be loaded this way to be able to facilitate recovery for encrypted tables.

Apart from installing the plugin you also need to set the keyring_vault_config variable. This variable should point to the keyring_vault configuration file, whose contents are discussed below.

This plugin supports the SQL interface for keyring key management described in General-Purpose Keyring Key-Management Functions manual.

To enable the functions you’ll need to install the keyring_udf plugin:

mysql> INSTALL PLUGIN keyring_udf SONAME 'keyring_udf.so';

Usage¶

On plugin initialization keyring_vault connects to the Vault server using credentials stored in the credentials file. Location of this file is specified in by keyring_vault_config. On successful initialization it retrieves keys signatures and stores them inside an in-memory hash map.

Configuration file should contain the following information:

• vault_url - the address of the server where Vault is running. It can be a named address, like one in the following example, or just an IP address. The important part is that it should begin with https://.
• secret_mount_point - the name of the mount point where keyring_vault will store keys.
• token - a token generated by the Vault server, which keyring_vault will further use when connecting to the Vault. At minimum, this token should be allowed to store new keys in a secret mount point (when keyring_vault is used only for transparent data encryption, and not for keyring_udf plugin). If keyring_udf plugin is combined with keyring_vault, this token should be also allowed to remove keys from the Vault (for the keyring_key_remove operation supported by the keyring_udf plugin).
• vault_ca [optional] - this variable needs to be specified only when the Vault’s CA certificate is not trusted by the machine that is going to connect to the Vault server. In this case this variable should point to CA certificate that was used to sign Vault’s certificates.

An example of the configuration file looks like this:

vault_url = https://vault.public.com:8202
secret_mount_point = secret
token = 58a20c08-8001-fd5f-5192-7498a48eaf20
vault_ca = /data/keyring_vault_confs/vault_ca.crt

When a key is fetched from a keyring for the first time the keyring_vault communicates with the Vault server, and retrieves the key type and data. Next it queries the Vault server for the key type and data and caches it locally.

Key deletion will permanently delete key from the in-memory hash map and the Vault server.

System Variables¶

variable keyring_vault_config¶

Version Info:	5.7.20-18 – Implemented
Command Line:	--keyring-vault-config
Dynamic:	Yes
Scope:	Global
Variable Type:	Text
Default Value:

This variable is used to define the location of the Keyring Vault plugin configuration file.

variable keyring_vault_timeout¶

Version Info:	5.7.21-20 – Implemented
Command Line:	--keyring-vault-timeout
Dynamic:	Yes
Scope:	Global
Variable Type:	Numeric
Default Value:	15

This variable allows to set the duration in seconds for the Vault server connection timeout. Default value is 15. Allowed range is from 1 second to 86400 seconds (24 hours). The timeout can be also completely disabled to wait infinite amount of time by setting this variable to 0.

System Variables¶

variable innodb_background_scrub_data_compressed¶

Version Info:	5.7.23-24 – Implemented
Command Line:	--innodb-background-scrub-data-compressed
Dynamic:	Yes
Scope:	Global
Variable Type:	Boolean
Default Value:	OFF

variable innodb_background_scrub_data_uncompressed¶

Version Info:	5.7.23-24 – Implemented
Command Line:	--innodb-background-scrub-data-uncompressed
Dynamic:	Yes
Scope:	Global
Variable Type:	Boolean
Default Value:	OFF