InnoDB tablespace encryption feature
provides at-rest data encryption for
tablespace data files.
Tablespace encryption 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
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 tablespace encryption feature relies on a keyring plugin for master encryption key management.
All MySQL editions provide a
plugin, which stores keyring data in a file local to the server
MySQL Enterprise Edition offers additional keyring plugins:
keyring_encrypted_fileplugin, which stores keyring data in an encrypted file local to the server host.
keyring_okvplugin, which includes a KMIP client (KMIP 1.1) that uses a KMIP-compatible product as a back end for keyring storage. Supported KMIP-compatible products include centralized key management solutions such as Oracle Key Vault, Gemalto KeySecure, Thales Vormetric key management server, and Fornetix Key Orchestration.
keyring_awsplugin, which communicates with the Amazon Web Services Key Management Service (AWS KMS) as a back end for key generation and uses a local file for key storage.
keyring_encrypted file plugins are not
intended as regulatory compliance solutions. 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 tablespace encryption feature uses a centralized key management solution, the feature is referred to as “MySQL Enterprise Transparent Data Encryption (TDE)”.
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.
For frequently asked questions about the tablespace encryption feature, see Section A.16, “MySQL 5.7 FAQ: InnoDB Tablespace Encryption”.
A keyring plugin must be installed and configured. Keyring plugin installation is performed at startup using the
early-plugin-loadoption. Early loading ensures that the plugin is available prior to initialization of the
InnoDBstorage engine. For keyring plugin installation and configuration instructions, see Section 6.5.4, “The MySQL Keyring”.
Only one keyring plugin can be enabled at a time. Enabling multiple keyring plugins is not supported.Important
Once encrypted tablespaces are created in a MySQL instance, the keyring plugin that was loaded when creating the encrypted tablespace must continue to be loaded at startup using the
early-plugin-loadoption. Failing to do so results in errors when starting the server and during
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'keyring%'; +--------------+---------------+ | PLUGIN_NAME | PLUGIN_STATUS | +--------------+---------------+ | keyring_file | ACTIVE | +--------------+---------------+
Encryption is only supported for file-per-table tablespaces. To create a table in a file-per-table tablespace, ensure that
innodb_file_per_tableis enabled (the default) before executing a
CREATE TABLEstatement. Alternatively, use the
When encrypting production data, ensure that you take steps to prevent loss of the master encryption key. If the master encryption key is lost, data stored in encrypted tablespace files is unrecoverable. If you use the
keyring_encrypted_fileplugin, create a backup of the keyring data file immediately after creating the first encrypted tablespace, before master key rotation, and after master key rotation. The
keyring_file_dataconfiguration option defines the keyring data file location for the
keyring_encrypted_file_dataconfiguration option defines the keyring data file location for the
keyring_encrypted_fileplugin. If you use the
keyring_awsplugin, ensure that you have performed the necessary configuration. For instructions, see Section 6.5.4, “The MySQL Keyring”.
To enable encryption for a new file-per-table tablespace,
ENCRYPTION option in a
CREATE TABLE statement.
mysql> CREATE TABLE t1 (c1 INT) ENCRYPTION='Y';
To enable encryption for an existing file-per-table tablespace,
ENCRYPTION option in an
ALTER TABLE statement.
mysql> ALTER TABLE t1 ENCRYPTION='Y';
To disable encryption for file-per-table tablespace, set
mysql> ALTER TABLE t1 ENCRYPTION='N';
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,
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
To rotate the master encryption key, run:
mysql> ALTER INSTANCE ROTATE INNODB MASTER KEY;
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 an
ROTATE INNODB MASTER KEY operation is running, it must
finish before a tablespace encryption operation can proceed, and
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
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
InnoDB initialization and recovery
activities access tablespace data. (See
InnoDB Tablespace Encryption Prerequisites.)
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.
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,
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.
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
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
file. For related information, see
Section 184.108.40.206, “Copying Tablespaces to Another Instance”.
ALTER INSTANCE ROTATE INNODB MASTER KEYstatement is only supported in replication environments where the master and slaves run a version of MySQL that supports tablespace encryption.
ALTER INSTANCE ROTATE INNODB MASTER KEYstatements are written to the binary log for replication on slaves.
ALTER INSTANCE ROTATE INNODB MASTER KEYstatement fails, it is not logged to the binary log and is not replicated on slaves.
Replication of an
ALTER INSTANCE ROTATE INNODB MASTER KEYoperation fails if the keyring plugin is installed on the master but not on the slave.
keyring_encrypted_fileplugin is installed on both the master and a slave but the slave does not have a keyring data file, the replicated
ALTER INSTANCE ROTATE INNODB MASTER KEYstatement creates the keyring data file on the slave, assuming the keyring file data is not cached in memory.
ALTER INSTANCE ROTATE INNODB MASTER KEYuses keyring file data that is cached in memory, if available.
ENCRYPTION option is specified in a
CREATE TABLE or
ALTER TABLE statement, it is
recorded in the
CREATE_OPTIONS column of
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" | +--------------+------------+----------------+
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 | +-------+---------+------------+
Plan appropriately when altering an existing tablespace with the
ENCRYPTIONoption. The table is rebuilt using the
INPLACEalgorithm is not supported.
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 tablespace 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 encryption, which is an
ALGORITHM=COPYoperation that rebuilds the table.
If a keyring data file (the file named by
keyring_encrypted_file_data) is empty or missing, the first execution of
ALTER INSTANCE ROTATE INNODB MASTER KEYcreates a master encryption key.
keyring_encrypted_fileplugin does not remove an existing keyring data file.
It is recommended that you not place a keyring data file under the same directory as tablespace data files.
keyring_encrypted_file_datasetting at runtime or when restarting the server can cause previously encrypted tablespaces to become inaccessible, resulting in lost data.
Advanced Encryption Standard (AES) is the only supported encryption algorithm.
InnoDBtablespace encryption uses Electronic Codebook (ECB) block encryption mode for tablespace key encryption and Cipher Block Chaining (CBC) block encryption mode for data encryption.
ENCRYPTIONattribute of a table is performed using the
INPLACEalgorithm is not supported.
You cannot move or copy a table from an encrypted file-per-table tablespace to a tablespace type that does not support encryption.
Tablespace encryption only applies to data in the tablespace. Data is not encrypted in the redo log, undo log, or binary log.
It is not permitted to change the storage engine of a table that resides or previously resided in an encrypted tablespace.