Copying Tables Between Databases and Servers

With MariaDB it's very easy to copy tables between different MariaDB databases and different MariaDB servers. This works for tables created with the Archive, Aria, CSV, InnoDB, MyISAM, MERGE, and XtraDB engines.

The normal procedures to copy a table is:

The table files can be found in datadir/databasename (you can executeSELECT @@datadir to find the correct directory). When copying the files, you should copy all files with the same table_name + various extensions. For example, for an Aria table of name foo, you will have files foo.frm, foo.MAI, foo.MAD and possibly foo.TRG if you have triggers.

If one wants to distribute a table to a user that doesn't need write access to the table and one wants to minimize the storage size of the table, the recommended engine to use is Aria or MyISAM as one can pack the table with aria_pack or myisampack respectively to make it notablly smaller. MyISAM is the most portable format as it's not dependent on whether the server settings are different. Aria and InnoDB require the same block size on both servers.

Copying Tables When the MariaDB Server is Down

The following storage engines support export without FLUSH TABLES ... FOR EXPORT, assuming the source server is down and the receiving server is not accessing the files during the copy.

Copying Tables Live From a Running MariaDB Server

For all of the above storage engines (Archive, Aria, CSV, MyISAM and MERGE), one can copy tables even from a live server under the following circumstances:

• You have done a FLUSH TABLES or FLUSH TABLE table_name for the specific table.

• The server is not accessing the tables during the copy process.

The advantage of is that the table is read locked until is executed.

Warning: If you do the above live copy, you are doing this on your own risk as if you do something wrong, the copied table is very likely to be corrupted. The original table will of course be fine.

An Efficient Way to Give Someone Else Access to a Read Only Table

If you want to give a user access to some data in a table for the user to use in their MariaDB server, you can do the following:

First let's create the table we want to export. To speed up things, we create this without any indexes. We use TRANSACTIONAL=0 ROW_FORMAT=DYNAMIC for Aria to use the smallest possible row format.

Then we pack it and generate the indexes. We use a big sort buffer to speed up generating the index.

The procedure for MyISAM tables is identical, except that doesn't have the --ignore-control-file option.

Copying InnoDB's 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. See for more information.

Importing Tables

Tables that use most storage engines are immediately usable when their files are copied to the new .

However, this is not true for tables that use . InnoDB tables have to be imported with . See for more information.

See Also

• - Compressing the MyISAM data file for easier distribution.

• - Compressing the Aria data file for easier distribution

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