How to speed up MySQL database migration process for multiple databases?

[Tom01_Wonder]

Current Migration Setup:

• Source server contains 200 MySQL databases with the same schema but different content.
• Need to transfer all databases to the new hosting provider.
• Both servers are behind a VPN, so direct connection is not possible.

Current Migration Method:

For export, I use this command for each database:

mysqldump --single-transaction -u admin -p --add-drop-database --databases mydb_01 | pv | gzip -9 > mydb_01.sql.gz

For import on the destination server:

gunzip < mydb_01.sql.gz | mysql -u admin -p

Performance Results:

• Export time: approximately 1.5 hours.
• Compressed files total size: around 2.5GB.
• Import time: 7.5 hours total.

The Issue:
We need to minimize downtime for our live application. The current process takes too long, and we can only afford a maximum of 4 hours of maintenance window.

What are some techniques or alternative approaches that could help reduce the migration time significantly? Are there any parallel processing methods or different tools that work better for bulk database transfers?

[liamj]

Percona Toolkit’s pt-table-sync has saved me tons of time in these situations. Set up replication between servers first, then use pt-table-sync for final consistency checks during your maintenance window.

What worked great for us was batching by database size. We grabbed the biggest 20-30 databases and migrated those separately with parallel streams, then bundled the smaller ones together. Way better control over timing.

For transfer bottlenecks, try AWS DataSync if you’re moving to cloud. It handles compression and parallel transfers much better than manual rsync. We cut our migration window from 8 hours down to under 3 hours with about 150 databases this size.

[AdventurousHiker17]

xtrabackup might be the way to go! i heard it’s much faster than mysqldump for big databases. Also, consider running several exports in parallel; doing like 4-5 at a time could really speed things up. But just watch out for import speed - that’s usually a choke point.

[Samuel87]

Try mydumper instead of mysqldump. It’s built for parallel processing and will slash your export times. With 200 databases, mydumper can run multiple threads at once - should cut that 1.5 hour window way down. Myloader (mydumper’s partner) does parallel imports too. Here’s another idea: do a two-phase migration. Copy databases during off-peak hours while your app keeps running, then do a quick incremental sync during maintenance using binary log replication. You’ll only need minutes for the actual cutover instead of hours. For file transfers, use rsync with compression rather than moving individual gzipped files - much faster.