Tutorial

How to Import and Export Large Databases Without Timeout

May 23, 2026

Tutorial May 23, 2026

A modern alternative to cPanel, Plesk and CyberPanel — isolated, secure, AI-assisted.

The Problem: Database Imports That Never Finish

You have a 5 GB MySQL dump file. You upload it through phpMyAdmin, wait 45 minutes, and get a blank screen or a cryptic timeout error. You try again with a smaller chunk, only to discover the import is partially complete and your data is now inconsistent. Sound familiar?

Large database imports fail for predictable reasons: PHP execution time limits, MySQL packet size restrictions, connection timeouts, and insufficient memory. The good news is that every single one of these bottlenecks has a solution. With the right tools and settings, you can import and export multi-gigabyte databases reliably and efficiently.

This guide covers optimized export techniques with mysqldump, compression strategies that reduce transfer times by 80%, parallel dump and restore with mydumper/myloader, progress monitoring, and the server settings you need to adjust before attempting large imports. By the end, you will have a reliable pipeline for moving databases of any size.

Why Database Imports Time Out

Before fixing the problem, you need to understand what causes it. Multiple timeout and size limits create a gauntlet that large imports must survive:

Setting	Default	What It Controls	Impact
`max_execution_time` (PHP)	30s	PHP script runtime	Kills phpMyAdmin imports
`upload_max_filesize` (PHP)	2M	Maximum file upload size	Blocks large file uploads
`post_max_size` (PHP)	8M	Maximum POST data size	Limits upload payload
`max_allowed_packet` (MySQL)	64M	Maximum query/packet size	Fails on large INSERT batches
`wait_timeout` (MySQL)	28800s	Idle connection timeout	Drops idle connections
`net_read_timeout` (MySQL)	30s	Network read wait	Fails on slow transfers
`innodb_log_file_size`	48M	Redo log capacity	Slows large transactions

The fundamental mistake: phpMyAdmin is a web application bound by PHP limits. Importing a 2 GB database through a tool designed for 2 MB uploads is like trying to move a house through a garden gate. Use the MySQL CLI for anything over 50 MB.

Optimized Exports with mysqldump

Essential mysqldump Flags

The default mysqldump command produces correct but unoptimized output. For large databases, these flags make a dramatic difference:

$ mysqldump \
--single-transaction \
--quick \
--routines \
--triggers \
--set-gtid-purged=OFF \
--max-allowed-packet=512M \
-u root -p mydb > mydb_backup.sql

Here is what each flag does and why it matters:

--single-transaction — Takes a consistent snapshot using a single transaction instead of locking all tables. Essential for InnoDB tables because it allows reads and writes to continue during the dump. Without this flag, large databases can lock your application out for the entire dump duration.

--quick — Fetches rows one at a time from the server instead of buffering the entire result set in memory. Without this, dumping a table with 50 million rows loads all rows into memory before writing any output. This flag is the difference between using 100 MB of RAM and 10 GB.

--routines --triggers — Includes stored procedures, functions, and triggers in the dump. These are skipped by default and are a common source of "my import is missing functionality" problems.

--set-gtid-purged=OFF — Prevents GTID (Global Transaction ID) metadata from being written to the dump. If you are not using MySQL replication, this avoids confusing warnings during import. If you are importing to a replica, leave this at the default.

--max-allowed-packet=512M — Increases the maximum size of a single SQL statement in the dump. Large BLOB columns or extended INSERT statements can exceed the default 64M limit.

Faster Imports with --extended-insert and --disable-keys

Two additional flags optimize the import speed dramatically:

$ mysqldump \
--single-transaction --quick \
--extended-insert \
--disable-keys \
--net-buffer-length=32768 \
-u root -p mydb > mydb_fast.sql

Flag	Effect	Speed Improvement
`--extended-insert`	Combines multiple rows into single INSERT statements	3-5x faster import
`--disable-keys`	Disables indexes during insert, rebuilds after	2-10x faster on indexed tables
`--net-buffer-length`	Controls the size of extended INSERT batches	Moderate improvement

Compression During Export

SQL dump files compress extremely well because they contain repetitive text (SQL keywords, column names). A 10 GB dump typically compresses to 1-2 GB, reducing storage requirements and transfer times by 80-90%.

Comparing Compression Tools

# gzip — universally available, moderate speed
mysqldump --single-transaction --quick mydb | gzip > mydb.sql.gz

# pigz — parallel gzip, uses all CPU cores
mysqldump --single-transaction --quick mydb | pigz -p 4 > mydb.sql.gz

# zstd — best ratio + speed balance (recommended)
mysqldump --single-transaction --quick mydb | zstd -T4 > mydb.sql.zst

# lz4 — fastest compression, larger output
mysqldump --single-transaction --quick mydb | lz4 > mydb.sql.lz4

Tool	Compression Ratio	Speed	CPU Usage	Best For
`gzip`	Good (85-90%)	Moderate	Single core	Universal compatibility
`pigz`	Good (85-90%)	Fast	Multi-core	Multi-core servers
`zstd`	Excellent (88-93%)	Very Fast	Multi-core	Best overall choice
`lz4`	Moderate (70-80%)	Fastest	Low	When speed matters most

Recommendation: Use zstd with -T0 (auto-detect CPU cores) for the best balance of compression ratio and speed. A 10 GB dump compresses in under 2 minutes on a modern server while achieving 90%+ compression.

Splitting Large Dumps

Sometimes you need to split a dump into manageable pieces — for transfer through limited upload channels, for partial imports, or for parallel processing.

Per-Table Dumps

# Dump each table to a separate file
TABLES=$(mysql -u root -p -N -e "SHOW TABLES" mydb)
for TABLE in $TABLES; do
mysqldump --single-transaction --quick \
mydb "$TABLE" | gzip > "mydb_${TABLE}.sql.gz"
echo "Exported: $TABLE"
done

# Import tables selectively
gunzip -c mydb_orders.sql.gz | mysql -u root -p mydb
gunzip -c mydb_products.sql.gz | mysql -u root -p mydb

Splitting by File Size

# Split a dump into 500MB chunks
split -b 500M mydb.sql mydb_part_

# Results in: mydb_part_aa, mydb_part_ab, mydb_part_ac, ...

# Reassemble and import
cat mydb_part_* | mysql -u root -p mydb

Warning: File-based splitting with split breaks SQL statements at arbitrary byte boundaries. The reassembled file works because cat reconstructs the original, but individual chunks are not valid SQL. For independent chunk imports, use per-table dumps or tools like mydumper.

Parallel Dump and Restore with mydumper/myloader

For databases over 10 GB, mydumper and myloader provide parallel dump and restore operations that can be 5-10x faster than single-threaded mysqldump:

# Install mydumper
apt install mydumper

# Parallel export with 4 threads
mydumper \
--host localhost \
--user root \
--password 'yourpass' \
--database mydb \
--threads 4 \
--compress \
--rows 500000 \
--outputdir /backup/mydb_dump

# Parallel import with 4 threads
myloader \
--host localhost \
--user root \
--password 'yourpass' \
--database mydb \
--threads 4 \
--directory /backup/mydb_dump

5-10x

Faster than single-threaded mysqldump

4-16

Parallel threads for dump/restore

mydumper creates one file per table (or per chunk if using --rows), which means myloader can import multiple tables simultaneously. This parallel processing is the key to handling 50 GB+ databases in reasonable timeframes.

Importing Large Files via MySQL CLI

The MySQL command-line client is the most reliable tool for importing large databases. Unlike phpMyAdmin, it has no PHP-imposed limits and can stream files of any size:

Increase server limits before import

mysql> SET GLOBAL max_allowed_packet = 1073741824; -- 1GB
mysql> SET GLOBAL net_read_timeout = 3600; -- 1 hour
mysql> SET GLOBAL net_write_timeout = 3600;
mysql> SET GLOBAL wait_timeout = 28800; -- 8 hours

Disable foreign key checks and unique checks for speed

mysql> SET GLOBAL foreign_key_checks = 0;
mysql> SET GLOBAL unique_checks = 0;
mysql> SET GLOBAL innodb_flush_log_at_trx_commit = 2;

Import the file

# Plain SQL file
mysql -u root -p mydb < mydb_backup.sql

# Compressed file (decompress on the fly)
zcat mydb_backup.sql.gz | mysql -u root -p mydb
zstd -d -c mydb_backup.sql.zst | mysql -u root -p mydb

Restore settings after import

mysql> SET GLOBAL foreign_key_checks = 1;
mysql> SET GLOBAL unique_checks = 1;
mysql> SET GLOBAL innodb_flush_log_at_trx_commit = 1;

Critical: Remember to re-enable foreign_key_checks and innodb_flush_log_at_trx_commit = 1 after the import. Leaving these disabled in production compromises data integrity and crash safety.

Progress Monitoring with pv

One of the most frustrating parts of large imports is not knowing how far along you are. The pv (pipe viewer) utility solves this by showing real-time progress, speed, and ETA:

# Install pv
apt install pv

# Import with progress bar
pv mydb_backup.sql | mysql -u root -p mydb
2.35GiB 0:12:45 [3.15MiB/s] [=========> ] 47% ETA 0:14:10

# With compressed file
pv mydb_backup.sql.gz | gunzip | mysql -u root -p mydb

# Export with progress (estimate size first)
mysqldump --quick mydb | pv -s $(mysql -u root -N -e \
"SELECT SUM(data_length+index_length) FROM \
information_schema.tables WHERE table_schema='mydb'") \
| gzip > mydb.sql.gz

LOAD DATA INFILE for Bulk Row Imports

When you need to import raw data (CSV, TSV) rather than SQL dumps, LOAD DATA INFILE is dramatically faster than INSERT statements — often 20x faster for large datasets:

mysql> LOAD DATA INFILE '/tmp/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(name, email, created_at);

Query OK, 5000000 rows affected (45.23 sec)
Records: 5000000 Deleted: 0 Skipped: 0 Warnings: 0

20x

Faster than INSERT statements

Rows imported in under 1 minute

Security note: LOAD DATA INFILE reads files from the server filesystem, requiring the FILE privilege. For client-side files, use LOAD DATA LOCAL INFILE instead, which reads from the client machine but requires local_infile=1 in the server configuration.

Network Transfer Optimization

When moving databases between servers, the network itself can be the bottleneck. These techniques minimize transfer time:

Direct Pipe Between Servers

# Dump on source, compress, transfer, decompress, import on target
mysqldump --single-transaction --quick -u root -p mydb \
| zstd -T4 \
| ssh user@target-server \
"zstd -d | mysql -u root -p mydb"

# With pv for progress monitoring
mysqldump --single-transaction --quick -u root -p mydb \
| pv -s 5g \
| gzip \
| ssh user@target-server "gunzip | mysql -u root -p mydb"

This approach never writes the dump to disk on either server. Data flows directly from the source MySQL instance, through compression, over the network, through decompression, and into the target MySQL instance. For a 10 GB database, this can complete in 15-20 minutes on a 1 Gbps link.

Using rsync for Dump File Transfer

# Transfer compressed dump with progress
rsync -avz --info=progress2 \
mydb.sql.zst user@target:/tmp/

# Partial transfer resume (if connection drops)
rsync -avz --partial --info=progress2 \
mydb.sql.zst user@target:/tmp/

Server Tuning for Large Imports

Before a large import, temporarily tune your MySQL server for bulk loading performance. These settings prioritize speed over crash safety — appropriate during initial data loads but not for production operation:

# Temporary import optimizations
SET GLOBAL innodb_flush_log_at_trx_commit = 0;
SET GLOBAL innodb_doublewrite = 0;
SET GLOBAL innodb_buffer_pool_size = 4294967296; -- 4GB
SET GLOBAL bulk_insert_buffer_size = 268435456; -- 256MB
SET GLOBAL max_allowed_packet = 1073741824; -- 1GB
SET GLOBAL foreign_key_checks = 0;
SET GLOBAL unique_checks = 0;
SET GLOBAL autocommit = 0;

After the import, immediately restore production settings:

# Restore production settings
SET GLOBAL innodb_flush_log_at_trx_commit = 1;
SET GLOBAL innodb_doublewrite = 1;
SET GLOBAL foreign_key_checks = 1;
SET GLOBAL unique_checks = 1;
SET GLOBAL autocommit = 1;
COMMIT;

Choosing the Right Tool for the Job

Scenario	Recommended Tool	Why
Database under 50 MB	phpMyAdmin	Convenient web UI, no CLI needed
50 MB - 5 GB, single database	mysqldump + mysql CLI	Reliable, widely documented
5 GB - 50 GB	mysqldump + compression + pv	Manageable with optimized flags
50 GB+	mydumper / myloader	Parallel processing essential
CSV/TSV data import	LOAD DATA INFILE	20x faster than INSERT statements
Server-to-server migration	Direct pipe (mysqldump \| ssh \| mysql)	No intermediate disk usage
Point-in-time recovery	Binary log replay	Granular recovery without full dump

Troubleshooting Common Errors

ERROR 2006: MySQL server has gone away

Cause: max_allowed_packet too small or wait_timeout exceeded.

Fix: SET GLOBAL max_allowed_packet=1073741824; and increase wait_timeout.

ERROR 1153: Got a packet bigger than max_allowed_packet

Cause: Single INSERT statement or BLOB exceeds packet limit.

Fix: Increase max_allowed_packet in both server and client configs.

ERROR 1045: Access denied for user

Cause: Wrong credentials or missing privileges for the target database.

Fix: Verify credentials and ensure the user has CREATE and INSERT privileges.

ERROR 1227: Access denied; need SUPER privilege

Cause: Dump contains DEFINER clauses for stored routines or views.

Fix: sed -i 's/DEFINER=[^*]*\*/\*/g' dump.sql or use --skip-definer in mydumper.

Complete Import/Export Script

Here is a production-ready script that handles the entire export and import workflow:

#!/bin/bash
# export-database.sh — Optimized MySQL export
DB_NAME="$1"
BACKUP_DIR="/backups/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
FILENAME="${BACKUP_DIR}/${DB_NAME}_${DATE}.sql.zst"

mkdir -p "$BACKUP_DIR"

echo "Starting export of $DB_NAME..."
mysqldump \
--single-transaction \
--quick \
--routines \
--triggers \
--events \
--extended-insert \
--set-gtid-purged=OFF \
--max-allowed-packet=512M \
-u root "$DB_NAME" \
| pv \
| zstd -T0 -o "$FILENAME"

echo "Export complete: $FILENAME"
echo "Size: $(du -sh "$FILENAME" | cut -f1)"

Managing Large Databases with Panelica

Panelica handles large database imports through its backup and restore system. Upload database dumps through the panel or CLI without phpMyAdmin size limits. The backup system uses optimized mysqldump settings automatically, compresses backups efficiently, and provides progress tracking for both import and export operations. No need to manually tune MySQL settings or remember the right flags — the panel handles it all.

Summary

Large database operations do not have to be painful. The key is choosing the right tool for the size of your database and preparing your server before starting. For databases under a few gigabytes, optimized mysqldump with compression works perfectly. For tens of gigabytes, parallel tools like mydumper become essential. For any size, the MySQL CLI is always more reliable than phpMyAdmin for imports.

Remember the formula: optimize your dump flags, compress everything, monitor progress with pv, temporarily tune your server for bulk loading, and always restore production settings afterward. With these techniques, even a 50 GB database migration becomes a predictable, manageable operation instead of a multi-day headache.

Security-first hosting panel

Hosting management, the modern way.

Panelica is a modern, security-first hosting panel — isolated services, built-in Docker and AI-assisted management, with one-click migration from any panel.

Start Free — No Card Required See how Panelica compares

Zero-downtime migration Fully isolated services Cancel anytime