InnoDB Tablespaces Overview

When you create a table using the InnoDB storage engine, data written to that table is stored on the file system in a data file called a tablespace. Individual tablespaces can contain data from one or more InnoDB tables as well as the associated indexes.

MariaDB uses one tablespace file per InnoDB table. You can optionally revert to the legacy behavior of storing all InnoDB table data in the system tablespace, by disabling the innodb_file_per_table system variable.

Tablespace Locations

Tables that use the InnoDB storage engine write to tablespaces stored in the data directory. By default, this is located in the path set by the datadir system variable. If you want to store InnoDB data separate from other storage engines, you set a specific path using the innodb_data_home_dir system variable.

In the event that you have a specific tablespace that you need stored in a dedicated path, you can set the location using the DATA DIRECTORY and TABLESPACE table options.

For instance,

CREATE TABLE test.t1 (
   id INT PRIMARY KEY AUTO_INCREMENT,
   name VARCHAR(50)
) ENGINE=InnoDB
TABLESPACE "names"
DATA DIRECTORY = "/data/contact";

MariaDB then creates a database directory on that path and a tablespace file for the table. On Unix-like operating systems, you can see the file using the ls command:

# ls -al /data/contact/test
drwxrwx--- 2 mysql mysql  4096 Dec 8 18:46 .
drwxr-xr-x 3 mysql mysql  4096 Dec 8 18:46 ..
-rw-rw---- 1 mysql mysql 98304 Dec 8 20:41 t2.ibd

Note, the MariaDB user, (usually mysql), must have write permissions on the given path.

Copying Tablespaces

InnoDB tablespaces are transportable. That is, you can take a tablespace file created in the data directory of one MariaDB Server and copy or move it to another server. You may find this useful in cases where you need to transport full tables between servers and don't want to use backup tools like mariabackup or mysqldump.

Exporting the Tablespace

To copy the tablespace from the existing MariaDB, first issue a FLUSH TABLES statement to ready the tablespace for export.

FLUSH TABLES test.t1 FOR EXPORT;

This sets a read lock on the table, allowing you to manually copy the tablespace file and the metadata file to another location.

# cp /data/contacts/test/t1.ibd /data/saved-tablespaces/
# cp /data/contacts/test/t1.cfg /data/saved-tablespaces/

Importing the Tablespace

Create a table to receive the new tablespace on the target server. Use the same CREATE TABLE statement as above, then follow it with an ALTER TABLE statement to discard the tablespace.

ALTER TABLE test.t1 DISCARD TABLESPACE;

Copy the .ibd and .cfg files copied above into the data directory of the target MariaDB Server instance.

# scp /data/tablespaces/t1.ibd target-server.com:/var/lib/mysql
# scp /data/tablespaces/t1.cfg target-server.com:/var/lib/mysql

On the source server, unlock the tables:

UNLOCK TABLES;

Then on the target server, import the tablespace:

ALTER TABLE test.t1 IMPORT TABLESPACE;

Copying Tablespaces of Partitioned Tables

Note that this is not currently supported for partitioned tables. See MDEV-10568.

For a workaround, see the following blog post.

Tablespace Encryption

MariaDB supports data-at-rest encryption for the InnoDB storage engine. When enabled, the Server encrypts data before writing it to the tablespace and decrypts reads from the tablespace before returning result-sets. This means that a malicious user attempting to exfiltrate sensitive data won't be able to import the tablespace onto a different server as shown above without the encryption key.

For more information on data encryption, see Encrypting Data for InnoDB.