MySQL Data at Rest Encryption

This blog post will discuss the issues and solutions for MySQL Data at Rest encryption.

Data at Rest Encryption is not only a good-to-have feature, but it is also a requirement for HIPAA, PCI and other regulations.

There are three major ways to solve data encryption at rest:

1. Full-disk encryption
2. Database-level (table) encryption
3. Application-level encryption, where data is encrypted before being inserted into the database

I consider full disk encryption to be the weakest method, as it only protects from someone physically removing the disks from the server. Application-level encryption, on the other hand, is the best: it is the most flexible method with almost no overhead, and it also solves data in-flight encryption. Unfortunately, it is not always possible to change the application code to support application-level encryption, so database-level encryption can be a valuable alternative.

Sergei Golubchik, Chief Architect at MariaDB, outlined the pluses and minuses of database level encryption during his session at Percona Live Amsterdam:

Pros

• Full power of DBMS is available
• Full power of DBMS is availableEasy to implement
• Easy to implementOnly database can see the data
• Only databases can see the dataPer-table encryption, per-table keys, performance
• Per-table encryption, per-table keys, performance

Cons

• Cannot be done per-user
• Does not protect against malicious root user

Data at Rest Encryption: Database-Level Options

Currently, there are two options for data at rest encryption at the database level:

• MariaDB 10.1.3+ support encryption (using Google patch)
• MySQL 5.7.11+ (and Percona Server 5.7.11) has InnoDB tablespace level encryption

MariaDB’s implementation is different from MySQL 5.7.11. MySQL 5.7.11 only encrypts InnoDB tablespace(s), while MariaDB has an option to encrypt undo/redo logs, binary logs/relay logs, etc. However, there are some limitations (especially together with Galera Cluster):

• No key rotation in the open source plugin version (MySQL 5.7.11 has a key rotation)
• mysqlbinlog does not work with encrypted binlogs (bug reported)
• Percona XtraBackup does not work, so we are limited to RSYNC as SST method for Galera Cluster, which is a blocking method (one node will not be available for writes during the SST). The latest Percona XtraBackup works with MySQL 5.7.11 tablespace encryption
• The following data is not encrypted (bug reported)
  • Galera gcache + Galera replication data
  • General log / slow query log

Database level encryption also has its weakness:

1. Root and MySQL users can read the keyring file, which defeats the purpose. However, it is possible to place a key on the mounted drive and unmount it when MySQL starts (that can be scripted). The downside of this is that if MySQL crashes, it will not be restarted automatically without human intervention.
2. Both MariaDB version and MySQL version only encrypt data when writing to disk – data is not encrypted in RAM, so a root user can potentially attach to MySQL with gdb/strace or other tools and read the server memory. In addition, with gdb it is possible to change the root user password structure and then use mysqldump to copy data. Another potential method is to kill MySQL and start it with skip-grant-tables. However, if the key is unmounted (i.e., on USB drive), MySQL will either not start or will not be able to read the encrypted tablespace.

MariaDB Encryption Example

To enable the full level encryption we can add the following options to my.cnf:

MySQL

1 2 3 4 5 6 7 8 9 10 11

[mysqld] plugin-load-add=file_key_management.so file_key_management_filekey = FILE:/mount/keys/mysql.key file-key-management-filename = /mount/keys/mysql.enc innodb-encrypt-tables = ON innodb-encrypt-log = 1 innodb-encryption-threads=1 encrypt-tmp-disk-tables=1 encrypt-tmp-files=1 encrypt-binlog=1 file_key_management_encryption_algorithm = AES_CTR

After starting MariaDB with those settings, it will start encrypting the database in the background. The file_key_management plugin is used; unfortunately, it does not support key rotation. The actual keys are encrypted with:

Shell

1	# openssl enc -aes-256-cbc -md sha1 -k <key> -in keys.txt -out mysql.enc

The encryption <key> is placed in /mount/keys/mysql.key.

After starting MySQL, we can unmount the “/mount/key” partition. In this case, the key will not be available and a potential hacker will not be able to restart MySQL with “–skip-grant-tables” option (without passwords). However, it also prevents normal restarts, especially SSTs (cluster full sync).

Additional notes:

1. Encryption will affect the compression ratio, especially for the physical backups (logical backups, i.e. mysqldump does not matter as the data retrieved is not encrypted). If your original compressed backup size was only 10% of the database size, it will not be the case for the encrypted tables.
2. Data is not encrypted in flight and will not be encrypted on the replication slaves unless you enable the same options on the slaves. The encryption is also local to the server, so when encryption was just enabled on a server some tables may not be encrypted yet (but will be eventually)
3. To check which tables are encrypted, use the Information Schema INNODB_TABLESPACES_ENCRYPTION table, which contains encryption information. To find all tables that are encrypted, use this query:
   
   MySQL

   1	select * from information_schema.INNODB_TABLESPACES_ENCRYPTION where ENCRYPTION_SCHEME=1

MySQL 5.7 Encryption Example

To enable encryption, add the following option to my.cnf:

MySQL

1 2 3

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

Again, after starting MySQL we can unmount the “/mount/mysql-keyring/” partition.

To start encrypting the tables, we will need to run alter table table_name encryption='Y' , as MySQL will not encrypt tables by default.

The latest Percona Xtrabackup also supports encryption, and can backup encrypted tables.

To find all encrypted tablespaces in MySQL/Percona Server 5.7.11, we can use information_schema.INNODB_SYS_TABLESPACES and the flag field. For example, to find normally encrypted tables, use the following query:

MySQL

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

mysql> select * from information_schema.INNODB_SYS_TABLESPACES where flag = 8225G *************************** 1. row ***************************          SPACE: 4688           NAME: test/t1           FLAG: 8225    FILE_FORMAT: Barracuda     ROW_FORMAT: Dynamic      PAGE_SIZE: 16384 ZIP_PAGE_SIZE: 0     SPACE_TYPE: Single FS_BLOCK_SIZE: 4096      FILE_SIZE: 98304 ALLOCATED_SIZE: 98304 *************************** 2. row ***************************          SPACE: 4697           NAME: sbtest/sbtest1_enc           FLAG: 8225    FILE_FORMAT: Barracuda     ROW_FORMAT: Dynamic      PAGE_SIZE: 16384 ZIP_PAGE_SIZE: 0     SPACE_TYPE: Single FS_BLOCK_SIZE: 4096      FILE_SIZE: 255852544 ALLOCATED_SIZE: 255856640 2 rows in set (0.00 sec)

You can also use this query instead: select * from information_schema.tables where CREATE_OPTIONS like'%ENCRYPTION="Y"%';.

Performance overhead

This is a debatable topic, especially for the MariaDB implementation when everything is configured to be encrypted. During my tests I’ve seen ~10% of overhead for the standalone MySQL instance, and ~20% with Galera Cluster.

The MySQL 5.7/Percona Server 5.7 tablespace-level encryption shows an extremely low overhead, however, that needs to be tested in different conditions.

Conclusion

Even with all the above limitations, database-level encryption can be a better option than the filesystem-level encryption if the application can not be changed. However, it is a new feature (especially MySQL 5.7.11 version) and I expect a number of bugs here.