Importing an Encrypted InnoDB Tablespace into MySQL

Importing an Encrypted InnoDB Tablespace into MySQLTransportable tablespaces were introduced in MySQL 5.6. Using this feature, we can directly copy a tablespace to another server and populate the table with data. This is a very useful feature for large tables. The transportable tablespace mechanism is faster than any other method for exporting and importing tables because the files containing the data just need to be copied to the target location using traditional Linux commands (cp, scp, rsync). Our post MySQL 5.6 Transportable Tablespaces best practices covers the best practices about transportable tablespaces. The feature also supports encrypted tablespaces, and in this article, I am going to explain how to use this feature with them.

Requirements

Below I am sharing my current setup and the requirements.

  • I have two servers – s1 and s2.
  • I am running Percona Server for MySQL 5.7.33 on both servers.
  • Data-at-Rest Encryption is enabled on both servers.
  • S1 has the encrypted table “percona.enc_EI_test”, which needs to be copied to s2 using a transportable tablespace feature. 

The following steps will be helpful to understand the process involved.

Step 1 (Prepare the table to copy):

This step ensures that changes to that particular table have been flushed to disk so that binary table copies can be made while the server is running. 

At S1:

Once the command is executed, it will create two additional files (.cfg and .cfp) in the MySQL data directory. 

  • The .cfg file is the metadata file, which contains metadata that is used for schema verification during the import operation.
  • The .cfp file is only for the encrypted tables, the .cfp file contains a transfer key and an encrypted tablespace key. 

Step 2 (copy .ibd, .cfg, and .cfp files from s1 to s2): 

After executing step one, you need to copy the table files (.ib,.cfg,.cfp) to destination server s2.

Step 3 (Unlock table on S1):

Once the table files are copied to the destination server (s2), you need to unlock the table on s1 to allow the operations.

Step 4 (Create the table structure on s2):

Now, you have to create the empty table on the destination server s2.

Make sure that you have added the encryption on the table structure “ENCRYPTION=Y”. Otherwise, you will get the following error during the import process.

Step 5 (Remove the .ibd file):

Once the empty table has been created on s2, it will have two files (.frm and .ibd). You need to remove the .ibd file so that you can import the tablespace which was copied from s1. 

Step 6 (Copy the tablespace to data directory):

In this step, you need to copy the tablespace files (from s1) to the data directory under the database folder.