Tuesday, April 16, 2013

MySQL Backup fails with mysqldump: Error 2013: Lost connection to MySQL server during query'


MySQL Backup fails with mysqldump: Error 2013: Lost connection to MySQL server during query'

This error occurs mostly while backing up MySQL databases to tape devices. The reason for this is the MySQL Settingnet_write_timeout, which defaults to a value of 60 seconds.
The tape open can take longer than 60 seconds, as such, the MySQL database resets the connection during the backup process.
This problem can be solved by adding a higher value for the variable net_write_timeout in /etc/my.cnf.
[mysqld]
 ... other options ..
net_write_timeout = 180
after adding this setting, restart your mysql daemon. If a restart is not possible the setting can also be applied online, but is lost after next restart:
# mysql -u root -p -e "set global net_write_timeout=180;"
If 180 seconds are set and the backup is still failing with the same error message, try to use a higher value for the timeout.