Transportable 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | mysql> select @@version, @@version_commentG *************************** 1. row *************************** @@version: 5.7.33-36-log @@version_comment: Percona Server (GPL), Release 36, Revision 7e403c5 1 row in set (0.01 sec) mysql> show create table percona.enc_EI_testG *************************** 1. row *************************** Table: enc_EI_test Create Table: CREATE TABLE `enc_EI_test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(16) DEFAULT NULL, `exec_time` datetime DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=165520 DEFAULT CHARSET=latin1 ENCRYPTION='Y' 1 row in set (0.01 sec) |
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:
1 2 | mysql> flush table enc_EI_test for export; Query OK, 0 rows affected (0.00 sec) |
Once the command is executed, it will create two additional files (.cfg and .cfp) in the MySQL data directory.
1 2 3 4 | -rw-r-----. 1 mysql mysql 8.5K Mar 20 21:05 enc_EI_test.frm -rw-r-----. 1 mysql mysql 13M Mar 20 21:05 enc_EI_test.ibd -rw-r-----. 1 mysql mysql 100 Mar 20 21:05 enc_EI_test.cfp -rw-r-----. 1 mysql mysql 455 Mar 20 21:05 enc_EI_test.cfg |
- 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.
1 2 3 4 | [root@s1 percona]# scp -r enc_EI_test.ibd enc_EI_test.cfp enc_EI_test.cfg 172.28.128.17:/tmp/export/ enc_EI_test.cfg 100% 455 492.5KB/s 00:00 enc_EI_test.cfp 100% 100 133.3KB/s 00:00 enc_EI_test.ibd 100% 13MB 84.0MB/s 00:00 |
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.
1 2 | mysql> unlock tables; Query OK, 0 rows affected (0.00 sec) |
Step 4 (Create the table structure on s2):
Now, you have to create the empty table on the destination server s2.
1 2 3 4 5 6 7 | mysql> CREATE TABLE `enc_EI_test` ( -> `id` int(11) NOT NULL AUTO_INCREMENT, -> `name` varchar(16) DEFAULT NULL, -> `exec_time` datetime DEFAULT CURRENT_TIMESTAMP, -> PRIMARY KEY (`id`) -> ) ENGINE=InnoDB AUTO_INCREMENT=165520 DEFAULT CHARSET=latin1 ENCRYPTION='Y'; Query OK, 0 rows affected (0.01 sec) |
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.
1 2 | mysql> alter table enc_EI_test import tablespace; ERROR 1808 (HY000): Schema mismatch (Encryption attribute in the file does not match the dictionary.) |
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.
1 2 3 4 5 6 7 8 9 10 11 | [root@s2 percona]# ls -lrth total 112K -rw-r-----. 1 mysql mysql 8.5K Mar 20 21:08 enc_EI_test.frm -rw-r-----. 1 mysql mysql 96K Mar 20 21:08 enc_EI_test.ibd mysql> alter table enc_EI_test discard tablespace; Query OK, 0 rows affected (0.01 sec) [root@s2 percona]# ls -lrth total 16K -rw-r-----. 1 mysql mysql 8.5K Mar 20 21:08 enc_EI_test.frm |
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.
1 2 3 4 5 6 7 8 9 10 | [root@s2 percona]# pwd /var/lib/mysql/percona [root@s2 percona]# cp -r /tmp/export/enc_EI_test.ibd . [root@s2 percona]# cp -r /tmp/export/enc_EI_test.cf* . [root@s2 percona]# ls -lrth total 14M -rw-r-----. 1 mysql mysql 8.5K Mar 20 21:08 enc_EI_test.frm -rw-r-----. 1 root root 13M Mar 20 21:12 enc_EI_test.ibd -rw-r-----. 1 root root 100 Mar 20 21:12 enc_EI_test.cfp -rw-r-----. 1 root root 455 Mar 20 21:12 enc_EI_test.cfg |
Make sure that you are copying the .cfp file as well. Without the .cfp file, the import will not work, and you will get the following error.
1 2 | mysql> alter table enc_EI_test import tablespace; ERROR 1808 (HY000): Schema mismatch (Table is in an encrypted tablespace, but the encryption meta-data file cannot be found while importing.) |
Step 7 (Change ownership to MySQL user):
1 2 3 4 5 6 7 | [root@s2 percona]# chown -R mysql:mysql enc_EI_test.ibd enc_EI_test.cf* [root@repl percona]# ls -lrth total 14M -rw-r-----. 1 mysql mysql 8.5K Mar 20 21:08 enc_EI_test.frm -rw-r-----. 1 mysql mysql 13M Mar 20 21:12 enc_EI_test.ibd -rw-r-----. 1 mysql mysql 100 Mar 20 21:12 enc_EI_test.cfp -rw-r-----. 1 mysql mysql 455 Mar 20 21:12 enc_EI_test.cfg |
Step 8 (Import the tablespace):
Now, we are good to run the import command.
1 2 3 4 5 6 7 8 9 10 | mysql> alter table enc_EI_test import tablespace; Query OK, 0 rows affected (0.08 sec) mysql> select count(*) from enc_EI_test; +----------+ | count(*) | +----------+ | 100000 | +----------+ 1 row in set (0.03 sec) |
This process is quite similar to the normal InnoDB tablespace export/import process. But, here you need to take care of the following two things:
- You need to copy the .cfp file as well to the destination servers.
- Your destination table also needs to be configured with encryption (ENCRYPTION = Y).
