Import Large WordPress Database via SSH When phpMyAdmin Fails

phpMyAdmin falls flat on its face the moment you throw a database export over 50MB at it. Between PHP upload caps, max_execution_time limits, and proxy gateway drops, web-based database tools just aren’t built for heavy dumps.

Instead of wrestling with web UI timeouts, upload your raw SQL file straight to your server and import it via SSH. Using the native MySQL command line or WP-CLI takes seconds instead of failing after five minutes.

Why phpMyAdmin Chokes on Large SQL Files

Web-based managers sit behind multiple layers of timeouts. When you upload a 400MB .sql file through phpMyAdmin, that payload travels through Nginx or Apache, the PHP-FPM worker, and finally MySQL. If a single stage hits a ceiling, the entire import dies with a generic 504 Gateway Timeout or a blank white screen.

These are the bottlenecks that usually break web imports:

• PHP upload limits: upload_max_filesize and post_max_size are often hard-capped at 64MB or 128MB. You can tweak them by learning how to increase WordPress PHP memory limit and max upload size, but massive imports will still run into script execution caps.
• PHP execution timeouts: The max_execution_time directive kills long-running scripts (often set to 30, 60, or 300 seconds). A 1GB database takes several minutes to process sequential INSERT statements.
• Web server timeouts: Upstream web servers cut idle HTTP connections after 60 seconds, which frequently triggers a WordPress 504 gateway timeout error.
• Browser drops: If your Wi-Fi blips during the upload, the connection drops, leaving you with half-imported tables and broken schemas.

SSH bypasses the web stack entirely. The native mysql client reads directly from the server’s local disk and streams records straight into the database engine with zero HTTP timeouts in between.

Step 1: Upload the SQL Dump to Your Server

Before running the import, push your .sql or compressed .sql.gz file to the remote server using scp or rsync.

Run this from your local terminal to send the dump to your remote home directory:

# Transfer a compressed SQL file using SCP
scp ~/Downloads/backup_db.sql.gz username@your-server-ip:/home/username/

If you’re dealing with an unstable connection or a dump over 2GB, use rsync so you can resume interrupted transfers:

# Resume-enabled transfer with rsync
rsync -avzP ~/Downloads/backup_db.sql.gz username@your-server-ip:/home/username/

Pro tip: always gzip your SQL file locally before sending it up. A 1.2GB plain text SQL dump usually drops to around 120MB, saving serious bandwidth and transfer time.

# Compress on your local machine before upload
gzip backup_db.sql

Step 2: Grab Your WordPress Database Credentials

SSH into your server and check wp-config.php. You’ll need the database name, username, and password.

Log into your server and move to your WordPress root directory:

ssh username@your-server-ip
cd /var/www/html/

Pull the database constants straight from wp-config.php using grep:

grep -E 'DB_NAME|DB_USER|DB_PASSWORD|DB_HOST' wp-config.php

You’ll get output like this:

define( 'DB_NAME', 'wp_production_db' );
define( 'DB_USER', 'wp_db_user' );
define( 'DB_PASSWORD', 'SecureRandomPassword_982#' );
define( 'DB_HOST', 'localhost' );

Step 3: Run the Native MySQL Import Command

The standard MySQL batch client reads raw SQL commands directly from standard input. This approach churns through massive files without eating up RAM.

If you uploaded an uncompressed .sql file, run:

mysql -u wp_db_user -p wp_production_db < /home/username/backup_db.sql

Enter the password from wp-config.php when prompted. The terminal won’t show output until it’s finished—don’t cancel it.

If your dump is compressed as .sql.gz, don’t waste time extracting it to disk first. Stream gunzip or zcat directly into MySQL to save disk space:

# Stream compressed SQL directly into MySQL
zcat /home/username/backup_db.sql.gz | mysql -u wp_db_user -p wp_production_db

On an 850MB WooCommerce database dump, this piped stream took me 42 seconds on a budget VPS, compared to phpMyAdmin spinning for 5 minutes before throwing an HTTP 504.

Step 4: The WP-CLI Shortcut (`wp db import`)

If WP-CLI is installed on the server, you can skip looking up database credentials. WP-CLI reads wp-config.php on its own and passes the credentials straight to MySQL.

Jump to your WordPress web root and run wp db import:

cd /var/www/html/
wp db import /home/username/backup_db.sql --allow-root

You’ll see a clean confirmation once it wraps up:

Success: Imported from '/home/username/backup_db.sql'.

You can also hook this into backup routines. For details on scripting this, see our guide on how to automate WordPress backups with WP-CLI and Cron.

Fix Common MySQL Import Errors

Large database imports often hit server-level config limits. Here’s how to resolve the three most common roadblocks.

Error: MySQL Server Has Gone Away (Error 2006)

This happens when a single row (like a huge serialized Elementor layout or an encoded image in wp_posts) exceeds MySQL’s packet size limit.

Override the packet size directly in your import command using --max_allowed_packet:

mysql --max_allowed_packet=512M -u wp_db_user -p wp_production_db < /home/username/backup_db.sql

If you keep hitting it, update your MySQL configuration file in /etc/mysql/my.cnf or /etc/mysql/mysql.conf.d/mysqld.cnf:

[mysqld]
max_allowed_packet = 512M
wait_timeout = 600
net_read_timeout = 600

Restart MySQL to apply the changes:

sudo systemctl restart mysql

Error: Unknown Collation utf8mb4_unicode_520_ci

Moving a database from a newer MySQL/MariaDB server to an older server often throws collation errors because the target server doesn’t recognize utf8mb4_unicode_520_ci.

Fix it instantly with sed by converting the collation to standard utf8mb4_unicode_ci before piping it into MySQL:

sed -i 's/utf8mb4_unicode_520_ci/utf8mb4_unicode_ci/g' /home/username/backup_db.sql
sed -i 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g' /home/username/backup_db.sql

Error: Foreign Key Check Violations

Some plugins (like WooCommerce extension tables or custom analytics tools) set foreign key constraints. If tables

Temporarily disable foreign key checks for your session during the import:

mysql -u wp_db_user -p -e "SET GLOBAL foreign_key_checks = 0;"
mysql -u wp_db_user -p wp_production_db < /home/username/backup_db.sql
mysql -u wp_db_user -p -e "SET GLOBAL foreign_key_checks = 1;"

Verify the Import and Run Search-Replace

Once the import finishes, verify your tables and update your domain URLs if you moved environments.

Count the imported tables to make sure everything landed:

mysql -u wp_db_user -p wp_production_db -e "SHOW TABLES;" | wc -l

If you migrated from staging or a local environment, update hardcoded URLs using WP-CLI’s safe search and replace. This handles PHP serialized data without breaking widget or theme settings:

cd /var/www/html/
wp search-replace 'https://staging.example.com' 'https://example.com' --all-tables --dry-run --allow-root

Check the dry-run output, then run it for real by removing --dry-run:

wp search-replace 'https://staging.example.com' 'https://example.com' --all-tables --allow-root

For more details on keeping data safe across environments, read how to push WordPress staging to production without overwriting live customer data.

Finally, delete the SQL dump from your server so you don’t leave sensitive database records sitting in plain text on your filesystem:

rm -f /home/username/backup_db.sql
rm -f /home/username/backup_db.sql.gz

Frequently Asked Questions

How do I monitor the progress of a large MySQL import over SSH?

The standard mysql binary doesn’t show a progress bar. Pipe your dump through pv (Pipe Viewer) to see throughput and an estimated ETA in real time:

# Install pv on Ubuntu/Debian
sudo apt-get install pv # Pipe the file through pv to see a real-time progress bar
pv /home/username/backup_db.sql | mysql -u wp_db_user -p wp_production_db

Can I export my database via SSH before doing the import?

Yes. Run mysqldump on the source server to create a compressed backup:

mysqldump -u wp_db_user -p wp_production_db | gzip > backup_db.sql.gz

Why does WP-CLI throw an “Error establishing a database connection” during import?

WP-CLI can’t reach the database with the credentials in wp-config.php. Check that MySQL is running (sudo systemctl status mysql) and verify whether DB_HOST needs to be set to 127.0.0.1 instead of localhost to bypass Unix socket issues.

What should I do if the database import locks up the entire server?

On servers with 1GB of RAM or less, large imports can trigger the Linux Out-Of-Memory (OOM) killer. Set up a quick 2GB swap file before importing so MySQL doesn’t get terminated mid-stream:

sudo fallocate -l 2G /swapfile
sudo chmod 600 /swapfile
sudo mkswap /swapfile
sudo swapon /swapfile