MySQL Encryption: From Implementation to Audit Readiness

Encrypting a MySQL database is not just a configuration change. It is a chain of controls covering data in transit, data at rest, backups, and the keys that protect them. This guide walks through each layer for MySQL 8.x, highlights the gaps teams commonly miss, and shows how to build the evidence an auditor expects.

What is MySQL Encryption?

MySQL encryption protects database information by converting it into unreadable form using secret keys. It covers three areas: data in transit (protected by TLS), data at rest (protected by Transparent Data Encryption, or TDE), and the keys, logs and backups that support them. If the data is intercepted or stolen, it stays unreadable without the key.

1. Start with the Threat Model

Before touching any configuration, write down what you are protecting and from whom. Encryption controls only make sense when mapped to a threat.

2. Encryption in Transit

It scrambles data as it travels between the app and the database, so anyone listening on the network sees only gibberish. Turning it on is not enough; you must also require it, or insecure connections can still slip through.

Configure TLS on the server

[mysqld]
                        ssl_ca=/etc/mysql/certs/ca.pem
                        ssl_cert=/etc/mysql/certs/server-cert.pem
                        ssl_key=/etc/mysql/certs/server-key.pem
                        tls_version=TLSv1.2,TLSv1.3
                        require_secure_transport=ON

Turning TLS on only allows encrypted connections. Plaintext clients still connect unless you enforce it. require_secure_transport=ON enforces it globally.

Configure TLS on the replication

Replication traffic carries your data too. On the replica:

CHANGE REPLICATION SOURCE TO
                        SOURCE_HOST='db-primary.example.internal',
                        SOURCE_SSL=1,
                        SOURCE_SSL_CA='/etc/mysql/certs/ca.pem',
                        SOURCE_SSL_CERT='/etc/mysql/certs/client-cert.pem',
                        SOURCE_SSL_KEY='/etc/mysql/certs/client-key.pem';

On older MySQL versions and on MariaDB, use CHANGE MASTER TO ... MASTER_SSL=1 with the matching MASTER_SSL_* options.

Verify it

SHOW GLOBAL VARIABLES LIKE 'tls_version';
                        SHOW GLOBAL VARIABLES LIKE 'require_secure_transport';
                        SHOW SESSION STATUS LIKE 'Ssl_cipher';

To find plaintext connections across the server (MySQL):

SELECT t.PROCESSLIST_USER AS user,
                        t.PROCESSLIST_HOST AS host,
                        s.VARIABLE_VALUE   AS cipher
                        FROM performance_schema.threads t
                        JOIN performance_schema.status_by_thread s USING (THREAD_ID)
                        WHERE s.VARIABLE_NAME = 'Ssl_cipher'
                        AND t.TYPE = 'FOREGROUND';

The empty cipher on legacy_job means a plaintext connection. That is exactly the finding you want to discover before the auditor does.

3. Encryption at Rest (MySQL 8.x)

This scrambles data stored on disk, so a stolen hard drive or snapshot is unreadable. The database locks and unlocks the data automatically, so applications keep working unchanged.

Step 1: Choose a keyring

TDE needs a place to keep the master key. Prefer the component-based keyring (component_keyring_file, component_keyring_encrypted_file, or the Enterprise and Percona options for Vault, KMIP and cloud KMS). The older keyring plugins are deprecated.

A plain file keyring on the same disk as the data is fine for a lab, but it is hard to defend in an audit: whoever steals the disk steals the key as well. For production, use Vault or a cloud KMS.

Step 2: Turn on encryption settings

[mysqld]
                        default_table_encryption=ON
                        table_encryption_privilege_check=ON
                        innodb_redo_log_encrypt=ON
                        innodb_undo_log_encrypt=ON
                        innodb_temp_tablespaces_encrypt=ON
                        binlog_encryption=ON

Note that binlog_encryption=ON also encrypts relay logs on replicas.

Step 3: Encrypt existing objects

default_table_encryption only affects new tables. Existing ones stay plaintext until you convert them:

-- A single table (this rebuilds it)
                        ALTER TABLE sales.orders ENCRYPTION='Y';

MariaDB: a different model

MariaDB uses plugins and encrypts in the background with worker threads, so there is no big-bang rebuild:

[mysqld]
                        plugin_load_add=file_key_management   # or hashicorp_key_management
                        file_key_management_filename=/secure/keys.enc
                        file_key_management_encryption_algorithm=AES_CBC
                        innodb_encrypt_tables=ON               # or FORCE
                        innodb_encrypt_log=ON
                        innodb_encryption_threads=4
                        innodb_encryption_rotate_key_age=1
                        encrypt_binlog=ON
                        aria_encrypt_tables=ON
                        encrypt_tmp_disk_tables=ON
                        encrypt_tmp_files=ON

Track progress with information_schema.INNODB_TABLESPACES_ENCRYPTION. As with MySQL, keep the key file itself encrypted and off the data volume; Hashicorp Vault or a KMS is the better production choice.

4. Key Management

The keyring holds the secret keys that lock the data. Keeping it on the same disk as the data is like taping a safe’s key to the safe, so use a separate vault or key service. Encryption is only as strong as the handling of its keys.

1. Separation of duties: DBAs should not be the sole custodians of master keys.
2. External storage: Keep keys in a KMS or vault, never on the same volume as the data.
3. Rotation: Rotate on a schedule (at least annually, or per your policy) and log it.
4. Backup: Back up the keys separately from the data and test a restore. A lost keyring means permanently unrecoverable data.
5. Log access: Record who can read keys, with access logged at the vault level.

5. Backups and Dumps

An encrypted database with an unencrypted backup is an unencrypted database.

# Logical backup, encrypted with GPG
                        mysqldump --single-transaction --routines sales | gzip | gpg --encrypt --recipient backup-key@example.com > sales_$(date +%F).sql.gz.gpg
                        # Logical backup, encrypted with OpenSSL
                        mysqldump --single-transaction sales | openssl enc -aes-256-cbc -pbkdf2 -salt -pass file:/secure/backup.pass > sales_$(date +%F).sql.enc

For physical backups, use xtrabackup or mariabackup with their encryption options. Add storage-layer encryption too (for example S3 SSE-KMS), keep keys separate from the backup location, and schedule restore tests. A restore test that succeeds with the retrieved key is audit evidence.

6. When TDE Is Not Enough: Column and Application Encryption

Because TDE is transparent, anyone with SELECT sees plaintext. For regulated fields such as card numbers or national IDs, add another layer:

• Application-layer encryption with envelope keys (preferred): the app encrypts before writing, so the database only ever stores ciphertext.
• AES_ENCRYPT() / AES_DECRYPT(): avoid unless you control the risks, because the key appears in the SQL text and can land in the general and slow logs.

7. The Gaps Most Teams Miss

Even teams that "enabled encryption" often leave these exposed:

• Binary logs and relay logs: the most common gap, and they hold full row data.
• Temporary tablespaces and on-disk temp tables.
• The mysql system schema, undo logs and redo logs.
• General, slow and audit logs: they can contain literal values, so protect them with file permissions or ship them encrypted.
• Backups, dumps and replicas: a replica needs its own keyring configuration or it cannot read encrypted tables.