Import a Large MySQL SQL File Without Timeouts

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:

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:

scp /local/path/dump.sql deploy@203.0.113.10:/home/deploy/

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

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.

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:

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

Compressed dumps do not need to be unpacked first:

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:

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.

[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:

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:

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:

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:

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:

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:

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

Ask MySQL to check the tables for damage:

mysqlcheck -u root -p --all-databases

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

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

• Restore a MySQL Database from a mysqldump File covers the restore workflow end to end, including option files.
• Tune MySQL InnoDB Buffer Pool Size for a VPS helps when imports are slow because the buffer pool is too small.
• Automating MySQL backups with mysqldump and cron sets up the dumps you will be importing next time.
• Fix MySQL socket connection errors if the client cannot reach the server at all.