MySQL Data-at-Rest Encryption for Tables

This article explains how MySQL natively secures sensitive data-at-rest using InnoDB tablespace encryption. It covers the database’s two-tier key architecture, the role of keyring plugins, and the practical steps required to enable and manage encryption for individual database tables.

The Two-Tier Key Architecture

MySQL utilizes a two-tier encryption key architecture to secure tablespaces without sacrificing database performance. This system consists of a master encryption key and individual tablespace keys.

• Tablespace Key (Internal): Each encrypted tablespace has its own tablespace key. This key is used to encrypt and decrypt the actual data blocks written to disk. The tablespace key is stored directly within the tablespace header, but it is never stored in plaintext; it is always encrypted.
• Master Encryption Key (External): The master encryption key is used to encrypt the individual tablespace keys. It is managed externally from the database files and resides within a MySQL keyring.

When a query requests data from an encrypted table, MySQL uses the master key to decrypt the tablespace key, which in turn decrypts the data blocks in memory. When the master key is rotated, only the tablespace keys are decrypted and re-encrypted with the new master key; the actual table data on disk remains untouched, making key rotation a fast metadata operation.

The Role of Keyring Plugins

To manage the master encryption key, MySQL relies on keyring components or plugins. The keyring must be loaded early in the MySQL startup process so that the master key is available before the InnoDB storage engine initializes.

MySQL offers several keyring plugins depending on the edition (Community vs. Enterprise):

• keyring_file: Stores the master key in a local file on the server. This is suitable for basic setups but is less secure because the key file exists on the same storage system as the database.
• keyring_encrypted_file: An enterprise-grade version of the file plugin that encrypts the local keyring file itself.
• HashiCorp Vault / KMIP Plugins: Secure enterprise plugins that store the master key in external, dedicated key management systems (KMS).

How to Enable Table Encryption

To encrypt sensitive tables natively in MySQL, you must first configure a keyring plugin in the MySQL configuration file (my.cnf or my.ini).

[mysqld]
early-plugin-load=keyring_file.so
keyring_file_data=/var/lib/mysql-keyring/keyring

Once the keyring is active, you can natively encrypt individual tables during creation or alter existing tables using the ENCRYPTION clause.

Creating a New Encrypted Table

To create a table with data-at-rest encryption, append ENCRYPTION='Y' to the CREATE TABLE statement:

CREATE TABLE sensitive_user_data (
    id INT AUTO_INCREMENT PRIMARY KEY,
    social_security_number VARCHAR(11) NOT NULL,
    credit_card_hash VARCHAR(64) NOT NULL
) ENCRYPTION='Y';

Encrypting an Existing Table

To encrypt an already existing unencrypted table, use the ALTER TABLE statement:

ALTER TABLE sensitive_user_data ENCRYPTION='Y';

To remove encryption from a table, alter the table and set the option to 'N':

ALTER TABLE sensitive_user_data ENCRYPTION='N';

Encrypting Logs and Shared Tablespaces

While encrypting individual tables (which reside in their own file-per-table tablespaces) secures the primary data files, sensitive data can still leak into auxiliary files. MySQL 8.0 allows administrators to extend native encryption to these areas:

• System Tablespace: Can be encrypted by setting innodb_sys_tablespace_encrypt=ON.
• Redo Logs: The transaction logs can be encrypted by setting innodb_redo_log_encrypt=ON.
• Undo Logs: Active undo logs can be secured by setting innodb_undo_log_encrypt=ON.
• Doublewrite Buffer: MySQL automatically encrypts pages written to the doublewrite buffer if the originating tablespace is encrypted.