Configuring Database Encryption at Rest

MySQL

Before activating mysql encryption check if the innodb_file_per_table directive is ON

You can check by running the query show global variables like 'innodb_file_per_table';

The result should be something like this;

+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+

If the Value is OFF you must add the following configuration to MySQL configuration file

[mysqld]
innodb_file_per_table=ON

Configuration

First we need to activate the keyring plugin adding the following configuration

[mysqld]
early-plugin-load=keyring_file.so
keyring_file_data=/var/lib/mysql/keyring

Restart the mysql server to apply the changes sudo systemctl restart mysql

Login via to mysql terminal and run the following queries to check if everything is OK

SELECT plugin_name, plugin_status FROM INFORMATION_SCHEMA.PLUGINS WHERE plugin_name LIKE 'keyring%';
+--------------+---------------+
| plugin_name  | plugin_status |
+--------------+---------------+
| keyring_file | ACTIVE        |
+--------------+---------------+
1 row in set (0.00 sec)

show global variables like '%keyring%';
+--------------------+------------------------+
| Variable_name      | Value                  |
+--------------------+------------------------+
| keyring_file_data  | /var/lib/mysql/keyring |
| keyring_operations | ON                     |
+--------------------+------------------------+
2 rows in set (0.00 sec)

Encrypting tables

To encrypt a table run the query ALTER TABLE <db.table> encryption='Y';