Introduction to MySQL Data Encryption

The explosive growth of internet data has made data security increasingly important for major database vendors and users. MySQL also provides a lot of support in terms of data security. Through features such as encryption functions and transparent data encryption, MySQL can encrypt users' sensitive data, thereby preventing security issues such as data leakage. In this sharing session, we will provide a detailed introduction to MySQL's features in data encryption, hoping to give everyone a deeper understanding and offer some reference when choosing data security solutions.

Key Quote of this Article: To ensure data security, MySQL supports features such as transparent encryption and function encryption, among which transparent encryption especially provides full security protection for all data written to disk.

01 Introduction to Data Encryption

Data Encryption: Refers to encoding sensitive data to ensure data security and prevent data leakage.

Encryption Methods: Mainly include application system encryption, file system encryption, function encryption, tablespace encryption, and disk encryption.

MySQL also provides various encryption methods to ensure data security, mainly including:

• **Function Encryption: **Encoding sensitive data by providing several encryption functions to prevent plaintext data leakage; the diagram below is a schematic of MySQL enterprise-level function encryption.

• **Transparent Data Encryption: **Encrypt data in the storage engine without user awareness. All data written to disk can be encrypted. This feature is a very important aspect of MySQL in terms of security. In the following sharing, we will provide a detailed introduction to the principles and implementation methods of this feature.

In version 1.2 of the KlustronDB distributed database's Klustore instance, MySQL 8.0.26 is used, and the InnoDB storage engine is employed. Therefore, the Klustore instances of KlustronDB not only have security features consistent with MySQL but also implement full-process encryption for all data written to disk, including data files, log files, and backup files. Below is the overall architecture diagram of the KlustronDB distributed database:

02 Detailed Explanation of Data Encryption

Transparent Data Encryption Principle (Overall Design):

• Starting from MySQL 5.7.11, single-table data transparent encryption is supported, and version 8.0 supports encryption of all data (redo, binlog, relay log, and all other data written to disk).

In terms of overall architecture, MySQL adopts a two-layer key design:

• Tablespace key: The tablespace key is used to encrypt and decrypt each data page and is stored on the first page of each tablespace's data ibd file after encryption;
• Master key: The master key, used to encrypt and decrypt each tablespace key, is stored in the Oracle Key Vault or key file.

The greatest advantage of double-layer keys is that during key rotation, there is no need to decrypt all the data and re-encrypt it; you only need to re-encrypt the tablespace key.

Keyring plugin plugin:

Key interface plugin, used to obtain the master key from Oracle key vault or key file, and provide an interface for the storage engine to obtain the master key.

KlustronDB fully supports transparent encryption, and transparent encryption compliant with the National V emission standards is coming soon.

The diagram below shows the overall design of Transparent Data Encryption:

Encryption and Decryption Process:

As shown in the figure below, the main process of transparent data encryption is:

1. Read key:

InnoDB calls the keyring plugin interface to obtain the master key and uses it to decrypt the Tablespace key stored on the first page of the ibd file. The decrypted tablespace key is placed in the tablespace memory object fil_space_t;

2. Encryption:

When writing each data page, use the tablespace key in the fil_space_t object to encrypt the data portion of the data page;

3. Unveiling:

When reading each data page, use the tablespace key in the fil_space_t object to decrypt the data portion of the data page;

Other detailed issues:

1. Master key rotate：

Due to the adoption of a double-layer key design, when rotating, it is only necessary to use the new master key to re-encrypt the tablespace key of all tablespaces and write it to the first page of the ibd file.

2. Import/Export of encryption table:

When exporting, randomly generate a temporary transfer_key, use the transfer_key to encrypt the existing tablespace_key, and write both to the .cfp file simultaneously. During import, the transfer_key is read to decrypt the tablespace_key, and then the normal import operation can be performed.

03 Problems with Transparent Data Encryption

Transparent Data Encryption, while ensuring data security, also consumes resources due to the encryption and decryption process, and because of some design details, there are also some issues, mainly the following two problems.

Question 1: Performance Issues:

• 5.7In early versions, using yassl caused a severe performance degradation, up to over 30%.
• 8.0After adopting OpenSSL, the overall impact on performance is not significant, but in certain scenarios, the performance impact can be considerable. For example: OLTP_read_only at low concurrency may exceed 10%.

As shown in the figure below: (Data source: Alibaba Cloud RDS MySQL TDE test report)

Question 2: Data Security Issues

• The tablespace key is placed in a fixed location in the ibd file. Although it is encrypted, there is still a possibility of leakage.
• The temporary transfer_key generated by import/export is stored in the .cfp file, and there is also a possibility of leakage.

04 Q&A

q1: How does key rotation ensure atomicity?

a1: As previously described, key rotation is the process of re-encrypting the keys of each tablespace and writing them to the first page of the ibd file, and this process will record redo logs. InnoDB, in addition to writing the relevant redo logs, will also write the current table's encryption information to the redo logs, such as the version number of the master key. Therefore, in the event of a crash, during the recovery process, the relevant information recorded in the redo logs can ensure the consistent state of key rotation.

Q2: What work has KlustronDB done in the field of encryption?

a2: In addition to supporting the encryption features already provided by MySQL, KlustronDB also implements full-process encryption for data at rest (including data, logs, backups, etc.). In addition, KlustronDB is in the process of implementing support for the Guo Wu encryption standard.

END