Welcome 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:
1 2 | early-plugin-load = keyring_file.so keyring_file_data = /var/lib/mysql-keyring/keyring |
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:
1 2 | mysql> INSTALL PLUGIN keyring_file SONAME 'keyring_file.so'; mysql> SET GLOBAL keyring_file_data = '/var/lib/mysql-keyring/keyring'; |
You can now CREATE encrypted tables or ALTER existing tables to encrypt them with a simple command:
1 2 3 4 5 | mysql> CREATE TABLE t1 (c1 INT) ENCRYPTION='Y'; Query OK, 0 rows affected (0.04 sec) mysql> ALTER TABLE users ENCRYPTION='Y'; Query OK, 1 row affected (0.04 sec) |
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:
1 | pt-online-schema-change --execute --alter "engine=innodb encryption='Y'" D=mydb,t=mytab |
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:
1 2 3 | # dbname=mydb # mysql -B -N -e "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='$dbname' and TABLE_TYPE='BASE TABLE'" | while read line; do pt-online-schema-change --execute --alter "engine=innodb encryption='Y'" D=$dbname,t=$line; done |
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:
1 | mysql> ALTER INSTANCE ROTATE INNODB MASTER KEY; |
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.
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
Thanks
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.
I am using Galera, do I have same keyring for all nodes, the subsequent nodes are unable to join via SST?
@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.