Beta Draft: 2017-03-27
InnoDB supports data encryption for
InnoDB tables stored in
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,
uses the master encryption key to decrypt the tablespace key. The
master encryption key is stored in a
file in the location specified by the
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.
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.
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
tablespace encryption feature, see
Section A.15, “MySQL 8.0 FAQ: InnoDB Tablespace Encryption”.
keyring_fileplugin must be installed. 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 7.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-loadoption, prior to
InnoDBinitialization. Failing to do so results in errors on startup and during
SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS->
WHERE PLUGIN_NAME LIKE 'keyring%';+--------------+---------------+ | PLUGIN_NAME | PLUGIN_STATUS | +--------------+---------------+ | keyring_file | ACTIVE | +--------------+---------------+
innodb_file_per_tableoption must be enabled (the default).
InnoDBtablespace 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
InnoDBtablespace 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
keyringfile immediately after creating the first encrypted table and before and after master key rotation. The
keyringfile location is defined by the
keyring_file_dataconfiguration option. For keyring plugin configuration information, see Section 7.5.4, “The MySQL Keyring”.
To enable encryption for a new
ENCRYPTION option in a
CREATE TABLE statement.
CREATE TABLE t1 (c1 INT) ENCRYPTION='Y';
To enable encryption for an existing
table, specify the
ENCRYPTION option in an
ALTER TABLE statement.
ALTER TABLE t1 ENCRYPTION='Y';
To disable encryption for an
ALTER TABLE t1 ENCRYPTION='N';
Plan appropriately when altering an existing table with the
ALTER TABLE ...
ENCRYPTION operations rebuild the table using
ALGORITM=INPLACE is not supported.
Redo log data encryption is enabled using the
configuration option. Redo log encryption is disabled by
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.
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
disabled, encrypted redo log pages that are present on disk
remain encrypted, and new redo log pages are written to disk in
Redo log encryption metadata, including the tablespace
encryption key, is stored in the header of the first redo log
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
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 16.20.2, “Forcing InnoDB Recovery”.
Undo log data encryption is enabled using the
configuration option. Undo log encryption only applies undo logs
that reside in separate
See Section 16.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.
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
disabled, encrypted undo log pages that are present on disk
remain encrypted, and new undo log pages are written to disk in
Undo log encryption metadata, including the tablespace
encryption key, is stored in the header of the undo log file
N is the space ID).
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,
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:
ALTER INSTANCE ROTATE INNODB MASTER KEY;
ROTATE INNODB MASTER KEY supports concurrent DML.
However, it cannot be run concurrently with
CREATE TABLE ...
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.
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 tablespaces 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 table is exported,
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 16.7.6, “Copying File-Per-Table 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 the tablespace encryption feature.
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_fileplugin is installed on both the master and a slave but the slave does not have a
keyringfile, the replicated
ALTER INSTANCE ROTATE INNODB MASTER KEYstatement creates the
keyringfile on the slave, assuming the
keyringfile data is not cached in memory.
ALTER INSTANCE ROTATE INNODB MASTER KEYuses
keyringfile 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 field of
field may be queried to identify encrypted tables in a 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" | +--------------+------------+----------------+
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, which is an
ALGORITHM=COPYoperation that rebuilds the table.
keyringfile is empty or missing, the first execution of
ALTER INSTANCE ROTATE INNODB MASTER KEYcreates a master encryption key.
keyring_fileplugin does not remove an existing
It is recommended that you not place the
keyringfile under the same directory as tablespace data files. The location of the
keyringfile is specified by the
keyring_file_dataoption at runtime or restarting the server with a new
keyring_file_datasetting can cause previously encrypted tables to become inaccessible, resulting in the loss of 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 an
ALGORITHM=INPLACEis not supported.
InnoDBtablespace encryption only supports
InnoDBtables that are stored in a file-per-table tablespaces. Encryption is not supported for tables stored in other
InnoDBtablespace 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
By default, tablespace encryption only applies to data in the tablespace. Redo log and undo log data may be encrypted using the
innodb_undo_log_encryptoptions. See Redo Log Data Encryption, and Undo Log Data Encryption. Binary log data is not encrypted.