Fast Migration of Millions of MySQL Rows: Step-by-Step Guide

Using mysqldump works for small datasets, but handling tables with millions of rows requires a different approach. The following procedure demonstrates how to migrate a large MySQL table efficiently.

Modify my.cnf to disable secure_file_priv:

[mysqld]
secure_file_priv=''

Export the data to a file on the source server:

SELECT * FROM t_message_firebase INTO OUTFILE '/data/t_message_firebase.dat';

Compress the exported file:

zip t_message_firebase.dat.zip /data/t_message_firebase.dat

Transfer the compressed file to the destination server (e.g., using scp or another file‑transfer tool).

Decompress the file on the destination server:

unzip t_message_firebase.dat.zip

Import the data into the target table:

LOAD DATA INFILE '/data/t_message_firebase.txt' INTO TABLE t_message_firebase;

If the import fails with the error "ERROR 1261 (01000): Row 1 doesn't contain data for all columns", it is caused by the server running in STRICT_TRANS_TABLES mode. To resolve this, remove the strict mode setting:

-- Check current sql_mode
SHOW VARIABLES LIKE 'sql_mode';

-- Temporarily disable strict mode for the session
SET sql_mode='';

References:

https://www.cnblogs.com/zst062102/p/13129178.html https://my.oschina.net/u/1018607/blog/857728