MySQL Isolation

InnoDB Tablespace Encryption Configuration, Backup & Restoration Steps

Configuration Changes:

InnoDB uses a keyring to manage encryption keys on a per-table basis. But you must load the keyring plugin before InnoDB starts. Add the following lines to your my.cnf under the [mysqld] section. You may substitute a different path for the keyring file, but be sure that mysqld has permissions to this path:.You do not need to restart MySQL at this time, but you can if you wish. These settings are for when you restart MySQL in the future.

EARLY-PLUGIN-LOAD = KEYRING_FILE.SO

KEYRING_FILE_DATA = /VAR/LIB/MYSQL-KEYRING/KEYRING




Install the Keyring Plugin in MySQL:

If you don’t wish to restart MySQL at this time, you can still continue. Run the following SQL statements within MySQL to load the plugin and configure the keyring file:

MYSQL> INSTALL PLUGIN KEYRING_FILE SONAME 'KEYRING_FILE.SO';

MYSQL> SET GLOBAL KEYRING_FILE_DATA = '/VAR/LIB/MYSQL-KEYRING/KEYRING';

Run below command to make sure keyring plug-in installed successfully:

MYSQL> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS

 WHERE PLUGIN_NAME LIKE 'KEYRING%'

+--------------+---------------+

| PLUGIN_NAME  | PLUGIN_STATUS |

+--------------+---------------+

| KEYRING_FILE | ACTIVE        |

+--------------+---------------+

CREATE encrypted tables or ALTER existing tables:

Once you create/alter first table with encryption enabled ,it will generate a master in keyfile.

MYSQL> ALTER TABLE TEST ENCRYPTION='Y';

QUERY OK, 4667 ROWS AFFECTED (0.18 SEC)

MYSQL> CREATE TABLE T1 (C1 INT PRIMARY KEY) ENCRYPTION='Y';

QUERY OK, 0 ROW AFFECTED (0.03 SEC)

Run backup of complete instance using below command:

MYSQLBACKUP --BACKUP_DIR=/TMP/BKP/2017-12-21_05-39-31 --DATADIR=/VAR/LIB/MYSQL --ENCRYPT-PASSWORD="BKPEPASS" COPY-BACK-AND-APPLY-LOG >/TMP/BKP/LOG.8888.RESTORE.1.`DATE +%Y%M%D_%H%M%S`.TXT 2>/TMP/BKP/LOG.8888.RESTORE.2.`DATE +%Y%M%D_%H%M%S`.TXT

After successful restoration ,you will get following error during startup of instance.You can login to instance but can't access above encrypted tables,as restored instance not able to find master key file ,which helps to decrypt above encrypted files.To access those encrypted tables,copy master key file from backup instance and restored instance keyring file directory.

[ERROR] INNODB: ENCRYPTION INFORMATION IN DATAFILE: ./TETSDB/T1.IBD CAN'T BE DECRYPTED , PLEASE CONFIRM THE KEYFILE IS MATCH AND KEYRING PLUGIN IS LOADED.
[ERROR] INNODB: ENCRYPTION INFORMATION IN DATAFILE: ./TESTDB/TEST.IBD CAN'T BE DECRYPTED , PLEASE CONFIRM THE KEYFILE IS MATCH AND KEYRING PLUGIN IS LOADED.

MYSQL>SELECT * FROM TEST;

--------------
SELECT * FROM TEST
--------------

ERROR 3185 (HY000): CAN'T FIND MASTER KEY FROM KEYRING, PLEASE CHECK KEYRING PLUGIN IS LOADED.

Test Cases :

On restored instance ,moved master key file to different directory and now trying to access those encrypted tables.It will throw an error.But i am able to create new encrypted table on the DB and it is generating new key file.

-BASH-4.1$ LS -L /VAR/MYSQL
-RW-R----- 1 MYSQL MYSQL  155 DEC 21 06:24 KEYRING

-BASH-4.1$ MV KEYRING TMP/
MYSQL>SELECT * FROM TEST;
--------------
SELECT * FROM TEST
--------------
ERROR 3185 (HY000): CAN'T FIND MASTER KEY FROM KEYRING, PLEASE CHECK KEYRING PLUGIN IS LOADED.

MYSQL> CREATE TABLE T5 (C1 INT PRIMARY KEY) ENCRYPTION='Y';
--------------
CREATE TABLE T5 (C1 INT PRIMARY KEY) ENCRYPTION='Y'
--------------

Important Note :

• Encrypted InnoDB tables are never included in a backup created using TTS.A warning is issued in the log file whenever an encrypted InnoDB table that matches the table selection criteria has been skipped over.
• MEB will not copy keyring file into the backup directory,even though you keep keyring file in datadir.
• Tablespace encryption only applies to data in the tablespace. Data is not encrypted in the redo log, undo log, or binary log.
• MEB backup will fail ,if it doesn't find master key in location mentioned in keyring_file_data variable. 
• Backup will get success with --encrypt-password option,even after uninstalling thekeyring plugin.
• Restarting instance doesn't change keyring file.
• MEB Backup restoration doesn't care about master key of backup DB.Using --generate-new-master-key we can have a new master key on restored DB server.
• MySQL Server 5.7 does not support encrypting the InnoDB redo and undo logs(encryption support for these have been added in MySQL 8.0), and all versionsof MySQL Server have no support for encrypting the binary and relay logs. So unless MySQL 8.0 (not yet GA!) is used with encrypted redo and undo logs, and --skip-binlog and --skip-relaylog is used, it is recommended to encrypt the whole backup as well.