EmergencyEMERGENCY? Get 24/7 Help Now!

MySQL Encryption at Rest – Part 2 (InnoDB)

and  | June 28, 2017 |  Posted In: InnoDB, MySQL, Security

PREVIOUS POST
NEXT POST

MySQL Encryption at RestWelcome to Part 2 in a series of blog posts on MySQL encryption at rest. This post covers InnoDB tablespace encryption.

At Percona, we work with a number of clients that require strong security measures for PCI, HIPAA and PHI compliance, where data managed by MySQL needs to be encrypted “at rest.” As with all things open source, there several options for meeting the MySQL encryption at rest requirement. In this three-part series, we cover several popular options of encrypting data and present the various pros and cons to each solution. You may want to evaluate which parts of these tutorials work best for your situation before using them in production.

Part one of this series covered implementing disk-level encryption using crypt+LUKS.

In this post, we discuss InnoDB tablespace encryption (TE). You can choose to use just LUKS, just InnoDB TE, or you can use both. It depends on your compliance needs and your configuration. For example, say you put your binary logs and InnoDB redo logs on partition X, and your InnoDB tablespaces (i.e., your .ibd files) on partition Y. InnoDB TE only handles encrypting the tablespaces. You would need to use LUKS on partition X to encrypt those files.

InnoDB Tablespace Encryption Tutorial

The manual describes InnoDB encryption as follows:

InnoDB tablespace encryption uses a two-tier encryption key architecture, consisting of a master encryption key and tablespace keys. When an InnoDB table is encrypted, a tablespace key is encrypted and stored in the tablespace header. When an application or authenticated user wants to access encrypted tablespace data, InnoDB uses a master encryption key to decrypt the tablespace key. The decrypted version of a tablespace key never changes, but you can change the master encryption key as required. This action is referred to as master key rotation.

As a real-world example, picture a high-school janitor’s key-chain. Each key is different; each key works for only one door (or in MySQL’s case, table). The keys themselves never change, but the label on each key can change based on how the janitor wants to note which key works on which door. At any time, the janitor can re-label the keys with special names, thwarting any hooligans from opening doors they shouldn’t be opening.

Configuration Changes

As noted above, InnoDB uses a keyring to manage encryption keys on a per-table basis. But you must load the keyring plugin before InnoDB. Add the following lines to your my.cnf under the [mysqld] section. You may substitute a different path for the keyring file, but be sure that mysqld has permissions to this path:

You do not need to restart MySQL at this time, but you can if you wish. These settings are for when you restart MySQL in the future.

Install the Keyring Plugin in MySQL

If you don’t wish to restart MySQL at this time, you can still continue. Run the following SQL statements within MySQL to load the plugin and configure the keyring file:

You can now CREATE encrypted tables or ALTER existing tables to encrypt them with a simple command:

Be careful with the above ALTER: It will most likely block all other transactions during this conversion.

Encrypting Tables Using pt-online-schema-change

Using pt-online-schema-change is fairly straightforward. While it has many options, the most basic usage to encrypt a single table is the following:

pt-online-schema-change creates an encrypted, shadow copy of the table mytab. Once the table is populated with the original data, it drop-swaps the table and replaces the original table with the new one.

It is always recommended to first run pt-online-schema-change with the --dry-run flag first, in order to validate the command and the change.

Encrypting All Tables in a Database

To encrypt all the tables in a database, we can simply take the above command and wrap it in a shell loop. The loop is fed a list of tables in the database from MySQL. You just need to change the dbname variable:

Be careful with the above command, as it will take off and blast through all the changes!

Rotating InnoDB Encryption Keys

Your compliance policy dictates how often you need to rotate the encryption keys within InnoDB:

This command creates a new key, decrypts all encrypted tablespace headers (where you store the per-table key) using the previous key, and then re-encrypts the headers with the new key. Notice that the table itself is not re-encrypted. This would be an extremely intrusive and potentially dangerous operation. Recall above that each tables’ encryption key never changes (unless the entire table gets decrypted). What can change is the key used to encrypt that key within each tables’ header.

The keyring UDFs in MySQL

The keyring plugin can also be used to store sensitive data within MySQL for later retrieval. This functionality is beyond the scope of this blog as it doesn’t deal directly with InnoDB TE. You can read more about it in the MySQL manual.

Conclusion

InnoDB Tablespace Encryption makes it easy to use MySQL encryption at rest. Keep in mind the downsides of using only TE: InnoDB tablespace encryption doesn’t cover undo logs, redo logs or the main ibdata1 tablespace. Additionally, InnoDB encryption doesn’t cover binary-logs and slow-query-logs.

PREVIOUS POST
NEXT POST
Manjot Singh

Manjot Singh is an Architect with Percona in California. He loves to learn about new technologies and apply them to real world problems. Manjot is a veteran of startup and Fortune 50 enterprise companies alike with a few years spent in government, education, and hospital IT.

Matthew Boehm

Matthew joined Percona in the fall of 2012 as a MySQL consultant. His areas of knowledge include the traditional Linux/Apache/MySQL/PHP stack, memcached, MySQL Cluster, massive sharding topologies, PHP development and a bit of MySQL-C-API development. Previously, Matthew was a DBA for the 5th largest world-wide MySQL installation at eBay/PayPal and also hails from managed hosting environments. During his off-hours, Matthew is a nationally ranked, competitive West Coast Swing dancer and travels to competitions around the US. He enjoys working out, camping, biking and playing Guild Wars 2 with his son.

7 Comments

  • Nice blog.

    Couple of questions:
    1. Is the recommendation to use block level encryption with table space encryption (Keep in mind the downsides of using only TE) ?
    2. What was the performance impact between both?

  • Hello icsomu,
    1. You can do both, but that doesn’t really gain you anything. If everything (tablespace, redo, binlog, etc) is already on 1 partition, you should do LUKS so that you encrypt everything at rest. If you had things split up, like binlogs on partition A, redo/undo on partition B, and tablespace on partition C, then you could do a combo of LUKS on A, and B, and use InnoDB TE for the tables.
    2. The performance impact is minimal if your CPU has AES hardware acceleration. You can compile this test suite, written in assembly, to determine if your CPU supports the AES. https://github.com/kmcallister/aesni-examples

  • 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 (ALTER INSTANCE ROTATE INNODB MASTER KEY;) does it change the key in
    keyring_file_data=/mount/mysql-keyring/keyring

  • Hello @Srinivas. You can export encrypted tablespaces. Here is more info from the manual, https://dev.mysql.com/doc/refman/5.7/en/innodb-tablespace-encryption.html#innodb-tablespace-encryption-exporting

    Yes, rotating the master key changes the encryption key inside the keyring.

  • @Manuel. Yes, you will need the same keyring on all nodes. You may need to pass additional parameters to the xtrabackup_v2 SST script. I am unaware at this time if it can handle encrypted tablespaces by default.

Leave a Reply