Fast Database Migration Methods: Logical, File, and Physical Approaches with MySQL

When a database needs to be moved to a more powerful server, quickly migrating the data becomes essential. This guide explores three migration strategies for MySQL: logical migration, file migration, and physical migration.

Logical migration converts data and schema into an SQL dump, typically using mysqldump . After generating 50,000 test rows with a stored procedure, the dump is created and imported on the target server. Optimisation flags such as --add-locks=0 , --single-transaction and --set-gtid-purged=OFF can reduce I/O, though the performance gain is minimal for large tables.

-- 1. Prepare table
create table s1(
  id int,
  name varchar(20),
  gender char(6),
  email varchar(50)
);

-- 2. Stored procedure for bulk insert
delimiter $$
create procedure auto_insert1()
BEGIN
  declare i int default 1;
  while(i<50000) do
    insert into s1 values(i,'shanhe','male',concat('shanhe',i,'@helloworld'));
    set i=i+1;
  END WHILE;
END$$
delimiter ;

call auto_insert1();

Using mysqldump -h172.17.16.2 -uroot -pTest123! s1 s1 --result-file=/opt/s1.sql creates the dump, which is then sourced on the destination server.

mysql> use s2;
Database changed

mysql> source /opt/s1.sql

File migration exports data directly to a file with SELECT * FROM s1 INTO OUTFILE '/var/lib/mysql-files/1.txt' , which is much faster than mysqldump. The file can be loaded into another database using LOAD DATA INFILE '/var/lib/mysql-files/1.txt' INTO TABLE s3.s1 . Note that the target table must exist beforehand and the MySQL server must allow the directory via secure_file_priv .

SELECT * FROM s1 INTO OUTFILE '/var/lib/mysql-files/1.txt';

LOAD DATA INFILE '/var/lib/mysql-files/1.txt' INTO TABLE s3.s1;

Physical migration copies the raw InnoDB files. After stopping the server, the data directory s1 is copied to a new directory s4 , permissions are fixed, and the server is restarted. Because InnoDB tablespaces must be registered in the data dictionary, the target database needs a matching table definition, the original tablespace is discarded, and the new tablespace is imported.

# cp -r s1 s4
# chown -R mysql.mysql s4
# systemctl restart mysqld

CREATE TABLE s1 (
  id int,
  name varchar(20),
  gender char(6),
  email varchar(50)
) ENGINE=InnoDB;

ALTER TABLE t1.s1 DISCARD TABLESPACE;
FLUSH TABLE s1 FOR EXPORT;
ALTER TABLE s1 IMPORT TABLESPACE;

Physical migration offers the best performance for very large datasets but is complex and error‑prone.

Conclusion – Logical migration with mysqldump is suitable for small to medium datasets; file migration provides a faster logical‑style alternative when table structures are pre‑created; physical migration is reserved for massive tables where downtime is acceptable and careful handling of InnoDB files is possible.