How to Export and Import Large MySQL Databases via Command Line (No Timeout)

Why phpMyAdmin Fails on Large Database Imports

When databases grow beyond 50 MB, importing through web interfaces (like phpMyAdmin) frequently fails with 504 Gateway Timeout, 413 Request Entity Too Large, or Maximum execution time exceeded errors due to web server limits (upload_max_filesize and max_execution_time).

Using the native Linux MySQL command-line client streams data directly into the database engine with dedicated system memory, allowing multi-gigabyte databases to import in seconds.

Step 1: Exporting with mysqldump (Production Optimized)

Use optimal flags to prevent table locking and ensure consistent reads on active WooCommerce databases:

# Export and compress on the fly to save disk space
mysqldump -u db_user -p --single-transaction --quick --lock-tables=false db_name | gzip > /var/backups/db_backup.sql.gz

Step 2: Importing Large SQL Dumps via Terminal

Create the target database if it does not already exist:

mysql -u root -p -e "CREATE DATABASE target_dbname DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

Execute the import directly from the compressed archive:

gunzip < /var/backups/db_backup.sql.gz | mysql -u root -p target_dbname

Step 3: Visual Progress Tracking with Pipe Viewer (pv)

When importing massive 5 GB+ databases, terminal commands display no output until complete. Install pv (Pipe Viewer) to view live progress bars, transfer speeds, and ETA:

sudo apt install pv -y

# Monitor real-time import progress
pv db_backup.sql | mysql -u root -p target_dbname

Speeding Up Large Imports by Temporarily Disabling Foreign Key Checks

If importing millions of rows with complex relational foreign keys, temporarily disable index checks during import to speed up processing by 5x:

mysql -u root -p target_dbname -e "SET FOREIGN_KEY_CHECKS=0; SOURCE /path/to/backup.sql; SET FOREIGN_KEY_CHECKS=1;"

Tuning MySQL Server Parameters for High-Speed Large Imports

When restoring massive multi-gigabyte SQL database archives on a Linux VPS, temporarily increase these MySQL engine memory buffers in /etc/mysql/my.cnf to prevent buffer exhaustion and speed up data ingestion by up to 10x:

[mysqld]
# Temporary import buffer optimization
max_allowed_packet = 1024M
net_buffer_length = 1048576
innodb_buffer_pool_size = 4G
innodb_log_buffer_size = 512M
innodb_write_io_capacity = 2000
innodb_flush_log_at_trx_commit = 0

Note: Setting innodb_flush_log_at_trx_commit = 0 tells MySQL to flush redo logs once per second rather than on every single INSERT transaction, slashing disk write overhead during bulk data restoration.

Handling MySQL 8.0 Default Collation Conflicts (utf8mb4_0900_ai_ci)

If you export a database from a newer MySQL 8.0 server and attempt to import it into a MariaDB 10.11 or older MySQL 5.7 host, you will encounter the error: Unknown collation: 'utf8mb4_0900_ai_ci'. Clean the collation using a sed stream editor replacement:

sed -i 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g' db_backup.sql
sed -i 's/utf8mb4_0900_as_cs/utf8mb4_unicode_ci/g' db_backup.sql

Automating MySQL Backups and Remote Cloud Synchronization via Cron

Combine command-line database dumps with automated cloud synchronization to establish an offsite disaster recovery workflow:

#!/bin/bash
# Automated MySQL Backup & Cloud Upload Script
TIMESTAMP=$(date +"%Y%m%d_%H%M%S")
BACKUP_FILE="/var/backups/mysql/db_${TIMESTAMP}.sql.gz"

# Dump and compress database
mysqldump -u root -p'YourPassword' --single-transaction --quick --all-databases | gzip > $BACKUP_FILE

# Sync to S3 storage via Rclone
rclone copy $BACKUP_FILE cloudstorage:my-database-backups/daily/

# Clean local backups older than 7 days
find /var/backups/mysql/ -type f -mtime +7 -name "*.sql.gz" -delete

Repairing and Optimizing Corrupted Tables with mysqlcheck

If an unexpected power outage or server reboot causes database corruption, repair and optimize all tables across all databases in a single CLI command:

mysqlcheck -u root -p --auto-repair --check --optimize --all-databases

Frequently Asked Questions

Optimizing MySQL my.cnf Buffer Allocation for Large Database Dumps

When importing multi-gigabyte SQL files containing millions of rows, increasing innodb_buffer_pool_size to 70% of available RAM prevents the MySQL storage engine from paging data to disk, completing complex restorations in minutes rather than hours.

Executing database imports and exports via native Linux CLI commands bypasses PHP timeout barriers, enabling developers to restore enterprise-scale MySQL databases safely in seconds.

Finally, always test database restoration in a staging environment to verify data integrity before running production database cutovers.