Import Large MySQL Database: Optimization and Speed Tips

Problems with Large Dump Imports

Importing 1 GB+ dumps via standard mysql < dump.sql often hangs or runs very slowly. Causes: small max_allowed_packet, slow InnoDB writes, too many individual transactions, timeouts.

Optimize MySQL Before Import

mysql -u root -p -e "
SET GLOBAL max_allowed_packet = 1073741824;
SET GLOBAL innodb_flush_log_at_trx_commit = 2;
SET GLOBAL innodb_buffer_pool_size = 2147483648;"

Import with Speed Optimizations

# With progress bar
apt install pv
pv dump.sql.gz | gunzip | mysql -u root -p mydb

Disable Checks for Faster Import

mysql -u root -p mydb -e "
SET FOREIGN_KEY_CHECKS=0;
SET AUTOCOMMIT=0;
SOURCE /path/to/dump.sql;
COMMIT;
SET FOREIGN_KEY_CHECKS=1;"

mydumper/myloader — Parallel Backup and Import

sudo apt install mydumper
mydumper -u root -p password -B mydb -t 4 -c -o /backup/dump/
myloader -u root -p password -B mydb -t 4 -d /backup/dump/