Database Encryption

Project Alpha uses separate controls for separate data-at-rest risks:

• ZFS or another encrypted host filesystem protects whole datasets while locked.
• APP_ENCRYPTION_KEY protects selected application credentials and private fields with AES-256-GCM.
• BACKUP_ENCRYPTION_KEY protects PA-created database and full backup archives.
• MySQL’s component_keyring_file protects InnoDB application tables, the mysql system tablespace, redo logs, undo logs, and binary/relay logs.

Do not reuse one key for these different purposes. Do not replace an existing APP_ENCRYPTION_KEY; PA must retain the original key to decrypt existing application secrets.

Deployment

The published Project Alpha database image contains the reviewed keyring manifest, component configuration, encryption defaults, bootstrap logic, and health check. Compose only mounts a persistent keyring volume at /var/lib/mysql-keyring. The database entrypoint creates a valid empty keyring only when the file is absent, resolves the image’s MySQL account rather than a hard-coded UID, restricts the directory to mode 0700 and the keyring file to mode 0600, and never overwrites a non-empty existing keyring.

The database health check fails until the keyring reports Active and these settings are enabled:

default_table_encryption=ON
table_encryption_privilege_check=ON
innodb_redo_log_encrypt=ON
innodb_undo_log_encrypt=ON
binlog_encryption=ON

After normal migrations, the one-shot migrator sets the application schema default, encrypts the mysql system tablespace, converts every existing PA InnoDB table that is not already encrypted, and verifies that none remain unencrypted. Web and background services do not start if this step fails.

Existing tables are rebuilt during their first conversion. Before enabling the deployment on an established installation:

1. Configure the same stable BACKUP_ENCRYPTION_KEY on web and cron.
2. Create and validate an encrypted .full.zip PA backup.
3. Store recovery copies of both PA keys outside the server.
4. Snapshot the database, config, uploads, and backups datasets.
5. Ensure enough free storage and use a maintenance window.

MySQL 8.4 enables binary logging by default. Enabling binlog_encryption encrypts newly rotated binary and relay logs, but does not retroactively encrypt older files. After the first successful encrypted startup, review SHOW BINARY LOGS. If this installation has no replicas and does not retain those logs for point-in-time recovery, purge the pre-encryption logs during the maintenance window. Never purge logs still required by a replica or recovery policy.

For TrueNAS bind mounts, create a persistent mysql-keyring dataset alongside the database dataset and add its bind mount to the db service alongside the existing database mount. Never place the keyring file inside /var/lib/mysql.

Verification

Run these queries in the database container after deployment:

MYSQL_PWD="$MYSQL_ROOT_PASSWORD" mysql -uroot -t -e \
  "SELECT * FROM performance_schema.keyring_component_status;"

MYSQL_PWD="$MYSQL_ROOT_PASSWORD" mysql -uroot -t -e \
  "SELECT @@global.default_table_encryption,
          @@global.table_encryption_privilege_check,
          @@global.innodb_redo_log_encrypt,
          @@global.innodb_undo_log_encrypt,
          @@global.binlog_encryption;"

MYSQL_PWD="$MYSQL_ROOT_PASSWORD" mysql -uroot -t -e \
  "SELECT ENCRYPTION, COUNT(*)
     FROM information_schema.INNODB_TABLESPACES
    WHERE NAME LIKE 'project_alpha/%'
    GROUP BY ENCRYPTION;
   SELECT NAME, ENCRYPTION
     FROM information_schema.INNODB_TABLESPACES
    WHERE NAME='mysql';"

The component must be Active, all five variables must be 1, application tablespaces must report only Y, and the mysql tablespace must report Y.

Recovery

The keyring volume is required to open raw encrypted MySQL data files and ZFS snapshots of the database dataset. Snapshot and back up the keyring dataset after first conversion and after every ALTER INSTANCE ROTATE INNODB MASTER KEY operation. Keep a recovery copy separately from the database dataset.

PA’s logical .full.zip and .db.zip backups do not depend on the MySQL keyring after creation. They are restored using BACKUP_ENCRYPTION_KEY, which provides the recovery path if the raw MySQL keyring is lost.

Never delete or recreate the keyring volume while encrypted tables exist. If the keyring is unavailable, stop the deployment and restore the matching keyring snapshot or restore a verified logical PA backup into a clean database.

Limits

MySQL-native encryption protects files at rest. Authorized SQL clients, the running MySQL process, and application memory receive decrypted data. The Community file keyring is not password-protected like MySQL Enterprise’s encrypted-file component, so keep its separate dataset ZFS-encrypted, tightly permissioned, and backed up. It is also not a compliance-grade external KMS or HSM. Deployments with regulatory key-custody requirements need a reviewed external key-management design.