{"id":1491,"date":"2026-09-03T12:17:04","date_gmt":"2026-09-03T06:47:04","guid":{"rendered":"https:\/\/cpanelfree.com\/blog\/how-to-export-import-large-mysql-database-command-line\/"},"modified":"2026-09-03T12:31:03","modified_gmt":"2026-09-03T07:01:03","slug":"how-to-export-import-large-mysql-database-command-line","status":"publish","type":"post","link":"https:\/\/cpanelfree.com\/blog\/how-to-export-import-large-mysql-database-command-line\/","title":{"rendered":"How to Export and Import Large MySQL Databases via Command Line (No Timeout)"},"content":{"rendered":"
\n

\n Quick Answer:<\/strong> To export a large MySQL database without locking production tables, execute mysqldump -u username -p --single-transaction --quick dbname > backup.sql<\/code>. To import a large SQL dump without web browser timeout errors, execute mysql -u username -p dbname < backup.sql<\/code> (or pipe through pv backup.sql | mysql -u username -p dbname<\/code> for live progress tracking).\n <\/p>\n<\/div>\n

Why phpMyAdmin Fails on Large Database Imports<\/h2>\n

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

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.<\/p>\n

Step 1: Exporting with mysqldump (Production Optimized)<\/h2>\n

Use optimal flags to prevent table locking and ensure consistent reads on active WooCommerce databases:<\/p>\n

# Export and compress on the fly to save disk space\nmysqldump -u db_user -p --single-transaction --quick --lock-tables=false db_name | gzip > \/var\/backups\/db_backup.sql.gz<\/pre>\n
Step 2: Importing Large SQL Dumps via Terminal<\/h2>\n
Create the target database if it does not already exist:<\/p>\n
mysql -u root -p -e \"CREATE DATABASE target_dbname DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;\"<\/pre>\n
Execute the import directly from the compressed archive:<\/p>\n
gunzip < \/var\/backups\/db_backup.sql.gz | mysql -u root -p target_dbname<\/pre>\n
Step 3: Visual Progress Tracking with Pipe Viewer (pv)<\/h2>\n
When importing massive 5 GB+ databases, terminal commands display no output until complete. Install pv<\/code> (Pipe Viewer) to view live progress bars, transfer speeds, and ETA:<\/p>\n
sudo apt install pv -y\n\n# Monitor real-time import progress\npv db_backup.sql | mysql -u root -p target_dbname<\/pre>\n
Speeding Up Large Imports by Temporarily Disabling Foreign Key Checks<\/h2>\n
If importing millions of rows with complex relational foreign keys, temporarily disable index checks during import to speed up processing by 5x<\/strong>:<\/p>\n
mysql -u root -p target_dbname -e \"SET FOREIGN_KEY_CHECKS=0; SOURCE \/path\/to\/backup.sql; SET FOREIGN_KEY_CHECKS=1;\"<\/pre>\n
Tuning MySQL Server Parameters for High-Speed Large Imports<\/h2>\n
When restoring massive multi-gigabyte SQL database archives on a Linux VPS, temporarily increase these MySQL engine memory buffers in \/etc\/mysql\/my.cnf<\/code> to prevent buffer exhaustion and speed up data ingestion by up to 10x<\/strong>:<\/p>\n
[mysqld]\n# Temporary import buffer optimization\nmax_allowed_packet = 1024M\nnet_buffer_length = 1048576\ninnodb_buffer_pool_size = 4G\ninnodb_log_buffer_size = 512M\ninnodb_write_io_capacity = 2000\ninnodb_flush_log_at_trx_commit = 0<\/pre>\n
Note:<\/strong> Setting innodb_flush_log_at_trx_commit = 0<\/code> tells MySQL to flush redo logs once per second rather than on every single INSERT transaction, slashing disk write overhead during bulk data restoration.<\/p>\n
Handling MySQL 8.0 Default Collation Conflicts (utf8mb4_0900_ai_ci)<\/h2>\n
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'<\/code>. Clean the collation using a sed<\/code> stream editor replacement:<\/p>\n
sed -i 's\/utf8mb4_0900_ai_ci\/utf8mb4_unicode_ci\/g' db_backup.sql\nsed -i 's\/utf8mb4_0900_as_cs\/utf8mb4_unicode_ci\/g' db_backup.sql<\/pre>\n
Automating MySQL Backups and Remote Cloud Synchronization via Cron<\/h2>\n
Combine command-line database dumps with automated cloud synchronization to establish an offsite disaster recovery workflow:<\/p>\n
#!\/bin\/bash\n# Automated MySQL Backup & Cloud Upload Script\nTIMESTAMP=$(date +\"%Y%m%d_%H%M%S\")\nBACKUP_FILE=\"\/var\/backups\/mysql\/db_${TIMESTAMP}.sql.gz\"\n\n# Dump and compress database\nmysqldump -u root -p'YourPassword' --single-transaction --quick --all-databases | gzip > $BACKUP_FILE\n\n# Sync to S3 storage via Rclone\nrclone copy $BACKUP_FILE cloudstorage:my-database-backups\/daily\/\n\n# Clean local backups older than 7 days\nfind \/var\/backups\/mysql\/ -type f -mtime +7 -name \"*.sql.gz\" -delete<\/pre>\n
Repairing and Optimizing Corrupted Tables with mysqlcheck<\/h2>\n
If an unexpected power outage or server reboot causes database corruption, repair and optimize all tables across all databases in a single CLI command:<\/p>\n
mysqlcheck -u root -p --auto-repair --check --optimize --all-databases<\/pre>\n
\n
\n        \ud83d\udd17<\/span> Recommended Related Technical Guides:
\n    <\/h3>\n