EmergencyEMERGENCY? Get 24/7 Help Now!

MySQL Data at Rest Encryption

 | April 8, 2016 |  Posted In: MySQL

PREVIOUS POST
NEXT POST

MySQL Data at Rest EncryptionThis 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’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.

PREVIOUS POST
NEXT POST
Alexander Rubin

Alexander joined Percona in 2013. Alexander worked with MySQL since 2000 as DBA and Application Developer. Before joining Percona he was doing MySQL consulting as a principal consultant for over 7 years (started with MySQL AB in 2006, then Sun Microsystems and then Oracle). He helped many customers design large, scalable and highly available MySQL systems and optimize MySQL performance. Alexander also helped customers design Big Data stores with Apache Hadoop and related technologies.

14 Comments

  • “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.

  • 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

    • 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.

    • 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.

  • 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.

      • 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

  • 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.

  • 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.

  • 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

Leave a Reply