Import a mysqldump file that is too large to import in one go.
A single mysql < dump.sql on a multi-gigabyte dump is all-or-nothing: one dropped
connection at 90% and you start over. This splits the dump into per-table pieces and
imports them one at a time, recording progress as it goes — so a failure costs you one
part, not the whole night.
# 1. split (streams the file; a 16 GB dump never enters RAM)
python split_dump.py split dump.sql --out parts --max-chunk-mb 200
# 2. import (re-run this exact command after any failure — it resumes)
python split_dump.py import parts --host 127.0.0.1 --user root --db mydbNo dependencies beyond the Python standard library and the mysql client binary.
Python 3.8+.
| one big import | this | |
|---|---|---|
| connection drops at 90% | start over | resume at the failing part |
| a single bad statement | aborts everything | skippable by error code, logged |
| progress | silence for hours | per-part timing, ETA during split |
| partial state after a crash | half a table, unclear | rolled back, part not marked done |
| RAM | client buffers a huge statement | streams, 1 MB at a time |
Splitting. The file is read as bytes, one line at a time. mysqldump writes one
statement per line and escapes newlines inside string data, so a line-oriented split is
safe. New files start at mysqldump's -- Table structure for table markers; within a
large table, the data rolls to a new part every --max-chunk-mb, but only after a line
ending in ; — a part never ends mid-statement. DELIMITER ;; … DELIMITER ; blocks
(triggers, stored routines) are never split. After splitting, every part is checked to
confirm it ends on a statement boundary.
The dump's preamble — SET NAMES, sql_mode, FOREIGN_KEY_CHECKS=0, and the rest —
is saved to 000_header.sql and re-sent before every part, because each part is
imported over its own connection and session variables don't survive between them.
Importing. Each part is streamed into a fresh mysql process with
autocommit=0 … COMMIT, so the part is one transaction. If the connection dies
mid-part, the server rolls the whole thing back and nothing is recorded as done —
re-running can't produce half-applied data or duplicate rows. Finished parts are
appended to parts/_progress.log, which is all the resume state there is; delete a line
to force that part to re-run.
parts/
├── 000_header.sql session setup, re-sent before every part
├── 0001_users.001.sql table 1, chunk 1
├── 0001_users.002.sql table 1, chunk 2
├── 0002_orders.001.sql
├── ...
├── _progress.log one filename per completed part
└── _skipped_errors.log statements skipped via --ignore-errors
Files sort in dump order, so cat parts/000_header.sql parts/0*.sql reconstructs a
valid dump (minus LOCK TABLES, see below).
python split_dump.py split dump.sql --out parts [options]| flag | default | meaning |
|---|---|---|
--out DIR |
parts |
output directory |
--max-chunk-mb N |
200 |
roll to a new file every N MB inside a table's data; 0 = one file per table |
--mode comments|generic |
comments |
comments uses mysqldump's -- Table structure markers; generic splits on DROP/CREATE TABLE for dumps made with --skip-comments |
--keep-locks |
off | keep LOCK TABLES / UNLOCK TABLES (stripped by default — a lock can't span parts, and it's only an optimisation) |
--force |
off | overwrite a non-empty output directory |
--progress-secs N |
2 |
seconds between progress lines |
Accepts .sql or .sql.gz (gzip is decompressed on the fly).
Progress looks like:
22.9% 50.5MB / 220.9MB | 6 tables | 100.9MB/s | ETA 0m01s
If it finishes reporting 1 tables, the dump has no standard comment markers — re-run
with --mode generic.
python split_dump.py inspect partsReports the largest single statement in the split, which is the minimum
max_allowed_packet the server must accept. Run this before importing; it turns the
most common import failure into a number you can act on.
biggest statement anywhere: 6.9MB (0323_mail.013.sql, line 31)
server needs at least this much max_allowed_packet. Recommended: 16.0MB
python split_dump.py import parts --db mydb [options]| flag | default | meaning |
|---|---|---|
--db NAME |
required | target database — must already exist |
--host / --port |
127.0.0.1 / 3306 |
TCP connection |
--socket PATH |
— | connect over a Unix socket instead (overrides --host) |
--docker CONTAINER |
— | pipe each part through docker exec -i CONTAINER mysql …, using the container's local socket |
--user |
root |
MySQL user |
--password |
prompt | passed via MYSQL_PWD, never on the command line |
--no-password |
off | connect with no password, skip the prompt |
--mysql PATH |
mysql |
path to the client binary |
--max-allowed-packet |
1G |
client-side limit (the server has its own — see troubleshooting) |
--ignore-errors CODES |
— | comma-separated error codes to skip instead of aborting |
--skip-all-errors |
off | skip every failing statement |
--no-fast |
off | don't disable autocommit / unique_checks / foreign_key_checks |
--no-binlog |
off | also SET sql_log_bin=0 — needs SUPER, large speedup |
--dry-run |
off | list what would be imported and stop |
The database is not created for you:
CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;A dump often contains objects that can't be created in isolation — a view referencing a
schema you aren't importing, a trigger whose DEFINER doesn't exist on this server.
Name the codes you're willing to lose:
python split_dump.py import parts --db mydb --ignore-errors 1146,1449Every skipped statement is written to parts/_skipped_errors.log with its part and line,
and counted at the end — a skip is always a record, never a silent hole.
0323_mail.001.sql: ERROR 1146 (42S02) at line 12: Table 'other_db.consulta' doesn't exist
Common codes:
| code | meaning |
|---|---|
1146 |
table doesn't exist (view or trigger referencing another schema) |
1050 |
table already exists |
1062 |
duplicate entry |
1091 |
can't drop; doesn't exist |
1449 |
definer user doesn't exist |
1227 |
access denied (needs SUPER for the definer) |
When an unlisted error stops the import, the message names the flag that would skip it.
Connection errors (codes 2000–2999) are never skippable, even with
--skip-all-errors. MySQL server has gone away means the rest of the part never
reached the server; skipping it would silently drop real data. Those always abort, and
the part is left un-recorded so the retry re-imports it in full.
Almost always a statement larger than the server's max_allowed_packet — not a network
problem, and it happens on localhost too. Compare the two numbers:
python split_dump.py inspect parts # what the dump needsSELECT @@max_allowed_packet, @@wait_timeout, @@net_read_timeout;max_allowed_packet is dynamic, so you can fix it without restarting:
SET GLOBAL max_allowed_packet = 268435456; -- 256M
SET GLOBAL net_read_timeout = 600;Then reconnect — the session value is copied from the global when the connection opens, so your current client keeps reporting the old number. This works well here because the importer opens a fresh connection per part.
To make it permanent, put it under [mysqld] (not [client], which only configures
client tools):
[mysqld]
max_allowed_packet = 256M
net_read_timeout = 600If the error log instead shows mysqld got signal or an OOM kill, the server crashed
rather than rejected the statement. Check free disk — a 16 GB dump typically wants
25–35 GB of InnoDB tablespace plus redo and temp — and check that
innodb_buffer_pool_size isn't larger than the machine can back.
MySQL refuses to read a config file that is writable by everyone, so whatever you put in
it never applied. chmod 644 the file. If it's bind-mounted into Docker from a Windows
host, permissions can't be fixed that way — pass the settings as server flags instead:
command: --max-allowed-packet=256M --net-read-timeout=600Add --no-binlog (needs SUPER) and, on the server for the duration of the import,
innodb_flush_log_at_trx_commit = 2. Restore it afterwards. Both trade crash durability
for speed, which is the right trade while loading a dump you still have on disk.
Delete parts/_progress.log to re-import everything, or delete individual lines to
re-run specific parts. Dropping and recreating the target database first is usually
cleaner than re-running over existing rows.
- Standard
mysqldumpoutput: one statement per line, newlines inside strings escaped. Dumps hand-edited into multi-line statements may split incorrectly — the post-split boundary check will warn you. - Parts are imported in filename order, which is dump order. Foreign keys are disabled during import, so ordering between tables doesn't matter.
- MySQL and MariaDB. Not PostgreSQL —
pg_dumpcustom-format dumps already restore in parallel withpg_restore -j. - The atomic-part guarantee is InnoDB's. MyISAM tables don't roll back, so a crash
mid-part can leave a partial table; re-running that part re-applies from the start
(its
DROP TABLE IF EXISTShandles the first part, later chunks may duplicate).
For dumps you control from the source side, mydumper /
myloader is the better tool — it dumps into
per-table chunks and restores them in parallel. This project exists for the case where
someone hands you a single enormous .sql file and re-dumping isn't an option.
MIT.