MySQL Tablespace Encryption (TDE): Encrypting Data at Rest

In 2023, a healthcare SaaS company suffered a breach that exposed patient records stored on decommissioned EBS volumes — volumes that were detached from their RDS instances, snapshotted for archiving, and handed to a storage vendor without ever having at-rest encryption enabled. The data files were fully readable because MySQL's InnoDB tablespace files store rows in plaintext by default: anyone with filesystem access has unrestricted access to every row, index, and undo record. SOC 2, HIPAA, and PCI-DSS all require encryption at rest, and auditors increasingly demand evidence that the encryption is enforced at the storage layer — not just in transit. MySQL's Transparent Data Encryption (TDE), available since MySQL 5.7.11 for file-per-table tablespaces and substantially extended in MySQL 8.0, gives you AES-256 encryption at the InnoDB page level with no changes to application code, no query rewrites, and a verified key management integration path.

What TDE Is and What It Actually Protects

Transparent Data Encryption operates at the storage layer, below the SQL engine. When InnoDB flushes a dirty buffer pool page to disk, TDE intercepts the write and encrypts the page using AES-256-CBC (CBC for older releases, CBC or XTS depending on version and configuration). When the page is read back into the buffer pool, the reverse decryption happens in the I/O thread before the page is placed in memory. From the perspective of every layer above the storage engine — the SQL optimizer, the replication stream writer, the query cache — data is always plaintext.

This threat model is deliberately narrow. TDE protects against:

• Stolen or decommissioned storage media — raw disk images, EBS volume clones, SAN snapshots handed to a third party, or physical disks removed from a data center.
• Filesystem-level access by unauthorized users — OS administrators, storage operators, or cloud provider support staff who can access the underlying file system but do not have MySQL credentials.
• Unencrypted backup files — cold backups of .ibd files, mysqldump output is plaintext regardless of TDE, but physical backups via XtraBackup or MySQL Enterprise Backup of encrypted tablespaces remain encrypted.

TDE does not protect against:

• SQL-level access by authenticated users — a user with SELECT privileges reads plaintext data because decryption happens transparently in the InnoDB I/O path.
• Memory inspection — data in the buffer pool is always decrypted and readable to anything with access to process memory.
• Logical backup files — mysqldump reads through the engine and produces plaintext SQL, regardless of tablespace encryption status.

Keyring Plugins: Choosing the Right Key Manager

TDE requires a running keyring plugin before any encrypted tablespace can be created or opened. The keyring plugin is the trusted store for the master encryption key (MEK). MySQL ships with four options, with substantial differences in security posture and operational complexity.

keyring_file — Development and Testing Only

The keyring_file plugin stores the master key in a plaintext file on the local filesystem. It exists for development convenience and should never appear in a production deployment: if an attacker gets the tablespace files, they almost certainly also get the keyfile.

# my.cnf — early-plugin-load is required so the keyring
# is available before InnoDB opens encrypted tablespaces on startup
[mysqld]
early-plugin-load = keyring_file.so
keyring_file_data = /var/lib/mysql-keyring/keyring

keyring_encrypted_file — Single-Server Passphrase Protection

The keyring_encrypted_file plugin stores the master key in an AES-encrypted file, protected by a passphrase you supply at startup. This is meaningfully more secure than keyring_file for single-server environments where a separate key management appliance is not practical — the passphrase must be provided out-of-band (e.g., via a secrets manager or a startup script that reads from HashiCorp Vault) and is not stored on disk.

[mysqld]
early-plugin-load = keyring_encrypted_file.so
keyring_encrypted_file_data     = /var/lib/mysql-keyring/keyring.enc
keyring_encrypted_file_password = MyStr0ngPassphrase!

keyring_okv — Oracle Key Vault

keyring_okv integrates MySQL with Oracle Key Vault (OKV), a KMIP-compatible hardware security module appliance. This is the appropriate choice for enterprises already running Oracle infrastructure or those required to use a FIPS 140-2 Level 3 certified HSM. The plugin connects to the OKV endpoint over mTLS and delegates all key generation, storage, and rotation to the vault.

[mysqld]
early-plugin-load = keyring_okv.so
keyring_okv_conf_dir = /etc/mysql/keyring-okv
# The conf directory must contain:
#   okvclient.ora  — OKV endpoint configuration
#   ssl/           — client certificate and CA bundle for mTLS

keyring_aws — AWS Key Management Service

keyring_aws is the production standard for MySQL running on AWS, whether on EC2 or as a foundation for RDS-compatible self-managed deployments. It uses an AWS KMS Customer Master Key (CMK) to envelope-encrypt the local keyring. The plugin calls the KMS GenerateDataKey API at initialization, stores the encrypted data key locally, and uses it to protect all tablespace encryption keys. Key material never leaves KMS in plaintext.

[mysqld]
early-plugin-load = keyring_aws.so
keyring_aws_cmk_id  = arn:aws:kms:us-east-1:123456789012:key/mrk-abc123
keyring_aws_region  = us-east-1
# IAM role or instance profile must have:
#   kms:GenerateDataKey
#   kms:Decrypt
# scoped to the specific CMK ARN

Enabling Tablespace Encryption on New Tables

Once a keyring plugin is loaded and running, enabling encryption on a new InnoDB table requires a single clause. MySQL creates the individual tablespace key (a per-table AES-256 key), encrypts it with the master key, and stores the encrypted key in the tablespace header.

-- Create an encrypted table (file-per-table tablespace)
CREATE TABLE payments (
    id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    account_id    BIGINT UNSIGNED NOT NULL,
    amount_cents  BIGINT NOT NULL,
    currency      CHAR(3) NOT NULL,
    created_at    DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    INDEX idx_account (account_id, created_at)
) ENGINE=InnoDB
  ENCRYPTION='Y';

-- Confirm the tablespace is encrypted
SELECT NAME, ENCRYPTION
FROM information_schema.INNODB_TABLESPACES
WHERE NAME = 'your_db/payments';
-- NAME              | ENCRYPTION
-- your_db/payments  | Y

To make encryption the default for all new tables in a schema without specifying it on every CREATE TABLE, set the default_table_encryption variable:

-- Session-level (affects this session's CREATE TABLE statements)
SET SESSION default_table_encryption = ON;

-- Global default (persisted across restarts with SET PERSIST)
SET PERSIST default_table_encryption = ON;

-- Verify
SHOW VARIABLES LIKE 'default_table_encryption';

Encrypting Existing Tables

Retrofitting encryption onto existing InnoDB tables is an online operation in MySQL 8.0 — InnoDB performs the tablespace re-encryption in the background without blocking reads or writes. The ALTER TABLE command triggers an in-place rebuild where each page is re-written with the tablespace key applied.

-- Enable encryption on an existing table (online, non-blocking)
ALTER TABLE orders ENCRYPTION='Y';

-- Disable encryption on a table (e.g., for testing or migration)
ALTER TABLE orders ENCRYPTION='N';

-- Verify current encryption status for all tables in a schema
SELECT
    t.TABLE_SCHEMA,
    t.TABLE_NAME,
    ts.ENCRYPTION,
    ts.ROW_FORMAT,
    ROUND(ts.FILE_SIZE / 1024 / 1024, 1) AS size_mb
FROM information_schema.TABLES t
JOIN information_schema.INNODB_TABLESPACES ts
    ON ts.NAME = CONCAT(t.TABLE_SCHEMA, '/', t.TABLE_NAME)
WHERE t.TABLE_SCHEMA = 'your_db'
ORDER BY ts.ENCRYPTION DESC, size_mb DESC;

Encrypting the mysql System Schema, Undo Logs, and Redo Logs

Per-table ENCRYPTION='Y' covers your user tablespaces, but sensitive data also flows through the mysql system schema (which stores user credentials, stored procedure definitions, and privilege tables), the InnoDB undo tablespaces, and the redo log. A complete TDE deployment must address all four components.

Encrypting the mysql System Schema

-- The mysql schema uses a general tablespace named 'mysql'
-- Enable encryption on the mysql general tablespace
ALTER TABLESPACE mysql ENCRYPTION='Y';

-- Verify
SELECT TABLESPACE_NAME, ENCRYPTION
FROM information_schema.INNODB_TABLESPACES
WHERE TABLESPACE_NAME = 'mysql';

Encrypting Undo Tablespaces

-- List all undo tablespaces and their current encryption status
SELECT TABLESPACE_NAME, FILE_NAME, ENCRYPTION
FROM information_schema.FILES
WHERE FILE_TYPE = 'UNDO LOG';

-- Encrypt each undo tablespace individually
ALTER UNDO TABLESPACE innodb_undo_001 SET ENCRYPTION='Y';
ALTER UNDO TABLESPACE innodb_undo_002 SET ENCRYPTION='Y';

-- For MySQL 8.0.16+, enable automatic undo space encryption globally
SET PERSIST innodb_undo_log_encrypt = ON;

Encrypting the Redo Log

-- Enable redo log encryption (MySQL 8.0.17+)
-- This is a dynamic variable; no restart required
SET PERSIST innodb_redo_log_encrypt = ON;

-- Verify
SHOW VARIABLES LIKE 'innodb_redo_log_encrypt';
-- Variable_name           | Value
-- innodb_redo_log_encrypt | ON

Binary Log Encryption

Binary logs are a frequently overlooked TDE gap. Even if every tablespace is encrypted, an unencrypted binary log contains the logical row changes for every DML operation in plaintext — including inserts into your payments or users tables. An attacker who obtains your binary log files can reconstruct your data without ever touching the encrypted tablespace.

-- Enable binary log encryption (MySQL 8.0.14+)
-- Requires an active keyring plugin
SET PERSIST binlog_encryption = ON;

-- Verify the current state
SHOW VARIABLES LIKE 'binlog_encryption';
-- Variable_name      | Value
-- binlog_encryption  | ON

-- Verify the binary log file is encrypted
-- (encrypted binlogs have a magic header; mysqlbinlog will refuse to decode without keyring)
SHOW BINARY LOGS;
-- Encrypted binary logs are opaque to mysqlbinlog without keyring access

-- Persist in my.cnf for server restarts:
-- [mysqld]
-- binlog_encryption = ON

Master Key Rotation

Key rotation is a compliance requirement under PCI-DSS (annual rotation) and many internal security policies. MySQL makes rotation an online, single-statement operation. When you rotate the master key, MySQL generates a new MEK, re-encrypts every tablespace key with the new MEK (the tablespace data pages themselves are not re-encrypted — only the tablespace key header is rewritten), and then replaces the old MEK in the keyring. The operation is fast and non-blocking regardless of tablespace count.

-- Rotate the InnoDB master encryption key
-- Requires ENCRYPTION_KEY_ADMIN privilege (MySQL 8.0+)
ALTER INSTANCE ROTATE INNODB MASTER KEY;

-- Verify rotation completed by checking the key ID version
-- The KEY_ID in the tablespace header increments after rotation
SELECT NAME, ENCRYPTION, KEY_ID
FROM information_schema.INNODB_TABLESPACES
WHERE ENCRYPTION = 'Y'
LIMIT 10;

-- Rotate the binary log master key independently
ALTER INSTANCE ROTATE BINLOG MASTER KEY;

Master key rotation does not require any application downtime, does not lock tables, and does not re-encrypt tablespace data pages — making it safe to schedule on live production systems. The InnoDB buffer pool continues serving queries from already-decrypted pages during the rotation.

Verifying Encryption Status

Auditors and compliance frameworks require documented evidence that all in-scope tablespaces are encrypted. The canonical source of truth is information_schema.INNODB_TABLESPACES:

-- Full encryption audit: all tablespaces and their encryption status
SELECT
    SPACE,
    NAME,
    ENCRYPTION,
    ROW_FORMAT,
    FILE_SIZE,
    ALLOCATED_SIZE
FROM information_schema.INNODB_TABLESPACES
ORDER BY ENCRYPTION DESC, NAME;

-- Identify any unencrypted user tablespaces (compliance gap detection)
SELECT NAME, ENCRYPTION
FROM information_schema.INNODB_TABLESPACES
WHERE ENCRYPTION != 'Y'
  AND NAME NOT LIKE 'innodb_%'   -- exclude internal tablespaces
  AND NAME NOT LIKE 'sys/%'      -- exclude sys schema
ORDER BY NAME;

-- Cross-reference with table metadata for a readable report
SELECT
    t.TABLE_SCHEMA  AS schema_name,
    t.TABLE_NAME    AS table_name,
    ts.ENCRYPTION   AS encrypted,
    t.TABLE_ROWS    AS approx_rows,
    ROUND(ts.FILE_SIZE / 1024 / 1024, 1) AS file_size_mb
FROM information_schema.TABLES t
JOIN information_schema.INNODB_TABLESPACES ts
    ON ts.NAME = CONCAT(t.TABLE_SCHEMA, '/', t.TABLE_NAME)
WHERE t.TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
ORDER BY ts.ENCRYPTION ASC, file_size_mb DESC;

Performance Impact

TDE imposes measurable CPU overhead because every InnoDB page read and write involves an AES-256 cipher operation. In practice, the impact is bounded by hardware and workload characteristics.

Typical overhead: 5–10% additional CPU utilization and throughput reduction on servers without AES-NI hardware acceleration. On modern Intel and AMD CPUs with AES-NI (all current EC2 instance types, all current bare metal platforms), the overhead drops to 2–5% for OLTP workloads, because AES encryption is offloaded to dedicated CPU instructions that execute in a single clock cycle per 16-byte block.

Factors that increase overhead:

• High read I/O pressure — workloads with low buffer pool hit rates (large datasets relative to innodb_buffer_pool_size) decrypt more pages per second, amplifying CPU cost.
• Small page sizes — innodb_page_size=4096 means more cipher operations per MB of data than the default 16KB page size.
• Write-heavy workloads with redo log encryption — every redo log write now involves encryption in addition to the tablespace page encryption.
• Undo log encryption on busy OLTP systems — high-concurrency transaction workloads that generate substantial undo volume will see more overhead from undo encryption than read-heavy analytics workloads.

Baseline measurement approach: capture SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests', Innodb_rows_read, and CPU utilization before and after enabling TDE in a staging environment mirroring your production workload profile. Do not rely on synthetic benchmarks — TDE overhead is highly sensitive to the ratio of cold reads (requiring decryption) to buffer pool hits (no decryption).

AWS RDS: Encryption at Rest with KMS

Amazon RDS for MySQL handles TDE transparently at the infrastructure layer using AWS KMS. When you enable encryption at rest on an RDS instance, AWS encrypts the underlying EBS volumes, automated snapshots, read replicas, and manual snapshots using a KMS CMK of your choice.

# Create an encrypted RDS MySQL instance at launch
aws rds create-db-instance \
    --db-instance-identifier prod-mysql \
    --db-instance-class db.r6g.2xlarge \
    --engine mysql \
    --engine-version 8.0.35 \
    --master-username admin \
    --master-user-password "${DB_PASS}" \
    --storage-type gp3 \
    --allocated-storage 500 \
    --storage-encrypted \
    --kms-key-id arn:aws:kms:us-east-1:123456789012:key/mrk-abc123 \
    --no-publicly-accessible

RDS encryption at rest is a launch-time property — you cannot enable it on an existing unencrypted instance in place. To encrypt an existing unencrypted RDS instance, the required procedure is:

1. Take a manual snapshot of the unencrypted instance.
2. Copy the snapshot, enabling encryption during the copy operation with a KMS CMK.
3. Restore a new encrypted RDS instance from the encrypted snapshot.
4. Promote the new instance and update your application's connection string.
5. Decommission the original unencrypted instance after verifying the new instance is healthy.

# Step 1: Create a manual snapshot
aws rds create-db-snapshot \
    --db-instance-identifier prod-mysql-unencrypted \
    --db-snapshot-identifier prod-mysql-snap-for-encryption

# Step 2: Copy the snapshot with encryption enabled
aws rds copy-db-snapshot \
    --source-db-snapshot-identifier prod-mysql-snap-for-encryption \
    --target-db-snapshot-identifier prod-mysql-snap-encrypted \
    --kms-key-id arn:aws:kms:us-east-1:123456789012:key/mrk-abc123 \
    --copy-tags

# Step 3: Restore an encrypted instance from the encrypted snapshot
aws rds restore-db-instance-from-db-snapshot \
    --db-instance-identifier prod-mysql-encrypted \
    --db-snapshot-identifier prod-mysql-snap-encrypted \
    --db-instance-class db.r6g.2xlarge \
    --no-publicly-accessible

MySQL TDE is a necessary baseline control for any database handling regulated or sensitive data, but it is one layer in a defense-in-depth posture that also requires network encryption, robust authentication, privilege minimization, and audit logging. Getting the keyring plugin integration right, verifying that all tablespace types are covered, and establishing a tested key rotation procedure are the steps most teams skip when they think encryption is done after adding ENCRYPTION='Y' to a single table. If you need hands-on help auditing your MySQL TDE configuration, implementing a keyring_aws integration, or building a repeatable key rotation runbook, the JusDB team works with engineering teams on exactly these deployments — visit jusdb.com to connect with a MySQL DBA.