When InnoDB needs to store general information relating to the system as a whole, rather than a specific table, the specific file it writes to is the system tablespace. By default, this is the ibdata1 file located in the data directory, (as defined by either the datadir or innodb_data_home_dir system variables). InnoDB uses the system tablespace to store the data dictionary, change buffer, and undo logs.

You can define the system tablespace filename or filenames, size and other options by setting the innodb_data_file_path system variable. This system variable can be specified as a command-line argument to mariadbd or it can be specified in a relevant server option group in an option file:

This system variable defaults to the file ibdata1, and it defaults to a minimum size of 12M, and it defaults with the autoextend attribute enabled.

Changing Sizes

InnoDB defaults to allocating 12M to the ibdata1 file for the system tablespace. While this is sufficient for most use cases, it may not be for all. You may find after using MariaDB for a while that the allocation is too small for the system tablespace or it grows too large for your disk. Fortunately, you can adjust this size as need later.

Increasing the Size

When setting the system variable, you can define a size for each file given. In cases where you need a larger system tablespace, add the autoextend option to the last value.

Under this configuration, when the last system tablespace grows beyond the size allocation, InnoDB increases the size of the file by increments. To control the allocation increment, set the system variable.

Decreasing the Size

MariaDB starting with

From , when MariaDB starts up, unused InnoDB tablespace can be reclaimed, reducing the file size (). This is disabled by default and is enabled by adding the :autoshrink attribute to the system variable, e.g.:

Alternatively, starting with , the shrinking can be set to happen during a slow shutdown (e.g. after SET GLOBAL innodb_fast_shutdown=0) (). Technically, how this works is:

1. Find the last used extent in the system tablespace by iterating through the BITMAP in the extent descriptor page.

2. Check whether the tablespace is being used within fixed size, and if the last used extent is less than the fixed size, then set the desired target size to fixed size.

3. Flush all pages belonging to the system tablespace in flush list.

4. Truncate the truncated pages from FSP_FREE and FSP_FREE_FRAG list.

MariaDB until

In cases where the InnoDB system tablespace has grown too large, before , the process to reduce it in size is a little more complicated than increasing the size. MariaDB does not allow you to remove data from the tablespace file itself. Instead you need to delete the tablespace files themselves, then restore the database from backups. The backup utility produces backup files containing the SQL statements needed to recreate the database. As a result, it restores a database with the bare minimum data rather than any additional information that might have built up in the tablespace file. Use mariadb-dump to backup all of your InnoDB database tables, including the system tables in the mysql database that use InnoDB. You can find out what they are using the Information Schema.

If you only use InnoDB, you may find it easier to back up all databases and tables.

Then stop the MariaDB Server and remove the InnoDB tablespace files. In the data directory or the InnoDB data home directory, delete all the ibdata and ib_log files as well as any file with an .ibd or .frm extension. Once this is done, restart the server and import the dump file:

Using Raw Disk Partitions

Instead of having InnoDB write to the file system, you can set it to use raw disk partitions. On Windows and some Linux distributions, this allows you to perform non-buffered I/O without the file system overhead. Note that in many use cases this may not actually improve performance. Run tests to verify if there are any real gains for your application usage.

To enable a raw disk partition, first start MariaDB with the newraw option set on the tablespace:

When the MariaDB Server starts, it initializes the partition. Don't create or change any data, (any data written to InnoDB at this stage are lost on restart). Once the server has successful started, stop it then edit the configuration file again, changing the newraw keyword to raw.

When you start MariaDB again, it'll read and write InnoDB data to the given disk partition instead of the file system.

Raw Disk Partitions on Windows

When defining a raw disk partition for InnoDB on the Windows operating system, use the same procedure as defined above, but when defining the path for the system variable, use ./ at the start:

The given path is synonymous with the Windows syntax for accessing the physical drive.

System Tables within the InnoDB System Tablespace

InnoDB creates some system tables within the InnoDB System Tablespace:

• SYS_DATAFILES

• SYS_FOREIGN

• SYS_FOREIGN_COLS

• SYS_TABLESPACES

These tables cannot be queried. However, you might see references to them in some places, such as in the table in the database.

_{This page is licensed: CC BY-SA / Gnu FDL}

When the user creates a temporary table using the CREATE TEMPORARY TABLE statement and the engine is set as InnoDB, MariaDB creates a temporary tablespace file. When the table is not compressed, MariaDB writes to a shared temporary tablespace as defined by the innodb_temp_data_file_path system variable. MariaDB does not allow the creation of ROW_FORMAT=COMPRESSED temporary tables. All temporary tables are uncompressed. MariaDB deletes temporary tablespaces when the server shuts down gracefully and is recreated when it starts again. It cannot be placed on a raw device.

Internal temporary tablespaces, (that is, temporary tables that cannot be kept in memory) use either Aria or MyISAM, depending on the aria_used_for_temp_tables system variable. You can set the default storage engine for user-created temporary tables using the default_tmp_storage_engine system variable.

Prior to , temporary tablespaces existed as part of the InnoDB system tablespace or were file-per-table depending on the configuration of the innodb_file_per_table system variable.

Syntax for the value of the innodb_temp_data_file_path variable

The options for is one path or a set of paths, separated by ';'.

The syntax for each path is:

size can have extensions 'G' (Gigabytes), 'M' (Megabytes) or 'K' (Kilobytes). If no extension is given, then megabytes is assumed.

• The first size argument is the initial size of the temporary table space.

• autoextend means that the file size will automatically increase if needed.

• max can be used to limit the total size of the temporary file if autoextend is used.

Only the last path can have the autoextend , max and autoshrink options.

Sizing Temporary Tablespaces

In order to size temporary tablespaces, use the system variable. This system variable can be specified as a command-line argument to or it can be specified in a relevant server in an :

This system variable's syntax is the same as the system variable. That is, a file name, size and option. By default, it writes a 12MB autoextending file to ibtmp1 in the data directory.

To increase the size of the temporary tablespace, you can add a path to an additional tablespace file to the value of the system variable. Providing additional paths allows you to spread the temporary tablespace between multiple tablespace files. The last file can have the autoextend attribute, which ensures that you won't run out of space:

Unlike normal tablespaces, temporary tablespaces are deleted when you stop MariaDB. To shrink temporary tablespaces to their minimum sizes, restart the server.

Shrinking the Tablespace

From , the temporary tablespace can be shrunk by setting to ON:

_{This page is licensed: CC BY-SA / Gnu FDL}

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. Tablespace files contain both the data and indexes.

When innodb_file_per_table=ON is set, InnoDB uses one tablespace file per InnoDB table. These tablespace files have the .ibd extension. When innodb_file_per_table=OFF is set, InnoDB stores all tables in the InnoDB system tablespace.

InnoDB versions in MySQL 5.7 and above also support an additional type of tablespace called general tablespaces that are created with CREATE TABLESPACE. However, InnoDB versions in MariaDB Server do not support general tablespaces or CREATE TABLESPACE.

File-Per-Table Tablespace Locations

By default, InnoDB's file-per-table tablespaces are created in the system's data directory, which is defined by the system variable. The system variable will not change the location of file-per-table tablespaces.

In the event that you have a specific tablespace that you need stored in a dedicated path, you can set the location using the table option when you create the table.

For instance,

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

Note, the system user that runs the MariaDB Server process (which is usually mysql) must have write permissions on the given path.

Copying Transportable Tablespaces

InnoDB's file-per-table tablespaces are transportable, which means that you can copy a file-per-table tablespace from one MariaDB Server 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 or . In fact, this process can even be used with in some cases, such as when or when .

Copying Transportable Tablespaces for Non-partitioned Tables

You can copy the transportable tablespace of a non-partitioned table from one server to another by exporting the tablespace file from the original server, and then importing the tablespace file into the new server.

MariaDB starting with

The workflow is simplified starting from . On the source server, simply do:

On the destination server, simply do:

Exporting Transportable Tablespaces for Non-partitioned Tables

You can export a non-partitioned table by locking the table and copying the table's .ibd and .cfg files from the relevant for the table to a backup location. For example, the process would go like this:

• First, use the statement on the target table:

This forces the server to close the table and provides your connection with a read lock on the table.

• Then, while your connection still holds the lock on the table, copy the tablespace file and the metadata file to a safe directory:

• Then, once you've copied the files, you can release the lock with :

Importing Transportable Tablespaces for Non-partitioned Tables

You can import a non-partitioned table by discarding the table's original tablespace, copying the table's .ibd and .cfg files from the backup location to the relevant for the table, and then telling the server to import the tablespace.

For example, the process would go like this:

• First, on the destination server, you need to create a copy of the table. Use the same statement that was used to create the table on the original server.

• Then, use to discard the new table's tablespace:

• Then, copy the .ibd and .cfg files from the original server to the relevant directory on the target MariaDB Server.

File-per-table tablespaces can be imported with just the .ibd file in many cases. If you do not have the tablespace's .cfg file for whatever reason, then it is usually worth trying to import the tablespace with just the .ibd file.

• Then, once the files are in the proper directory on the target server, use to import the new table's tablespace:

Copying Transportable Tablespaces for Partitioned Tables

Currently, MariaDB does not directly support the transport of tablespaces from partitioned tables. See for more information about that. It is still possible to transport partitioned tables if we use a workaround. You can copy the transportable tablespaces of a partitioned table from one server to another by exporting the tablespace file of each partition from the original server, and then importing the tablespace file of each partition into the new server.

Exporting Transportable Tablespaces for Partitioned Tables

You can export a partitioned table by locking the table and copying the .ibd and .cfg files of each partition from the relevant for the partition to a backup location. For example, the process would go like this:

• First, let's create a test table with some data on the original server:

• Then, we need to export the partitioned tablespace from the original server, which follows the same process as exporting non-partitioned tablespaces. That means that we need to use the statement on the target table:

This forces the server to close the table and provides your connection with a read lock on the table.

• Then, if we grep the database directory in the data directory for the newly created t2 table, we can see a number of .ibd and .cfg files for the table:

• Then, while our connection still holds the lock on the table, we need to copy the tablespace files and the metadata files to a safe directory:

• Then, once we've copied the files, we can release the lock with :

Importing Transportable Tablespaces for Partitioned Tables

You can import a partitioned table by creating a placeholder table, discarding the placeholder table's original tablespace, copying the partition's .ibd and .cfg files from the backup location to the relevant for the placeholder table, and then telling the server to import the tablespace. At that point, the server can exchange the tablespace for the placeholder table with the one for the partition. For example, the process would go like this:

• First, we need to copy the saved tablespace files from the original server to the target server:

• Then, we need to import the partitioned tablespaces onto the target server. The import process for partitioned tables is more complicated than the import process for non-partitioned tables. To start with, if it doesn't already exist, then we need to create a partitioned table on the target server that matches the partitioned table on the original server:

• Then, using this table as a model, we need to create a placeholder of this table with the same structure that does not use partitioning. This can be done with a statement:

This statement will create a new table called t2_placeholder that has the same schema structure as t2, but it does not use partitioning and it contains no rows.

For Each Partition

From this point forward, the rest of our steps need to happen for each individual partition. For each partition, we need to do the following process:

• First, we need to use to discard the placeholder table's tablespace:

• Then, copy the .ibd and .cfg files for the next partition to the relevant directory for the t2_placeholder table on the target MariaDB Server:

File-per-table tablespaces can be imported with just the .ibd file in many cases. If you do not have the tablepace's .cfg file for whatever reason, then it is usually worth trying to import the tablespace with just the .ibd file.

• Then, once the files are in the proper directory on the target server, we need to use to import the new table's tablespace:

The placeholder table now contains data from the p0 partition on the source server.

• Then, it's time to transfer the partition from the placeholder to the target table. This can be done with an statement:

The target table now contains the first partition from the source table.

• Repeat this procedure for each partition you want to import. For each partition, we need to discard the placeholder table's tablespace, and then import the partitioned table's tablespace into the placeholder table, and then exchange the tablespaces between the placeholder table and the partition of our target table.

When this process is complete for all partitions, the target table will contain the imported data:

• Then, we can remove the placeholder table from the database:

Known Problems with Copying Transportable Tablespaces

Differing Storage Formats for Temporal Columns

added the system variable, which enables a new MySQL 5.6-compatible storage format for the , and data types.

If a file-per-tablespace file contains columns that use one or more of these temporal data types and if the tablespace file's original table was created with a certain storage format for these columns, then the tablespace file can only be imported into tables that were also created with the same storage format for these columns as the original table. Otherwise, you will see errors like the following:

See for more information.

See the pages for the , and data types to determine how to update the storage format for temporal columns in tables that were created before or that were created with .

Differing ROW_FORMAT Values

InnoDB file-per-table tablespaces can use different . A specific row format can be specified when creating a table either by setting the table option or by the setting the system variable. See for more information on how to set an InnoDB table's row format.

If a file-per-tablespace file was created with a certain row format, then the tablespace file can only be imported into tables that were created with the same row format as the original table. Otherwise, you will see errors like the following:

The error message is a bit more descriptive in and later:

Be sure to check a tablespace's row format before moving it from one server to another. Keep in mind that the default row format can change between major versions of MySQL or MariaDB. See for information on how to check an InnoDB table's row format.

See and for more information.

Foreign Key Constraints

DISCARD on a table with foreign key constraints is only possible after disabling :

IMPORT on the other hand does not enforce foreign key constraints. So when importing tablespaces, referential integrity can only be guaranteed to import all tables bound by foreign key constraint at the same time, from an EXPORT of those tables taken with the same transactional state.

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 .

See Also

_{This page is licensed: CC BY-SA / Gnu FDL}