5

Importing an Encrypted InnoDB Tablespace into MySQL

 3 years ago
source link: https://www.percona.com/blog/2021/03/24/importing-an-encrypted-innodb-tablespace-into-mysql/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
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. 
Shell
mysql> select @@version, @@version_comment\G
*************************** 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_test\G  
*************************** 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:

Shell
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. 

Shell
-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.

Shell
[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.

Shell
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.

Shell
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.

Shell
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. 

Shell
[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. 

Shell
[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.

Shell
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):

Shell
[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.

Shell
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).

Learn about Percona Distribution for MySQL, an enterprise-grade solution for your most critical business applications.


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK