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 environmentscomponent_keyring_encrypted_file— Same as above, but the keyring file itself is also encryptedcomponent_keyring_oci— Oracle Cloud Infrastructure Vaultcomponent_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.
