Data-at-Rest Encryption Concepts, Key Management Boundaries, and Backup Encryption

Learning outcomes

A ServiceHub database can use encrypted transport and still leave sensitive information readable in tablespace files, redo, binary logs, filesystem snapshots, or logical dumps. “Encryption at rest” is therefore not one switch. Each artifact has a different encryption boundary and, crucially, a different key dependency.

Draw the encryption boundary before choosing a feature

These layers can complement each other. Disk encryption protects a stolen volume but does not necessarily protect a database administrator who can read through the running server. MySQL tablespace encryption protects InnoDB pages on disk but does not automatically encrypt a logical dump produced by mysqldump. Backup encryption protects an exported artifact but says nothing about live tablespace files.

Inspect the Community Server capabilities before enabling anything

-- Run as a local administrator on a disposable MySQL instance.CREATE DATABASE IF NOT EXISTS servicehub_security_lab  CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;CREATE TABLE IF NOT EXISTS servicehub_security_lab.security_events (  event_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,  event_time TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),  actor VARCHAR(80) NOT NULL,  event_type VARCHAR(40) NOT NULL,  detail VARCHAR(255) NOT NULL) ENGINE=InnoDB;INSERT INTO servicehub_security_lab.security_events(actor,event_type,detail)VALUES ('chapter12','LAB_START','Chapter 12 disposable security lab');SELECT COUNT(*) AS event_rowsFROM servicehub_security_lab.security_events;

SELECT VERSION() AS server_version;SELECT component_urn FROM mysql.component ORDER BY component_urn;SHOW VARIABLES WHERE Variable_name IN (  'default_table_encryption',  'table_encryption_privilege_check',  'innodb_redo_log_encrypt',  'innodb_undo_log_encrypt',  'binlog_encryption');SELECT PLUGIN_NAME, PLUGIN_STATUS, PLUGIN_TYPEFROM INFORMATION_SCHEMA.PLUGINSWHERE PLUGIN_NAME LIKE 'keyring%';

The Community distribution includes component_keyring_file, but availability in the distribution does not mean it is configured in the running instance. InnoDB tablespace encryption requires a keyring component or plugin loaded early enough for InnoDB to use it. Treat “keyring library exists” and “keyring is configured and contains the required key” as separate facts.

Understand InnoDB's two-tier key model

InnoDB encrypts a tablespace with a tablespace key. That key is itself protected by a master encryption key maintained through the keyring service. Master-key rotation rewraps the tablespace keys; it does not mean every data page must be rewritten with an entirely new data key at the moment of rotation.

SELECT SPACE, NAME, SPACE_TYPE, ENCRYPTIONFROM INFORMATION_SCHEMA.INNODB_TABLESPACESWHERE NAME LIKE 'servicehub_security_lab/%'ORDER BY NAME;

Querying INNODB_TABLESPACES requires diagnostic visibility such as PROCESS. Keep that privilege on an operator account rather than giving it to the application merely to make a lab query succeed.

If a correctly configured keyring is present on your disposable instance, the optional experiment is ALTER TABLE servicehub_security_lab.security_events ENCRYPTION='Y', followed by the metadata query above. If no keyring is loaded, the expected failure is useful evidence: the prerequisite is missing. Do not weaken security or edit internal files by hand to bypass it.

Redo, undo, and binary logs are separate decisions

Tablespace encryption does not imply that every other on-disk structure is encrypted. MySQL exposes separate controls for redo, undo, and binary/relay logs. In MySQL 8.4, innodb_redo_log_encrypt and binlog_encryption default to OFF. Binary-log encryption also requires a keyring service. Existing binary logs are not retroactively encrypted merely because encryption is turned on; new log data is protected from the activation point forward.

SHOW GLOBAL VARIABLES LIKE 'innodb_redo_log_encrypt';SHOW GLOBAL VARIABLES LIKE 'innodb_undo_log_encrypt';SHOW GLOBAL VARIABLES LIKE 'binlog_encryption';SHOW BINARY LOGS;

SHOW BINARY LOGS may fail or return nothing if binary logging is not enabled. That is not an encryption failure; it tells you this local topology does not currently produce binary logs. Chapter 13 will build the backup/PITR chain in depth.

Mandatory free lab: prove a logical backup needs its own protection

This lab does not require a keyring. Its purpose is to expose an important boundary: a logical backup contains SQL/textual data, not encrypted InnoDB pages. Therefore it must be protected as an artifact even when the source tablespace uses MySQL encryption.

INSERT INTO servicehub_security_lab.security_events(actor,event_type,detail)VALUES ('backup-lab','KNOWN_MARKER','restore-me-2026');SELECT COUNT(*) AS before_count,       SUM(event_type='KNOWN_MARKER') AS marker_countFROM servicehub_security_lab.security_events;

# The -p option prompts; it does not embed the password in the process command line.mysqldump -h 127.0.0.1 -u root -p   --single-transaction --routines --triggers   servicehub_security_lab > servicehub_security_lab.sql# Inspect only enough to prove the artifact is readable SQL/text.# Linux/macOS:head -n 20 servicehub_security_lab.sql# PowerShell:Get-Content .\servicehub_security_lab.sql -TotalCount 20

If you can read schema names and INSERT data in the dump, that is expected. Logical dump utilities serialize logical content after MySQL has already decrypted data for the authenticated session. The dump therefore requires its own file permissions and, where confidentiality requires it, a separate encryption mechanism.

# Linux/macOSsha256sum servicehub_security_lab.sql# Windows PowerShellGet-FileHash .\servicehub_security_lab.sql -Algorithm SHA256

For artifact encryption, use an organization-approved tool such as GPG, age, an encrypted archive, or storage-service encryption. The course does not pretend one external tool is universally installed. Whatever you choose, record its version, key owner, recovery procedure, and a hash of the decrypted artifact.

Restore-oriented failure: prove that the chain is complete

A backup artifact is not trustworthy merely because a file exists. First create a damaged copy and prove that its checksum differs from the recorded good hash. Do not attempt to repair a truncated SQL stream by guessing what was lost.

# Linux/macOScp servicehub_security_lab.sql servicehub_security_lab.truncated.sqlpython -c "p='servicehub_security_lab.truncated.sql'; d=open(p,'rb').read(); open(p,'wb').write(d[:max(1,len(d)//3)])"sha256sum servicehub_security_lab.sql servicehub_security_lab.truncated.sql# Windows PowerShell equivalentCopy-Item .\servicehub_security_lab.sql .\servicehub_security_lab.truncated.sql$bytes = [IO.File]::ReadAllBytes('.\servicehub_security_lab.truncated.sql')[IO.File]::WriteAllBytes('.\servicehub_security_lab.truncated.sql', $bytes[0..([Math]::Max(0,[int]($bytes.Length/3)-1))])Get-FileHash .\servicehub_security_lab.sql -Algorithm SHA256Get-FileHash .\servicehub_security_lab.truncated.sql -Algorithm SHA256

The two hashes should differ. That is enough to reject the damaged artifact before restore. A checksum proves byte-for-byte identity with the artifact you recorded; it does not prove the original backup was logically complete, so we still perform a restore test.

INSERT INTO servicehub_security_lab.security_events(actor,event_type,detail)VALUES ('backup-lab','POST_BACKUP_ROW','must-disappear-after-restore');SELECT SUM(event_type='KNOWN_MARKER') AS marker_now,       SUM(event_type='POST_BACKUP_ROW') AS post_backup_nowFROM servicehub_security_lab.security_events;-- Expected: marker_now = 1, post_backup_now = 1

The next destructive step is allowed only because servicehub_security_lab is the chapter's disposable schema and the intact dump plus its checksum have already been recorded. Never generalize this exercise to a valuable database.

DROP DATABASE servicehub_security_lab;CREATE DATABASE servicehub_security_lab  CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

# Password is prompted; the good dump remains unchanged.mysql -h 127.0.0.1 -u root -p servicehub_security_lab < servicehub_security_lab.sql

SELECT SUM(event_type='KNOWN_MARKER') AS marker_restored,       SUM(event_type='POST_BACKUP_ROW') AS post_backup_restoredFROM servicehub_security_lab.security_events;-- Expected: marker_restored = 1, post_backup_restored = 0SHOW CREATE TABLE servicehub_security_lab.security_events;

The known marker returning while the post-backup row disappears proves that the server recovered the state represented by this backup boundary. This is stronger evidence than an exit code alone. For encrypted tablespaces, encrypted binary logs, or encrypted physical backups, the restore drill must additionally prove that the required keyring/key-management material is available.

Production judgment: key custody is part of recoverability

Encryption introduces a dependency that backups alone cannot satisfy: keys. Separate data backups from key backups, restrict access to both, and avoid storing the only copy of a key beside the only encrypted data it unlocks. Key rotation must be rehearsed together with restore, not treated as a purely cryptographic housekeeping task.

Enterprise products can provide additional encrypted-backup capabilities, but they are not required for this course. The architectural rule is edition-neutral: document exactly which artifacts are encrypted, which are plaintext, which key service they depend on, and how a clean restore proves the chain.

Summary and bridge to Lesson 3

At-rest security is a set of boundaries, not a checkbox. You now know how to inventory those boundaries and why backup recoverability includes key custody. Next we protect the credentials applications use to reach the server in the first place.

Authoritative references

• MySQL 8.4 — The MySQL Keyring
• MySQL 8.4 — InnoDB Data-at-Rest Encryption FAQ
• MySQL 8.4 — Encrypting Binary Log Files and Relay Log Files
• MySQL 8.4 — mysqldump