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
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
sudoaccess and MySQL 8.0 or MariaDB 10.x running. - The
.sqldump 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,ALTERandDROPrights on it.
Check the file and the free space first:
ls -lh /home/deploy/dump.sql
df -h /var/lib/mysqlRun every long import inside tmux or screen. If your SSH connection drops mid-import, a foreground mysql process is killed and you are left with a half-loaded database. Start a session with tmux new -s import and detach with Ctrl+B then D.
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 [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:
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.sqlThe 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 appdbCompressed dumps do not need to be unpacked first:
zcat /home/deploy/dump.sql.gz | mysql -u root -p --max_allowed_packet=512M appdbFix MySQL import timeouts and packet errors
The two errors that stop most large imports are:
ERROR 2006 (HY000): MySQL server has gone awayERROR 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=600Changing [mysqld] settings requires a MySQL restart, which drops every open connection and interrupts running queries. Restart at a quiet moment with systemctl restart mysql (or mariadb).
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
doneSplitting by line count can cut a single INSERT statement in half, which produces ERROR 1064 syntax errors. If you control the dump, re-create it with mysqldump --skip-extended-insert so each row is its own statement ending in a semicolon, then split on line boundaries safely.
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.sqlSpeed 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 appdbOnly do this when the target database is empty or you are replacing it entirely. Skipping foreign key and unique checks on a live database can leave rows that violate constraints, and the checks are not re-validated afterwards.
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-databasesThen 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.logIf 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_packeton 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=512Mto themysqlcommand. - Import stops the moment you close the terminal — the process was a child of your SSH session. Re-run it inside
tmuxorscreen, or start it withnohup. - `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.
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.
Related guides
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
Databases
How to create a database and user for a web app in MySQL or MariaDB
Set up a utf8mb4 database and a dedicated user with a strong password for WordPress, Laravel or any web app, then test the login before you configure the app.
- Beginner
- Linux VPS
- 4 min read