Importing Large SQL Files Without Timeouts and Errors

You've selected the dump.sql file, clicked the Import button, the progress bar went halfway, and then the screen went white. Or worse: you got a 504 Gateway Timeout message, and when you open the MySQL log, you see that the table was half-created and the next import gets stuck on Table already exists. This is exactly where most site administrators lose several hours. The problem is neither your internet speed nor a corrupt dump; the issue is that web tools weren't built for large files.

Why importing a large SQL file from phpMyAdmin fails

When you import from the web panel, the file first has to be uploaded from the browser to PHP, then PHP passes it to MySQL. There are three separate ceilings in your way, and each one gives a different error message:

• upload_max_filesize and post_max_size in php.ini — if the file is larger, $_FILES comes back empty and phpMyAdmin says "No file was uploaded."
• max_execution_time — usually 30 or 60 seconds. A 200-megabyte file takes much longer than this and the script gets killed midway.
• max_allowed_packet in MySQL — the default in many versions is 4 megabytes. If a single large INSERT statement exceeds this, you get the MySQL server has gone away error, even if you've passed the previous two ceilings.

A point that's rarely mentioned: raising these values on shared hosting doesn't always work, because the web server has a proxy layer in front of PHP that has its own independent timeout. Even if you set max_execution_time to 600, the proxy may close the connection at second 120. To find out which ceiling is actually hitting you, see the complete reference of hosting resource limits; every number there specifies which parameter counts what.

Method one: importing the dump in chunks with split

If you have SSH access, this is the cleanest way. Break the file into small chunks and import each chunk separately. The split tool on Linux does this in a few seconds, but you need to be careful not to cut in the middle of an INSERT statement.

split -l 5000 dump.sql chunk_ --additional-suffix=.sql
for f in chunk_*.sql; do
  mysql -u dbuser -p dbname < "$f" || echo "FAILED: $f"
done

The number 5000 lines is a reasonable starting point; if your tables have very large records, lower it. The advantage of this method is that when the seventh chunk fails, you only re-run that one, not the entire dump. Its downside is also obvious: if your dump is transaction-based and the tables have foreign key dependencies on each other, sequential execution of chunks can temporarily produce constraint errors. In that case, you should put SET FOREIGN_KEY_CHECKS=0; at the beginning of each chunk and turn it back on at the end of the last chunk.

Why split isn't always enough

Some dumps have a single record that is itself 50 megabytes (for example, a LONGTEXT field with base64 content). Here line-based split doesn't help at all, because that one line is larger than the max_allowed_packet ceiling. You must either raise the packet value or take the dump with mysqldump using the --skip-extended-insert option so that each record is a separate statement.

Method two: running directly on the server without uploading from the browser

If the dump file is on the same server, you don't need PHP or the browser at all. Import it directly with the MySQL client:

mysql -u dbuser -p --max_allowed_packet=256M dbname < /home/user/dump.sql

This command has neither a browser timeout nor a PHP upload ceiling. The only remaining limitation is the execution time of the queries themselves, which can be managed with SET SESSION wait_timeout=0;. If the file is on another server, first transfer it with scp, then import. Transferring 500 megabytes over the internal network usually finishes in under a minute, whereas the same file through the browser might take ten minutes and still fail in the end.

On a dedicated server this method is almost always the best choice, because you have the resources at your disposal and can temporarily raise innodb_buffer_pool_size to make the import faster. On shared hosting these parameters can't be changed and you have to work with the defaults.

Method three: when only phpMyAdmin is available

Some hosts don't give SSH and you only have the web panel. In this case you can do two things. First, compress the file with gzip; phpMyAdmin opens .sql.gz files itself and reduces the transfer size by up to 80 percent. Second, use its own partial import option: in the Import tab, open the "Partial import" section and set the number of lines per batch to, say, 2000. phpMyAdmin itself continues the file from the specified line.

This method is slow and becomes practically unusable for dumps above 500 megabytes. If you regularly deal with large dumps, it's time to migrate to Linux hosting with SSH access; the difference shows itself in exactly these moments.

This is where people go wrong

The most common mistake I see is this: the user abandons the import halfway, then runs it again from the start and hits Table 'x' already exists. Then they start manually deleting tables, and since the tables have foreign keys on each other, the deletion also fails. The sign of this is that you see the list of tables in phpMyAdmin but the record count is zero or half. The right way is to completely empty the database before every retry:

DROP DATABASE dbname;
CREATE DATABASE dbname CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Mistake number two: ignoring the collation. If the dump was created with utf8mb4_unicode_ci and the destination database is latin1, the import succeeds but the Persian text later turns into something like سلام. This can't be fixed after the import; you have to create the database with the correct collation from the start.

Before you start, check three things

1. Know the file size and its line count: wc -l dump.sql and du -h dump.sql.
2. Compare the source and destination MySQL versions. A dump taken from MySQL 8 on a MySQL 5.7 server often gives syntax errors, especially in collation definitions.
3. Make sure there's enough disk space. A 200-megabyte dump usually takes up two to three times its size after import, because indexes are added.

For a quick check of the destination server's status and to make sure it resolves correctly, the DNS and network lookup tool will get you going. And if you want to take a healthy backup before making any changes to the database, the free webmaster tools are a good starting point.

The summary is this: if you have SSH, never import from the browser. If you don't have SSH, compress the file and run partial import with a low line count. And before every retry, completely wipe the database so you don't get tangled up with the duplicate table error.

Frequently asked questions

Why does a large SQL import stop with the MySQL server has gone away error?

This error is almost always related to max_allowed_packet. When an INSERT statement is larger than this value, the MySQL server closes the connection. The default value in many installations is 4 megabytes. Increase it to 64 or 256 megabytes, or take the dump with --skip-extended-insert so that each record is a separate statement.

Can I compress the SQL file with gzip and import it directly?

Yes, both phpMyAdmin and the MySQL command-line client read .sql.gz files. On the command line, just use zcat dump.sql.gz | mysql -u user -p dbname. Compression greatly reduces the transfer size and typically saves between 70 and 85 percent on text dumps.

Why do Persian texts appear as question marks or strange characters after import?

The problem is the collation, not the import. The destination database must be created with CHARACTER SET utf8mb4 and COLLATE utf8mb4_unicode_ci. If the dump was created with these settings but the destination is latin1, the bytes are interpreted incorrectly. This can't be repaired after the import; you have to recreate the database with the correct collation and import the dump again.

What method do you recommend for a 2-gigabyte dump?

At this size, set phpMyAdmin aside. The best option is transferring the file to the server with scp and running mysql < dump.sql directly. If you don't have SSH, split the dump into 50-megabyte chunks and import each chunk separately. On shared hosting, dumps above one gigabyte usually hit resource limits, and it's better to talk to support about upgrading to a higher plan.