Importing a Large MySQL Database: Timeouts and max_allowed_packet

When you move a site to a new host or restore it from a backup, importing a large MySQL database is where things most often go wrong. A few megabytes load through phpMyAdmin in seconds, but once the dump reaches hundreds of megabytes or several gigabytes, the import breaks halfway. The browser shows a blank page, phpMyAdmin reports a timeout, or MySQL simply drops the connection. Here is why it happens and how to import a big database reliably.

Why a large dump fails in phpMyAdmin

phpMyAdmin is a regular PHP application, so PHP limits apply to it. The file only reaches the server if upload_max_filesize and post_max_size are larger than the dump, and even then the script cannot run longer than max_execution_time allows. On top of that, the web server and proxy have their own timeouts, so the browser may show 504 Gateway Timeout while the import stops half done. A partially imported database is dangerous: some tables are complete, others empty or truncated, and the site may misbehave silently. If an import was interrupted, drop the tables and start over.

What the max_allowed_packet error means

The max_allowed_packet setting defines the largest packet or statement the MySQL server accepts at once. mysqldump groups many rows into one long INSERT, and tables with BLOB or LONGTEXT columns can make even a single row several megabytes. When a statement exceeds the limit you get one of these:

ERROR 1153 (08S01): Got a packet bigger than 'max_allowed_packet' bytes
ERROR 2006 (HY000): MySQL server has gone away

The second message can have other causes, such as an idle connection being closed, but during an import it usually points to the packet limit. You can raise the client-side value on the command line; the global server value on shared hosting is not something users can change, so ask support.

The reliable way: SSH and the mysql client

If SSH access is enabled on your hosting plan, the command line is the most stable option because PHP limits and browser timeouts are not involved at all. Upload the dump via FTP or SFTP, then run:

mysql -u db_user -p --default-character-set=utf8mb4 \
      --max_allowed_packet=512M db_name < dump.sql

A compressed dump can be streamed directly, which saves disk space:

gunzip < dump.sql.gz | mysql -u db_user -p db_name

For long imports, run the job so it survives a dropped SSH session, and keep the password in an option file only you can read:

# ~/.my.cnf (chmod 600 ~/.my.cnf)
[client]
user=db_user
password=PASSWORD

nohup sh -c 'gunzip < dump.sql.gz | mysql db_name' > import.log 2>&1 &

An empty import.log at the end means no errors. Remove the password from .my.cnf when you are done, since it is stored in plain text.

Preparing the dump properly

On the old server, --single-transaction gives a consistent InnoDB snapshot without stopping the site, and --quick streams big tables row by row instead of buffering them in memory:

mysqldump -u db_user -p --single-transaction --quick \
          --default-character-set=utf8mb4 db_name | gzip > dump.sql.gz

For very large databases, dump tables into separate files so a failure means reloading one table, not everything. Avoid cutting a dump with split by line count, since it can slice an INSERT in half. Check character sets too, especially when moving from utf8 to utf8mb4.

No SSH? Other options

You can split the dump by table into files below the upload limit and import them one by one, use a staggered import script that executes a limited number of statements per request, or upload the file and ask support to run the import. Picture an electronics shop in Tashkent, misol.uz, moving with a 2.5 GB database: every phpMyAdmin attempt dies after ten minutes, while a gzipped dump imported over SSH finishes in about half an hour. On sayt.uz hosting you can upload the file and open a ticket in the cabinet.

Checks after the import

Compare table counts between the old and new databases, run SELECT COUNT(*) on key tables, confirm the site config has the new database name, user and password, and test pages, search and checkout. Question marks or garbled text mean an encoding problem, and the import should be repeated with the correct --default-character-set.