Movatterモバイル変換

• About Data-at-Rest Encryption

• Encryption Prerequisites

• Defining an Encryption Default for Schemas and General Tablespaces

• File-Per-Table Tablespace Encryption

• General Tablespace Encryption

• Doublewrite File Encryption

• mysql System Tablespace Encryption

• Redo Log Encryption

• Undo Log Encryption

• Master Key Rotation

• Encryption and Recovery

• Exporting Encrypted Tablespaces

• Encryption and Replication

• Identifying Encrypted Tablespaces and Schemas

• Monitoring Encryption Progress

• Encryption Usage Notes

• Encryption Limitations

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 tablespace 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 asmaster key rotation.

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

All MySQL editions provide acomponent_keyring_file component andkeyring_file plugin, each of which stores keyring data in a file local to the server host.

MySQL Enterprise Edition offers additional keyring components and plugins:

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, seeSection A.17, “MySQL 8.0 FAQ: InnoDB Data-at-Rest Encryption”.

As of MySQL 8.0.16, thedefault_table_encryption system variable defines the default encryption setting for schemas and general tablespaces.CREATE TABLESPACE andCREATE SCHEMA operations apply thedefault_table_encryption setting when anENCRYPTION clause is not specified explicitly.

ALTER SCHEMA andALTER TABLESPACE operations do not apply thedefault_table_encryption setting. AnENCRYPTION clause must be specified explicitly to alter the encryption of an existing schema or general tablespace.

Thedefault_table_encryption variable can be set for an individual client connection or globally usingSET syntax. For example, the following statement enables default schema and tablespace encryption globally:

mysql> SET GLOBAL default_table_encryption=ON;

The default encryption setting for a schema can also be defined using theDEFAULT ENCRYPTION clause when creating or altering a schema, as in this example:

mysql> CREATE SCHEMA test DEFAULT ENCRYPTION = 'Y';

If theDEFAULT ENCRYPTION clause is not specified when creating a schema, thedefault_table_encryption setting is applied. TheDEFAULT ENCRYPTION clause must be specified to alter the default encryption of an existing schema. Otherwise, the schema retains its current encryption setting.

By default, a table inherits the encryption setting of the schema or general tablespace it is created in. For example, a table created in an encryption-enabled schema is encrypted by default. This behavior enables a DBA to control table encryption usage by defining and enforcing schema and general tablespace encryption defaults.

Encryption defaults are enforced by enabling thetable_encryption_privilege_check system variable. Whentable_encryption_privilege_check is enabled, a privilege check occurs when creating or altering a schema or general tablespace with an encryption setting that differs from thedefault_table_encryption setting, or when creating or altering a table with an encryption setting that differs from the default schema encryption. Whentable_encryption_privilege_check is disabled (the default), the privilege check does not occur and the previously mentioned operations are permitted to proceed with a warning.

TheTABLE_ENCRYPTION_ADMIN privilege is required to override default encryption settings whentable_encryption_privilege_check is enabled. A DBA can grant this privilege to enable a user to deviate from thedefault_table_encryption setting when creating or altering a schema or general tablespace, or to deviate from the default schema encryption when creating or altering a table. This privilege does not permit deviating from the encryption of a general tablespace when creating or altering a table. A table must have the same encryption setting as the general tablespace it resides in.

As of MySQL 8.0.16, a file-per-table tablespace inherits the default encryption of the schema in which the table is created unless anENCRYPTION clause is specified explicitly in theCREATE TABLE statement. Prior to MySQL 8.0.16, theENCRYPTION clause must be specified to enable encryption.

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

To alter the encryption of an existing file-per-table tablespace, anENCRYPTION clause must be specified.

mysql> ALTER TABLE t1 ENCRYPTION = 'Y';

As of MySQL 8.0.16, if thetable_encryption_privilege_check variable is enabled, specifying anENCRYPTION clause with a setting that differs from the default schema encryption requires theTABLE_ENCRYPTION_ADMIN privilege. SeeDefining an Encryption Default for Schemas and General Tablespaces.

As of MySQL 8.0.16, thedefault_table_encryption variable determines the encryption of a newly created general tablespace unless anENCRYPTION clause is specified explicitly in theCREATE TABLESPACE statement. Prior to MySQL 8.0.16, anENCRYPTION clause must be specified to enable encryption.

mysql> CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' ENCRYPTION = 'Y' Engine=InnoDB;

To alter the encryption of an existing general tablespace, anENCRYPTION clause must be specified.

mysql> ALTER TABLESPACE ts1 ENCRYPTION = 'Y';

As of MySQL 8.0.16, if thetable_encryption_privilege_check variable is enabled, specifying anENCRYPTION clause with a setting that differs from thedefault_table_encryption setting requires theTABLE_ENCRYPTION_ADMIN privilege. SeeDefining an Encryption Default for Schemas and General Tablespaces.

Encryption support for doublewrite files is available as of MySQL 8.0.23.InnoDB automatically encrypts doublewrite file pages that belong to encrypted tablespaces. No action is required. Doublewrite file pages are encrypted using the encryption key of the associated tablespace. The same encrypted page written to a tablespace data file is also written to a doublewrite file. Doublewrite file pages that belong to an unencrypted tablespace remain unencrypted.

During recovery, encrypted doublewrite file pages are unencrypted and checked for corruption.

Encryption support for themysql system tablespace is available as of MySQL 8.0.16.

Themysql system tablespace contains themysql system database and MySQL data dictionary tables. It is unencrypted by default. To enable encryption for themysql system tablespace, specify the tablespace name and theENCRYPTION option in anALTER TABLESPACE statement.

mysql> ALTER TABLESPACE mysql ENCRYPTION = 'Y';

To disable encryption for themysql system tablespace, setENCRYPTION = 'N' using anALTER TABLESPACE statement.

mysql> ALTER TABLESPACE mysql ENCRYPTION = 'N';

Enabling or disabling encryption for themysql system tablespace requires theCREATE TABLESPACE privilege on all tables in the instance (CREATE TABLESPACE on *.*).

Redo log data encryption is enabled using theinnodb_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 tablespace encryption key.

Wheninnodb_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, wheninnodb_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.

From MySQL 8.0.30, redo log encryption metadata, including the tablespace encryption key, is stored in the header of the redo log file with the most recent checkpoint LSN. Before MySQL 8.0.30, redo log encryption metadata, including the tablespace encryption key, is stored in the header of the first redo log file (ib_logfile0). If the redo log file with the encryption metadata is removed, redo log encryption is disabled.

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

Undo log data encryption is enabled using theinnodb_undo_log_encrypt configuration option. Undo log encryption applies to undo logs that reside inundo tablespaces. SeeSection 17.6.3.4, “Undo Tablespaces”. 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 tablespace encryption key.

Wheninnodb_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, wheninnodb_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.

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 theENCRYPTION_KEY_ADMIN privilege (or the deprecatedSUPER 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.

If a server failure occurs during an encryption operation, the operation is rolled forward when the server is restarted. For general tablespaces, the encryption operation is resumed in a background thread from the last processed page.

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

The keyring component or 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 beforeInnoDB initialization and recovery activities access tablespace data. (SeeEncryption Prerequisites.)

WhenInnoDB 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.

Tablespace export is only supported for file-per-table tablespaces.

When an encrypted tablespace is exported,InnoDB generates atransfer key that is used to encrypt the tablespace key. The encrypted tablespace key and transfer key are stored in atablespace_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 thetablespace_name.cfp file. For related information, seeSection 17.6.1.3, “Importing InnoDB Tables”.

The Information SchemaINNODB_TABLESPACES table, introduced in MySQL 8.0.13, includes anENCRYPTION column that can be used to identify encrypted tablespaces.

mysql> SELECT SPACE, NAME, SPACE_TYPE, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES       WHERE ENCRYPTION='Y'\G*************************** 1. row ***************************     SPACE: 4294967294      NAME: mysqlSPACE_TYPE: GeneralENCRYPTION: Y*************************** 2. row ***************************     SPACE: 2      NAME: test/t1SPACE_TYPE: SingleENCRYPTION: Y*************************** 3. row ***************************     SPACE: 3      NAME: ts1SPACE_TYPE: GeneralENCRYPTION: Y

When theENCRYPTION option is specified in aCREATE TABLE orALTER TABLE statement, it is recorded in theCREATE_OPTIONS column ofINFORMATION_SCHEMA.TABLES. 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" |+--------------+------------+----------------+

Query the Information SchemaINNODB_TABLESPACES table to retrieve information about the tablespace associated with a particular schema and table.

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

You can identify encryption-enabled schemas by querying the Information SchemaSCHEMATA table.

mysql> SELECT SCHEMA_NAME, DEFAULT_ENCRYPTION FROM INFORMATION_SCHEMA.SCHEMATA       WHERE DEFAULT_ENCRYPTION='YES';+-------------+--------------------+| SCHEMA_NAME | DEFAULT_ENCRYPTION |+-------------+--------------------+| test        | YES                |+-------------+--------------------+

SHOW CREATE SCHEMA also shows theDEFAULT ENCRYPTION clause.

You can monitor general tablespace andmysql system tablespace encryption progress usingPerformance Schema.

Thestage/innodb/alter tablespace (encryption) stage event instrument reportsWORK_ESTIMATED andWORK_COMPLETED information for general tablespace encryption operations.

The following example demonstrates how to enable thestage/innodb/alter tablespace (encryption) stage event instrument and related consumer tables to monitor general tablespace ormysql system tablespace encryption progress. For information about Performance Schema stage event instruments and related consumers, seeSection 29.12.5, “Performance Schema Stage Event Tables”.