MySQL Data at Rest EncryptionThis blog post will discuss the issues and solutions for MySQL Data at Rest encryption. There is an update, too, by my colleague Ceri Williams – you can check it out here.

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 of database level encryption

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

Cons of database level encryption

  • 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’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:

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:

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 5.7 Encryption Example

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

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:

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.

17 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Pix

“Data at rest is an information technology term referring to inactive data which is stored physically in any digital form”, i wonder why consider the RAM data as data are at rest in point 2 ,which is in my opinion a good point, but not related with @rest.

0
Mind Snapper

Instead – if security and your data is important enough to warrant encryption – Spend some money and be well protected https://msdn.microsoft.com/en-us/library/bb669072%28v=vs.110%29.aspx?f=255&MSPPError=-2147217396

0
Alexander Rubin

Thank you for your feedback. However, “Spend some money and be well protected” in relation to MS SQL Server contradicts my (quite old) experience with MS SQL where a virus storm could have compromised the entire SQL Server data. Lots of things have obviously changed those days, at the same time I would prefer open source technologies, especially for encryption.

0
Joeri

I don’t get this remark. Microsoft is the company that called internet a hype that will blow over. Why would I believe this company is on the crest of internet security.

0
MAEDA Atsushi

Hi

Thank you allways for your good articles
I translated this article into Japanese for users in Japan.

Translated one is as follows.
https://yakst.com/ja/posts/3917

If there is any problem, please notice me.
Thank you.

0
Alexander Rubin

Thank you!

0
deep

Thanks for this great article.
I am trying to implement encryption at rest on maria db on 10.1.14. I have added
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

code to my.cnf
when I run openssl enc -aes-256-cbc -md sha1 -k /mount/keys/mysql.key -in keys.txt -out mysql.enc I get following error.

keys.txt: No such file or directory
140167121389216:error:02001002:system library:fopen:No such file or directory:bss_file.c:398:fopen(‘keys.txt’,’r’)
140167121389216:error:20074002:BIO routines:FILE_CTRL:system lib:bss_file.c:400:

Can you please help me.

Thanks

0
Anthony Garratt

Because of repetition in the Pros and Cons lists it’s not entirely clear which list “Cannot be done per-user” is in. Oracle are mailing about Data At Rest (which is how I arrived here, looking for alternative viewpoints), so it might be worth cleaning-up the lists.

0
Alexander Rubin

Anthony, thank you for noticing, I’ve fixed the post. Currently, database level encryption can not be done per-user.

0
Andrew

Application-level encryption is the best until that moment when you need to have partial text match (e.g. when doing search), and so you must have unencrypted text to utilize indexes and together with them – the columns must have plaintext data as well.

0
Srinivas Mitta

Can you please let us know how to move encrypted tables/or complete database to other servers which are using TDE.
If we change the MAster encrypted key does it change the key in
keyring_file_data=/mount/mysql-keyring/keyring

0
Alexander Rubin

UPDATE: as of the Sep 2017, both xtrabackup and SST with Percona XtraDB Cluster works with the tablespace encryption

0
sebutirpasirdijagatraya

What is the content of mysql.key and keys.txt and what’s the rule to create them?

0
EL HADJI DEM

Hello, Is it possible when using mysqldump to encrypt the sql file ?

0
Kiran Bhalerao

I created one table with MyISAM engine and enable encryption using “alter table table_name encryption=’Y'” command. But I insert data in table it showing the plan text data not showing in encrypted formatt in Mysql 5.7.19. After that I checked the “information_schema” database, but I did not found “INNODB_TABLESPACES_ENCRYPTION” table. Can you please give me the detail step by step solution to encrypt data in mysql 5.7.19.

0
Ajay Kumar

I know I can encrypt particular fields of a database, but I’m interested in encrypting every field of the database. I want to make sure no one who gains access to a mysql shell but who does not have access to a decryption key cannot read anything out of the database at all.

I also want to make sure that if someone got root access to the machine, but didn’t have a decryption key, they couldn’t read the data.

How should I do this? Does it make sense to do? I’m concerned if someone has access to the mysql database they inevitably will have access to the key, so this makes no sense. Am I missing something?

0
Naveen

Hi,

This is very useful, would you know of a way to get this done for Postgres

0