mysqldump lost connection
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table
Trying to dump and reload a large (multi-hundred GB) database via a pipe, was failing with that error.
mysqldump [stuff] | mysql [other stuff]
Most of the pages on the internet fail to find any useful resolution, most suggesting that you try altering the timeouts on the destination server. It turns out that it's caused by timeouts on the source server. After loading the data, mysql re-enables the keys, and stalls while this is done. If the index rebuild takes longer than the value of net_{read,write}_timeout on the source server, then it drops the connection. The solution is (on the source server, that mysqldump is reading from):
set global net_write_timeout = 28800; -- Or something more suitable
set global net_read_timeout = 28800; -- Or something more suitable
This is the case, even if you're reading from a UNIX domain socket.
It works when you're writing to a file, because the file write doesn't stall for the same length of time, and of course, files don't care how long it is between reads.
Trying to dump and reload a large (multi-hundred GB) database via a pipe, was failing with that error.
mysqldump [stuff] | mysql [other stuff]
Most of the pages on the internet fail to find any useful resolution, most suggesting that you try altering the timeouts on the destination server. It turns out that it's caused by timeouts on the source server. After loading the data, mysql re-enables the keys, and stalls while this is done. If the index rebuild takes longer than the value of net_{read,write}_timeout on the source server, then it drops the connection. The solution is (on the source server, that mysqldump is reading from):
set global net_write_timeout = 28800; -- Or something more suitable
set global net_read_timeout = 28800; -- Or something more suitable
This is the case, even if you're reading from a UNIX domain socket.
It works when you're writing to a file, because the file write doesn't stall for the same length of time, and of course, files don't care how long it is between reads.