Import a Large MySQL SQL File Without Timeouts

Import a large MySQL dump from the command line without phpMyAdmin timeouts. Raise packet limits, split oversized files and verify the result.

  • Intermediate
  • Linux VPS
  • 6 min read

By RunRacks Team · Published

When a database dump is too big for phpMyAdmin or a web uploader, the import dies halfway with a timeout or "MySQL server has gone away". You can import a large MySQL SQL file reliably from the Linux command line, and the fix is usually two settings plus a way to survive a dropped SSH session.

This guide walks through the command-line import, the packet and timeout limits that break it, and how to split a dump that is simply too large for one run.

Before you start

You need:

  • A Linux VPS with root or sudo access and MySQL 8.0 or MariaDB 10.x running.
  • The .sql dump file, either already on the server or ready to upload.
  • Enough free disk space for the database plus the indexes. A dump of 5 GB can need 10 GB or more once InnoDB builds its tablespaces.
  • The database name you want to import into, and a MySQL user with CREATE, INSERT, ALTER and DROP rights on it.

Check the file and the free space first:

Terminal
ls -lh /home/deploy/dump.sql
df -h /var/lib/mysql

Import a large MySQL SQL file from the command line

Upload the dump if it is not on the server yet. SFTP over SSH keys is the safest route:

Terminal
scp /local/path/dump.sql [email protected]:/home/deploy/

Create the target database with an explicit character set so you do not inherit a server default that differs from the dump:

Terminal
mysql -u root -p -e "CREATE DATABASE appdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

Now run the import. The two client flags matter: --max_allowed_packet lets the client send big INSERT statements, and --net_buffer_length controls how much it buffers per round trip.

Terminal
mysql -u root -p \
  --max_allowed_packet=512M \
  --net_buffer_length=16384 \
  appdb < /home/deploy/dump.sql

The command prints nothing while it works. On a large file that silence can last a long time. If you want a progress bar, install pv and pipe through it:

Terminal
pv /home/deploy/dump.sql | mysql -u root -p --max_allowed_packet=512M appdb

Compressed dumps do not need to be unpacked first:

Terminal
zcat /home/deploy/dump.sql.gz | mysql -u root -p --max_allowed_packet=512M appdb

Fix MySQL import timeouts and packet errors

The two errors that stop most large imports are:

  • ERROR 2006 (HY000): MySQL server has gone away
  • ERROR 2013 (HY000): Lost connection to MySQL server during query

Both usually mean the server rejected a packet that was larger than max_allowed_packet, or the connection sat idle past wait_timeout while a single statement ran. Check the current values:

SQL
SHOW VARIABLES LIKE 'max_allowed_packet';
SHOW VARIABLES LIKE 'wait_timeout';
SHOW VARIABLES LIKE 'net_read_timeout';
SHOW VARIABLES LIKE 'net_write_timeout';

On Ubuntu and Debian, add overrides in /etc/mysql/mysql.conf.d/mysqld.cnf. On AlmaLinux and Rocky, use /etc/my.cnf.d/mysql-server.cnf.

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

The client flag and the server setting must both be large enough. If the server allows 512M but you run mysql without --max_allowed_packet, the client can still fail on a big statement. Keep them equal.

Split a large SQL file into smaller chunks

If the dump is enormous or the VPS has limited memory, import it in pieces. The simplest split is by line count:

Terminal
split -l 500000 -d --additional-suffix=.sql /home/deploy/dump.sql /home/deploy/part-

Then loop over the parts and stop at the first failure:

Terminal
for f in /home/deploy/part-*.sql; do
  echo "Importing $f"
  mysql -u root -p --max_allowed_packet=512M appdb < "$f" || break
done

If you cannot re-dump, split only at lines that end with a semicolon. A short awk pass does that without breaking statements:

Terminal
awk 'BEGIN{n=0} {print > sprintf("/home/deploy/part-%03d.sql", int(n/200000)); if ($0 ~ /;[[:space:]]*$/) n++}' /home/deploy/dump.sql

Speed up the import with safe session settings

For a first import into an empty database, you can turn off checks that only matter for live traffic. Build a small prefix file and concatenate it in front of the dump:

Terminal
cat > /tmp/import-prefix.sql <<'SQL'
SET foreign_key_checks=0;
SET unique_checks=0;
SQL
cat /tmp/import-prefix.sql /home/deploy/dump.sql | mysql -u root -p --max_allowed_packet=512M appdb

If the server is otherwise idle, raising innodb_flush_log_at_trx_commit to 2 for the duration shortens the import noticeably:

SQL
SET GLOBAL innodb_flush_log_at_trx_commit = 2;

Set it back to 1 when the import finishes. Never leave it at 2 on a production server unless you accept losing the last second of committed transactions after a crash.

Check it works

Confirm the tables arrived and the row counts look sane:

SQL
USE appdb;
SHOW TABLES;
SELECT COUNT(*) FROM orders;

Ask MySQL to check the tables for damage:

Terminal
mysqlcheck -u root -p --all-databases

Then look for errors in the server log, which is where a partially failed import leaves its trace:

Terminal
sudo tail -n 100 /var/log/mysql/error.log

If the log shows Table ... is marked as crashed or InnoDB page errors, stop and repair before you point the application at the database.

Troubleshooting

  • `ERROR 2006 MySQL server has gone away` — the packet exceeded max_allowed_packet on the server or client. Raise both to the same value and restart MySQL.
  • `ERROR 1153 Got a packet bigger than 'max_allowed_packet' bytes` — same cause, seen when the client flag is missing. Add --max_allowed_packet=512M to the mysql command.
  • Import stops the moment you close the terminal — the process was a child of your SSH session. Re-run it inside tmux or screen, or start it with nohup.
  • `ERROR 1045 Access denied for user` — the account lacks rights on the target database. Grant them or use a different user; see Fix MySQL error 1045: Access denied.
  • `ERROR 1064` after splitting — a split point landed inside a statement. Re-split at semicolon boundaries or re-dump with --skip-extended-insert.
  • Import dies with no message and `df -h` shows 100% — the disk filled while InnoDB expanded the tablespaces. Free space and start again from an empty database.

Next steps

If you need more disk, RAM or a clean server for the import, review the Linux VPS plans and manage the instance from your dashboard.

Was this guide helpful?

Still stuck?

Ask RunRacks AI for a quick answer, or open a ticket and our team will take a look at your server.

Databases

Reset a Forgotten MySQL Root Password on Linux

Recover access to MySQL or MariaDB on a Linux VPS by starting the server with skip-grant-tables, setting a new root password and restarting normally.

  • Intermediate
  • Linux VPS
  • 5 min read

Databases

MySQL Point-in-Time Recovery Using Binary Logs

Recover a MySQL or MariaDB database to an exact moment after a bad write or dropped table by restoring a full backup and replaying binary logs up to the incident.

  • Advanced
  • Linux VPS
  • 7 min read