Copy an InnoDB Table Between MySQL Servers Without a Dump

A logical dump exports every row and inserts it again on the receiving server. For a large, standalone InnoDB table, MySQL can instead detach and import its file-per-table tablespace. The table definition is recreated at the destination, while the existing data and indexes travel as an .ibd file.

This is a physical copy, not a live migration: writes to the source table pause while its tablespace is exported and copied. The procedure below targets MySQL 8.4 and deliberately excludes encrypted, partitioned, foreign-key, and FULLTEXT tables, which need additional handling.

Check Compatibility First

Run this query on both servers and compare the results. Use the same MySQL release and InnoDB page size. The destination must support file-per-table tablespaces; matching the default row format also avoids surprises when the table definition leaves it implicit.

SELECT
    VERSION() AS mysql_version,
    @@innodb_page_size AS page_size,
    @@innodb_file_per_table AS file_per_table,
    @@innodb_default_row_format AS default_row_format,
    @@datadir AS data_directory;

The global innodb_file_per_table setting controls new table creation. It does not move an existing table out of a shared tablespace. Check the source table itself:

SELECT NAME, SPACE_TYPE
FROM INFORMATION_SCHEMA.INNODB_TABLES
WHERE NAME = 'app/events';

For the workflow here, SPACE_TYPE must be Single. Confirm that the table is not partitioned and has no foreign keys or FULLTEXT indexes. Encrypted tablespaces need their encryption transfer files and destination keyring configured; do not use this unencrypted procedure for them.

Create the Destination Definition

On the source, obtain the exact definition, including indexes, row format, character set, and collation:

SHOW CREATE TABLE app.events\G

Create the database if needed, then run that CREATE TABLE statement unchanged on the destination. The table must have the same name and a compatible definition before importing its tablespace.

CREATE DATABASE IF NOT EXISTS app;
USE app;

-- Run the CREATE TABLE statement returned by SHOW CREATE TABLE here.

Discard the empty destination tablespace:

ALTER TABLE app.events DISCARD TABLESPACE;

This is destructive to the destination table's current data. Use a newly created receiving table, or resolve any existing-table conflict before discarding its tablespace.

Export and Copy the Files

In an interactive MySQL session on the source, start the export:

FLUSH TABLES app.events FOR EXPORT;

MySQL flushes the table and creates events.cfg, which carries metadata used to validate the import. The statement also blocks writes to this table. Keep the session open while copying; disconnecting releases the export lock and removes the metadata file. Reads can continue after the lock is acquired.

In a separate shell, copy both files to the destination data directory. Replace the hosts and paths with the values from @@datadir:

scp /var/lib/mysql/app/events.{ibd,cfg} \
    mysql-destination:/var/lib/mysql/app/

Wait for the transfer to finish successfully. Then return to the original source session and release the lock:

UNLOCK TABLES;

The copied files represent the source table at export time. Changes made after unlocking are not included. If the network transfer would extend the write pause, first copy both files to a secure local staging directory while the lock is held, unlock after that local copy completes, and transfer the staged copies afterward. Never resume copying the live .ibd after unlocking.

Import the Tablespace

On the destination host, give the MySQL service account ownership of the imported files. The account may differ from mysql on your system:

chown mysql:mysql /var/lib/mysql/app/events.{ibd,cfg}

Then attach the tablespace to the prepared table on the destination:

ALTER TABLE app.events IMPORT TABLESPACE;

SELECT * FROM app.events LIMIT 10;

Check representative rows and application queries before switching consumers. If practical, compare row counts with a count captured from the source while its export lock was held. A successful sample query confirms that the table is readable; it does not copy triggers, grants, routines, events, or other database objects.

When This Is Not a Fit

Foreign-key-related tables need a coordinated export, and importing files does not validate the relationships. Partitioned tables use separate tablespaces per partition. FULLTEXT indexes are not supported by the export operation used here. Encrypted tablespaces require the corresponding .cfp transfer file and compatible encryption keys. Follow the matching MySQL procedure for these cases.

The destination must be compatible with the source's MySQL release, page size, table definition, and tablespace features. If filesystem access is unavailable, versions or settings are incompatible, or writes cannot pause for a consistent file copy, use a logical export or a migration method that captures ongoing changes instead.

References

• [InnoDB Import]MySQL 8.4 Reference Manual: Importing InnoDB Tables.
• [FLUSH]MySQL 8.4 Reference Manual: FLUSH Statement.
• [File-Per-Table]MySQL 8.4 Reference Manual: File-Per-Table Tablespaces.