How to Enable Encryption at Rest in MySQL

What does “encryption at rest” mean in MySQL?

Encryption at rest protects physical files—tablespaces, redo/undo logs, and binary logs—by encrypting them on disk using keys managed by MySQL or an external KMS. Users and applications continue to query data normally; decryption happens in memory.

Why should I encrypt my MySQL data files?

Regulations (GDPR, PCI-DSS) and internal security policies require mitigating the risk of stolen disks or snapshots. Encrypted files remain unreadable without the server-side key hierarchy, reducing breach impact.

How do I configure keyring file-based key management?

Add these lines to my.cnf, then restart the server:

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

The keyring plugin stores master encryption keys in keyring, which should be on an encrypted volume and restricted to mysql user.

How can I enable tablespace encryption by default?

Setting innodb_encrypt_tables=ON forces every new InnoDB table to be encrypted automatically. You can still override per table.

How do I encrypt an existing table?

Use ALTER TABLE ... ENCRYPTION='Y' to trigger an online rebuild that writes the table with a tablespace key:

ALTER TABLE Orders ENCRYPTION='Y';

The operation copies rows to an encrypted tablespace, then swaps it in—minimal downtime for small tables.

How do I encrypt only sensitive columns?

InnoDB only supports tablespace encryption. For per-column protection, use AES_ENCRYPT()/AES_DECRYPT() functions when inserting or selecting data, e.g., encrypting Customers.email.

How can I verify encryption status?

Query information_schema.INNODB_TABLESPACES_ENCRYPTION:

SELECT NAME, ENCRYPTION_SCHEME
FROM information_schema.INNODB_TABLESPACES_ENCRYPTION
WHERE NAME LIKE 'sales/%';

ENCRYPTION_SCHEME=1 indicates tablespace encryption is active.

What are best practices for production?

Rotate master keys with ALTER INSTANCE ROTATE INNODB MASTER KEY; on a schedule, monitor keyring backups, and store keyring on an encrypted, access-controlled volume. Test restores to confirm keys decrypt backups.

Common mistakes and quick fixes

Skipping key backups leaves backups undecryptable. Automate secure copies of the keyring file or integrate a KMS.Encrypting without SSL protects disks but not data in transit. Enable TLS on client connections to avoid network snooping.

Frequently asked questions

Does encryption impact performance?

CPU overhead is 3-7% on modern processors with AES-NI. Benchmark critical workloads before rollout.

Can I mix encrypted and unencrypted tables?

Yes. Each tablespace stores its own key, so you choose which tables to encrypt.

Is replication affected?

No. Data is decrypted before being sent over the replication channel. Ensure replicas have the same keyring to read relay logs on disk.