A large import through a web interface almost always fails: the upload limit, the execution time, or the request timeout stops it partway. Import from the command line and none of those apply.

mysql -u user -p dbname < dump.sql\n# or, compressed\nzcat dump.sql.gz | mysql -u user -p dbname

Make it fast

# at the top of the session
SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;
-- import --
SET foreign_key_checks=1;
SET unique_checks=1;
COMMIT;

Most of the time in a large import is spent checking constraints and flushing after every row. Turning that off for a trusted dump and back on afterwards routinely turns hours into minutes.

Only on a dump you trust and on a database nothing else is writing to. With foreign key checks off, a broken dump imports broken data silently.

Watch it happen

pv dump.sql | mysql -u user -p dbname

pv prints a progress bar and an estimate. Without it a long import is indistinguishable from a hung one, and people kill it halfway - which is worse than waiting.

If it fails partway

  • Out of disk - the database needs room for the data AND the temporary files. Check df -h before starting.
  • MySQL server has gone away - a single row larger than max_allowed_packet. Raise it: max_allowed_packet = 256M.
  • Unknown collation - the dump came from a newer MySQL. Either upgrade, or rewrite the collation in the file.
sed -i 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g' dump.sql
Import into an empty database, not over a live one. If it fails halfway you have a half-imported copy to throw away rather than a half-destroyed site.