So recently I was attempting to migrate some rather large tables from one (slow) database host to another.  I was running mysqldump piped into a mysql client on localhost.  Unfortunately, I ran into a snag:

mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `SOME_TABLE` at row: 14913098

I had two things working against me in this situation.

1. I was forking a mysqldump process for each table in the database, so I was running 100+ mysqldump processes at the same time.
2. The host the data was dumping from was slow.

So since mysqldump returned the error, the issue seems to have originated on the host I was dumping from.  This is sometimes due to a max_allowed_packet issue, but max_allowed_packet was set at 16M on both hosts.  I also found this blog entry that sounded similar:
http://jeremy.zawodny.com/blog/archives/000690.html

Unfortunately, -q is enabled by default with –opt.  Foiled again!  I found some mentions of setting timeouts really really high on the database server, which made me think “What if the host is so slow it’s not able to return data before the session timeout is hit?”  So how do I make mysqldump return the data more often…

I started playing with the different options.  max_allowed_packet still returns a large INSERT.  Setting  –no-extended-insert would also get this result, but that could more than double my migration time (already projected to be several days).  Then I found this only slightly documented option:

–net_buffer_length

The default setting seems to be 1M in my installation, so setting this down to 128K or 64K will reduce the size of the INSERT generated.  This also means that data is flushed out to the client more often, working around setting the timeouts obcenely high.  This also means that if something is really causing the source database host to crunch and return really slowly, we’ll probably return data fast enough to avoid hitting the timeouts.  If you’ve got rows bigger than what you set net_buffer_length to, mysqldump is smart enough to adjust the buffer for that row so you won’t get a partial result.