SQL Dump Splitter: Split Large SQL Files for Import

Fifteen chunk files, fourteen of them 40 MiB to the byte plus a 643 KB remainder, produced in 2.9 seconds by the command every tutorial for this problem recommends. The first one aborts at line 1,143,275 with a syntax error, after loading 1,143,193 of the 1,145,352 rows it contained. The second aborts on line 1. That is what split -C 40m does to a 560 MB dump, and we ran it on 13 August 2026 rather than trust the write-ups, this article's own earlier version included.

A dump splitter is the right instrument for exactly one failure: the web interface refuses your file because it is too big. It is the wrong instrument for a statement that exceeds the server's packet limit, for a PHP execution timeout, and for a dump whose rows are individually enormous. Sorting out which one you have takes two minutes and saves the afternoon, which is where this starts. The context is usually a migration, a restore, or the moment a web scraping job hands you a database instead of a CSV. Everything below was checked against MariaDB 10.11.14, MySQL client 8.0.46, GNU coreutils 9.11 and the vendors' own pages on 13 August 2026.

What actually blocks the import

Five PHP settings decide whether phpMyAdmin will even accept the file, and none of them has anything to do with MySQL:

• upload_max_filesize and post_max_size cap the uploaded file. post_max_size has to be the larger of the two, a detail phpMyAdmin's own FAQ calls out.
• max_execution_time and max_input_time kill the script mid-restore, leaving the database half-loaded and no record of where it stopped.
• memory_limit bites when a single statement is large enough to matter.

Then there is the limit nobody mentions, and it is the one that no splitter can help with. max_allowed_packet caps a single statement, not the file. The mysql client ships with a 16,777,216-byte default, which we read from --help on both the MySQL 8.0.46 and the MariaDB 10.11 clients. Server side the defaults diverge: MySQL 8.4 documents 64 MB, while a stock MariaDB 10.11.14 reported 16,777,216 when we asked it. Feed it a statement bigger than that and you get this, with exit status 1 and nothing loaded:

ERROR 1153 (08S01) at line 39: Got a packet bigger than 'max_allowed_packet' bytes

We produced that deliberately: two rows of 20 MB each in one INSERT, giving a 41,945,038-byte file that is essentially a single statement, imported against a server set to 16 MB. Cutting that file into 40 MB pieces is arithmetically impossible. The floor on your chunk size is the largest single statement in the dump, and no tool can go below it.

So the triage is short. File too big for the upload form, statements normal size: split it, or move it by FTP. Statements too big: raise max_allowed_packet on both ends, or re-dump with a smaller --net-buffer-length. Timeout rather than size: a staggered importer or the command line. One giant row: nothing helps except raising the limit.

The split that corrupts your dump

Here is the claim that circulates everywhere, this site's previous version of this page included: a mysqldump file has one statement per line, so splitting at line boundaries keeps statements intact. It is wrong, and how wrong depends on which tool wrote the file.

We dumped the same 3,000 rows twice, from the same server, changing only the client binary:

Both wrote a single INSERT. MySQL put it on one line roughly a megabyte long, capped by --net-buffer-length (default 1,046,528). MariaDB spread the identical statement across 3,060 lines, one row per line, ending each with a comma and the last with a semicolon. On MariaDB output, line count tells you nothing about statement boundaries: dumping with --skip-extended-insert changed the file from 4,200,647 lines to 4,200,608 and grew it by 20.5%, because the only difference was repeating the INSERT INTO ... VALUES prefix on every row.

Which is why split -C 40m shreds a MariaDB dump. Our 587,843,030-byte test file became fifteen pieces. The first ended (1145352,517014,'seabright',360.30), with a dangling comma; the second opened (1145353,225459,'bluepine',399.55), with no verb in sight. Importing them:

chunk_aa.sql: ERROR 1064 (42000) at line 1143275 ... near '' at line 2160
chunk_ab.sql: ERROR 1064 (42000) at line 1 ... near '1145353,225459,'bluepine',399.55),

The arithmetic is exact: 1,145,352 rows reached the server, 1,143,193 landed, and the 2,159-row statement that got guillotined was lost whole. mysql returns 1 in both cases, which is the good news. The bad news is that mysql --force returns 0 on the same broken input, so a loop that adds --force to push past errors reports success over a corrupt database.

split -C does not mean what it sounds like

GNU coreutils 9.11 documents -C, --line-bytes=SIZE as "put at most SIZE bytes of records per output file". That reads like a guarantee. It is not one, and the failure mode is undocumented on the man page, so we tested it: a file containing one 50 MB line and one short line, run through split -C 40m, produced 41,943,040 and 10,485,862 bytes. The long line was cut mid-record. -C keeps lines whole only while lines fit.

That matters for MySQL-format dumps too, because their lines run to a megabyte by default and go higher if anyone raised --net-buffer-length. Push one statement past your chunk size and the same corruption appears in a file whose structure looked safe.

Portability is the other trap. FreeBSD's split(1) documents -a, -b, -c, -d, -l, -n and -p, with no -C and no --additional-suffix. If your machine ships a BSD split rather than GNU coreutils, the command in every tutorial is not available to you at all.

What every chunk needs that split never gives it

Even when the pieces are syntactically valid, they are missing the ten-line preamble mysqldump writes once at the top. Two of those lines do real work.

Character set. We dumped a row reading Björk Ångström café, stripped the preamble, and imported it over a latin1 connection, which is what a chunk arriving without SET NAMES utf8mb4 looks like. The row came back as Björk Ångström café, 32 bytes instead of 23. Exit status 0. No warning. This is the only failure in the whole article that does not announce itself, which makes it the expensive one.

Foreign keys. A chunk holding CREATE TABLE child whose parent lives in a later chunk fails with ERROR 1005 (HY000) ... errno: 150 "Foreign key constraint is incorrectly formed". Prepend SET FOREIGN_KEY_CHECKS=0; and the same chunk imports with exit 0. That line is in the preamble, and split leaves it in chunk one.

There is also no such thing as "the schema chunk". In our two-table dump, CREATE TABLE offers sat on line 26 and CREATE TABLE products on line 2,800,152 of 4,200,647. mysqldump interleaves each table's definition with its own data, alphabetically, so advice to "import the schema first" describes a file layout that does not exist. Chunks have to go in order, and any chunk can carry a CREATE TABLE.

A splitter that does not break anything

Thirteen lines of awk fix all of it: cut only after a line ending in a semicolon, and copy the preamble into every part.

awk -v max=$((40*1024*1024)) '
BEGIN { n = 1; head = 1 }
head && $0 !~ /^(DROP|CREATE|INSERT|LOCK)/ { pre = pre $0 "\n"; next }
{
  head = 0
  if (out == "") { out = sprintf("part_%03d.sql", n); printf "%s", pre > out; size = length(pre) }
  print > out
  size += length($0) + 1
  if ($0 ~ /;[ \t]*$/ && size > max) {
    close(out); n++
    out = sprintf("part_%03d.sql", n); printf "%s", pre > out; size = length(pre)
  }
}' big_dump.sql

On the 560 MB file this ran in 5.2 seconds and produced 14 parts of 30 to 43 MB. Every part ends in a semicolon, every part carries all ten preamble lines, and importing them in filename order restored 1,400,000 and 2,800,000 rows exactly. If the input happens to end on a chunk boundary you get a final part containing nothing but the preamble, which imports as a no-op.

Two habits make the parts survivable. Dump with --insert-ignore so a re-run of a part that partly succeeded does not collide on the primary key: we imported the same chunk twice and got exit 0 and 1,000 rows, not a duplicate-key error. And run the loop so it stops on the first failure, because the default is to keep going:

for f in part_*.sql; do
  mysql -u user -p"$PASS" target_db < "$f" || { echo "failed on $f"; break; }
done

The tools the roundups still recommend

We rechecked each of these against its own site, repository or release page in August 2026.

SQLDumpSplitter3 is gone. Its GitHub repository returns 404, and philiplb.de/sqldumpsplitter3/ now redirects to a different product. The last announcement post on the author's own site is dated 16 October 2020 and covers a release that added a command-line interface and USE-statement tracking. Every article still calling it "the best-known free option" is describing software you cannot download from its own homepage.

Its successor is SQLSplitter, and it is not free. Version 1.1.0, dated 2024-07-24, with builds for Windows AMD64, macOS on ARM64 and AMD64, and Linux AppImages for both architectures. The pricing on the vendor's page reads: "Splitting files up to 10 MB is free. Afterwards, it is a one-time per file purchase of 1.50 €." Per file, not per licence, which is an odd shape for a tool whose entire audience has files over 10 MB. It does use a real SQL parser rather than a byte counter, so it will not produce the corruption above.

BigDump still works and is still from 2015. The script's own header reads BigDump ver. 0.36b from 2015-04-30, GPL, copyright 2003-2015, and it moved to mysqli at some point before that. It solves the timeout problem rather than the size problem: you FTP the dump to the server, drop the script beside it, and it runs a limited number of queries per request before reloading itself. phpMyAdmin's own FAQ 1.16 still names it. Eleven years without a release is not automatically a defect for a single-file PHP script, but check it against your PHP version before you trust a restore to it.

phpMyAdmin 5.2.3 shipped 8 October 2025, and the project says support for the 5.x line ends soon, with 6 requiring PHP 8.2 or newer. Before reaching for any splitter, use its $cfg['UploadDir'] setting: the FAQ describes it as letting you "upload a file to the server via scp, FTP, or your favorite file transfer method", after which phpMyAdmin imports it from disk with no HTTP upload involved. That removes upload_max_filesize and post_max_size from the picture in one config line.

Adminer 6.0.0 shipped 2026-08-07, one week before this article, which makes it the healthiest project in this section by a wide margin.

mysqldumpsplitter is a shell script under MIT that pulls a named database or table out of a dump rather than cutting it by size. That is usually the operation you actually wanted.

The numbers

Measured on a two-vCPU Ubuntu 24.04 container, MariaDB 10.11.14 with stock settings: innodb_buffer_pool_size 128 MB, innodb_flush_log_at_trx_commit 1, doublewrite on. Source data was 1,400,000 product rows and 2,800,000 offer rows, 560.6 MiB as a default mariadb-dump. Your absolute numbers will differ; the ratios are the point.

Three results are worth arguing with. Splitting correctly costs nothing: fourteen sequential imports beat the single file, well inside run-to-run noise. Compression is for transfer, not for speed: the gzip stream took about half again as long, because on two cores gunzip competes with the server for CPU. It still turns a 560 MB upload into a 118 MB one, which is the reason to do it. Parallel loading is the only route that changes the shape of the problem, and it came in 1.6 to 2 times faster than the serial pipe depending on which baseline run you compare against.

The --skip-extended-insert result deserves its own measurement, because badly configured export tools produce that format constantly. On a controlled 500,000-row subset:

Forty-three times slower, and the cause is durability rather than parsing: with innodb_flush_log_at_trx_commit at 1, every autocommitted row is its own fsync. Wrapping the file in SET autocommit=0; and COMMIT; recovers most of it without touching server config. If a dump you have been handed is one row per statement, that is a five-second fix worth knowing.

Do not split it, dump it in pieces

Every technique above is damage control on an output format built for a single serial pipe. Both current dump tools already produce chunked output, and neither needs a splitter.

MySQL Shell's dump utilities chunk by default. The manual states that chunking defaults to true, bytesPerChunk to 64 MB, compression to zstd at level 1, and threads to 4. On the way back in, util.loadDump() loads chunks in parallel on 4 threads and keeps a progress file named load-progress.server_uuid.json, so an interrupted restore resumes where it stopped instead of starting over. That last property is the one the whole splitting exercise was trying to fake. Oracle is steering people there deliberately: mysqlpump has been deprecated since MySQL 8.0.34, with the manual pointing at MySQL Shell and mysqldump instead.

mydumper does the same for MySQL and MariaDB, with -F for chunk size in MB and -c to gzip each piece. Our mydumper -B db -t 4 -F 40 -c produced 21 files totalling 119 MiB in 18.6 seconds. It reached 1.0.0 in April 2026 and is now on 1.0.3-1, dated 3 June 2026, with 1.0.1-1 of 17 May 2026 announced as its first LTS. Check what you actually have installed: Ubuntu 24.04 ships 0.10.x, several major versions behind.

If you are stuck with mysqldump, two flags make the output splitter-friendly before it ever hits disk. --net-buffer-length sets the statement size directly, and it is proportional: on the same 3,000 rows we got 64 statements at 16 KB, 4 at 256 KB, and 1 at the 1,046,528-byte default. Dumping one table per file with a loop over SHOW TABLES removes the ordering problem entirely.

Where each route stops working

No shell access at all. This is the case that keeps BigDump and the GUI splitters alive. $cfg['UploadDir'] plus FTP is the better first move, and it is free.

MyISAM tables. --single-transaction gives a consistent snapshot for InnoDB only. Mixed-engine databases dumped that way are internally inconsistent and no import technique fixes it after the fact.

One row larger than the packet limit. Reducing --net-buffer-length does not help. We tried it against a table of 20 MB blobs and still got two statements with 20 MB lines, because the batching knob cannot make a single row smaller.

Views, triggers and stored routines. They carry DEFINER= clauses naming a user that may not exist on the target. Chunked or whole, the restore fails on those statements, and the answer is sed on the dump or creating the user first.

Dumps of scraped data are the worst-shaped input for any of this: usually one enormous table, no natural split point, and regenerated on a schedule. If the same 40 GB moves every week, the file is the wrong unit of transfer, and a per-table incremental export or a managed data feed removes the import step rather than optimising it. Where the rows come from a crawler rather than a backup, the cheaper fix is upstream: have the extraction pipeline write batches straight into the target database instead of producing a monolithic .sql file at all.

For anything that takes more than a minute, put pv in the pipe. Version 1.11.0, dated 11 June 2026, GPLv3, and it turns a silent hour into a progress bar with an ETA:

pv big_dump.sql | mysql -u username -p target_database

Which method should you use

The bottom line

The GUI splitter that every article on this subject recommends has been replaced by a paid one, and the free command that replaced it corrupts half the dumps it touches. What survives contact with measurement is duller: move the file by FTP and import it from disk, split on statement boundaries with the preamble repeated if you must split at all, and switch to a dump tool that chunks its own output the moment this becomes a recurring job rather than an emergency.

Check which limit you are actually hitting before you split anything. Only one of the six is the file size.