The short version
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table means the server dropped the connection in the middle of sending rows. The common cause is a slow reader: whatever mysqldump writes into (a pipe, gzip on a busy box, ssh, a network share) stops reading, the server waits longer than net_write_timeout to push the next block, and it gives up. The fix is to give the dump more time (leave mysqldump's --network-timeout on, or raise net_write_timeout for the session), or write the dump to a local file first and move it afterwards. A connection killed by someone else, or by a proxy, gives the same message.
What the error looks like
I reproduced it against MySQL 8.4.11 with the Oracle mysqldump 9.3.0 client, on a test table of 300,000 orders (about 42 MB of SQL once dumped). The dump went into a reader that sleeps for 20 seconds before it reads anything, which is what a stalled gzip, a full disk on the other end of a pipe, or a slow ssh link looks like to the server. In the commands below the database name is shortened to shop, and the exit= lines are printed by my test script, not by mysqldump:
$ mysqldump --defaults-extra-file=my.cnf --skip-network-timeout \
--init-command="SET SESSION net_write_timeout=5" \
--single-transaction shop orders | (sleep 20; cat > /dev/null)
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `orders` at row: 48501
exit=3
The dump got 48,501 rows out, as many as fit into the pipe and socket buffers, then nothing moved for five seconds and the server closed the connection. Exit status 3 is what a script should check for; a dump that ends with this error leaves a truncated file behind.
Why does mysqldump lose the connection during a query?
MySQL's own page on Lost connection to MySQL server lists three likely causes. The one that matters for dumps: "the 'during query' form happens when millions of rows are being sent as part of one or more queries", and the advice is to raise the network timeouts. mysqldump reads each table with one SELECT and, with --quick (on by default), retrieves rows "a row at a time" instead of buffering the whole table. That keeps memory low, but it also means the server has to keep writing to a client that may not be keeping up.
The server side of that is net_write_timeout: "The number of seconds to wait for a block to be written to a connection before aborting the write." Its default is 60. The same page defines wait_timeout, the idle limit for a connection, default 28800 seconds. A dump that is busy sending rows is not idle, so wait_timeout is rarely the culprit for a dump that dies halfway; net_write_timeout is.
The defaults on my test server, for reference:
Variable_name Value
max_allowed_packet 67108864
net_read_timeout 30
net_write_timeout 60
wait_timeout 28800
Why the same dump works for some people and not others
The mysqldump client turns most of this off for you. Its --network-timeout option is "enabled by default" and, per the manual, it enables "large tables to be dumped by setting --max-allowed-packet to its maximum value and network read and write timeouts to a large value". To get the error above at all, I had to switch it off with --skip-network-timeout. With it left on, the identical command, including my own SET SESSION net_write_timeout=5, finished:
$ mysqldump --defaults-extra-file=my.cnf \
--init-command="SET SESSION net_write_timeout=5" \
--single-transaction shop orders | (sleep 20; cat > /dev/null)
exit=0
So if you see 2013 from a dump, check how it is invoked. Wrapper scripts, backup tools and old my.cnf files sometimes carry skip-network-timeout, and some tools that call the dump code do not set the timeouts the way the command-line client does.
Fix 1: give the session more time
If you must keep a slow consumer, raise the timeout for the dump's own session, without touching the server's global settings:
$ mysqldump --defaults-extra-file=my.cnf --skip-network-timeout \
--init-command="SET SESSION net_write_timeout=600" \
--single-transaction shop orders | (sleep 20; cat > /dev/null)
exit=0
Simpler still: do not pass --skip-network-timeout, and let the default do this for you.
Fix 2: write to a local file, then move it
The slow part is usually the thing on the far side of the pipe. Dumping straight to a local file removes it:
$ mysqldump --defaults-extra-file=my.cnf --skip-network-timeout \
--init-command="SET SESSION net_write_timeout=5" \
--single-transaction shop orders > orders.sql
to file exit=0 size= 43925539
Even with a five-second write timeout, the dump completed, because a local disk keeps up. Compress and copy the file afterwards. If local disk is short, dump one table at a time, or split a large table with --where ranges on its primary key, for example --where="id <= 1000000", then --where="id > 1000000 AND id <= 2000000".
A killed connection looks exactly the same
I started the same dump, waited three seconds, found its query in the process list and killed it from another session:
$ mysql -e "SELECT id, command, LEFT(info,60) FROM information_schema.processlist
WHERE info LIKE 'SELECT /*!40001 SQL_NO_CACHE */%'"
328 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `orders`
$ mysql -e "KILL 328"
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `orders` at row: 49886
exit=3
Same message, same exit code. If the timeouts are not the problem, look for something that ends long connections: a DBA's kill script, a load balancer or proxy with an idle limit, a firewall dropping long-lived TCP sessions, or the server restarting. The server's error log and the proxy's logs will say which; mysqldump cannot tell the difference.
What about max_allowed_packet?
The MySQL page above names one more cause: "BLOB values that are larger than max_allowed_packet, which can cause this error with some clients". I tested it with a 12 MB LONGBLOB row. With this client, the failure came out as a different error, 2020, when I lowered the client's own limit below the row size:
$ mysqldump --defaults-extra-file=my.cnf --skip-network-timeout \
--max-allowed-packet=4M shop attachments > att.sql
mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table `attachments` at row: 1
exit=3
Lowering the server's global max_allowed_packet to 4 MB did not stop the dump of that row in my run; the client's limit was the one that bit. The client's default here is 24 MB (max-allowed-packet 25165824 in mysqldump --help), and --network-timeout raises it to the maximum. If your rows are bigger than the limit you set, raise --max-allowed-packet on the dump, and on the server you restore into.
A checklist when 2013 shows up
- Is
--skip-network-timeoutset anywhere: the command, a wrapper, an option file? Remove it. - Is the dump piped into something slow? Dump to a local file first.
- Does the error repeat at a similar row each time, after a similar number of seconds? That points to a timeout, so raise
net_write_timeoutfor the session. - Does it happen at random points, or after a fixed wall-clock time? Look for a proxy, firewall or kill script.
- Are there very large rows? Raise
--max-allowed-packet.
The other mysqldump failures, and the options that make a dump clean for a migration, are in mysqldump errors 1044 and 1045 and mysqldump options for a clean migration.