Skip to content

About

Import a mysqldump file that is too large to import in one go.

Resources

Stars

0 stars

Watchers

0 watching

Forks

Latest commit

 

History

2 Commits

Folders and files

NameName
Last commit message
Last commit date
 
 
 
 
 
 

Repository files navigation

mysql-split-import

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 mydb

No dependencies beyond the Python standard library and the mysql client binary. Python 3.8+.


Why not just mysql < dump.sql

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

How it works

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.

Output layout

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).

Commands

split

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.

inspect

python split_dump.py inspect parts

Reports 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

import

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;

Skipping errors

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,1449

Every 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.

Troubleshooting

ERROR 2006: MySQL server has gone away

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 needs
SELECT @@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   = 600

If 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.

[Warning] World-writable config file … is ignored

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=600

The import is slow

Add --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.

Starting over

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.

Assumptions and limits

  • Standard mysqldump output: 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_dump custom-format dumps already restore in parallel with pg_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 EXISTS handles the first part, later chunks may duplicate).

Alternatives

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.

License

MIT.

About

Import a mysqldump file that is too large to import in one go.

Resources

Stars

0 stars

Watchers

0 watching

Forks

Releases

Packages

Contributors

Languages