MySQL :: MySQL 8.0 Reference Manual :: 15.7.10 InnoDB Tablespace Encryption

5.7 current

MySQL 8.0 Reference Manual / ... / InnoDB Tablespace Encryption

Pre-General Availability Draft: 2017-07-17

15.7.10 InnoDB Tablespace Encryption

InnoDB supports data encryption for InnoDB tables stored in file-per-table tablespaces. This feature provides at-rest encryption for physical tablespace data files.

InnoDB tablespace encryption uses a two tier encryption key architecture, consisting of a master encryption key and tablespace keys. When an InnoDB table is encrypted, a tablespace key is encrypted and stored in the tablespace header. When an application or authenticated user wants to access encrypted tablespace data, InnoDB uses the master encryption key to decrypt the tablespace key. The master encryption key is stored in a keyring file in the location specified by the keyring_file_data configuration option. The decrypted version of a tablespace key never changes, but the master encryption key may be changed as required. This action is referred to as master key rotation.

The InnoDB tablespace encryption feature relies on a keyring plugin for master encryption key management.

InnoDB tablespace encryption supports the Advanced Encryption Standard (AES) block-based encryption algorithm. It uses Electronic Codebook (ECB) block encryption mode for tablespace key encryption and Cipher Block Chaining (CBC) block encryption mode for data encryption.

Note

The InnoDB tablespace encryption feature provided with MySQL Community Edition is not intended as a regulatory compliance solution. Security standards such as PCI, FIPS, and others require use of key management systems to secure, manage, and protect keys in key vaults or hardware security modules (HSMs).

For frequently asked questions about the InnoDB tablespace encryption feature, see Section A.15, “MySQL 8.0 FAQ: InnoDB Tablespace Encryption”.

InnoDB Tablespace Encryption Prerequisites

The keyring_file plugin must be installed. Keyring plugin installation is performed at startup using the --early-plugin-load option. Early loading ensures that the plugin is available prior to initialization of the InnoDB storage engine. For keyring plugin installation and configuration instructions, see Section 6.5.4, “The MySQL Keyring”.

Important

Once encrypted tables are created in a MySQL instance, the keyring plugin must continue to be loaded using the early-plugin-load option, prior to InnoDB initialization. Failing to do so results in errors on startup and during InnoDB recovery.

To verify that the keyring plugin is active, use the SHOW PLUGINS statement or query the INFORMATION_SCHEMA.PLUGINS table. For example:
```
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
       FROM INFORMATION_SCHEMA.PLUGINS
       WHERE PLUGIN_NAME LIKE 'keyring%';
+--------------+---------------+
| PLUGIN_NAME  | PLUGIN_STATUS |
+--------------+---------------+
| keyring_file | ACTIVE        |
+--------------+---------------+
```
The innodb_file_per_table option must be enabled (the default). InnoDB tablespace encryption only supports file-per-table tablespaces. Alternatively, you can specify the TABLESPACE='innodb_file_per_table' option when creating an encrypted table or altering an existing table to enable encryption.
Before using the InnoDB tablespace encryption feature, ensure that you have taken steps to prevent loss of the master encryption key. If the master encryption key is lost, data stored in encrypted tablespace files is unrecoverable. It is recommended that you create a backup of the keyring file immediately after creating the first encrypted table and before and after master key rotation. The keyring file location is defined by the keyring_file_data configuration option. For keyring plugin configuration information, see Section 6.5.4, “The MySQL Keyring”.

Enabling and Disabling InnoDB Tablespace Encryption

To enable encryption for a new InnoDB table, specify the ENCRYPTION option in a CREATE TABLE statement.

mysql> CREATE TABLE t1 (c1 INT) ENCRYPTION='Y';

To enable encryption for an existing InnoDB table, specify the ENCRYPTION option in an ALTER TABLE statement.

mysql> ALTER TABLE t1 ENCRYPTION='Y';

To disable encryption for an InnoDB table, set ENCRYPTION='N' using ALTER TABLE.

mysql> ALTER TABLE t1 ENCRYPTION='N';

Note

Plan appropriately when altering an existing table with the ENCRYPTION option. ALTER TABLE ... ENCRYPTION operations rebuild the table using ALGORITHM=COPY. ALGORITM=INPLACE is not supported.

Redo Log Data Encryption

Redo log data encryption is enabled using the innodb_redo_log_encrypt configuration option. Redo log encryption is disabled by default.

As with tablespace data, redo log data encryption occurs when redo log data is written to disk, and decryption occurs when redo log data is read from disk. Once redo log data is read into memory, it is in unencrypted form. Redo log data is encrypted and decrypted using the tablepace encryption key.

When innodb_redo_log_encrypt is enabled, unencrypted redo log pages that are present on disk remain unencrypted, and new redo log pages are written to disk in encrypted form. Likewise, when innodb_redo_log_encrypt is disabled, encrypted redo log pages that are present on disk remain encrypted, and new redo log pages are written to disk in unencrypted form.

Redo log encryption metadata, including the tablespace encryption key, is stored in the header of the first redo log file (ib_logfile0). If this file is removed, redo log encryption is disabled.

Once redo log encryption is enabled, a normal restart without the keyring plugin or without the encryption key is not possible, as InnoDB must be able to scan redo pages during startup, which is not possible if redo log pages are encrypted. Without the keyring plugin or the encryption key, only a forced startup without the redo logs (SRV_FORCE_NO_LOG_REDO) is possible. See Section 15.20.2, “Forcing InnoDB Recovery”.

Undo Log Data Encryption

Undo log data encryption is enabled using the innodb_undo_log_encrypt configuration option. Undo log encryption only applies undo logs that reside in separate undo tablespaces. See Section 15.7.7, “Storing InnoDB Undo Logs in Separate Tablespaces”. Encryption is not supported for undo log data that resides in the system tablespace. Undo log data encryption is disabled by default.

As with tablespace data, undo log data encryption occurs when undo log data is written to disk, and decryption occurs when undo log data is read from disk. Once undo log data is read into memory, it is in unencrypted form. Undo log data is encrypted and decrypted using the tablepace encryption key.

When innodb_undo_log_encrypt is enabled, unencrypted undo log pages that are present on disk remain unencrypted, and new undo log pages are written to disk in encrypted form. Likewise, when innodb_undo_log_encrypt is disabled, encrypted undo log pages that are present on disk remain encrypted, and new undo log pages are written to disk in unencrypted form.

Undo log encryption metadata, including the tablespace encryption key, is stored in the header of the undo log file (undoN.ibd, where N is the space ID).

InnoDB Tablespace Encryption and Master Key Rotation

The master encryption key should be rotated periodically and whenever you suspect that the key may have been compromised.

Master key rotation is an atomic, instance-level operation. Each time the master encryption key is rotated, all tablespace keys in the MySQL instance are re-encrypted and saved back to their respective tablespace headers. As an atomic operation, re-encryption must succeed for all tablespace keys once a rotation operation is initiated. If master key rotation is interrupted by a server failure, InnoDB rolls the operation forward on server restart. For more information, see InnoDB Tablespace Encryption and Recovery.

Rotating the master encryption key only changes the master encryption key and re-encrypts tablespace keys. It does not decrypt or re-encrypt associated tablespace data.

Rotating the master encryption key requires the ENCRYPTION_KEY_ADMIN or SUPER privilege.

To rotate the master encryption key, run:

mysql> ALTER INSTANCE ROTATE INNODB MASTER KEY;

ALTER INSTANCE ROTATE INNODB MASTER KEY supports concurrent DML. However, it cannot be run concurrently with CREATE TABLE ... ENCRYPTED or ALTER TABLE ... ENCRYPTED operations, and locks are taken to prevent conflicts that could arise from concurrent execution of these statements. If one of the conflicting statements is running, it must complete before another can proceed.

InnoDB Tablespace Encryption and Recovery

If a server failure occurs during master key rotation, InnoDB continues the operation on server restart.

The keyring plugin must be loaded prior to storage engine initialization so that the information necessary to decrypt tablespace data pages can be retrieved from tablespace headers before InnoDB initialization and recovery activities access tablespace data. (See InnoDB Tablespace Encryption Prerequisites.)

When InnoDB initialization and recovery begin, the master key rotation operation resumes. Due to the server failure, some tablespaces keys may already be encrypted using the new master encryption key. InnoDB reads the encryption data from each tablespace header, and if the data indicates that the tablespace key is encrypted using the old master encryption key, InnoDB retrieves the old key from the keyring and uses it to decrypt the tablepace key. InnoDB then re-encrypts the tablespace key using the new master encryption key and saves the re-encrypted tablespace key back to the tablespace header.

Exporting Encrypted Tables

When an encrypted table is exported, InnoDB generates a transfer key that is used to encrypt the tablespace key. The encrypted tablespace key and transfer key are stored in a tablespace_name.cfp file. This file together with the encrypted tablespace file is required to perform an import operation. On import, InnoDB uses the transfer key to decrypt the tablespace key in the tablespace_name.cfp file. For related information, see Section 15.7.6, “Copying File-Per-Table Tablespaces to Another Instance”.

InnoDB Tablespace Encryption and Replication

The ALTER INSTANCE ROTATE INNODB MASTER KEY statement is only supported in replication environments where the master and slaves run a version of MySQL that supports the tablespace encryption feature.
Successful ALTER INSTANCE ROTATE INNODB MASTER KEY statements are written to the binary log for replication on slaves.
If an ALTER INSTANCE ROTATE INNODB MASTER KEY statement fails, it is not logged to the binary log and is not replicated on slaves.
Replication of an ALTER INSTANCE ROTATE INNODB MASTER KEY operation fails if the keyring plugin is installed on the master but not on the slave.
If the keyring_file plugin is installed on both the master and a slave but the slave does not have a keyring file, the replicated ALTER INSTANCE ROTATE INNODB MASTER KEY statement creates the keyring file on the slave, assuming the keyring file data is not cached in memory. ALTER INSTANCE ROTATE INNODB MASTER KEY uses keyring file data that is cached in memory, if available.

Identifying Tables that Use InnoDB Tablespace Encryption

When the ENCRYPTION option is specified in a CREATE TABLE or ALTER TABLE statement, it is recorded in the CREATE_OPTIONS field of INFORMATION_SCHEMA.TABLES. This field may be queried to identify encrypted tables in a MySQL instance.

mysql> SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
    -> WHERE CREATE_OPTIONS LIKE '%ENCRYPTION="Y"%';
+--------------+------------+----------------+
| TABLE_SCHEMA | TABLE_NAME | CREATE_OPTIONS |
+--------------+------------+----------------+
| test         | t1         | ENCRYPTION="Y" |
+--------------+------------+----------------+

InnoDB Tablespace Encryption Usage Notes

If the server exits or is stopped during normal operation, it is recommended to restart the server using the same encryption settings that were configured previously.
The first master encryption key is generated when the first new or existing table is encrypted.
Master key rotation re-encrypts tablespaces keys but does not change the tablespace key itself. To change a tablespace key, you must disable and re-enable table encryption using ALTER TABLE tbl_name ENCRYPTION, which is an ALGORITHM=COPY operation that rebuilds the table.
If a table is created with both the COMPRESSION and ENCRYPTION options, compression is performed before tablespace data is encrypted.
If a keyring file is empty or missing, the first execution of ALTER INSTANCE ROTATE INNODB MASTER KEY creates a master encryption key.
Uninstalling the keyring_file plugin does not remove an existing keyring file.
It is recommended that you not place the keyring file under the same directory as tablespace data files. The location of the keyring file is specified by the keyring_file_data option.
Modifying the keyring_file_data option at runtime or restarting the server with a new keyring_file_data setting can cause previously encrypted tables to become inaccessible, resulting in the loss of data.

InnoDB Tablespace Encryption Limitations

Advanced Encryption Standard (AES) is the only supported encryption algorithm. InnoDB tablespace encryption uses Electronic Codebook (ECB) block encryption mode for tablespace key encryption and Cipher Block Chaining (CBC) block encryption mode for data encryption.
Altering the ENCRYPTION attribute of a table is an ALGORITHM=COPY operation. ALGORITHM=INPLACE is not supported.
InnoDB tablespace encryption only supports InnoDB tables that are stored in a file-per-table tablespaces. Encryption is not supported for tables stored in other InnoDB tablespace types including general tablespaces, the system tablespace, undo log tablespaces, and the temporary tablespace.
You cannot move or copy an encrypted table from a file-per-table tablespace to an unsupported InnoDB tablespace type.
By default, tablespace encryption only applies to data in the tablespace. Redo log and undo log data may be encrypted using the innodb_redo_log_encrypt and innodb_undo_log_encrypt options. See Redo Log Data Encryption, and Undo Log Data Encryption. Binary log data is not encrypted.

PREV HOME UP NEXT

User Comments