Client Portal

Transportable Tablespace in MySQL: the definitive guide

Hey folks, everything cool, everything smooth, all good? I hope so.

Today, one more article about that darn dolphin database.

I’m going to talk about how handy TTS is, yes, TTS in MySQL (just like in Oracle, it exists in MySQL too).

⚠️ CONTAINS TEXT IMPROVED BY AI, AND THAT’S TOTALLY FINE (IF YOU KNOW HOW TO USE IT 🤭)⚠️

After banging our heads on that mess above, let’s get to it.

1. Prerequisites

  1. Check and enable the configuration needed for Transportable Tablespace to work properly.
  2. Have a MySQL 🤓
  3. Have read my previous article on TABLESPACES IN MYSQL

This little buddy checks whether InnoDB is set up to create a separate .ibd file for each table, which is essential for TTS.

SHOW VARIABLES LIKE 'innodb_file_per_table';

+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+

If you need to enable it:

Enables file-per-table mode, allowing each InnoDB table to have its own tablespace file.

SET GLOBAL innodb_file_per_table=ON;

Query OK, 0 rows affected (0.01 sec)

2. Schema example

On the source box:

Create a new database of BRABOS (haha, good one, admit it) for our tests:

CREATE DATABASE db_brabo;

Query OK, 1 row affected (0.01 sec)

Select our freshly created little DB:

USE db_brabo;

Database changed

Then we create a table so we can pretend we have data in our super, mega, master, blaster production environment:

CREATE TABLE clientes_brabo (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    data_cadastro DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

Query OK, 0 rows affected (0.01 sec)

Let’s add some data, nice and easy:

INSERT INTO clientes_brabo (nome, email) VALUES 
('Zé Brabo', 'ze@brabo.com'), -- Essa vai pro Marião
('DBA Master', 'dba@brabo.com');

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

3. Export process

Step 1: Preparing for export

This little guy is powerful and plays a crucial role when you want to move or migrate tables in MySQL using TTS:

FLUSH TABLES clientes_brabo FOR EXPORT;

Query OK, 0 rows affected (0.00 sec)

What does it do?

  1. Locks the table for writes
  2. Finishes all pending transactions
  3. Creates a .cfg file with the table metadata

Step 2: Locating the files

ls -l /var/lib/mysql/db_brabo/

-rw-r----- 1 mysql mysql  12345 Jun 10 15:30 clientes_brabo.ibd
-rw-r----- 1 mysql mysql   1024 Jun 10 15:30 clientes_brabo.cfg

Which files are we talking about?

Step 3: Backing up the files (if you’re a DBA, you’ll get it)

Copy the files to a safe place before moving on (don’t be childish, damn it):

cp /var/lib/mysql/db_brabo/clientes_brabo.{ibd,cfg} /backup/

Step 4: Releasing the table

UNLOCK TABLES;

Query OK, 0 rows affected (0.00 sec)

Releases the table on the source server after the files are copied.

4. Import process

Step 1: Preparing the target environment

CREATE DATABASE db_brabo_destino;
USE db_brabo_destino;

Creates and selects the target database.

CREATE TABLE clientes_brabo (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    data_cadastro DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

Query OK, 0 rows affected (0.00 sec)

Recreates the same structure as the original table, without data.

Step 2: Cleaning up the existing tablespace

Removes the freshly created empty tbs to prepare for the import.

ALTER TABLE clientes_brabo DISCARD TABLESPACE;

Query OK, 0 rows affected (0.00 sec)

Step 3: Transferring the files

Copies the files to the MySQL directory and fixes the permissions.

cp /backup/clientes_brabo.{ibd,cfg} /var/lib/mysql/db_brabo_destino/
chown mysql:mysql /var/lib/mysql/db_brabo_destino/clientes_brabo.*

Step 4: Final import

This command is crucial, check it out:

ALTER TABLE clientes_brabo IMPORT TABLESPACE;

Query OK, 0 rows affected (0.00 sec)

Know why? I know you don’t 🤣, here you go:

  1. Reads the .cfg file to validate the structure
  2. Imports the data from the .ibd file
  3. Rebuilds the indexes

Step 5: Verification

So let’s check the data:

SELECT * FROM clientes_brabo;

+----+------------+---------------+---------------------+
| id | nome       | email         | data_cadastro       |
+----+------------+---------------+---------------------+
|  1 | Zé Brabo   | ze@brabo.com  | 2023-06-10 15:30:00 |
|  2 | DBA Master | dba@brabo.com | 2023-06-10 15:30:00 |
+----+------------+---------------+---------------------+

5. Final validation of the schema

Let’s validate the table’s integrity:

CHECK TABLE clientes_brabo;

+-------------------+-------+----------+----------+
| Table             | Op    | Msg_type | Msg_text |
+-------------------+-------+----------+----------+
| db_brabo.clientes | check | status   | OK       |
+-------------------+-------+----------+----------+

Best practices:

  1. Always check version compatibility between the boxes/servers/machines etc.
  2. For large tables, consider compressing the files during the transfer
  3. Keep backups of the .ibd and .cfg files until you confirm it was all smooth sailing.

My final thoughts:

This process is extremely useful for:

Got the gist? Now grab your lab and go to town ON IT.

Did it go wrong? Worse than a backup without the binlog? (FFS) 😂

(Just kidding, we’ve got your back! Need anything? Just call the BRABOS!)