MySQL :: MySQL 5.7 Reference Manual :: 14.14 InnoDB Data-at-Rest Encryption (original) (raw)

InnoDB supports data-at-rest encryption forfile-per-table tablespaces.

About Data-at-Rest Encryption

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

The data-at-rest encryption feature relies on a keyring plugin for master encryption key management.

All MySQL editions provide a keyring_file plugin, which stores keyring data in a file local to the server host.

MySQL Enterprise Edition offers additional keyring plugins:

Warning

For encryption key management, thekeyring_file andkeyring_encrypted_file plugins are 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 encryption keys in key vaults or hardware security modules (HSMs).

A secure and robust encryption key management solution is critical for security and for compliance with various security standards. When the data-at-rest encryption feature uses a centralized key management solution, the feature is referred to as “MySQL Enterprise Transparent Data Encryption (TDE)”.

The data-at-rest encryption feature 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.

For frequently asked questions about the data-at-rest encryption feature, see Section A.17, “MySQL 5.7 FAQ: InnoDB Data-at-Rest Encryption”.

Encryption Prerequisites

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS  
       FROM INFORMATION_SCHEMA.PLUGINS  
       WHERE PLUGIN_NAME LIKE 'keyring%';  
+--------------+---------------+  
| PLUGIN_NAME  | PLUGIN_STATUS |  
+--------------+---------------+  
| keyring_file | ACTIVE        |  
+--------------+---------------+

Enabling File-Per-Table Tablespace Encryption

To enable encryption for a new file-per-table tablespace, specify the ENCRYPTION option in aCREATE TABLE statement. The following example assumes thatinnodb_file_per_table is enabled.

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

To enable encryption for an existing file-per-table tablespace, specify the ENCRYPTION option in anALTER TABLE statement.

mysql> ALTER TABLE t1 ENCRYPTION='Y';

To disable encryption for file-per-table tablespace, setENCRYPTION='N' using ALTER TABLE.

mysql> ALTER TABLE t1 ENCRYPTION='N';

Master Key Rotation

The master encryption key should be rotated periodically and whenever you suspect that the key has 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, seeEncryption 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 theSUPER 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 tablespace encryption operations, and locks are taken to prevent conflicts that could arise from concurrent execution. If anALTER INSTANCE ROTATE INNODB MASTER KEY operation is running, it must finish before a tablespace encryption operation can proceed, and vice versa.

Encryption and Recovery

If a server failure occurs during an encryption operation, the operation is rolled forward when the server is restarted.

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. (SeeEncryption Prerequisites.)

When InnoDB initialization and recovery begin, the master key rotation operation resumes. Due to the server failure, some tablespace 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 tablespace 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 Tablespaces

When an encrypted tablespace 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_`tablespacename`_.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_`tablespacename`_.cfp file. For related information, seeSection 14.6.1.3, “Importing InnoDB Tables”.

Encryption and Replication

Identifying Encrypted Tablespaces

When the ENCRYPTION option is specified in aCREATE TABLE orALTER TABLE statement, it is recorded in the CREATE_OPTIONS column of the Information Schema TABLES table. This column can be queried to identify tables that reside in encrypted file-per-table tablespaces.

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

QueryINFORMATION_SCHEMA.INNODB_SYS_TABLESPACES to retrieve information about the tablespace associated with a particular schema and table.

mysql> SELECT SPACE, NAME, SPACE_TYPE FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES WHERE NAME='test/t1';
+-------+---------+------------+
| SPACE | NAME    | SPACE_TYPE |
+-------+---------+------------+
|     3 | test/t1 | Single     |
+-------+---------+------------+

Encryption Usage Notes

Encryption Limitations