Enabling data at rest encryption in Mysql

In an era where data breaches and cyber threats loom large, safeguarding sensitive information has become a top priority for businesses of all sizes. As organizations increasingly rely on databases to store and manage critical data, implementing robust security measures has become imperative. One powerful weapon in the data protection arsenal is the data-at-rest encryption, a technique that shields data when it’s stored on disk.

Let’s see how data-at-rest encryption can be delivered in the context of MySQL.

Encryption prerequisites

The idea behind data-at-rest encryption is to make sure, that data on the disk can’t be accessed without the encryption key. Mysql introduces the concept of the master encryption key, which is used to encrypt data. When this key is unavailable there’s no way to access the data.

First of all, make sure you have the latest possible Mysql version installed:

mysql -e "select version()"
+-------------------------+
| version()               |
+-------------------------+
| 8.0.33-0ubuntu0.23.04.2 |
+-------------------------+

Mysql 5.x branch also supports the data-at-rest encryption.

Another requirement is to have all tables use the InnoDB engine since other engines are not supported for encryption. If you have MyISAM tables (and that’s not a well-considered choice), you can easily convert to InnoDB:

ALTER TABLE table_name ENGINE=InnoDB;

Enabling encryption

First of all, we have to enable the keyring_file plugin.

[mysqld]
early-plugin-load=keyring_file.so

After the server restart we can see this plugin is loaded:

mysql> show plugins;
+---------------------------------+----------+--------------------+-----------------+---------+
| Name                            | Status   | Type               | Library         | License |
+---------------------------------+----------+--------------------+-----------------+---------+
| keyring_file                    | ACTIVE   | KEYRING            | keyring_file.so | GPL     |
...

Encrypting tables

Now if we want to create the encrypted table, we just add ENCRYPTION='Y' to the DDL statement:

CREATE TABLE new_table (
  ...
) ENGINE=InnoDB ENCRYPTION='Y'

Existing tables can be encrypted using the ALTER statement:

ALTER TABLE old_table ENCRYPTION = 'y';

This can take some time for bigger tables since Mysql will have to encrypt and save the entire table data.

That’s it, now your table data is encrypted and safe.

Checking encryption

We can find out if tables are encrypted or not using the INFORMATION_SCHEMA database:

SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
+-------------------------------------------------+------------+
| NAME                                            | ENCRYPTION |
+-------------------------------------------------+------------+
| mysql                                           | N          |
| db/users                                        | Y          |
...

Securing master key

The master key is used for encrypting and decrypting table data on disk. It is generated automatically upon first usage and is stored in a password-protected file on the disk:

show variables like 'keyring_file_data';
+-------------------+--------------------------------+
| Variable_name     | Value                          |
+-------------------+--------------------------------+
| keyring_file_data | /var/lib/mysql-keyring/keyring |
+-------------------+--------------------------------+

A good practice is to backup this file to protected external storage.

Another thing to do periodically - is to rotate the master key with the following query:

ALTER INSTANCE ROTATE INNODB MASTER KEY;

Data-in-transit encryption

Another part of securing our data is to use data-in-transit encryption as well. This means encrypting what’s being sent and received by the Mysql server. SSL is a popular way to achieve that and is well supported by Mysql. Check the Mysql guide to set up Mysql SSL connection.

Further reading

Published 2 years ago in #data about #mysql and #security by Denys Golotiuk

Edit this article on Github
Denys Golotiuk · golotyuk@gmail.com · my github