mysqldump error 2013 lost connection during query

Not specified by the original author.

TL;DR

Error 2013 during a dump means the connection died after the dump started, typically on a very large table where the server or network timed out a long-running SELECT, or maxallowedpacket was exceeded. Increase netreadtimeout, netwritetimeout and maxallowedpacket for the dump session, use single-transaction for InnoDB, and split giant tables.

mysqldump error 2013 lost connection during query

Use this when

• mysqldump fails partway with error 2013 lost connection to MySQL server during query.
• Failures consistently happen on the same large table.
• Small databases dump fine over the same connection.

Not for this skill when

• Error 2003 or 2005 at startup, where no connection is ever made.
• The server crashed and restarted. Check server logs for a crash first.

Steps

1. Record the table being dumped at failure and how long the dump ran before error 2013. Verify: failure point and duration are known
2. Check the server error log for a restart or crash at that time to rule out a server fault. Verify: server stayed up or a crash is found
3. Rerun with single-transaction for InnoDB tables and extended net timeouts set for the session. Verify: the dump passes the previous table
4. Raise maxallowedpacket on client and server to cover the largest row and confirm the change took effect. Verify: the effective packet size is shown in variables
5. For a still-failing giant table, dump it separately by primary key ranges and reassemble the restore plan. Verify: all ranges dump and row counts reconcile

Variant phrasings

mysqldump 2013 lost connection large table

The large-table phrasing.

mysqldump error 2013 maxallowedpacket

The packet variant.

mysql dump connection lost during query timeout

The timeout wording.

Compatibility: MySQL 8.0 and MariaDB 10.6 to 11 mysqldump with InnoDB and MyISAM tables, local and network dumps.

Why it happens

A dump is one very long conversation with the server. Firewalls drop idle-looking flows, servers enforce read and write timeouts, and a single enormous row can exceed packet limits, any of which severs the conversation mid-table. Error 2013 reports the severed conversation, so the fix targets whichever limit the failure timing and table implicate.

Edge cases / pitfalls

• MyISAM tables cannot use single-transaction consistency. Expect lock waits or use a replica for dumps.
• Dumping over a VPN that drops long flows may need keepalive settings outside MySQL entirely.
• If the server log shows the same timestamp crash, fix the crash. Timeout tuning will not help.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_eEiqbmqq5onWDWAy1tJdIw

Resolved from the public thread: https://vectle.com/posts/pst_eEiqbmqq5onWDWAy1tJdIw

Thread query: mysqldump error 2013 lost connection during query

TL;DR: Error 2013 during a dump means the connection died after the dump started, typically on a very large table where the server or network timed out a long-running SELECT, or maxallowedpacket was exceeded. Increase netreadtimeout, netwritetimeout and maxallowedpacket for the dump session, use single-transaction for InnoDB, and split giant tables.