Guide to MySQL InnoDB Tablespace Encryption: Overview, Configuration, Key Management, Import/Export, Backup and Restore

InnoDB from MySQL 5.7.11 supports static tablespace encryption using AES via YaSSL/OpenSSL, with a master encryption key and per‑tablespace keys.

Encryption requires the keyring_file plugin (or other keyring plugins in Enterprise edition). The keyring file must be stored securely and not placed with tablespace files.

Limitations: only independent tablespaces can be encrypted, only AES algorithm, ENCRYPTION uses COPY, not INPLACE, and logs are not encrypted.

Installation steps: add early-plugin-load="keyring_file.so" and keyring_file_data to my.cnf , enable innodb_file_per_table=1 , create the keyring directory with proper permissions.

-- vim my.cnf, add:
[mysqld]
early-plugin-load="keyring_file.so"
keyring_file_data=/opt/mysql/keyring/3306/keyring
innodb_file_per_table=1

Example commands to create an encrypted table, insert data, alter encryption, and view the CREATE statement:

CREATE TABLE mydata.test_1 (id INT primary key, age int) ENCRYPTION='Y';
INSERT INTO mydata.test_1 VALUES (9,9);
ALTER TABLE mydata.test_1 ENCRYPTION='N';
ALTER TABLE mydata.test_1 ENCRYPTION='Y';
SHOW CREATE TABLE mydata.test_1;

To list encrypted tables:

SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE CREATE_OPTIONS LIKE '%ENCRYPTION%';

Master key rotation is performed with:

ALTER INSTANCE ROTATE INNODB MASTER KEY;

Export/Import of encrypted tables uses a transfer key stored in a .cfp file. The process involves DISCARD TABLESPACE on the target, FLUSH TABLES ... FOR EXPORT on the source, copying .ibd , .cfg , .cfp files, and IMPORT TABLESPACE on the target.

Backup of encrypted tablespaces can be done with mysqlbackup or innobackupex , ensuring the keyring file is included or copied manually.

# Full backup with innobackupex:
mkdir /data2/all_backup
innobackupex --defaults-file=/opt/mysql/etc/3306/my.cnf --user=root --password=test --parallel=8 --keyring-file-data=/opt/mysql/keyring/3306/keyring --no-timestamp /data2/all_backup
# Apply log
innobackupex --apply-log --keyring-file-data=/opt/mysql/keyring/3306/keyring /data2/all_backup/
# Restore
innobackupex --copy-back --keyring-file-data=/opt/mysql/keyring/3307/keyring /data2/all_backup/

References to official MySQL documentation and Percona XtraBackup are provided for further reading.