When executing mysqldump against a MySQL slave server, you might encounter:

mysqldump: Got error: 2013: Lost connection to MySQL server during query when doing refresh

This typically indicates that the connection between the client (mysqldump) and MySQL server was terminated before the query completed. Unlike basic timeout errors, Error 2013 specifically occurs mid-query execution.

Several factors unique to replication environments can cause this:

• Replication Lag: When the slave is significantly behind the master, large transactions might time out
• Network Instability: Especially common in cloud or containerized environments
• Resource Constraints: The slave server might be under-provisioned for dump operations

Add these parameters to your mysqldump command or my.cnf:

mysqldump \
--net-read-timeout=3600 \
--net-write-timeout=3600 \
--max-allowed-packet=1G \
--wait-timeout=28800 \
--compress \
--quick \
--single-transaction

For the MySQL server side (my.cnf):

[mysqld]
net_read_timeout = 7200
net_write_timeout = 7200
interactive_timeout = 28800
wait_timeout = 28800
max_allowed_packet = 1G
slave_net_timeout = 600

For large databases, consider these approaches:

# Use Percona XtraBackup for physical backups
xtrabackup --backup --slave-info --target-dir=/backup/$(date +%F)

# Or use MySQL Shell's parallel dump
mysqlsh -e "util.dumpSchemas(['schema1','schema2'], '/backup', {threads: 8})"

Enable these logs to diagnose the root cause:

# On the MySQL server
SET GLOBAL general_log = 'ON';
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;

# Monitor network connectivity during dump
tcpdump -i eth0 -w mysqldump.pcap port 3306

MySQL Error 2013 ("Lost connection to MySQL server during query") typically occurs when a client (in this case, mysqldump) loses its connection to the MySQL server while executing a query. This is particularly common during long-running operations like backups.

The error often appears in these scenarios:

• The server timed out the connection due to inactivity
• Network issues between the client and server
• Server-side resource constraints (OOM killer, crashed)
• Replication lag when dumping from a slave

Here are the most effective fixes:

1. Increase Connection Timeout

Add these parameters to your mysqldump command:

mysqldump --net_read_timeout=3600 --net_write_timeout=3600 \
--connect_timeout=60 [other_options] database_name

2. Optimize Server Configuration

On the MySQL server, adjust these in my.cnf:

[mysqld]
wait_timeout = 28800
interactive_timeout = 28800
max_allowed_packet = 1G
net_read_timeout = 3600
net_write_timeout = 3600

3. For Replication Slaves

When dumping from a slave, add:

mysqldump --master-data=2 --single-transaction \
--flush-logs [other_options] database_name

If the issue persists:

• Check MySQL error logs (/var/log/mysql/error.log)
• Monitor network stability between servers
• Verify slave replication status with SHOW SLAVE STATUS\G
• Consider using mydumper instead of mysqldump for large databases

Here's a robust backup script template:

#!/bin/bash

TIMESTAMP=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="/backups/mysql"
LOG_FILE="$BACKUP_DIR/backup_$TIMESTAMP.log"

mysqldump \
--host=slave-db.example.com \
--user=backup_user \
--password='secure_password' \
--single-transaction \
--max_allowed_packet=512M \
--net_read_timeout=3600 \
--net_write_timeout=3600 \
--all-databases \
--routines \
--events \
--triggers \
--master-data=2 \
| gzip > "$BACKUP_DIR/full_backup_$TIMESTAMP.sql.gz" 2>> "$LOG_FILE"

Remember to test your backup restoration procedure regularly.