Transportable tablespaces in InnoDB

Introduction

Transportable tablespace is a mechanism to copy and move large data very easily and efficiently between different MySQL instances.

Transportable tablespace is faster than any other mechanism, such as importing and exporting, because the files containing the actual data are just copied to the target location using traditional UNIX commands such as cp locally or scp between servers.

This tool fits very well in data migration processes in the following context:

• Moving data from a transactional database into a data warehouse database
• Moving data from a staging database into a data warehouse database
• Moving data from a data warehouse to a data mart
• Archiving historical data
• Moving data between data centers in different physical locations
• Moving data files to a new volume when the actual volume is full

In this article, I'll explain the required steps to move data tables using the transportable tablespace mechanism, so you can apply this walk-through in real world scenarios.

Demo

In this part of the article, we’re going to explain transportable tablespaces by executing a walk-through. In order to make the explanation simpler, we’re going to create two databases in the same server and move some data files between these databases. 

Notice that this mechanism requires that the configuration parameter innodb_file_per_table be on.

The strategy for data migration using the transportable tablespace mechanism is:

• In the source database:
  • Flush and lock the tables to be imported. FLUSH TABLE <table_name> FOR EXPORT
  • Copy the .cfg and .idb files into a temporary location. Unix command cp
  • Unlock the tables. UNLOCK <table_name>
  • In the target database:
    • Create the target table. CREATE TABLE <table_name>
    • Discard the tablespace to remove .idb file. ALTER TABLE <table_name> DISCARD TABLESPACE
    • Copy the .cfg and .idb files from the temporary location to the target database. UNIX command cp
    • Import the tablespace. ALTER TABLE <table_name> IMPORT TABLESPACE

Let’s start by creating both source and target databases as shown in the listing 01.

mysql> CREATE DATABASE db_source;

mysql> CREATE DATABASE db_target;

Listing 01

Now let’s create the table testing in the database db_source, and populate it with some data as shown in listing 02.

mysql> USE db_source;
 
db_source> CREATE TABLE testing (
 id INT PRIMARY KEY,
 payload VARCHAR(10)
) ENGINE=innodb;
 
db_source> INSERT INTO testing VALUES (1, 'one'), (2, 'two');

Listing 02

Now let’s create the table structure on the target database db_target with the same definition of the source one as shown in the listing 03.

mysql> USE db_target;
 
db_target> CREATE TABLE testing (
 id INT PRIMARY KEY,
 payload VARCHAR(10)
) ENGINE=innodb;

Listing 03

Before copying the data files, we need to discard the tablespace of the table testing on the target database, so the new one can be imported as shown in the listing 04.

db_target> ALTER TABLE testing DISCARD TABLESPACE;

Listing 04

We can validate this step as shown in the listing 05.

$sudo ls -alh /var/lib/mysql/db_target
 
total 24K
drwxr-x---  2 mysql mysql 4.0K May 11 14:19 .
drwxr-x--- 13 mysql mysql 4.0K May 11 14:08 ..
-rw-r-----  1 mysql mysql   65 May 11 14:08 db.opt
-rw-r-----  1 mysql mysql 8.4K May 11 14:11 testing.frm

Listing 05

Now let’s start the migration process from the database db_source as shown in the listing 06.

db_source> FLUSH TABLE testing FOR EXPORT;

Listing 06

At this moment, the table testing is read-only, so we can copy the underlying data files from the source database to the target database using the traditional UNIX command cp.

While the table testing is locked, we need to copy the .ibd and .cfg files. The .frm file does not need to be copied. In recent versions of MySQL we don’t need to copy the .cfg file in order for the table structure to be discovered.

Remember that the .cfg file contains the InnoDB data dictionary’; that is, the meta-data required to understand the data in the .ibd file. The .cfg file records how many columns, what data types, and what indexes exist.

We may need to execute the command cp as sudo because the MySQL data directory is only accessed by the user mysql as shown in the listing 07.

$ sudo cp /var/lib/mysql/db_source/testing.{ibd,cfg} /var/lib/mysql/db_target

Listing 07

Remember to change the ownership of the data files in the target database to the user mysql as shown in the listing 08. We can also verify that the new data files are copied correctly using the command ls.

$ sudo chown mysql:mysql -R /var/lib/mysql/db_target
 
$ sudo ls -alh /var/lib/mysql/db_target
 
total 124K
drwxr-x---  2 mysql mysql 4.0K May 11 14:25 .
drwxr-x--- 13 mysql mysql 4.0K May 11 14:08 ..
-rw-r-----  1 mysql mysql   65 May 11 14:08 db.opt
-rw-r-----  1 mysql mysql  404 May 11 14:25 testing.cfg
-rw-r-----  1 mysql mysql 8.4K May 11 14:11 testing.frm
-rw-r-----  1 mysql mysql  96K May 11 14:25 testing.ibd

Listing 08

Once the data files are copied, then we can unlock the testing table in the db_source database to continue recording data on it as shown in the listing 09.

db_source> UNLOCK TABLES;

Listing 09

Now we’re ready to import the data files into the target database as shown in the listing 10.

db_target> ALTER TABLE testing IMPORT TABLESPACE;

Listing 10

Let’s verify that the migration process was successfully by printing the rows of the testing table in the target database as shown in the listing 11.

db_target> SELECT * FROM testing;
 
+----+---------+
| id | payload |
+----+---------+
|  1 | one     |
|  2 | two     |
+----+---------+
2 rows in set (0.00 sec)

Listing 11

Conclusion

In this article, I've explained the idea and benefits of the transportable tablespace mechanism in InnoDB as an alternative for moving large dataset between MySQL instances.

I’ve also illustrated the concept through a common real-world scenario. Now you can start using this amazing feature in your own database environment.