Copying Tables Between Different MariaDB Databases and MariaDB Servers - Source

Revision: 127956
User: Michael Widenius
Date: 2023-07-22 12:00

The normal procedures to copy a table is:

<<code>>
FLUSH TABLES db_name.table_name FOR EXPORT

# Copy the relevant files associated with the table

UNLOCK TABLES;
<</code>>

The table files can be found in [[server-system-variables#datadir|datadir]]/databasename (you can execute
##SELECT @@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.

<<style class="darkheader-nospace-borders">>
|= Engine |= Comment
| [[archive|Archive]] ||
| [[aria|Aria]] | Requires clean shutdown. Table will automatically be fixed on the receiving server if ##aria_chk --zerofill## was not run.  If ##aria_chk --zerofill## is run, then the table is immediately usable without any delays|
| [[csv|CSV]] ||
| [[myisam|MyISAM]] ||
| [[merge|MERGE]] | .MRG files can be copied even while server is running as the file only contains a list of tables that are part of merge. |
<</style>>

== 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 [[flush-tables-for-export|FLUSH TABLES table_name FOR EXPORT]] is that the table is read locked until [[/lock-tables-and-unlock-tables|UNLOCK TABLES]] 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.

<<code>>
CREATE TABLE new_table ... ENGINE=ARIA TRANSACTIONAL=0;
ALTER TABLE new_table DISABLE_KEYS;
# Fill the table with data:
INSERT INTO new_table SELECT * ...
FLUSH TABLE new_table WITH READ LOCK;

# Copy table data to some external location, like /tmp with something
# like cp /my/data/test/new_table.* /tmp/

UNLOCK TABLES;
<</code>>

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

<<code>>
> ls -l /tmp/new_table.*
-rw-rw---- 1 mysql my 42396148 Sep 21 17:58 /tmp/new_table.MAD
-rw-rw---- 1 mysql my     8192 Sep 21 17:58 /tmp/new_table.MAI
-rw-rw---- 1 mysql my     1039 Sep 21 17:58 /tmp/new_table.frm
> aria_pack /tmp/new_table
Compressing /tmp/new_table.MAD: (922666 records)
- Calculating statistics
- Compressing file
46.07%
> aria_chk -rq --ignore-control-file --sort_buffer_size=1G /tmp/new_table
Recreating table '/tmp/new_table'
- check record delete-chain
- recovering (with sort) Aria-table '/tmp/new_table'
Data records: 922666
- Fixing index 1
State updated
> ls -l /tmp/new_table.*
-rw-rw---- 1 mysql my 26271608 Sep 21 17:58 /tmp/new_table.MAD
-rw-rw---- 1 mysql my 10207232 Sep 21 17:58 /tmp/new_table.MAI
-rw-rw---- 1 mysql my     1039 Sep 21 17:58 /tmp/new_table.frm
<</code>>

The procedure for MyISAM tables is identical, except that
[[myisamchk]] 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 [[innodb-file-per-table-tablespaces/#copying-transportable-tablespaces|Copying Transportable Tablespaces]] for more information.

== Importing Tables

Tables that use most storage engines are immediately usable when their files are copied to the new ##[[server-system-variables#datadir|datadir]]##.

However, this is not true for tables that use [[innodb|InnoDB]]. InnoDB tables have to be imported with [[alter-table/#import-tablespace|ALTER TABLE ... IMPORT TABLESPACE]]. See [[innodb-file-per-table-tablespaces/#copying-transportable-tablespaces|Copying Transportable Tablespaces]] for more information.

== See Also

* [[flush-tables-for-export|FLUSH TABLES FOR EXPORT]]
* [[flush|FLUSH TABLES]]
* [[myisampack]] - Compressing the MyISAM data file for easier distribution.
* [[aria_pack]] - Compressing the Aria data file for easier distribution
* [[mariadb-dump]] - Copying tables to other SQL servers. You can use the ##--tab## to create a CSV file of your table content.

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.

Copying Tables Between Different MariaDB Databases and MariaDB Servers - Source

Products

Services

Pricing

Resources

About Us

Download MariaDB