Data at Rest Encryption¶
Percona Server for MySQL enables data at rest encryption of the InnoDB (file-per-table) tablespace by encrypting the physical database files. The data is automatically encrypted prior to writing to storage and automatically decrypted when read. If unauthorized users access the data files, they cannot read the contents. Percona Server for MySQL 5.7 data at rest encryption is similar to the MySQL 5.7 data-at-rest encryption. Percona Server for MySQL 8.0 provides more encryption features and options which are not available in this version.
The following table lists the various features that are considered Generally Available (GA) or in tech preview. The tech preview features and variables are not recommended to be used in production. Features and variables marked as deprecated perform no action.
| Feature | Status | GA Version | Tech Preview Version | Deprecated Version |
|---|---|---|---|---|
| Vault Keyring Plugin | Generally Available, supported | Percona Server 5.7.21-21 | ||
| Encrypting a File-Per-Table Tablespace | Generally Available, supported | Percona Server 5.7.21-21 | ||
| Encrypting a General Tablespace | Generally Available, supported | Percona Server 5.7.21-21 | ||
| Temporary file encryption | Generally Available, supported | Percona Server 5.7.22-22 | ||
| binlog_encrypt | Generally Available, supported | Percona Server 5.7.21-21 | ||
| InnoDB System Tablespace Encryption | Deprecated | Percona Server 5.7.23-24 | Percona Server for MySQL 5.7.32-35 | |
| Doublewrite buffer | Deprecated | Percona Server 5.7.23-24 | Percona Server for MySQL 5.7.32-35 | |
| InnoDB Undo Tablespace Encryption | Deprecated | Percona Server 5.7.23-24 | Percona Server for MySQL 5.7.32-35 | |
| Redo Log Encryption | Deprecated | Percona Server 5.7.23-24 | Percona Server for MySQL 5.7.32-35 | |
| Data Scrubbing | Deprecated | Percona Server 5.7.23-24 | Percona Server for MySQL 5.7.32-35 |
Architecture¶
The data at rest encryption uses a two-tier architecture with the following components:
| Type | Description |
|---|---|
| Master key | The Master key is used to encrypt or decrypt the tablespace keys. |
| Tablespace key for each tablespace | The tablespace key encrypts the data pages and is written in the tablespace header. |
When the server must access the data, the master key decrypts the tablespace key, the tablespace is decrypted and available for read or write operations.
The two separate keys architecture allows the master key to be rotated in a minimal operation. During the master key rotation, each tablespace key is re-encrypted with the new master key. Only the first page of the tablespace file (.ibd) is read and written during the rotation. An encrypted page is decrypted at the I/O layer, added to the buffer pool, and used to read and write the data. A buffer pool page is not encrypted. The I/O layer encrypts the page before the page is flushed to disk.
An encryption key in the tablespace header is required to encrypt or decrypt the tablespace. The Master key is stored in the keyring plugin.
Note
Percona XtraBackup version 2.4 supports the backup of encrypted general tablespaces.
Vault Keyring Plugin¶
To enable encryption, use either of the following plugins:
-
keyring_file stores the keyring data locally in a flat file
-
keyring_vault provides an interface for the database with a HashiCorp Vault server to store key and secure encryption keys.
Enable only one keyring plugin at a time. Enabling multiple keyring plugins is not supported and may result in data loss.
Note
The keyring_file plugin should not be used for regulatory compliance.
To install the selected plugin, follow the installing and uninstalling plugins instructions.
Loading the Keyring Plugin¶
Load the plugin at server startup with the early-plugin-load Option to enable the keyring. To make encrypted table recovery more efficient,load the plugin with the configuration file.
Run the following command to load the keyring_file plugin:
$ mysqld --early-plugin-load="keyring_file=keyring_file.so"
Note
To start a server with different early plugins to be loaded, the --early-plugin-load option can contain the plugin names in a double-quoted list with each plugin name separated by a semicolon. The use of double quotes ensures the semicolons do not create issues when the list is executed in a script.
To enable Master key vault encryption, the user must have SUPER privileges.
The following statements loads the keyring_vault plugin and the keyring_vault_config. The second statement provides the location to the keyring_vault configuration file.
[mysqld]
early-plugin-load="keyring_vault=keyring_vault.so"
loose-keyring_vault_config="/home/mysql/keyring_vault.conf"
Add the following statements to my.cnf:
[mysqld]
early-plugin-load="keyring_vault=keyring_vault.so"
loose-keyring_value_config="/home/mysql/keyring_vault.conf"
Restart the server.
Note
The keyring_vault extension, “.so”, and the file location for the vault configuration should be changed to match your operating system’s extension and operating system location.
Describing the keyring_vault_config file¶
The keyring_vault_config file has the following information:
-
vault_url- the Vault server address -
secret_mount_point- where the keyring_vault stores the keys -
secret_mount_point_version- theKV Secrets Engine version (kv or kv-v2)used. Implemented in Percona Server for MySQL 5.7.33-36. -
token- a token generated by the Vault server -
vault_ca [optional]- if the machine does not trust the Vault’s CA certificate, this variable points to the CA certificate used to sign the Vault’s certificates.
The following is a configuration file example:
vault_url = https://vault.public.com:8202
secret_mount_point = secret
secret_mount_point_version = AUTO
token = 58a20c08-8001-fd5f-5192-7498a48eaf20
vault_ca = /data/keyring_vault_confs/vault_ca.crt
Warning
Each secret_mount_point must be used by only one server. Multiple servers using the same secret_mount_point may cause unpredictable behavior.
Create a backup of the keyring configuration file or data file immediately after creating the encrypted tablespace. If you are using Master key encryption, backup before master key rotation and after master key rotation.
The first time a key is retrieved from a keyring, the keyring_vault communicates with the Vault server to retrieve the key type and data.
| Variables |
|---|
| keyring_vault_config |
| keyring_vault_timeout |
Verifying the Keyring Plugin is Active¶
To verify the keyring plugin is active, run the SHOW PLUGINS statement or run a query on the INFORMATION_SCHEMA.PLUGINS table. You can also query the PLUGINS view.
SELECT plugin_name, plugin_status FROM INFORMATION_SCHEMA.PLUGINS WHERE plugin_name LIKE 'keyring%';
The output could be the following:
+---------------+----------------+
| plugin_name | plugin_status |
+===============+================+
| keyring_file | ACTIVE |
+---------------+----------------+
Encrypting a File-Per-Table Tablespace¶
The CREATE TABLESPACE statement is extended to allow the ENCRYPTION=['Y/N'] option to encrypt a File-per-Table tablespace.
CREATE TABLESPACE tablespace_name ENCRYPTION='Y';
To enable encryption to an existing tablespace, add the ENCRYPTION option to the ALTER TABLE statement.
ALTER TABLESPACE myexample ENCRYPTION='Y';
You must add the ENCRYPTION option to ALTER TABLE to change the table encryption state. Without the ENCRYPTION option, an encrypted table remains encrypted or an unencrypted table remains unencrypted.
To change the tablespace key, run the optimize table command.
mysql> optimize table t1;
Encrypting a General Tablespace¶
As of Percona Server 5.7.20-18, Percona Server for MySQL supports general tablespace encryption. You cannot partially encrypt the tables in a general tablespace. All of the tables must be encrypted or none of the tables are encrypted.
Automatically Encrypting Tablespaces¶
Add the innodb_encrypt_tables variable to my.cnf to automatically encrypt general tablespaces. The possible values for the variable are:
| Value | Description |
|---|---|
| OFF | The default value which disables automatic encryption of new tables |
| ON | Enables automatic encryption for new tables |
| FORCE | New tables are automatically created with encryption. |
| Adding ENCRYPTION=NO to either a CREATE TABLE or ALTER TABLE statement results in a warning. | |
The CREATE TABLESPACE statement is extended to allow the ENCRYPTION=['Y/N'] option.
CREATE TABLESPACE tablespace_name ENCRYPTION='Y';
To encrypt an existing table, add the ENCRYPTION option in the ALTER TABLE statement.
ALTER TABLE t1 ENCRYPTION='Y';
You can also disable encryption for a table, set the encryption to N.
ALTER TABLE t1 ENCRYPTION='N';
Note
The ALTER TABLE statement modifies the current encryption mode only if the ENCRYPTION clause is explicitly added.
System Variables¶
Note
You cannot change the tablespace key for tables in a general tablespace.
Encrypting Binary Logs¶
To start binlog encryption, start the server with -encrypt-binlog=1. This state requires -master_verify_checksum and -binlog_checksum to be ON and one of the keyring plugins loaded.
NOTE: These actions do not encrypt all binlogs in a replication schema. You must enable encrypt-binlog on each of the replica servers, even if they do not produce binlog files. Enabling encryption on replica servers enable relay log encryption.
You can rotate the encryption key used by Percona Server for MySQL by running the following statement:
SELECT rotate_system_key("percona_binlog");
Note
The rotate_system_key("percona_binlog") command is Experimental quality.
This command creates a new binlog encryption key in the keyring. The new key encrypts the next binlog file.
Temporary file encryption¶
Percona Server for MySQL supports the encryption of temporary file storage. Users enable the encryption with encrypt-tmp_files.
Enable the variable in the following command:
[mysqld]
...
encrypt-tmp-files=ON
...
Verifying the Encryption Setting¶
For single tablespaces, verify the ENCRYPTION option using INFORMATION_SCHEMA.TABLES and the CREATE OPTIONS settings.
SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS FROM
INFORMATION_SCHEMA.TABLES WHERE CREATE_OPTIONS LIKE '%ENCRYPTION%';
The output could be the following:
+----------------------+-------------------+------------------------------+
| TABLE_SCHEMA | TABLE_NAME | CREATE_OPTIONS |
+----------------------+-------------------+------------------------------+
|sample | t1 | ENCRYPTION="Y" |
+----------------------+-------------------+------------------------------+
A flag field in the INFORMATION_SCHEMA.INNODB_TABLESPACES has the bit
number 13 set if the tablespace is encrypted. This bit can be checked with the
flag & 8192 expression with the following method:
SELECT space, name, flag, (flag & 8192) != 0 AS encrypted FROM
INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE name in ('foo', 'test/t2', 'bar',
'noencrypt');
The output could be the following:
+-------+-----------+-------+-----------+
| 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)
To allow for master Key rotation, you can encrypt an already encrypted InnoDB
system tablespace with a new master key by running the following ALTER
INSTANCE statement:
ALTER INSTANCE ROTATE INNODB MASTER KEY;
Rotating the Master Key¶
For security, you should rotate the Master key in a timely manner. Use the ALTER INSTANCE statement. To rotate the key, you must have SUPER privilege.
ALTER INSTANCE ROTATE INNODB MASTER KEY;
The statement cannot be run at the same time you run CREATE TABLE ... ENCRYPTION or ALTER TABLE ENCRYPTION statements. The ALTER INSTANCE statement uses locks to prevent conflicts. If a DML statement is running, that statement must complete before the ALTER INSTANCE statement begins.
When the Master key is rotated, the tablespace keys in that instance are re-encrypted. The operation does not re-encrypt the tablespace data.
The re-encryption for the tablespace keys must succeed for the key rotation to be successful. If the rotation is interrupted, for example, if there is a server failure, the operation rolls forward when the server restarts.
InnoDB System Tablespace Encryption¶
This feature was in tech preview from version 5.7.23-24 but is deprecated from version 5.7.32-35. This feature is not recommended to be used in production.
The InnoDB system tablespace is encrypted by using master key encryption. The
server must be started with the --bootstrap option.
If the variable innodb_sys_tablespace_encrypt is set to ON and the server has been started in the bootstrap mode, you may create an encrypted table as follows:
mysql> CREATE TABLE ... TABLESPACE=innodb_system ENCRYPTION='Y'
Note
You cannot encrypt existing tables in the System tablespace.
It is not possible to convert the system tablespace from encrypted to unencrypted or vice versa. A new instance should be created and user tables must be transferred to the desired instance.
You can encrypt the already encrypted InnoDB system tablespace (key rotation)
with a new master key by running the following ALTER INSTANCE statement:
mysql> ALTER INSTANCE ROTATE INNODB MASTER KEY
innodb_sys_tablespace_encrypt¶
| Option | Description |
|---|---|
| Command-line | –innodb-sys-tablespace-encrypt |
| Scope | Global |
| Dynamic | No |
| Data type | Boolean |
| Default | OFF |
The variable has been implemented in Percona Server 5.7.23-24 and deprecated in Percona Server for MySQL 5.7.32-35. Enables the encryption of the InnoDB System tablespace. It is essential that the
server is started with the --bootstrap option.
Doublewrite buffer¶
This feature was in tech preview from version Percona Server 5.7.23-24 but is deprecated from version Percona Server for MySQL 5.7.32-35. This feature is not recommended to be used in production.
The two types of doublewrite buffers used in Percona Server for MySQL are encrypted differently.
When the InnoDB system tablespace is encrypted, the doublewrite buffer pages
are encrypted as well. The key which was used to encrypt the InnoDB system
tablespace is also used to encrypt the doublewrite buffer.
Percona Server for MySQL encrypts the parallel doublewrite buffer with the respective
tablespace keys. Only encrypted tablespace pages are written as encrypted in the
parallel doublewrite buffer. Unencrypted tablespace pages will be written as
unencrypted.
innodb_parallel_dblwr_encrypt¶
| Option | Description |
|---|---|
| Command-line | –innodb-parallel-dblwr-encrypt |
| Scope | Global |
| Dynamic | Yes |
| Data type | Boolean |
| Default | OFF |
The variable has been implemented in Percona Server 5.7.23-24 and deprecated in Percona Server for MySQL 5.7.32-35. Enables the encryption of the parallel doublewrite buffer. For encryption, uses the key of the tablespace where the parallel doublewrite buffer is used.
InnoDB Undo Tablespace Encryption¶
This feature was in tech preview from version Percona Server 5.7.23-24 but is deprecated from version Percona Server for MySQL 5.7.32-35. This feature is not recommended to be used in production.
The encryption of InnoDB Undo tablespaces is only available when using separate undo tablespaces. Otherwise, the InnoDB undo log is part of the InnoDB system tablespace.
System variables¶
innodb_undo_log_encrypt¶
| Option | Description |
|---|---|
| Command-line | –innodb-undo-log-encrypt |
| Scope | Global |
| Dynamic | Yes |
| Data type | Boolean |
| Default | Off |
The variable has been implemented in Percona Server 5.7.23-24 and deprecated in Percona Server for MySQL 5.7.32-35. Enables the encryption of InnoDB Undo tablespaces. You can enable encryption and disable encryption while the server is running.
Note
If you enable undo log encryption, the server writes encryption information into the header. That information stays in the header during the life of the undo log. If you restart the server, the server will try to load the encryption key from the keyring during startup. If the keyring is not available, the server cannot start.
Redo Log Encryption¶
This feature was in tech preview from version 5.7.23-24 but is deprecated from version 5.7.32-35. This feature is not recommended to be used in production.
InnoDB redo log encryption is enabled by setting the variable
innodb_redo_log_encrypt. This variable has three values:
MASTER_KEY, KEYRING_KEY and OFF (set by default).
MASTER_KEY uses the InnoDB master key to encrypt with unique keys for each
log file in the redo log header.
KEYRING_KEY uses the percona_redo versioned 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.
System variables¶
Implemented in version Percona Server for MySQL 5.7.27-30, the key rotation is redesigned to allow SELECT rotate_system_key("percona_redo). The currently used key version is available in the innodb_redo_key_version status. The feature is Experimental.
Data Scrubbing¶
This feature was in tech preview from version Percona Server 5.7.23-24 but is deprecated from version Percona Server for MySQL 5.7.32-35. This feature is not recommended to be used in production.
While data encryption ensures that the existing data are not stored in plain
form, the data scrubbing literally removes the data once the user decides they
should be deleted. Compare this behavior with how the DELETE statement works
which only marks the affected data as deleted - the space claimed by this data
is overwritten with new data later.
Once enabled, data scrubbing works automatically on each tablespace separately. To enable data scrubbing, you need to set the following variables:
-
innodb-background-scrub-data-uncompressed
-
innodb-background-scrub-data-compressed
Uncompressed tables can also be scrubbed immediately, independently of key rotation or background threads. This can be enabled by setting the variable innodb-immediate-scrub-data-uncompressed. This option is not supported for compressed tables.
Note that data scrubbing is made effective by setting the innodb_online_encryption_threads variable to a value greater than zero.
System Variables¶
innodb_background_scrub_data_compressed¶
| Option | Description |
|---|---|
| Command-line | –innodb-background-scrub-data-compressed |
| Scope | Global |
| Dynamic | Yes |
| Data type | Boolean |
| Default | OFF |
The variable has been implemented in Percona Server 5.7.23-24 and deprecated in Percona Server for MySQL 5.7.32-35.
innodb_background_scrub_data_uncompressed¶
| Option | Description |
|---|---|
| Command-line | –innodb-background-scrub-data-uncompressed |
| Scope | Global |
| Dynamic | Yes |
| Data type | Boolean |
| Default | OFF |
The variable has been implemented in Percona Server 5.7.23-24 and deprecated in Percona Server for MySQL 5.7.32-35.
Variables¶
keyring_vault_config - Defines the location of the Loading the Keyring Plugin configuration file.
| Option | Description |
|---|---|
| Command line | –keyring-vault-config |
| Dynamic | Yes |
| Scope | Global |
| Variable Type | Text |
| Default |
keyring_vault_timeout - Set the duration in seconds for the Vault server connection timeout. The default value is 15. The allowed range is from 0 to 86400. To wait an infinite amount of time set the variable to 0.
| Option | Description |
|---|---|
| Command line | –keyring-vault-timeout |
| Dynamic | Yes |
| Scope | Global |
| Variable Type | Numeric |
| Default | 15 |
innodb_encrypt_tables¶
| Option | Description |
|---|---|
| Command-line | –innodb-encrypt-tables |
| Scope | Global |
| Dynamic | Yes |
| Data type | Text |
| Default | OFF |
The variable has been implemented in Percona Server 5.7.21-21.
Note
This variable is Experimental quality.
innodb_redo_log_encrypt¶
| Option | Description |
|---|---|
| Command-line | –innodb-redo-log-encrypt |
| Scope | Global |
| Dynamic | Yes |
| Data type | Text |
| Default | OFF |
The variable has been implemented in Percona Server 5.7.23-24. Enables the encryption of the redo log.
innodb_scrub_log¶
| Option | Description |
|---|---|
| Command-line | –innodb-scrub-log |
| Scope | Global |
| Dynamic | Yes |
| Data type | Boolean |
| Default | OFF |
The variable has been implemented in Percona Server 5.7.23-24. Specifies if data scrubbing should be automatically applied to the redo log.
innodb_scrub_log_speed¶
| Option | Description |
|---|---|
| Command-line | –innodb-scrub-log-speed |
| Scope | Global |
| Dynamic | Yes |
| Data type | Text |
| Default |
The variable has been implemented in Percona Server 5.7.23-24. Specifies the velocity of data scrubbing (writing dummy redo log records) in bytes per second.