How to deal with enormous line lengths created by mysqldump

By default, mysqldump generates only one INSERT command per table, resulting in one (very long) line of inserted data for each table that got dumped. This is essentially because the "batch" inserts are much faster than if it generated a separate INSERT query for every record in every table.

So, it's not that mysqldump has created arbitrarily long lines, and you can just impose some other cutoff length. The lines are long for a reason.

If it's really important to get the INSERTs broken down onto multiple lines, you can indicate that with:

mysqldump --extended-insert=FALSE --complete-insert=TRUE ...

Note, however, that restoring tables will take longer in this format.

I was browsing the MySQL source code looking for a solution to this problem today. The maximum line length is enforced by the variable opt_net_buffer_length which is supposed to match the MySQL server's buffer size. It is comically large.

But anyway, it's an option, so just do this:

mysqldump --net_buffer_length=5000 ...

The minimum value is 4096.

I came across an answer on the MySQL forums, which conclusively shows adding '\n' after each INSERT group is not possible using mysqldump alone, without modifying the source:

The extended format cannot be 100% properly parsed based on the comma or parenthesis, you would to count fields. The best solution, fix mysqldump to linebreak on output.

Very minor change: On line 3506, you can see where the row-ending comma is output:
fputc(',',md_result_file); /* Always row break */

Simply insert this line immediately after line 3506:
fputc('\n',md_result_file); /* Lon Binder says wrap that line! */

re-compile and done.

@see http://forums.mysql.com/read.php?28,420002,426110#msg-426110

Thanks Lon B!

(I've included the content from the MySQL forum just in case the forum disappears.)