Protecting the data stored in your database may have been at the top of your priorities recently, especially with the changes that were introduced earlier this year with GDPR.
There are a number of ways to protect this data, which until not so long ago would have meant either using an encrypted filesystem (e.g. LUKS), or encrypting the data before it is stored in the database (e.g. AES_ENCRYPT or other abstraction within the application). A few years ago, the options started to change, as Alexander Rubin discussed in MySQL Data at Rest Encryption, and now MariaDB®, MySQL®, and Percona Server for MySQL all support encryption at-rest. However, the options that you have—and, indeed, the variable names—vary depending upon which database version you are using.
In this blog post we will take a look at what constitutes the maximum level of at-rest encryption that can be achieved with each of the latest major GA releases from each provider. To allow a fair comparison across the three, we will focus on the file-based key management; keyring_file plugin for MySQL and Percona Server for MySQL along with file_key_management plugin for MariaDB.
MariaDB 10.3
The MariaDB team take the credit for leading the way with at-rest encryption, as most of their features have been present since the 10.1 release (most notably the beta release of 10.1.3 in March 2015). Google donated the tablespace encryption, and eperi donated per-table encryption and key identifier support.
The current feature set for MariaDB 10.3 comprises of the following variables:
- aria_encrypt_tables
- aws_key_management_key_spec
- aws_key_management_log_level
- aws_key_management_master_key_id
- aws_key_management_region
- aws_key_management_rotate_key
- encrypt_binlog
- encrypt_tmp_disk_tables
- encrypt_tmp_files
- file_key_management_encryption_algorithm (see file_key_management)
- file_key_management_filekey (see file_key_management)
- file_key_management_filename (see file_key_management)
- innodb_encrypt_log
- innodb_encrypt_tables
- innodb_encryption_rotate_key_age
- innodb_encryption_rotation_iops
- innodb_encryption_threads
Maximising at-rest encryption with MariaDB 10.3
Using the following configuration would give you maximum at-rest encryption with MariaDB 10.3:
plugin_load_add = file_key_management file_key_management_filename = /etc/mysql/keys.enc file_key_management_filekey = FILE:/etc/mysql/.key file_key_management_encryption_algorithm = aes_cbc innodb_encrypt_log = ON innodb_encrypt_tables = FORCE Innodb_encrypt_threads = 4 encrypt_binlog = ON encrypt_tmp_disk_tables = ON encrypt_tmp_files = ON aria_encrypt_tables = ON
This configuration would provide the following at-rest protection:
- automatic and enforced InnoDB tablespace encryption
- automatic encryption of existing tables that have not been marked with
ENCRYPTED=NO
- 4 parallel encryption threads
- encryption of temporary files and tables
- encryption of Aria tables
- binary log encryption
- an encrypted file that contains the main encryption key
You can read more about preparing the keys, as well as the other key management plugins in the Encryption Key Management docs.
There is an existing bug related to encrypt_tmp_files (MDEV-14884), which causes the use of
mysqld --help --verbose
to fail, which if you are using the official MariaDB Docker container for 10.3 will cause you to be unable to keep mysqld up and running. Messages similar to these would be visible in the Docker logs:
ERROR: mysqld failed while attempting to check config command was: "mysqld --verbose --help --log-bin-index=/tmp/tmp.HDiERM4SPx" 2018-08-15 13:38:15 0 [Note] Plugin 'FEEDBACK' is disabled. 2018-08-15 13:38:15 0 [ERROR] Failed to enable encryption of temporary files 2018-08-15 13:38:15 0 [ERROR] Aborting
N.B. you should be aware of the limitations for the implementation, most notably log tables and files are not encrypted and may contain data along with any query text.
One of the key features supported by MariaDB that is not yet supported by the other providers is the automatic, parallel encryption of tables that will occur when simply enabling
innodb_encrypt_tables
. This avoids the need to mark the existing tables for encryption using
ENCRYPTED=YES
, although at the same time it also does not automatically add the comment and so you would not see this information. Instead, to check for encrypted InnoDB tables in MariaDB you should check
information_schema.INNODB_TABLESPACES_ENCRYPTION
, an example query being:
mysql> SELECT SUBSTRING_INDEX(name, '/', 1) AS db_name, -> SUBSTRING_INDEX(name, '/', -1) AS db_table, -> COALESCE(ENCRYPTION_SCHEME, 0) AS encrypted -> FROM information_schema.INNODB_SYS_TABLESPACES -> LEFT JOIN INNODB_TABLESPACES_ENCRYPTION USING(name); +---------+----------------------+-----------+ | db_name | db_table | encrypted | +---------+----------------------+-----------+ | mysql | innodb_table_stats | 1 | | mysql | innodb_index_stats | 0 | | mysql | transaction_registry | 0 | | mysql | gtid_slave_pos | 0 | +---------+----------------------+-----------+
As can be inferred from this query, the system tables in MariaDB 10.3 are still predominantly MyISAM and as such cannot be encrypted.
MySQL
Whilst the enterprise version of MySQL has support for a number of data at-rest encryption features as of 5.7, most of them are not available to the community edition. The latest major release of the community version sees the main feature set comprise of:
The enterprise edition adds the following extra support:
- keyring_aws_cmk_id
- keyring_aws_conf_file
- keyring_aws_data_file
- keyring_aws_region
- keyring_encrypted_file_data
- keyring_encrypted_file_password
- keyring_okv_conf_dir
Maximising at-rest encryption with MySQL 8.0
Using the following configuration would give you maximum at-rest encryption with MySQL 8.0:
early-plugin-load=keyring_file.so keyring_file_data=/var/lib/mysql/keyring innodb_redo_log_encrypt=ON innodb_undo_log_encrypt=ON
This configuration would provide the following at-rest protection:
- optional InnoDB tablespace encryption
- redo and undo log encryption
You would need to create new, or alter existing tables with the
ENCRYPTION=Y
option, which would then be visible by examining
information_schema.INNODB_TABLESPACES
, an example query being:
mysql> SELECT TABLE_SCHEMA AS db_name, -> TABLE_NAME AS db_table, -> CREATE_OPTIONS LIKE '%ENCRYPTION="Y"%' AS encrypted -> FROM information_schema.INNODB_TABLESPACES ts -> INNER JOIN information_schema.TABLES t ON t.TABLE_SCHEMA = SUBSTRING_INDEX(ts.name, '/', 1) -> AND t.TABLE_NAME = SUBSTRING_INDEX(ts.name, '/', -1); +---------+-----------------+-----------+ | db_name | db_table | encrypted | +---------+-----------------+-----------+ | sys | sys_config | 1 | +---------+-----------------+-----------+
N.B. You are able to encrypt the tablespaces in 5.7, in which case you should use
information_schema.INNODB_SYS_TABLESPACES
as the internal system views on the data dictionary were renamed (InnoDB Changes).
Unfortunately, whilst all of the tables in the mysql schema use the InnoDB engine (except for the log tables), you cannot encrypt them and instead get the following error:
ERROR 3183 (HY000): This tablespace can't be encrypted.
Interestingly, you are led to believe that you can indeed encrypt the
general_log
and
slow_log
tables, but this is in fact a bug (#91791).
Percona Server for MySQL
Last, but not least we have Percona Server for MySQL, which, whilst not completely matching MariaDB for features, is getting very close. As we shall see shortly, it does in fact have some interesting differences to both MySQL and MariaDB.
The current feature set for 5.7, which does indeed exceed the features provided by MySQL 5.7 and for the most part 8.0, is as follows:
- encrypt_binlog
- encrypt_tmp_files
- innodb_encrypt_online_alter_logs
- innodb_encrypt_tables
- innodb_temp_tablespace_encrypt
- keyring_file_data
- keyring_operations
- keyring_vault_config
- keyring_vault_timeout
Maximising at-rest encryption with Percona Server for MySQL 5.7
Using the following configuration would give you maximum at-rest encryption with Percona Server 5.7:
early-plugin-load=keyring_file.so keyring_file_data=/var/lib/mysql-keyring/keyring innodb_temp_tablespace_encrypt=ON innodb_encrypt_online_alter_logs=ON innodb_encrypt_tables=FORCE encrypt_binlog=ON encrypt_tmp_files=
This configuration would provide the following at-rest protection:
- automatic and enforced InnoDB tablespace encryption
- encryption of temporary files and tables
- binary log encryption
- encryption when performing online DDL
There are some additional features that are due for release in the near future:
- Encryption of the doublewrite buffer
- Automatic key rotation
- Undo log and redo log encryption
- InnoDB system tablespace encryption
- InnoDB tablespace and redo log scrubbing
- Amazon KMS keyring plugin
Just like MySQL, encryption of any existing tables needs to be specified via
ENCRYPTION=Y
via an
ALTER
, however new tables are automatically encrypted. Another difference is that in order to check which tables are encrypted you can should the flag set against the tablespace in
information_schema.INNODB_SYS_TABLESPACES
, an example query being:
mysql> SELECT SUBSTRING_INDEX(name, '/', 1) AS db_name, -> SUBSTRING_INDEX(name, '/', -1) AS db_table, -> (flag & 8192) != 0 AS encrypted -> FROM information_schema.INNODB_SYS_TABLESPACES; +---------+---------------------------+-----------+ | db_name | db_table | encrypted | +---------+---------------------------+-----------+ | sys | sys_config | 1 | | mysql | engine_cost | 1 | | mysql | help_category | 1 | | mysql | help_keyword | 1 | | mysql | help_relation | 1 | | mysql | help_topic | 1 | | mysql | innodb_index_stats | 1 | | mysql | innodb_table_stats | 1 | | mysql | plugin | 1 | | mysql | servers | 1 | | mysql | server_cost | 1 | | mysql | slave_master_info | 1 | | mysql | slave_relay_log_info | 1 | | mysql | slave_worker_info | 1 | | mysql | time_zone | 1 | | mysql | time_zone_leap_second | 1 | | mysql | time_zone_name | 1 | | mysql | time_zone_transition | 1 | | mysql | time_zone_transition_type | 1 | | mysql | gtid_executed | 0 | +---------+---------------------------+-----------+
Here you will see something interesting! We are able to encrypt most of the system tables, including two that are of significance, as they can contain plain text passwords:
+---------+-------------------+-----------+ | db_name | db_table | encrypted | +---------+-------------------+-----------+ | mysql | servers | 1 | | mysql | slave_master_info | 1 | +---------+-------------------+-----------+
In addition to the above, Percona Server for MySQL also supports using the opensource HashiCorp Vault to host the keyring decryption information using the keyring_vault plugin; utilizing this setup (provided Vault is not on the same device as your mysql service, and is configured correctly) gains you an additional layer of security.
You may also be interested in my earlier blog post on using Vault with MySQL, showing you how to store your credentials in a central location and use them to access your database, including the setup and configuration of Vault with Let’s Encrypt certificates.
Summary
There are significant differences both in terms of features and indeed variable names, but all of them are able to provide encryption of the InnoDB tablespaces that will be containing your persistent, sensitive data. The temporary tablespaces, InnoDB logs and temporary files contain transient data, so whilst they should ideally be encrypted, only a small section of data would exist in them for a finite amount of time which is less of a risk, albeit a risk nonetheless.
Here are the highlights:
| MariaDB 10.3 | MySQL 8.0 | Percona Server 5.7 | |
| encrypted InnoDB data | Y | Y | Y |
| encrypted non-InnoDB data | Aria-only | N | N |
| encrypted InnoDB logs | Y | Y | TBA |
| automatic encryption | Y | N | Y |
| enforced encryption | Y | N | Y |
| automatic key rotation | Y | N | TBA |
| encrypted binary logs | Y | N | Y |
| encrypted online DDL | ? | N | Y |
| encrypted keyring | Y | Enterprise-only | N |
| mysql.slave_master_info | N | N | Y |
| mysql.servers | N | N | Y |
| Hashicorp Vault | N | N | Y |
| AWS KMS | Y | Enterprise-only | TBA |
Extra reading:
- Keep Sensitive Data Secure in a Replication Setup
- Binlog Encryption with Percona Server for MySQL
- Enabling InnoDB Tablespace Encryption on Percona XtraDB Cluster 5.7
- MySQL Encryption at Rest – Part 2 (InnoDB)
The post Comparing Data At-Rest Encryption Features for MariaDB, MySQL and Percona Server for MySQL appeared first on Percona Database Performance Blog.
