How to Migrate Millions of MySQL Records Efficiently: Practical Strategies

Background

Last month we built a WeChat mini‑program that quickly attracted over one million users. We added a formid collection point for sending template messages, and as data grew the original server space became insufficient. We purchased a 4‑core 8 GB machine and began migrating the data to a new backend framework.

Solution Choices

mysqldump migration

We commonly use mysqldump to export a SQL file and import it into the new database. Exporting a million‑row database produces a ~1 GB file in a few minutes; copying it via scp also takes minutes. However importing with the source command on the new server kept the CPU at 100 % and did not finish even after a night.

Script migration

Direct command‑line export/import is convenient but slow for large data and heavy on the server. If migration time is not critical, writing scripts is an option. Two script ideas were considered:

First method : Run a migration script on the target server that remotely connects to the source, reads data in chunks based on query conditions, and writes to the target. This is essentially synchronous, but can be parallelized with multiple threads.

Second method : Use Redis as a “producer‑consumer” pipeline. The source runs a multithreaded producer script that pushes rows into a Redis queue; the target runs a multithreaded consumer script that pulls from the queue and inserts into the target database, allowing simultaneous export and import.

Both can be implemented in Go to leverage its native concurrency.

Third method : Stop MySQL, copy the raw data files from the data directory to the new machine via scp, then start MySQL. This works reliably for us.

File migration

After evaluating the options, we chose MySQL’s built‑in import/export commands.

select data into outfile file.txt
load data infile file.txt into table

Migration Process

Export data from the source database:

select * from dc_mp_fans into outfile '/data/fans.txt';

Copy the data file to the target server:

zip fans.zip /data/fans.txt
scp fans.zip root@ip:/data/

Import the file on the target database:

unzip /data/fans.zip
load data infile '/data/fans.txt' into table wxa_fans(id,appid,openid,unionid,@dummy,created_at,@dummy,nickname,gender,avatar_url,@dummy,@dummy,@dummy,@dummy,language,country,province,city,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy);

These steps complete a million‑row table migration in a few minutes.

Important notes

MySQL security settings: the secure_file_priv variable must be empty to allow LOAD DATA INFILE and SELECT … INTO OUTFILE. Set it in /etc/my.cnf:

[mysqld]
secure_file_priv=''

Field mismatches: When importing, specify column names and use @dummy to discard unwanted columns.

Source table dc_mp_fans and target table wxa_fans have different column orders; matching by name resolves this.

Conclusion

For small datasets, mysqldump is simple and effective.

For large datasets with patience, custom scripts with parallelism can be used, though they require more coding effort.

For large datasets needing fast migration, MySQL’s file‑based import/export is the most efficient.

Source: https://idoubi.cc/posts/mysql-data-migration/