MySQL and MySQL Community information

  • August 1, 2016

Backup and Restore of Encrypted Innodb Tables

MySQL Enterprise Backup(MEB) 4.0.2 is provided to support the backup and restore of Encrypted Innodb Tables. MySQL Transparent Data Encryption(TDE) is introduced in MySQL 5.7.12. This enables data-at-rest encryption by encrypting the physical files of the database. MySQL TDE uses a two-tier encryption key architecture, consisting of a master encryption key and tablespace keys.

The InnoDB storage engine uses the keyring to store its key for tablespace encryption.
These keyring solutions are available as plugins. To use this feature on server, one of the plugins need to be installed and configured on the server.

Currently, MySQL Server Community Edition supports keyring_file plugin which stores keyring data in a file local to the server host.
MySQL Server Enterprise Edition Edition supports keyring_file and keyring_okv, a plugin that uses Oracle Key Vault (OKV) for keyring backend storage.

Thus the feature prevents malicious users to read data from tablespace files, database backups and disks.
The use of MEB ensures that the Backup also has the innodb tables encrypted. This will secure the database "backups" also from hackers.

When an InnoDB table is encrypted, a tablespace key is encrypted and stored in the tablespace header. When an application or authenticated user wants to access encrypted tablespace data, InnoDB uses a master encryption key to decrypt the tablespace key.

On Server :
Master encryption key + Innodb tablespace key = Encrypted Innodb tablespace key

When a backup is taken, MEB retrieves the Innodb tablespace key by using master key and decrypting the Encrypted Innodb tablespace key.

In Backup :
Innodb tablespace key + encrypt password(user key)= MEB re-encrypted Innodb tablespace key stored in a transfer file with .bkt extension per encrypted table.

To install keyring_okv plugin:

mysql> INSTALL PLUGIN keyring_okv SONAME 'keyring_okv.so';
mysql> SET @@global.keyring_okv_conf_dir= '/home/okv_enrollment_dir';

To install keyring_file plugin:

mysql> INSTALL PLUGIN keyring_file SONAME 'keyring_file.so';
mysql> SET @@global.keyring_file_data='/export/Data/keyring';

MEB is part of an Enterprise Edition and hence designed to access both File and Oracle Key Vault to backup and restore MySQL data.

Here is a brief overview on the steps to create tables with TDE and take a backup of the same with MEB:

To create a table with Encryption enabled:

mysql> CREATE DATABASE tde_db;
mysql> USE tde_db;

To take backup of the above server:

mysqlbackup --user=root –password –backup-image=/home/admin/backups/my.mbi \
--backup-dir=/home/admin/backup-tmp –encrypt-password= backup-to-image

Since MEB connects to the server, the plugin information is fetched from server, master key is read and MEB decrypts the tablespaces keys and re-encrypts the encryption keys of the tablespace with the encrypt password specified in "--encrypt-password" option while taking backup of the TDE tables. The resulting key is stored in the transfer file (.bkt) per tablespace.

To restore a backup:

mysqlbackup –defaults-file=/usr/mysql/my.cnf –backup-image=/home/admin/backup/my.mbi\
--backup-dir=/home/admin/restore-tmp –encrypt-password= \

MEB uses the encrypt-password to decrypt the encryption keys of the tablespace(s) to read and apply the logs.
Then a normal restore operation is performed.

MEB supports --keyring, --keyring-file-data/--keyring-conf-dir for providing keyring options as in MySQL Server.

This way, the backup taken in my.mbi also has encrypted Innodb tables and hence the backup is secure.

For more information on the support of this feature:

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.