MySQL Data Encryption

With the surge in internet data, data security has become paramount for database providers and users. MySQL offers a range of security measures. Leveraging encryption functions and transparent data encryption, MySQL safeguards sensitive user information against potential breaches. This article will explore MySQL's encryption capabilities, aiming to provide insights and guidance for those seeking robust data security solutions.

Key Takeaway: MySQL ensures data security through mechanisms like transparent and function encryption, with transparent encryption particularly bolstering security for stored data.

01 Introduction to Data Encryption

Data Encryption: Refers to encoding sensitive data to ensure its safety and prevent leaks.

Encryption Methods: Primarily includes application system encryption, file system encryption, function encryption, tablespace encryption, and disk encryption, among others.

[Image: MySQL data encryption diagram]

MySQL offers a variety of encryption methods to ensure data security, including:

• Function Encryption: Utilizes a range of encryption functions to encode sensitive data, guarding against plaintext exposure. Below is a depiction of MySQL's enterprise-level function encryption.

• Transparent Data Encryption (TDE): Data is encrypted within the storage engine, invisible to the user. All data at rest can be encrypted. This feature is a pivotal security aspect of MySQL. We will delve deeper into the principle and implementation of this feature in upcoming section.

In the storage node version 1.2 of Klustron's distributed database, MySQL version 8.0.26 is adopted, using the InnoDB storage engine. Thus, Klustron's storage node not only possesses security features consistent with MySQL but also realizes full-process encryption for all data at rest, including data files, log files, backup files, etc. Below is the overall architecture diagram of the Klustron distributed database:

[Image: MySQL data encryption diagram]

02 Deep Dive into Data Encryption

Principles of Transparent Data Encryption (Overall Design):

• Starting from MySQL 5.7.11, individual table data transparent encryption is supported. The 8.0 version enables encryption for all data, including redo logs, binlogs, relay logs, and all data-at-rest.

At a high-level architecture, MySQL employs a two-tier key design:

• Tablespace Key: This is used for encrypting and decrypting individual data pages. Once encrypted, it is stored in the first page of each tablespace data 'ibd' file.
• Master Key: This key encrypts and decrypts various tablespace keys and is stored either in the Oracle key vault or in a key file.

The primary advantage of the two-tier key system is that during key rotation, there's no need to decrypt all data and then re-encrypt it. Instead, only the tablespace key needs to be re-encrypted.

Keyring Plugin:

This is a key interface plugin, designed to retrieve the master key from the Oracle key vault or a key file. It offers an interface to the storage engine to access the master key.

Klustron fully supports transparent encryption and is on the brink of launching an encryption solution compliant with the national fifth standard.

Below is a diagram illustrating the overall design of transparent data encryption:

[Image: MySQL data encryption diagram]

Encryption and Decryption Process:

As illustrated in the subsequent diagram, the primary procedure for transparent data encryption is:

1. Key Retrieval:

   InnoDB uses the keyring plugin interface to obtain the master key. This master key decrypts the Tablespace key stored on the first page of the 'ibd' file. The decrypted tablespace key is then placed within the in-memory tablespace object, fil_space_t.

2. Encryption:

   When writing to each data page, the data section of the page is encrypted using the tablespace key found in the fil_space_t object.

3. Decryption:

   When reading from each data page, the data section of the page is decrypted using the tablespace key found in the fil_space_t object.

[Image: MySQL data encryption diagram]

Additional Details:

1. Master Key Rotation:

   With the dual-key system, during rotation, all that's needed is to re-encrypt every tablespace's tablespace key using the new master key and then store it on the first page of the 'ibd' file.

2. Encryption Table Import/Export:

   When exporting, a temporary transfer_key is randomly generated. The existing tablespace key is encrypted using this transfer key, and both are then written to a .cfp file. During import, the transfer_key is used to decrypt the tablespace_key, and regular import operations proceed.

03 Challenges with Transparent Data Encryption

While transparent data encryption ensures data security, the encryption and decryption processes can consume resources. Some design intricacies may also lead to challenges. There are primarily two main issues:

Issue 1: Performance Concerns

• Early versions of 5.7, when using yassl, saw a significant performance drop, with decrements exceeding 30% at times.
• After adopting openssl in version 8.0, the overall performance impact is minimal. However, under certain scenarios, there can still be a noticeable impact. For instance, in low-concurrency oltp_read_only situations, performance may decline by more than 10%.

As illustrated in the following chart (Data Source: Alibaba Cloud RDS MySQL TDE Test Report):

[Image: MySQL data encryption diagram]

Issue 2: Data Security Concerns

• Though the Tablespace key is encrypted, its fixed location in the 'ibd' file makes it vulnerable to potential leaks.
• Temporary transfer_key generated during import/export is stored in the .cfp file, which could also risk exposure.

04 Q&A

q1: How does key rotation ensure atomicity?

a1: As previously described, key rotation involves re-encrypting each tablespace key and writing it to the first page of the 'ibd' file. This process is logged in the redo log. In addition to writing the corresponding redo log, InnoDB also logs the current table's encryption information, such as the master key version number. Therefore, in the event of a crash, the recovery process can ensure the consistency status of key rotation through the information recorded in the redo log.

q2: What work has Klustron done in the field of encryption?

a2: Besides supporting the encryption features provided by MySQL, Klustron has implemented end-to-end encryption for all on-disk data, including data, logs, backups, etc. Moreover, KunlunBase is currently working on supporting the national fifth encryption standard.

END