Data at Rest Encryption¶
In Percona Server
5.7.20-18 existing tablespace encryption support
has been extended to handle general tablespaces. A general tablespace is either
fully encrypted, covering all the tables inside, either not encrypted at all.
It is not possible to have only some of the tables in the general tablespace
This feature extends the CREATE TABLESPACE
statement to accept the
Only one keyring plugin should be enabled at a time. Enabling multiple keyring plugins is unsupported and may result in data loss.
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.
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.
Percona XtraBackup currently doesn’t support backup of encrypted general tablespaces.
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).
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)
- 5.7.21-21 – Implemented
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.
To use this option, keyring plugin must be loaded, otherwise server will give error message and refuse to create new temporary tables.
- 5.7.21-21 – Implemented
This variable has 5 possible values.
ON makes InnoDB tables encrypted by
FORCE disables creation of unencrypted tables.
the like-before behavior.
This feature is considered ALPHA quality.
innodb_encrypt_tables=ON still allows to create unencrypted
ENCRYPTED=NO statement, and also allows to create unencrypted
ALTER TABLE statement used without explicit
does not change current table encryption mode even if
innodb_encrypt_tables is set to
While replicating, master sends the stream of decrypted binary log events to a slave (SSL connections can be set up to encrypt them in transport). That said, masters and slaves use separate keyring storages and are free to use differing keyring plugins.
Dumping of encrypted binary logs involves decryption, and can be done using
Taking into account that
--read-from-remote-server option is only
relevant to binary logs, encrypted relay logs can not be dumped/decrypted
in this way.
|Availability:||This feature is Alpha quality|
InnoDB redo log encryption is enabled by setting the variable
innodb_redo_log_encrypt. This variable has three values:
OFF (set by default).
MASTER_KEY uses the InnoDB master key to encrypt unique keys for each log
file in the redo log header.
KEYRING_KEY uses the
key from the keyring. When
innodb_redo_log_encrypt is set to
KEYRING_KEY, each new redo log file is encrypted with the latest
percona_redo key from the keyring.
Temporary files are currently used in Percona Server for the following purposes:
This feature is considered ALPHA quality.
- filesort (for example,
- binary log transactional caches,
- Group Replication caches.
For each temporary file, an encryption key is generated locally, only kept in memory for the lifetime of the temporary file, and discarded afterwards.
The keyring management is enabled for each tablespace separately when you set the encryption, using the ENCRYPTION clause, to ROTATED_KEYS in the supported SQL statement:
- CREATE TABLE .. ENCRYPTION=’ROTATED_KEYS`
- ALTER TABLE ... ENCRYPTION=’ROTATED_KEYS’
- CREATE TABLESPACE tablespace_name … ENCRYPTION=’ROTATED_KEYS’
ALTER TABLE .. ENCRYPTION=’Y’ on the tablespace created with
ENCRYPTION=’ROTATED_KEYS’ converts the table back to the existing MySQL
The safest way to load the plugin is to do it on the server startup by using –early-plugin-load variable option:
--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.
If server should be started with several plugins loaded early,
--early-plugin-load should contain their list separated by semicolons. Also
it’s a good practice to put this list in double quotes so that semicolons
do not create problems when executed in a script.
Apart from installing 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
mysql> INSTALL PLUGIN keyring_udf SONAME 'keyring_udf.so';
On plugin initialization
keyring_vault connects to the Vault server using
credentials stored in the credentials file. Location of this file is specified
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
secret_mount_point- the name of the mount point where
keyring_vaultwill store keys.
token- a token generated by the Vault server, which
keyring_vaultwill 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_vaultis used only for transparent data encryption, and not for
keyring_udfplugin is combined with
keyring_vault, this token should be also allowed to remove keys from the Vault (for the
keyring_key_removeoperation supported by the
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.
secret_mount_point should be used by only one server - otherwise
mixing encryption keys from different servers may lead to undefined
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.
Percona XtraBackup currently doesn’t support backup of tables encrypted with Keyring Vault plugin.
- 5.7.20-18 – Implemented
This variable is used to define the location of the Keyring Vault plugin configuration file.
- 5.7.21-20 – Implemented
This variable allows to set the duration in seconds for the Vault server
connection timeout. Default value is
15. Allowed range is from
86400 seconds (24 hours). The timeout can be also completely
disabled to wait infinite amount of time by setting this variable to