MySQL Tablespace Encryption ( TDE )

In this blog we are going to explore about innodb tablespace encryption technique, which will be useful for securing data.

In MySQL 5.7, a new feature “Innodb Tablespace Encryption“has been added to protect the data at rest. This is a most awaited feature in security. This encryption supports all file per table tablespaces and it will not  support shared tablespace. This encryption technique works on the basis of rotating  key files. There are two types of keyring plugins available for the key management and they are given below

  • keyring_file plugin – Available in all MySQL versions.
  • keyring_okv plugin – Available in MySQL Enterprise Edition.


Innodb tablespace encryption uses two tier encryption architecture, in which it has master encryption key and Tablespace keys. The master key is used to encrypt and decrypt the tablespace keys whereas tablespace key is used to encrypt and decrypt tablespace data. Here the tablespace is very safe, as it is not viewable by any users.

Tablespace Encryption Architecture

Master key is stored outside the database as it can see by the users. Tablespace keys are stored in tablespace header which is protected by the Master key. For encrypted tables, tablespace key is encrypted by master key and stored in tablespace header. Here, the encryption algorithm used is AES and encryption mode is block encryption mode(CBC).

In innodb, pages are encrypted using the tablespace key and it is done at the IO layer. A page could be modified may times in the buffer and then gets flushed. So, we avoid encrypting data the page whenever it is modified, instead we used to encrypt just before writing to disk. The encryption is done by background page cleaner threads, which states the query threads don’t spend more CPU. The buffer pool remain decrypted as there is no overhead of pages accessed.

Add keyring plugin:

To use the keyring_file plugin, add early-plugin-load in my.cnf. It will load keyring plugin before innodb is loaded. This is because keyring plugin is used to decrypt tablespaces before applying the redo log. After this we have to provide the location of keyring file data, this master key file is used for encrypting tablespace keys. It should be backed up very often, as losing keyring data file will make data inaccessible.

[mysqld]                                                                                               keyring_file_data=/var/lib/keyring/keyring
mysql> show variables like '%keyring%';
| Variable_name     | Value                   |
| keyring_file_data | /var/lib/keyring/keyring|

After adding the keyring_file, please make sure whether it is enabled on the server.

mysql> show plugins;
| Name         | Status | Type    | Library         | License |
| keyring_file | ACTIVE | KEYRING | | GPL     |

The keyring_file should be active to create encrypted tables.

| encrypt      | creative   | ENCRYPTION="Y"                  |

Master encryption key should be rotated periodically. Rotating the master encryption key changes only the master encryption key and re-encrypts tablespace keys. It does not decrypt or re-encrypt associated tablespace data.

Below command is used for rotating master key.

Query OK, 0 rows affected (0.00 sec)

MASTER_TBSKEY – This key is master innodb encryption key. For community server, this config file will saved on the filesystem. For MySQL enterprise server, this will be in memory.

TBS#KEY – This key is hidden and used to encrypt tablespace table. This key never changes and not stored on disk. It will be in mysql internal process memory.

Creating Encrypted Tables:

Once the keyring_file is active, please create a table with encryption and check the status.

mysql> create table test(id int primary key, name varchar(20), age int) 
Query OK, 0 rows affected (0.02 sec)

mysql> show create table test;
| Table | Create Table                                                   |
 `id` int(11) NOT NULL,
 `name` varchar(20) DEFAULT NULL,
 `age` int(11) DEFAULT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ENCRYPTION='Y'                      |

So, what to do, if we have to encrypt existing table. To do this, the table algorithm should be “COPY” as “INPLACE” algorithm is not supported by when turning encryption ON/OFF.

Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

The complete rebuild of table is done during encryption. Which can be costly operation in case of huge tables and there is a lack of IO.

Master key rotation:

  • The master key rotation is an atomic, instance-level operation. Each time the master encryption key is rotated, all tablespace keys in the mysql instance are re-encrypted and saved back to their respective tablespace headers.
  • The master encryption key only changes the master encryption key and re-encrypts tablespace keys. It does not decrypt or re-encrypt associated tablespace data.
mysql> ALTER Instance rotate innodb master key;

Note : It needs SUPER privilege

Validation ( Simple ):

Let us make a validation of encryption .

Load the data into the table. Check the lab_test table ibd file ( Tablespace ) .It contains a plain text contents.

[test@labs2 table_space]$ head lab_test.ibd

So we can encrypt is table using alter statement.

Now let’s check the lab_test.ibd file.

[test@labs2 table_space]$ head lab_test.ibd

The data file (lab_test.ibd) is encrypted .

Information Schema and Table space Encryption:

Information schema also stores the metadata information about the encrypted tables.

select table_schema,table_name,create_options from information_schema.tables where create_options like '%encryption="Y"%';

To have encryption on the percona xtradb cluster, you can visit this blogpercona xtradb cluster, you can visit this blog


  • Shared/System tablespaces (ibdata1) is not encrypted.
  • Binary, undo and redo logs are not encrypted. ( MySQL 8.0 Supports redo log & undo log encryption ).
  • Advanced Encryption Standard(AES) is the only supported encryption algorithm.
  • Encrypted table cannot be moved or copied from file_per_table tablespace to unsupported innodb tablespace.
  • It supports only InnoDB Engine .
  • In a replication topology the plugin ( keyring ) should be enabled on all nodes.

13 thoughts on “MySQL Tablespace Encryption ( TDE )

  1. Hi,
    I like your article but I need to know If we lost our master key what happened?
    And how we can backup our master key?


  2. Hi,

    If we lost our master key we can’t decrypt the encrypted tables and no way to recover it.

    If you are using mysql enterprice backup it always stores the master key for encryption in an encrypted file inside the backup.

    ( OR )

    you are using percona-xtrabackup kindly refer the below link –

    Its recommended to have safe backup of keyfile, elsewhere safely.


  3. Hi,
    Thanks for your quick reponse.
    But I want to know if we are use tablespace key we can’t see master key.
    But in which case we can lost our master key?
    I want to know if we use tablespace key TDE we export all table so all table are encrypted or decrypted?
    And I want to import all table to another server innoDB master key will change?
    And when master key will change automatically?
    Thanks again mydbops team.


    1. Yes Redo-logs are Undo logs are not encrypted as of MySQL 5.7, Percona server for mysql 5.7 latest has feature for binlog encryption not in oracle mysql, MySQL8.0 has the feature of encrypting these log files


    1. Hi Sundar,

      TDE is completely possible in community MySQL version too. But the only difference the master key should be managed by DB owners where it is stored in Memory in Enterprise version.


      1. Hi Thank you for the clarification. I would like to implement TDE in one of my MySQL v8.0 production cluster. It is completely constructed with community server.

        Can you provide some documents or pointers to implement TDE on this environment?


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s