The Ultimate Guide to High Availability with MariaDB

InnoDB Tablespaces Overview

You are viewing an old version of this article. View the current version here.

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

In cases where you don't want to work with a backup tool like mysqldump or mariabackup, you can move or copy a tablespace file directly from one MariaDB Server instance to another.

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;

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.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.