The compliance email said "data at rest must be encrypted within ninety days," and the engineering estimate said "ALTER TABLE ENCRYPTION='Y', how hard can it be." Six weeks later we had forty file-per-table tables encrypted, a passing audit — and a near-miss that still makes me careful: the keyring file, the one artifact every encrypted table depends on, was sitting inside the datadir on the same volume as the data it protected, and it was not in any backup job. A lost or corrupted datadir would have taken the keyring with it, and encrypted InnoDB files without their keys are not recoverable by anyone, including InnoDB. We caught it during a DR drill when a restored backup came up with zero readable tables. That is the real shape of tablespace encryption work: the cryptography is finished and boring, and everything that can hurt you is operational — where keys live, what rotation actually rewrites, how replicas and backups interact with keys, and what the performance bill looks like.
How does InnoDB's two-layer key scheme actually work?
Each encrypted tablespace gets its own random tablespace key, generated when you encrypt the table; that key encrypts the data pages. The tablespace key is itself encrypted with a single server-wide master key and stored inside the tablespace file's first page — so the .ibd file carries its own wrapped key everywhere it goes. The master key lives not in the datadir but in the keyring, an external keystore the server talks to through a plugin or component. This two-layer design is why master key rotation is cheap: rotating re-wraps every tablespace key with the new master key, touching only key blobs and file headers, never re-encrypting a single data page. It also explains the failure mode that matters: lose the master key and every wrapped tablespace key becomes garbage, and every encrypted table becomes unreadable regardless of how intact your data files are. There is no brute-force path back; the encryption is not the weak link, your key custody is.
The surface area is larger than tables alone once you look. Redo and undo logs have their own encryption toggles — innodb_redo_log_encrypt and innodb_undo_log_encrypt — each with its own keys under the same master key, and 8.0 can encrypt binary logs too. General tablespaces and the default shared spaces have their own encryption clause, and schema-level defaults arrived with innodb_default_table_encryption and the DEFAULT ENCRYPTION clause, so "encrypt new tables by default" is a configuration decision, not a per-table chore. Worth knowing before you promise auditors anything: encryption at rest covers files on disk, full stop. Data in the buffer pool is plaintext, the error log can still echo a failing statement's literals, and anyone with SELECT privilege reads decrypted rows. TDE answers the stolen-disk question, not the SQL-injection question.
Where should the keyring live, and why not in the datadir?
The free option most shops start with is keyring_file: a single AES-encrypted file holding the master key, configured at startup and loaded into memory. It works fine, and its two sharp edges are both about placement. First, the file must not live on the same failure domain as the data — a keyring file inside the datadir means one volume failure destroys keys and data together, which is strictly worse than no encryption because you believed you had it. Second, the file is only as backed-up as you make it: it changes on every master key rotation and on first encryption of new tablespaces in some flows, so a backup taken before last quarter's rotation may hold a master key that no longer unwraps current tables. Put it on a separate, small, monitored, separately-backed-up location, and replicate that backup to wherever your data backups land — the two must be restorable as a pair. The 8.0.24+ direction replaces the plugin with component_keyring_file via a manifest, same idea, different plumbing; if you are on the enterprise side, keyring_okv and the encrypted-file variant push custody into a KMIP server or at least a passphrase-protected file, which is where regulated shops usually end up. A honest word on early startup: the keyring must load before InnoDB opens encrypted files, so it is configured with early-plugin-load — a misconfigured keyring path does not degrade gracefully, it refuses to start, which is the correct behavior and an alarming first encounter.
-- is the keyring loaded, and which one?
SELECT PLUGIN_NAME, PLUGIN_STATUS
FROM information_schema.PLUGINS
WHERE PLUGIN_NAME LIKE 'keyring%';
-- which tables are encrypted right now?
SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS
FROM information_schema.TABLES
WHERE CREATE_OPTIONS LIKE '%ENCRYPTION="Y"%';
-- encrypt an existing table (this rebuilds it — plan the window)
ALTER TABLE billing.invoices ENCRYPTION='Y';
-- rotate the master key: re-wraps tablespace keys, data untouched
ALTER INSTANCE ROTATE INNODB MASTER KEY;What does rotation really cost, and when must it be manual?
Rotation is a metadata operation — seconds even with hundreds of encrypted tables — because of the two-layer scheme above. Run it on your compliance schedule without fear; the thing to plan around is not the rotation but what happens to your keyring backups afterward. Every rotation changes the keyring file, so a rotation that lands after tonight's keyring backup leaves tomorrow's data backup paired with a stale key. The durable pattern is to treat rotation and keyring backup as one atomic operational step: rotate, immediately back up the keyring, verify the backup file's checksum against the live file. There is also a failure mode worth rehearsing once in a staging environment: if the keyring is unavailable or out of sync at rotation time, the rotation fails partway and InnoDB starts logging warnings about being unable to encrypt or decrypt tablespace keys — tables keep working off in-memory keys until restart, which masks the problem until the worst possible moment. Rotate on a schedule, verify the keyring file changed, and never let rotation be the thing you try for the first time during an incident.
How do replicas, clones, and backups interact with encryption?
Replication transfers changes, not keys: every instance in the topology has its own keyring and its own master key, and a replica re-encrypts incoming changes under its own tablespace keys. That means provisioning a new replica of an encrypted primary is a key-management exercise as much as a data-copy exercise. Physical copies complicate this: a raw file copy carries tablespace files wrapped with the source's master key, so the target either needs a copy of the source keyring or a key transfer step, and blindly copying the source keyring file everywhere is its own custody smell. The clone plugin handles encrypted data with extra steps around keyrings, and xtrabackup supports encrypted tables but needs access to the keyring during backup and restore — the practical upshot is that your backup runbook must name where the keyring comes from at restore time, and your DR drill must prove it, which is precisely the step we had skipped. If you are already disciplined about restore rehearsals from the xtrabackup PITR workflow, extend the same drill to include keyring custody: restore the data, restore the keyring from its separate backup, and open one encrypted table before you call the drill green. The 5.7-to-8.0 upgrade notes matter here too, since several teams pick up default-table encryption settings during that upgrade without noticing new tables started encrypting themselves.
What is the honest performance bill?
On any CPU with AES-NI — which is every server CPU you should be running in production — the steady-state cost is small and shows up as CPU, not latency cliffs: pages are decrypted once on the way into the buffer pool and encrypted once on the way out, so cache-hit-heavy workloads barely notice and I/O-bound workloads pay a few percent of CPU on top of the I/O they were already paying. In our rollout the p99 query latency moved less than two percent and the CPU on the write-heavy primary moved about four percent under peak load; your numbers will differ, but that is the order of magnitude. Where you will feel it is bulk I/O: a full table scan of cold data, a big ALTER, a physical backup — anything that streams pages pays per-page crypto on the whole stream. The enabling ALTER is itself the biggest one-time cost: ENCRYPTION='Y' rebuilds the table, so a 200GB table pays a 200GB rebuild with its disk headroom and replication-lag consequences, and the online-DDL reasoning in instant versus in-place DDL applies in full. Roll out encryption table by table during low windows, watch the replicas, and budget double disk for the largest table you plan to convert.
Where MonPG stands on MySQL
I build MonPG, so the honest line: MonPG monitors PostgreSQL today, and MySQL support is in active development, not shipped. The signals in this piece — encrypted-table inventory drifting from policy, keyring warnings surfacing in the error log, rebuild progress and replica lag during conversion windows — are exactly what the MySQL work is designed to surface on one timeline, so an encryption rollout reads as a controlled operation instead of a leap of faith. The MySQL monitoring (coming soon) page tracks that work as it lands. Until it ships, the same evidence-first approach runs on the PostgreSQL side today, and the rest of these MySQL field notes live on the blog.