MySQL Encryption: Master Key Encryption in InnoDB

In the previous blog post of this series, MySQL Encryption: Talking About Keyrings, I described how keyrings work. In this post, I will talk about how master key encryption works and what the pros and cons are of using envelope encryption such as a master key.

The idea behind envelope encryption is that you use one key to encrypt multiple other keys. In InnoDB, this “one key” is the master encryption key and the “multiple other keys” are the tablespace keys. Those tablespace keys are the ones that are actually used to encrypt tablespaces. Graphically it can be presented like this:


The master key resides in the keyring, while encrypted tablespace keys reside in tablespace headers (written on page 0 of a tablespace). In the picture above:

Table A is encrypted with key 1. Key 1 is encrypted with the master key and stored (encrypted) in Table A’s header.

Table B is encrypted with key 2. Key 2 is encrypted with the master key and stored (encrypted) in Table B’s header.

And so on. When a server wants to decrypt Table A, it fetches the master key from the keyring, reads the encrypted key 1 from Table A’s header, and decrypts the key 1. The decrypted key 1 is cached in server memory and used to decrypt Table A.


In InnoDB, the actual encryption/decryption is done in the I/O layer of the server. So just before a page is flushed to disk it gets encrypted, and also, just after an encrypted page is read from a disk, it gets decrypted.

Encryption in InnoDB works only on the tablespace level. Normally when you create a standalone table you create a file-per-table tablespace (ON by default). So what you actually are creating is a tablespace that can contain only one table. You can also create a table that belongs to a general tablespace. Either way, your table always resides inside some tablespace. Since encryption works on tablespace level, a tablespace can be either fully encrypted or fully un-encrypted. So you cannot have some tables inside general tablespace encrypted, and some not.

If for some reason you have file-per-table disabled, then all the standalone tables are actually created inside system tablespace. In Percona Server for MySQL, you can encrypt system tablespace by specifying (variable innodb_sys_tablespace_encrypt) during bootstrap or using encryption threads (still an experimental feature). In MySQL, you cannot ;).

Before we go any further we need to understand how the master key ID is built. It consists of UUID, KEY_ID, and prefix INNODBKey. It looks like this: INNODBKey-UUID-KEY_ID.

UUID is the server’s uuid in which tablespace was encrypted. KEY_ID is just an ever-increasing value. When the first master key is created this KEY_ID is equal to 1. Then when you rotate the master key, a new master key is created with KEY_ID = 2 and so on. We will discuss master key rotation in-depth later in the next blog posts of this series.

Now that we know what master key ID looks like, we can have a look at an encryption header. When a tablespace is first encrypted, the encryption header is added to the tablespace header. It looks like this:

KEY ID is the KEY_ID from the master key ID that we have already discussed. UUID is the server uuid, later used in master key ID. The tablespace key consists of 256 randomly generated (by the server) bits. Tablespace IV also consists of 256 randomly generated keys (although it should be 128 bits). IV is used to initialize AES encryption and decryption (only 128 bits of those 256 bits are used). Last we have CRC32 checksum of tablespace key and IV.

All this time I was saying that we have an encrypted tablespace key in the header. I was oversimplifying this a bit. Actually, we store tablespace key and IV bundled together, and we encrypt them both using the master key. Remember, before we encrypt tablespace key and IV we first calculate CRC32 of both.

Why Do We Need CRC32?

Well, to make sure that we are using the correct master key to decrypt the tablespace key and IV. After we decrypt the tablespace key and IV, we calculate checksum and we compare it with the CRC32 stored in the header. In case they match, we know we have used the correct master key and we have a valid tablespace key and tablespace iv to decrypt the table. In case they do not match, we mark the tablespace as missing (we would not be able to decrypt it anyways).

You may ask – when do we check if we have correct master keys to decrypt all the tables? The answer is: at the server startup. At the startup, each encrypted table/tablespace server reads the UUID, KEY_ID from the header to construct the master key ID. Next, it fetches the needed master key from Keyring, decrypts tablespace key and iv, and checks the checksum. Again, if checksum matches we are all good, if not, the tablespace is marked as missing.

If you are following this encryption series of blog posts, you might remember that in MySQL Encryption: Talking About Keyrings I said that server-based keyrings only fetch a list of key ids (to be precise, key id and user id, as this pair uniquely identifies the key in the keyring) on server startup. Now I am saying that the server fetches all the keys it needs on the startup to validate that it can decrypt tablespace keys. So why does a server-based keyring fetch only key_id and user_id when initialized instead of fetching all the keys? Because not all the keys might be needed; this is mostly due to master key rotation. Master key rotation will generate a new master key in keyring but it will never delete old versions of the master key. So you might end up with many keys in Key Server (Vault server – if you are using keyring_vault) that are not needed by the server and thus not fetched on server startup.

It is time we talk a bit about the pros and cons of master key encryption. The biggest advantage is that you only need one encryption key (master key) to be stored separately from your encrypted data. This makes the server startup fast and keyring small,  thus easier to manage. Also, the master key can be rotated with one command.

However, master key encryption has one big disadvantage; once a tablespace is encrypted with tablespace_key it will always stay encrypted with the same key. Master key rotation will not help here. Why is this a disadvantage? We know that MySQL has bugs that can make it suddenly crash, producing a core file. Since the core file contains a memory dump of our server, it can so happen that this core dump will contain a decrypted tablespace key. What is worse, the decrypted tablespace keys are stored in memory that can be swapped to disk. You can say that it is not a disadvantage because you need to have root access to access those core files/swap partition. This is true – but you only need root access for some time. Once someone gets access to decrypted tablespace key, s/he can keep using it to decrypt the data, even if the root access is no longer possible for that person. Also, the disk can be stolen and swap partition/core files can be read with different means, and the purpose of TDE is to make it unreadable even if the disk gets stolen. Percona Server for MySQL offers a feature that makes actual re-encryption of a tablespace – with newly generated keys – possible. This feature is called encryption threads and is still experimental at the time I am writing this blog post.

Stay tuned for more information on transparent data encryption in the next blog posts of this series.

Share this post

Leave a Reply