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, a huge number of transactions, timeouts.

Optimize MySQL Before the Import

mysql -u root -p -e "
SET GLOBAL max_allowed_packet = 1073741824;  -- 1GB
SET GLOBAL net_buffer_length = 1048576;       -- 1MB
SET GLOBAL innodb_buffer_pool_size = 2147483648; -- 2GB (if the RAM is available)
SET GLOBAL innodb_log_buffer_size = 67108864; -- 64MB
SET GLOBAL innodb_flush_log_at_trx_commit = 2; -- faster, less durable"

Import with Speed Optimizations

# Standard import
mysql -u root -p mydb < dump.sql

# Compressed dump
gunzip < dump.sql.gz | mysql -u root -p mydb

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

Speeding Up by Disabling Checks

Add this at the top of the dump (or use the flags):

mysql -u root -p mydb << 'EOF'
SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;
SET AUTOCOMMIT = 0;
SOURCE /path/to/dump.sql;
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
SET UNIQUE_CHECKS = 1;
SET AUTOCOMMIT = 1;
EOF

mydumper / myloader — Parallel Backup and Import

# Installation
sudo apt install mydumper

# Create a parallel dump (4 threads)
mydumper -u root -p password -B mydb -t 4 -c -o /backup/mydb_dump/

# Parallel import
myloader -u root -p password -B mydb -t 4 -d /backup/mydb_dump/

Importing in Chunks (for Very Large Files)

# Split the dump into 100MB chunks
csplit dump.sql "/^-- Table structure/" {*}

# BigDump — a web tool for importing through the browser
# Download: https://www.ozerov.de/bigdump/

Monitoring Import Progress

# In another terminal - watch the current operation
mysql -u root -p -e "SHOW PROCESSLIST\G" | grep -i import

# Table sizes during the import
mysql -u root -p -e "
SELECT table_name,
    ROUND(data_length/1024/1024, 2) AS 'Data MB',
    ROUND(index_length/1024/1024, 2) AS 'Index MB'
FROM information_schema.tables
WHERE table_schema = 'mydb'
ORDER BY data_length DESC LIMIT 10;"

Common Errors Importing Large Dumps

Why Parallel Import Is Not Always Faster

mydumper/myloader speed up imports through parallel threads, but the gain only shows up when disk and CPU are not the bottleneck themselves. On a server with a single slow HDD, parallel threads compete for the same disk queue, and total write throughput does not increase — it can even drop due to random access instead of sequential. On NVMe drives, parallelization gives a multiplied speedup because there is no random-access penalty there.

When Import Optimizations Will Not Help

If the dump contains one huge table with several indexes, building the indexes themselves can take longer than loading the data — MySQL rebuilds secondary indexes after inserting all rows for InnoDB, and on a table with tens of millions of rows this can take hours regardless of buffer settings. In that case, temporarily dropping secondary indexes before import and recreating them afterward is the only way to speed things up, not tuning innodb_buffer_pool_size.

Verifying the Import Result

# Compare row count against the expected number (for example, from a mydumper report)
mysql -u root -p mydb -e "SELECT COUNT(*) FROM orders;"

# Check that all indexes were created and the table is not left in a Crashed state
mysql -u root -p -e "SHOW TABLE STATUS FROM mydb;" | grep -i orders

# Check the plan of a typical query — indexes should work as before
EXPLAIN SELECT * FROM orders WHERE status = 'pending' LIMIT 10;

# Check InnoDB state — there should be no unfinished transactions
SHOW ENGINE INNODB STATUS\G

The Comment column in SHOW TABLE STATUS will show an error if the table is corrupted after an interrupted import — check this first if the import ended with an error partway through the file. Creating backups that are later easy and fast to import is covered in the article on mysqldump and restore, and a lock-free hot backup for large databases is covered in the article on Percona XtraBackup.