Posted inMySQL

Configuring Transparent Data Encryption (TDE) in MySQL 8: Protecting Sensitive Data at the Storage Layer (At-Rest)

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

Background: When Database Encryption Is No Longer a Nice-to-Have

Last year I handled a security audit for a SaaS project that stored contract information and financial data. The production database ran MySQL 8.0 with around 50GB of data — not enormous, but enough to cause serious trouble if it leaked. The auditor’s question was: “If someone managed to copy the .ibd files off the server, could they read the data?”

The answer I didn’t want to hear: Yes.

InnoDB stores data in tablespace files (with the .ibd extension). Without encryption, anyone with filesystem read access — including hosting staff or an attacker who gains shell access — can extract raw data without needing a MySQL password. This is a classic vulnerability at the at-rest layer (data while stored), as opposed to in-transit (data traveling over the network, which SSL/TLS already handles).

Transparent Data Encryption (TDE) addresses exactly this problem. “Transparent” means your application doesn’t need to change a single line of code — MySQL handles all encryption and decryption at the storage engine layer.

What TDE Protects (and What It Doesn’t)

  • ✅ Tablespace files (.ibd) copied off the server
  • ✅ Redo log and undo log files read directly from disk
  • ✅ Physical backups (XtraBackup) if the encryption key isn’t compromised alongside them
  • ❌ Queries through normal MySQL connections — data is decrypted when MySQL reads it into RAM
  • ❌ Dump files from mysqldump — this is a logical backup; data is decrypted before export
  • ❌ A root user on the server who can read process memory

Understanding these limits upfront prevents a false sense of complete security.

Setup: Configuring the Keyring Component

TDE in MySQL 8 relies on a keyring component to manage the master encryption key. Starting with MySQL 8.0.24+, components fully replace the older plugin system. Common options include:

  • component_keyring_file — Stores keys in a local file; sufficient for most production environments
  • component_keyring_encrypted_file — Same as above, but the keyring file itself is also encrypted
  • component_keyring_oci — Oracle Cloud Infrastructure Vault
  • component_keyring_aws — AWS KMS (requires MySQL Enterprise)

I’ll walk through the setup using component_keyring_file — a good fit for VPS or bare-metal servers.

Step 1: Create the Manifest File for the MySQL Server

# This file declares which components MySQL will load at startup
sudo nano /var/lib/mysql/mysqld.my

File contents (pure JSON):

{
    "components": "file://component_keyring_file"
}

Step 2: Create the Key Directory and Component Config

# Important: place the keyring OUTSIDE the datadir to avoid being backed up with the data
sudo mkdir -p /etc/mysql/keyring
sudo chown mysql:mysql /etc/mysql/keyring
sudo chmod 750 /etc/mysql/keyring

# Create the config for the keyring component
sudo nano /var/lib/mysql/component_keyring_file.cnf

Config file contents:

{
    "path": "/etc/mysql/keyring/keyring_data",
    "read_only": false
}

# Set strict permissions on the config files
sudo chown mysql:mysql /var/lib/mysql/mysqld.my
sudo chown mysql:mysql /var/lib/mysql/component_keyring_file.cnf
sudo chmod 600 /var/lib/mysql/mysqld.my
sudo chmod 600 /var/lib/mysql/component_keyring_file.cnf

Step 3: Restart MySQL and Verify the Keyring Is Loaded

sudo systemctl restart mysql

# Check component status
mysql -u root -p -e "SELECT * FROM performance_schema.keyring_component_status;"

If you see Status: Active alongside component_keyring_file, MySQL is ready to encrypt.

Detailed Configuration: Encrypting Tables, Tablespaces, and Logs

Encrypting Individual Tables

This is the most flexible approach — encrypt only the tables that actually contain sensitive data, avoiding unnecessary overhead on log or cache tables.

-- Create a new table with encryption from the start
CREATE TABLE users_sensitive (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    id_card VARCHAR(20),
    phone VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENCRYPTION='Y';

-- Enable encryption on an existing table (online operation, minimal locking)
ALTER TABLE payment_info ENCRYPTION='Y';

-- Disable encryption if needed (e.g., migrating to another system)
ALTER TABLE payment_info ENCRYPTION='N';

Enabling Server-Wide Default Encryption

Add the following to /etc/mysql/mysql.conf.d/mysqld.cnf so all newly created tables are automatically encrypted:

[mysqld]
# All newly created InnoDB tables will be automatically encrypted
default_table_encryption = ON

# Encrypt the binary log — important if you use replication
binlog_encryption = ON

# Encrypt the undo tablespace (stores transaction rollback data)
innodb_undo_log_encrypt = ON

# Encrypt the redo log (stores InnoDB write-ahead log)
innodb_redo_log_encrypt = ON

sudo systemctl restart mysql

With default_table_encryption = ON, existing tables retain their current state — they need to be ALTERed manually. I usually write a script to migrate tables in batches, avoiding sudden spikes in disk I/O.

Rotating the Master Key Periodically

Security best practice is to rotate encryption keys periodically. MySQL TDE uses a two-tier key model:

  • Master Key — managed by the keyring, used to wrap the tablespace keys
  • Tablespace Encryption Key (TEK) — one TEK per tablespace, stored encrypted in the .ibd file header

When rotating the master key, MySQL generates a new master key and re-encrypts all TEKs. Data pages are not re-encrypted, so the operation completes quickly even on large databases:

-- Rotate the master key (online operation, no downtime required)
ALTER INSTANCE ROTATE INNODB MASTER KEY;

I add this command to crontab to run monthly:

# Add to /etc/cron.d/mysql-key-rotation
0 3 1 * * root mysql -u root -p'YOUR_PASSWORD' -e "ALTER INSTANCE ROTATE INNODB MASTER KEY;" >> /var/log/mysql-key-rotation.log 2>&1

Verification & Monitoring

Confirming Tables Are Encrypted

-- View all currently encrypted tablespaces
SELECT 
    SPACE,
    NAME,
    ENCRYPTION
FROM information_schema.INNODB_TABLESPACES
WHERE ENCRYPTION = 'Y'
ORDER BY NAME;

-- Check specific tables in a database
SELECT 
    TABLE_SCHEMA,
    TABLE_NAME,
    CREATE_OPTIONS
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database'
    AND CREATE_OPTIONS LIKE '%ENCRYPTION%';

Verifying by Reading the .ibd File Directly

The most hands-on test — use strings to read the contents of a tablespace file:

# Find the path to the .ibd file
sudo find /var/lib/mysql -name "users_sensitive.ibd"

# Read the encrypted file — you'll only see binary garbage
sudo strings /var/lib/mysql/your_db/users_sensitive.ibd | head -20

# Compare against a non-encrypted table
# (replace your_db and table_name as appropriate)
sudo strings /var/lib/mysql/your_db/non_encrypted_table.ibd | grep -i "@gmail"

This is the demo I always run with clients: seeing the .ibd file contain only random binary data instead of plaintext email addresses is more convincing than any documentation.

Monitoring Performance Impact

TDE does carry overhead from AES encryption/decryption, but modern CPUs include AES-NI hardware instructions, so the real-world impact is minimal:

# Check whether the CPU supports AES-NI
grep -m 1 aes /proc/cpuinfo
# Expected output: flags: ... aes ... (look for 'aes' in the flags)

-- Monitor I/O performance after enabling TDE
SELECT 
    EVENT_NAME,
    COUNT_READ,
    ROUND(SUM_TIMER_READ / 1000000000, 2) AS read_time_ms,
    COUNT_WRITE,
    ROUND(SUM_TIMER_WRITE / 1000000000, 2) AS write_time_ms
FROM performance_schema.file_summary_by_event_name
WHERE EVENT_NAME LIKE '%innodb%datafile%'
ORDER BY SUM_TIMER_READ DESC
LIMIT 10;

On my 50GB database, enabling full TDE (including redo log and undo log encryption) increased query time by roughly 2–3% — well within the noise threshold and a completely acceptable trade-off for security compliance.

Backing Up the Keyring File — The Most Overlooked Step

The most critical point that people often miss: the keyring file must be backed up separately, NOT alongside the data files. Losing the keyring file means losing all encrypted data — even a complete set of .ibd backups is useless without it.

# Back up the keyring after each key rotation
sudo cp /etc/mysql/keyring/keyring_data \
    /backup/keyring/keyring_data_$(date +%Y%m%d_%H%M%S)
sudo chmod 600 /backup/keyring/keyring_data_$(date +%Y%m%d_%H%M%S)

# List the backups
sudo ls -la /backup/keyring/

Ideally, store the keyring backup on a completely separate server or storage system from the database server. The principle of “separation of duties” dictates that keys and data should live in different places — this is also a requirement under most security standards such as PCI-DSS and ISO 27001.

Share:
Tags: