MySQL 5.7 TDE

Directory

• I. INTRODUCTION
• Two. Configuration
• Three. Use
• Four. Restrictions
• Five. Restore Test
• Six. Performance testing
• Seven. Secret key backup
• Eight. References

I. INTRODUCTION

MySQL 5.7 is supported by MySQL Keyring (a two-layer encryption architecture, consisting of a master key and multiple tablespace keys; When the InnoDB table is encrypted, it is actually the tablespace Key is encrypted and stored in the Tablespace file header, and when accessing the encrypted InnoDB table, InnoDB uses master key to decrypt data from the InnoDB tables in the stand-alone table space to enhance the security of the physical file.

" 企业版与非企业版插件区别 "

MySQL Enterprise EditionOffers the keyring_okv plugin,when InnoDB tablespace encryption uses OKV for encryption key management , the feature is referred to as " MySQL Enterprise Transparent Data Encryption (TDE) ".

non-enterprise editions of MySQLUses the for keyring_file plugin encryption Key management.

" 加密方式为AES "
InnoDB tablespace encryption supports the Advanced Encryption Standard (AES) block-based encryption algorithm

" 加密键 "
Tablespace key
Master Key rotation

Only one keyring plugin should is enabled at a time. Enabling multiple keyring plugins is not supported.

Two. Configuration

• After MySQL 5.7.11, all versions include the Keyring_file plugin
• After MySQL 5.7.12, the Enterprise Edition contains the KEYRING_OKV plugin
• After MySQL 5.7.13, all versions contain the key managed SQL interface (UDF, user-defined function)

" 配置文件 " Add 2 lines to the MY.CNF.
Early-plugin-load=keyring_file.so
Keyring_file_data=/usr/local/mysql/keyring/keyring

" 添加目录 "
Linux > Mkdir-p/usr/local/mysql/keyring
Linux > Chown-r mysql:mysql/usr/local/mysql/keyring/
Linux > Chmod 750/usr/local/mysql/keyring/

" 安装插件 "
mysql> INSTALL PLUGIN keyring_file soname ' keyring_file.so ';

" 卸载插件 " ignore
MySQL > UNINSTALL PLUGIN keyring_file;

" 秘钥备份 "
Linux > Mkdir/root/backup
Linux > Cp/usr/local/mysql/keyring/keyring/root/backup

" 插件检查 "
MySQL > Show plugins; See if plug-ins are enabled

" 旋转主加密密钥 "
MySQL > ALTER INSTANCE ROTATE INNODB MASTER KEY;

Three. Use

" 加密表创建 "
CREATE TABLE T1 (C1 INT) encryption= ' Y ';

" 查看所有加密表 "
SELECT * from Information_schema.tables where create_options like '%encryption%= "y"% ';

" 修改表加密 "
ALTER TABLE T1 encryption= ' Y ';
ALTER TABLE T1 encryption= ' N ';

" 查看秘钥 "
Show variables like '%keyring% ';

Four. Restrictions

InnoDB table Space Encryption limits

• 高级加密标准（AES）是唯一支持的加密算法。 InnoDB tablespace encryption is used for table space key encryption and cipher packet chaining (CBC) block encryption mode for data encryption using the electronic cipher (ECB) block encryption mode.

• Supports changing the encryption property of a table algorithm = copy operation. Algorithm = InPlace not supported.

• InnoDB table space Encryption supports only INNODB tables that are stored in a separate tablespace. Tables stored in other InnoDB table space types (including regular tablespace, System tablespace, undo log tablespace, and temporal tablespace) do not support encryption.

• You cannot move or copy an encrypted table from a separate table space to an unsupported InnoDB tablespace type.

• Tablespace encryption applies only to data in a table space. The data is 重做日志 , 撤消日志 or 二进制日志中 is not, encrypted.

• keyring_file插件not supported directly from migration to the keyring_okv插件 present. Changing the key ring plugin requires decrypting the table, uninstalling the current keyring plug-in, installing and configuring additional keyring plug-ins, and re-encrypting the table.

Five. Restore Test

Note: After discovering that the master key is deleted online, the encrypted table can still be accessed, so guess that every time MySQL starts, it calls master key to decrypt the encrypted table instead of decrypting it in real time, actually by observing the start-up process in errorlog.

The mysqldump backup file can be restored to the MySQL instance on which the encryption plug-in is enabled, and the master key is automatically generated

" xtranbackup in the absence of master key, you cannot open an encrypted table, and you can open a non-encrypted table.

" 物理备份 in the absence of master key, you cannot open an encrypted table, and you can open a non-encrypted table.
" 物理备份 " in the case of master key, you can open the encrypted table.

[ERROR] InnoDB: Encryption can‘t find master key, please check the keyring plugin is loaded.[ERROR] InnoDB: Encryption information in datafile: ./test/emp.ibd can‘t be decrypted , please confirm the keyfile is match and keyring plugin is loaded.

" 丢失master key 或 master key错误 "

[ERROR] InnoDB: Encryption information in datafile: ./test/emp.ibd can‘t be decrypted , please confirm the keyfile is match and keyring plugin is loaded.[Warning] InnoDB: Ignoring tablespace `test/emp` because it could not be opened.

Six. Performance testing

实验环境：5.7.19-log MySQL Community Server
实验目的：Verifying the performance impact of table space encryption and normal tablespace on SQL statements
实验步骤：

1. Copy two tables to test MySQL
2. One table is encrypted, the other one is not encrypted
3. Use Mysqlslap to test Select, UPDATE, and INSERT statements separately

实验结果：

SQL Type	Select	Update	Insert
Non-encrypted tables	8.762 seconds	32.447 seconds	24.064 seconds
Encrypt tables	8.899 seconds	32.505 seconds	26.154 seconds

结论：

The official data is the encryption table about 5% The impact of the performance, we test down on the SQL Performance encryption table and non-encrypted tables have little impact, can fully meet the needs of the company.

Seven. Secret key backup

The secret key is required to decrypt the encrypted tablespace when the MySQL instance is started, and if the secret key is lost, the encrypted tablespace cannot be opened and the data is lost, so the secret key is a very important file.
The official project needs to consider the secret key machine backup, the keyring_file_data parameters corresponding to the key directory of all files copied.

Eight. References

https://zhuanlan.zhihu.com/p/29761390
Https://dev.mysql.com/doc/refman/5.7/en/innodb-tablespace-encryption.html
Http://www.dbsec.cn/research/research/20170802.html

MySQL 5.7 TDE