MySQL

MySQL Dump File Too Large to Import, How to Split

A dump file too large to import comfortably usually isn't actually a hard limit being hit — it's more often a practical problem with timeouts, memory constraints, or the sheer time a single massive statement takes to execute, all of which are solvable by changing how the import is performed rather than needing to reduce the data itself.

The Problem

Trying to import a large SQL dump file fails partway through, times out, or consumes excessive memory:

mysql -u user -p mydb < huge_dump.sql
ERROR 2013 (HY000): Lost connection to MySQL server during query

Or the import process simply runs for an extremely long time without clear progress feedback, making it hard to know whether it's working or stuck.

Why It Happens

Several distinct factors contribute to large dump imports being difficult, and identifying which one applies changes the appropriate fix:

  • Connection or statement timeouts on either the client or server side interrupt a long-running import before it completes, particularly common with dumps containing very large individual INSERT statements or a huge total number of statements
  • Available memory is insufficient for MySQL to process especially large individual statements or transactions within the dump
  • The dump includes a large number of individual small statements rather than being batched efficiently, making the import slower than necessary due to per-statement overhead multiplied many times over
  • Network interruptions during a remote import (importing over a network connection to a remote server) cause the connection to drop partway through a very long-running operation

The Fix

First, if importing to a local or directly-accessible server, use the MySQL client directly rather than piping through other tools, and increase relevant timeout and buffer settings for the session:

mysql -u user -p --max_allowed_packet=1G mydb < huge_dump.sql

Increasing max_allowed_packet specifically addresses failures caused by individual statements or rows exceeding the default packet size limit, which is a common cause of import failures with dumps containing large individual rows (like ones with big BLOB or TEXT columns).

If the dump genuinely needs to be split into smaller, more manageable pieces, use a dedicated splitting tool rather than manually trying to find safe break points in a massive SQL file by hand:

pip install mysqldumpsplitter
# or, a straightforward command-line splitting approach:
csplit huge_dump.sql '/^-- Table structure for/' '{*}'

This splits the dump at table boundaries, producing separate files you can import individually, checking progress and catching failures at a much smaller granularity than trying to import the entire file as one unit.

For very large dumps, consider using the mysqlpump or mydumper/myloader tool pair instead of the standard mysqldump/mysql combination — these are specifically designed for better performance with large datasets, supporting parallel dumping and loading rather than the single-threaded, sequential approach of standard tools:

mydumper --outputdir /path/to/dump --threads 4
myloader --directory /path/to/dump --threads 4

If importing over a network connection that's prone to interruption, run the import inside a persistent session tool like tmux or screen, so a dropped SSH connection doesn't kill the import process along with it:

tmux new -s import_session
mysql -u user -p mydb < huge_dump.sql
# Detach with Ctrl+B then D, reattach later with: tmux attach -t import_session

For a dump containing a very large number of individual, small INSERT statements, disabling autocommit and index updates temporarily during the import can significantly speed up the process, since MySQL doesn't need to update indexes after every single row when they're batched into fewer, larger transactions:

SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;
SOURCE huge_dump.sql;
SET foreign_key_checks=1;
SET unique_checks=1;
COMMIT;

Re-enable these checks immediately after the import completes — leaving them disabled beyond the import itself removes safety guarantees you almost certainly want active during normal operation.

Still Not Working?

If the import consistently fails at the same point regardless of the approach used, isolate whether a specific problematic statement in the dump itself is the cause, rather than a general resource constraint. Extract and test just the section of the file around where the failure consistently occurs:

sed -n '10000,10100p' huge_dump.sql > test_segment.sql
mysql -u user -p mydb < test_segment.sql

If this smaller segment reproduces the same error, the issue is with a specific statement's content (a malformed row, an unusually large value, invalid encoding) rather than the overall file size, and fixing or removing that specific problematic section resolves the import rather than any of the general splitting or performance techniques above.

You might also like