Summary of Table Space Encryption

Starting from 5.7.11, InnoDB supports static data encryption for stand-alone table spaces. This encryption is an encryption method at the level of data pages in the engine. It is encrypted when the data pages are written to the file system and decrypted when the files are read into memory. At present, AES encryption algorithm provided by YaSSL/OpenSSL is widely used. The size of data pages remains unchanged before and after encryption, so it is also called transparent encryption.

It uses a two-tier encryption key architecture, including master encryption key and tablespace key:

• master encryption key is used to encrypt and decrypt tablespace key. When encrypting a table space, a tablespace key is encrypted and stored in the head of the table space.
• tablespace key is used to encrypt tablespace files. When accessing the encrypted table space,
  • InnoDB decrypts the encrypted tablespace key stored in the tablespace with master encryption key, gets the plaintext tablespace key, and decrypts the data with tablespace key.
• The plaintext information decrypted by tablespace key is basically unchanged (unless alter table test_1 encryption = NO, alter table test_1 encryption = YES). Master key can be changed at any time (such as using ALTER INSTANCE ROTATE INNODB MASTER KEY;), which is called master key rotation. Because the plaintext of the tablespace key will not change, you only need to re-encrypt the tablespace key and write it to the first page after updating the master encryption key.

Application scenarios

When table space encryption is not configured, data will most likely leak when similar drag-and-drop operations occur. When configuring table space encryption, the encrypted table space data cannot be read without the Keyring used in encryption (which is set by the keyring_file_data parameter). So when a similar drag-and-drop operation occurs, there is no relevant Keyring file, and the data will not be leaked. This requires that stored Keyrings must be kept strictly. The following measures can be taken to save keyrings:

• Avoid putting keyring files together with tablespace files
• The permissions of the directory in which keyring files reside need to be strictly controlled
• When using keyring_file plug-in to encrypt tablespace, Keyring files are placed locally, which is relatively unsafe. But you can put it in a non-local place and copy it to the relevant directory when you need it (such as restarting the database, updating master encryption key, and deleting the file when you don't need it).

Encryption plug-in

InnoDB table space encryption relies on plug-ins for encryption. Enterprise Edition provides the following four plug-ins: keyring_file, keyring_encrypted_file, keyring_okv, keyring_aws. The community version can only be encrypted with keyring_file at present. This article only introduces the keyring_file plug-in:

• Enterprise version is more secure. Because the key is encrypted and stored, and the community version of the key is stored locally, which is relatively unsafe.
• The Enterprise Edition supports multiple backend storage keys, making no difference to data encryption itself.

Encryption restrictions

1.AES is the only supported encryption algorithm. InnoDB static table space encryption uses Electronic Codebook (ECB) encryption mode to encrypt tablespace key and Cipher Block Chaining (CBC) encryption mode to encrypt data files. 2.ENCRYPTION uses the COPY command instead of the INPLACE command for table space encryption 3. Encryption of stand-alone table spaces is supported only. Encryption of other tablespace types (such as generic and system tablespaces) is not supported 4. Tables cannot be moved or copied from encrypted stand-alone tablespaces to tablespaces that do not support encryption 5. Table space encryption only encrypts data in table space, not data in redo log, undo log, binary log. 6. The storage engine of encrypted tables is not allowed to be modified (the innodb storage engine is no problem)

Matters needing attention

1. == Before and after the first encryption table space is created and master encryption key is updated, the key ring file must be backed up immediately. Because the data in the encrypted table space will not be recovered after the primary encryption key is lost. Therefore, when encrypting tables, measures must be taken to prevent the loss of the master encryption key, such as backing up the file regularly F44336 F44336== 2. From a security point of view, it is not recommended to place key ring data files and tablespace data files in the same directory. 3. If the database exits or stops during normal operation, the same encryption configuration must be used to restart the database, otherwise the previously encrypted tablespace will be inaccessible. 4. When table space is encrypted for the first time (whether new or old table encryption), the first master encryption key is generated. For running databases, Keyrings can still be removed to create, read and write encrypted table spaces (but these operations fail when the database restarts or updates master encryption keys) 5. Make sure that the keyring file exists before updating master encryption key. If it does not exist, the update will fail, which will result in failure of reading, writing and creating encryption tables. 6. Only when both master and slave are equipped with table space encryption, can table space encryption be performed successfully in both master and slave.

Encrypted table space

The following test cases were conducted in the following environments:

• linux version: 7.5
• mysql version: MySQL 5.7.21
• Encryption plug-in: keyring_file

Install encryption plug-ins

1. The encryption plug-in must be installed and configured first. Use the early-plugin-load option to specify the encryption plug-in to use when starting the database, and use keyring_file_data to define the path for the encryption plug-in to store the key ring file - You need to create relevant directories in advance and adjust permissions, otherwise you may report errors. 2. Only one encryption plug-in can be used, not multiple encryption plug-ins at the same time. 3. Once an encryption table is created in the MySQL instance, the early-plugin-load must be specified as the encryption plug-in used to create the encryption table when the instance is subsequently restarted. Failure to do so will result in errors during startup of the server and InnoDB recovery 4. Independent table spaces must be configured: innodb_file_per_table=1

-- vim my.cnf，stay [mysqld] Add the following parameters below
[mysqld]
early-plugin-load="keyring_file.so"
keyring_file_data=/opt/mysql/keyring/3306/keyring
innodb_file_per_table=1

-- Permission to create relevant directories and modify directories where key ring files reside
mkdir -p /opt/mysql/keyring/3306/
chown -R actiontech-universe:actiontech /opt/mysql/keyring/
chown -R actiontech-mysql:actiontech-mysql /opt/mysql/keyring/3306/

-- Check whether the plug-in is loaded or not
SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'keyring_file';

Configuration table space encryption

The following is tested with the mydata.test_1 table

/*
1. Encryption for new tables
2. Insert data into a new table
3. Cancel Table Encryption
4. Open table encryption
5. View the Encryption Table
*/

mysql> CREATE TABLE mydata.test_1 (id INT primary key,age int) ENCRYPTION='Y';
Query OK, 0 rows affected (0.02 sec)

mysql> insert into test_1 select 9,9;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE mydata.test_1 ENCRYPTION='N'; 
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE mydata.test_1 ENCRYPTION='Y';
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table mydata.test_1;select * from mydata.test_1;
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                               |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_1 | CREATE TABLE `test_1` (
  `id` int(11) NOT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ENCRYPTION='Y' |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.11 sec)

+----+------+
| id | age  |
+----+------+
|  9 |    9 |
+----+------+
1 row in set (0.00 sec)

/*
Delete the current keyring, restore to the original path using the backup Keyring and restart, then check mydata.test_1 to see the success. Because mydata.test_1 uses the same Keyring for encryption and decryption
*/

[root@localhost ~]# rm -rf /opt/mysql/keyring/3306/keyring 
[root@localhost ~]# mv /root/keyring /opt/mysql/keyring/3306/
[root@localhost ~]# systemctl restart mysqld_3306
[root@localhost ~]# mysql -h10.186.63.90 -uroot -p -P3306 -e"show create table mydata.test_1;select * from mydata.test_1;"
Enter password: 
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                               |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_1 | CREATE TABLE `test_1` (
  `id` int(11) NOT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ENCRYPTION='Y' |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
+----+------+
| id | age  |
+----+------+
|  9 |    9 |
+----+------+

View Encrypted Tables in Table Space

When the table space is encrypted with the ENCRYPTION option, the encrypted information of the table is stored in the CREATE_OPTIONS field of INFORMATION_SCHEMA.TABLES. So you can query the table to determine whether the table is encrypted.

-- View the encrypted table:
SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE CREATE_OPTIONS LIKE '%ENCRYPTION%';

-- View unencrypted tables:
select concat(TABLE_SCHEMA,".",TABLE_NAME) from INFORMATION_SCHEMA.TABLES where (TABLE_SCHEMA,TABLE_NAME) not in (SELECT TABLE_SCHEMA,TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE CREATE_OPTIONS LIKE '%ENCRYPTION%' and table_schema not in ('information_schema','performance_schema','sys','mysql','universe')) and TABLE_SCHEMA in ('mydata');

Update master encryption key

Master encryption keys should be replaced periodically, or when it is suspected that master encryption key s have been leaked, they need to be replaced.

Updating master encryption keys only changes master encryption keys and re-encrypts tablespace keys, and does not decrypt or re-encrypt tablespace keys.

Because the plaintext of tablespace_key will not change, after updating master_key, you just need to re-encrypt tablespace_key and write it to the first page.

The change of master encryption key is an atomic, instance-level operation. Every time the master encryption key is changed, all tablespace keys in MySQL instances are re-encrypted and saved to their respective tablespace headers. Because it is an atomic operation, once the update is started, the re-encryption of all tablespace keys must be successful.

-- manual update master encryption key，Success does not affect the use of encryption tables

[root@localhost ~]# ll /opt/mysql/keyring/3306/keyring 
-rw-r----- 1 mysql mysql 155 Apr 19 02:05 /opt/mysql/keyring/3306/keyring
[root@localhost ~]# mysql -h10.186.63.90 -uroot -p -P3306 -e"ALTER INSTANCE ROTATE INNODB MASTER KEY;"
Enter password: 
[root@localhost ~]# ll /opt/mysql/keyring/3306/keyring 
-rw-r----- 1 mysql mysql 283 Apr 19 02:24 /opt/mysql/keyring/3306/keyring
[root@localhost ~]# mysql -h10.186.63.90 -uroot -p -P3306 -e"select * from mydata.test_1;"
Enter password: 
+----+------+
| id | age  |
+----+------+
|  9 |    9 |
+----+------+

Import and export

In order to support the Export/Import encryption table, transfer_key is introduced. A transfer_key is generated randomly when export. The existing tablespace_key is encrypted with transfer_key, and both are written into the file of table_name.cfp at the same time. Note that the transfer_key saves plaintext here. Import reads transfer_key for decryption and then performs normal import operations. Once import is completed, the table_name.cfg file will be deleted immediately:

• When an encrypted table space is exported, innodb generates. cfg metadata files as well as. CFP files for encrypting the transfer key of tablespace key. Encrypted tablespace keys and transfer keys are stored in tablespace_name.cfp file
• When importing an encrypted table, both tablespace_name.cfp and encrypted table space need to be imported. InnoDB decrypts the tablespace key in the tablespace_name.cfp file by transferkey.

The import and export process is as follows: 1. Target libraries: CREATE TABLE mydata.test_1 (id INT primary key,age int) ENCRYPTION='Y'; establish tables with the same name and structure as source libraries. 2. Target library: ALTER TABLE test_1 DISCARD TABLESPACE; the. ibd file will be deleted at this time 3. Source libraries: use test; FLUSH TABLES test_1 FOR EXPORT; at this time, the. cfg,. cfp files will be generated. 4. Target library: scp root@10.186.63.90:/opt/mysql/data/3306/mydata/test_1.{ibd,cfg.cfp} . 5. Target library: chown actiontech-mysql:actiontech-mysql test_1*. After copying files, user groups and permissions need to be modified. 6. Source libraries: unlock tables; at this time. cfg,. cfp files will be deleted 7. Target library: ALTER TABLE test_1 IMPORT TABLESPACE; load table test_1

case

Source and sink: 10.186.63.90:3306 Target library: 10.186.63.91:3307

 # Establishing Tables with Same Name and Structure as Source Library in Target Library
[root@localhost ~]# fg
mysql -h10.186.63.91 -uroot -p -P3307	(wd: ~)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database mydata;
Query OK, 1 row affected (0.01 sec)

mysql> CREATE TABLE mydata.test_1 (id INT primary key,age int) ENCRYPTION='Y';
Query OK, 0 rows affected (0.02 sec)

# Target libraries perform DISCARD TABLESPACE
mysql> use mydata;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> ALTER TABLE test_1 DISCARD TABLESPACE;
Query OK, 0 rows affected (0.02 sec)

# Source library executes flush table... fro export
[root@localhost ~]# fg
mysql -h10.186.63.90 -uroot -P3306 -p
mysql> use mydata;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+------------------+
| Tables_in_mydata |
+------------------+
| test_1           |
+------------------+
1 row in set (0.00 sec)

mysql> FLUSH TABLES test_1 FOR EXPORT;
Query OK, 0 rows affected (0.01 sec)

mysql> 
[1]+  Stopped                 mysql -h10.186.63.90 -uroot -P3306 -p


# Copy files from source library to target library
[root@localhost mydata]# scp root@10.186.63.90:/opt/mysql/data/3306/mydata/test_1.{ibd,cfg,cfp} .
root@10.186.63.90's password: 
test_1.ibd                                                                                         100%   96KB  41.0MB/s   00:00    
root@10.186.63.90's password: 
test_1.cfg                                                                                         100%  400   343.7KB/s   00:00    
root@10.186.63.90's password: 
test_1.cfp                                                                                         100%  100   132.7KB/s   00:00    
[root@localhost mydata]# ll
total 120
-rw-r----- 1 actiontech-mysql actiontech-mysql    67 Sep 28 05:49 db.opt
-rw-r----- 1 root             root               400 Sep 28 05:57 test_1.cfg
-rw-r----- 1 root             root               100 Sep 28 05:57 test_1.cfp
-rw-r----- 1 actiontech-mysql actiontech-mysql  8584 Sep 28 05:50 test_1.frm
-rw-r----- 1 root             root             98304 Sep 28 05:57 test_1.ibd

# Permissions to modify files in the target library
[root@localhost mydata]# chown actiontech-mysql:actiontech-mysql *

# Execute unlock tables on the source library
[root@localhost mydata]# fg
mysql -h10.186.63.90 -uroot -P3306 -p	(wd: ~)
mysql> use mydata;
Database changed
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

# ALTER TABLE... IMPORT TABLESPACE is executed on the target library;
[root@localhost mydata]# fg
mysql -h10.186.63.91 -uroot -p -P3307	(wd: ~)
mysql> select * from mydata.test_1;
ERROR 1814 (HY000): Tablespace has been discarded for table 'test_1'
mysql> ALTER TABLE test_1 IMPORT TABLESPACE;
Query OK, 0 rows affected (0.05 sec)

mysql> select * from mydata.test_1;
Empty set (0.00 sec)

Backup recovery

mysqlbackup innobackupex can encrypt the encrypted table space, but you need to pay attention to the version:

• mysqlbackup 4.1.0 and updated versions support MySQL 5.7.20 and earlier versions of encrypted table space backup; mysqlbackup 4.1.1 and updated versions support MySQL 5.7 encrypted table space backup
• The minimum version of innobackupex supporting backup and recovery of encrypted tablespace was not found.

Here take innobackupex 2.4.5 as an example to backup and restore the encrypted table space

innobackupex

Notes for backing up encrypted table spaces in innobackupex:

• innobackupex only supports backup of encrypted table spaces using keyring_file, keyring_vault plug-ins
• innobackupex does not copy Keyring files to backup directories, so you need to manually copy Keyring files to the keyring-file-data path specified in the configuration file
  • If the key ring file is different before and after backup, the old key ring file should be used when restoring

Back up the data in 10.186.63.90 to 10.186.63.91:3307

# Full backup: The full backup process for encrypted table spaces is basically the same as regular backup recovery, except that an additional parameter is required: -- keyring-file-data

mkdir /data2/all_backup
/data/urman-agent/bin/innobackupex --defaults-file=/opt/mysql/etc/3306/my.cnf --user=root --password=test -P3306 --socket=/opt/mysql/data/3306/mysqld.sock --parallel=8 --keyring-file-data=/opt/mysql/keyring/3306/keyring --no-timestamp /data2/all_backup

# apply-log 
/data/urman-agent/bin/innobackupex --apply-log --keyring-file-data=/opt/mysql/keyring/3306/keyring /data2/all_backup/

# Copy the full file, keyring file to the target library
rm -rf /opt/mysql/data/3307/* && rm -rf /opt/mysql/keyring/3307/keyring  && rm -rf /opt/mysql/log/redolog/3307/ib_logfile*
scp -r /data2/all_backup/ root@10.186.63.91:/data2/
scp /opt/mysql/keyring/3306/keyring root@10.186.63.91:/opt/mysql/keyring/3307

# copy back
/data/urman-agent//bin/innobackupex --defaults-file=/opt/mysql/etc/3307/my.cnf --copy-back --keyring-file-data=/opt/mysql/keyring/3307/keyring /data2/all_backup/

# Modify permissions
chown actiontech-mysql:actiontech-mysql /opt/mysql/keyring/3307/keyring 
chown -R actiontech-mysql:actiontech-mysql /opt/mysql/data/3307/*
chown -R actiontech-mysql:actiontech-mysql /opt/mysql/log/redolog/*

# start-up
systemctl start mysqld_3307