How to Enable Encryption at Rest in MariaDB

How do I enable encryption at rest in MariaDB?

Encryption at rest in MariaDB secures data files by transparently encrypting tablespaces, logs, and backups using server-side keys.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What does “encryption at rest” mean in MariaDB?

Encryption at rest protects the physical files that store your databases—tablespaces, binary logs, temporary files, and backups—so stolen disks or snapshots reveal nothing readable.

How do I turn on global InnoDB tablespace encryption?

Set encryption user variables in my.cnf, restart the server, then issue ALTER INSTANCE statements to encrypt existing tablespaces. This secures every new InnoDB table by default.

my.cnf snippet

[mysqld]
plugin_load_add=file_key_management
file_key_management_filename=/etc/mysql/keys.enc
file_key_management_encryption_algorithm=AES_CBC
innodb_encrypt_tables=ON
innodb_encrypt_log=ON
innodb_temp_tablespace_encrypt=ON

How do I encrypt a single table only?

Use CREATE TABLE ... ENCRYPTION='Y' or ALTER TABLE .... This lets you encrypt sensitive objects—e.g., Customers—without the overhead on read-only reference data.

Can I rotate keys without downtime?

Yes. MariaDB supports online key rotation. ALTER INSTANCE ROTATE INNODB MASTER KEY; re-encrypts all tablespaces in the background while keeping the database available.

What are best practices for production?

Store key files on a different mount, restrict file permissions (chmod 600), back up keys separately, test restores regularly, and monitor the information_schema.INNODB_TABLESPACES_ENCRYPTION view for lagging pages.

How to verify that a table is encrypted?

Query INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION for the table’s space_id or check PAGE_TYPE in innodb_space if installed. The ENCRYPTION_SCHEME column should show 1.

Example: Encrypting ecommerce order data

Encrypt Orders and OrderItems while leaving catalog tables unencrypted to minimize CPU load.

ALTER TABLE Orders ENCRYPTION='Y', ENCRYPTION_KEY_ID=5;
ALTER TABLE OrderItems ENCRYPTION='Y', ENCRYPTION_KEY_ID=5;

How do I back up encrypted tables?

Logical backups (mysqldump, mysqlpump) remain in plaintext unless used with --routines --events --triggers. Physical backups with MariaDB Backup preserve encryption; keep the keyring file with the backup to allow restores.

Common use cases

PCI-DSS compliance, GDPR protection of personal data in Customers, regulatory separation of production vs. staging, and safe use of cloud block storage.

Key performance impact questions

CPU cost is usually < 5 % with AES-NI capable processors. IO patterns are unchanged, but backup and restore times rise because of additional crypto operations.

Common Mistakes

1. Forgetting to load the key management plugin. Without plugin_load_add=file_key_management, encryption statements fail silently. Always confirm with SHOW PLUGINS.

2. Storing keys on the same disk as data files. If the disk is lost, attackers get both data and keys. Keep keyring on a separate, access-restricted volume or use HashiCorp Vault/KMS.

Why How to Enable Encryption at Rest in MariaDB is important

How to Enable Encryption at Rest in MariaDB Example Usage


-- Encrypt both Orders and OrderItems tables with key ID 5
ALTER TABLE Orders     ENCRYPTION='Y', ENCRYPTION_KEY_ID=5;
ALTER TABLE OrderItems ENCRYPTION='Y', ENCRYPTION_KEY_ID=5;

-- Verify encryption status
SELECT TABLE_NAME, ENCRYPTION_SCHEME
FROM   information_schema.INNODB_TABLESPACES_ENCRYPTION
WHERE  TABLE_NAME IN ('Orders','OrderItems');

How to Enable Encryption at Rest in MariaDB Syntax


-- Enable file key management plugin and options
[mysqld]
plugin_load_add                = file_key_management
file_key_management_filename   = /etc/mysql/keys.enc
file_key_management_encryption_algorithm = AES_CBC
innodb_encrypt_tables          = ON        # encrypt new InnoDB tables by default
innodb_encrypt_log             = ON        # encrypt redo/undo logs
innodb_temp_tablespace_encrypt = ON        # encrypt temp space

-- Encrypt a specific table in an ecommerce schema
ALTER TABLE Customers ENCRYPTION='Y', ENCRYPTION_KEY_ID=3;

-- Create an encrypted Orders table from scratch
CREATE TABLE Orders (
    id           BIGINT PRIMARY KEY,
    customer_id  BIGINT NOT NULL,
    order_date   DATE,
    total_amount DECIMAL(12,2)
) ENGINE=InnoDB
  ENCRYPTION='Y'
  ENCRYPTION_KEY_ID=5;

Common Mistakes

Omitting the key management plugin: encryption statements silently fail; load file_key_management or another backend before restarting.
Keeping keyring files on the same disk as data: if the disk is stolen, attackers have both ciphertext and keys; store keys separately or use KMS.

Frequently Asked Questions (FAQs)

Does encryption at rest slow down queries?

With modern CPUs supporting AES-NI, the overhead is typically under 5 %. Disk IO patterns remain identical, so most workloads see negligible impact.

Can I encrypt only sensitive columns?

MariaDB encrypts at the page level; you cannot encrypt individual columns. Instead, place sensitive data in a dedicated table and encrypt that table.

Is key rotation automatic?

No. Schedule ALTER INSTANCE ROTATE INNODB MASTER KEY; periodically via cron or an orchestrator, then monitor INNODB_TABLESPACES_ENCRYPTION until KEY_ROTATION is complete.