Moving a MariaDB Database to Encrypted and Unencrypted States

In this blog, we present a way to move an existing database first to an encrypted state and then how to move your database to an unencrypted state.

In order to use encryption, you need to load a plugin to manage the encryption keys. See currently supported encryption plugins. Each key uses a 32-bit integer as a key identifier (key_id) and actual key. Keys can be versioned so that data is re-encrypted from older key to a newer version of the key. In this blog, we will use file key management plugin as an example (see encryption key management). We also assume that you are using the most recent version of MariaDB Server (this blog assumes that MDEV-15566 is fixed i.e. MariaDB version should be 10.1.33, 10.2.15 or 10.3.6).

Moving a database to an encrypted state or to an unencrypted state is done using a key_rotation. Key rotation moves the database from an existing encrypted state to another. Note that here tablespace could have no encrypted state (i.e. tablespace is unencrypted) or tablespace could have an encryption state that is moved to an unencrypted state. Key rotation can happen periodically (based on configuration variable innodb-encryption-rotate-key-age i.e. how old key can be before it is rotated), requested by a database administrator (e.g. by issuing set global innodb_encrypt_tables=ON;) or by encryption key management system (see e.g. rotate keys).

Database administrators need to make the decision if it is enough to encrypt only individual tables (see encrypting data for InnoDB) or the whole database including system tablespace. Note that table data is also written to redo log and undo log. Thus, if the database contains tables that contain very sensitive data innodb-encrypt-log should also be enabled. In this blog, we show how to encrypt the whole database.

Moving Database to an Encrypted State

Before the database can be moved to an encrypted state, we need to add an encryption plugin configuration to config file (see detailed description on parameters):

# File Key Management
plugin-load-add = file_key_management
file-key-management-filename = /mnt/flash/keys.txt
file-key-management-encryption-algorithm = aes_ctr

# InnoDB encryption setup
innodb-encrypt-tables=ON
innodb-encrypt-log=ON
innodb-encryption-rotate-key-age=1024
innodb-encryption-threads=4
innodb-tablespaces-encryption

After the restart, the progress of the encryption operation can be monitored from INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION table. In the following example, we query the name of the tablespace, the current page under key rotation, and the maximum page in the tablespace for those tables that are not yet encrypted:

MariaDB [(none)]> select name, KEY_ROTATION_PAGE_NUMBER, KEY_ROTATION_MAX_PAGE_NUMBER from information_schema.innodb_tablespaces_encryption where min_key_version = 0 or ROTATING_OR_FLUSHING = 1;
+---------------+--------------------------+------------------------------+
| name          | KEY_ROTATION_PAGE_NUMBER | KEY_ROTATION_MAX_PAGE_NUMBER |
+---------------+--------------------------+------------------------------+
| innodb_system |                    17641 |                      1397504 |
+---------------+--------------------------+------------------------------+
1 row in set (0.000 sec)

Naturally, you may also query the status of all tables:

MariaDB [tpcc1000]> select * from information_schema.innodb_tablespaces_encryption;
+-------+-------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+----------------+----------------------+
| SPACE | NAME              | ENCRYPTION_SCHEME | KEYSERVER_REQUESTS | MIN_KEY_VERSION | CURRENT_KEY_VERSION | KEY_ROTATION_PAGE_NUMBER | KEY_ROTATION_MAX_PAGE_NUMBER | CURRENT_KEY_ID | ROTATING_OR_FLUSHING |
+-------+-------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+----------------+----------------------+
|     0 | innodb_system     |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |              1 |                    0 |
|     3 | tpcc1000/customer |                 1 |                  1 |               0 |                   1 |                     2401 |                      1317888 |              1 |                    1 |
+-------+-------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+----------------+----------------------+
2 rows in set (0.000 sec)

From this, we can see that system tablespace is already encrypted, but the table customer from database tpcc1000 is currently being encrypted. If your system has hardware resources and the encryption process seems slow, you may try the following parameters:

# Set close to number of cores
set global innodb_encryption_threads=16;
# For SSD increase number of I/O operations used for encryption in second
set global innodb_encryption_rotation_iops=40000;

Database encryption is finished when there are no tables in an unencrypted state:

MariaDB [tpcc1000]> select name, KEY_ROTATION_PAGE_NUMBER, KEY_ROTATION_MAX_PAGE_NUMBER from information_schema.innodb_tablespaces_encryption where min_key_version = 0 or ROTATING_OR_FLUSHING = 1;
Empty set (0.001 sec)

And to verify, list all tables that are encrypted:

MariaDB [tpcc1000]> select * from information_schema.innodb_tablespaces_encryption where min_key_version != 0;
+-------+---------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+----------------+----------------------+
| SPACE | NAME                | ENCRYPTION_SCHEME | KEYSERVER_REQUESTS | MIN_KEY_VERSION | CURRENT_KEY_VERSION | KEY_ROTATION_PAGE_NUMBER | KEY_ROTATION_MAX_PAGE_NUMBER | CURRENT_KEY_ID | ROTATING_OR_FLUSHING |
+-------+---------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+----------------+----------------------+
|     0 | innodb_system       |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |              1 |                    0 |
|     3 | tpcc1000/customer   |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |              1 |                    0 |
|     2 | tpcc1000/district   |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |              1 |                    0 |
|     4 | tpcc1000/history    |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |              1 |                    0 |
|     8 | tpcc1000/item       |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |              1 |                    0 |
|     5 | tpcc1000/new_orders |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |              1 |                    0 |
|     7 | tpcc1000/order_line |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |              1 |                    0 |
|     6 | tpcc1000/orders     |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |              1 |                    0 |
|     9 | tpcc1000/stock      |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |              1 |                    0 |
|     1 | tpcc1000/warehouse  |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |              1 |                    0 |
+-------+---------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+----------------+----------------------+
10 rows in set (0.000 sec)

As can be seen, all tablespaces use ENCRYPTION_SCHEME=1 (encrypted) and MIN_KEY_VERSION=1. After this phase, the database administrator should consider decreasing the number of used encryption threads and rotation iops. Furthermore, the need for further key rotation should be also considered as the file key management plugin does not support real key rotation. Key rotation can be disabled using innodb-encryption-rotate-key-age=0. Note that even with that setup, all new tables created are considered for encryption.

Moving Database to an Unencrypted State

Here, we assume that you have a database that is encrypted and there is no longer a need to encrypt data or data protection is done differently. We will use the same database as an example as in moving database to encrypted state. At this point, there is no need to restart the server. Instead, moving the database to an unencrypted state can be done as an online operation. First, the database administrator should check that there are no tables using explicit encryption i.e. there is a table where create table used ENCRYPTED=YES table option. Now, moving the database to an unencrypted state can be simply done by issuing:

SET GLOBAL innodb_encrypt_tables=OFF;

This will start un-encrypting all tablespaces including system tablespace, and progress of this operation can be monitored by:

MariaDB [tpcc1000]> select * from information_schema.innodb_tablespaces_encryption where min_key_version != 0;
+-------+---------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+----------------+----------------------+
| SPACE | NAME                | ENCRYPTION_SCHEME | KEYSERVER_REQUESTS | MIN_KEY_VERSION | CURRENT_KEY_VERSION | KEY_ROTATION_PAGE_NUMBER | KEY_ROTATION_MAX_PAGE_NUMBER | CURRENT_KEY_ID | ROTATING_OR_FLUSHING |
+-------+---------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+----------------+----------------------+
|     7 | tpcc1000/order_line |                 1 |                  1 |               1 |                   1 |                    76564 |                      1947904 |              1 |                    1 |
|     6 | tpcc1000/orders     |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |              1 |                    0 |
|     9 | tpcc1000/stock      |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |              1 |                    0 |
|     1 | tpcc1000/warehouse  |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |              1 |                    0 |
|    10 | tpcc1000/t1         |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |              1 |                    0 |
+-------+---------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+----------------+----------------------+
5 rows in set (0.001 sec)

From this, we can see that table order_line from database tpcc1000 is being rotated. The operation is finished when there are no tables using encryption i.e. have min_key_version != 0.

MariaDB [tpcc1000]> select * from information_schema.innodb_tablespaces_encryption where min_key_version != 0 or rotating_or_flushing = 1;
Empty set (0.000 sec)

If the encryption setup needs to be removed from the configuration, now is the time to shut down the server. If configuration uses redo log encryption i.e. innodb-encrypt-log=ON take backups from your database including InnoDB log files and after that, remove InnoDB log files as they are unusable if they contain encrypted data.

rm -rf ib_logfile*

Remove encryption setup from the configuration and restart the server. Now you have a database instance where no encryption is used.

Conclusion

Moving a database to an encrypted state as seen above requires the server to be restarted and requires a careful encryption plugin configuration. How long this operation takes depends on the number of tables and how big these tables are. We have presented a way to monitor this progress and how to speed it up if the hardware used has enough resources. Moving a database to an unencrypted state requires only setting one global variable. However, if encryption is no longer needed and there is a need to remove all references to it, there is a need for one restart. We have shown how to monitor this transition and how to fully remove encryption setup from both the database and configuration.