Home / ubuntu / Questions / 1110509
Accepted
WilliWonka
Asked: 2019-01-18 02:18:42 +0800 CST

mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table

  • 772

Recently, i was trying to make a slave working by dumping via ssh the databases.

I had discart percona-xtrabackup because i liked to select which databases i'de like to sync.

For make the slave work, i used the next script, which connect to the master, makes the dump of the select database and restore it:

#!/bin/bash

sql_error_check()
{
    if [ $? -ne 0 ]; then
        echo "ERROR at mysql command!"
        exit 1
    fi
}


DBS=( db1 db2 db3 db4 db5... )
PASSW="SuperSecurePass"

for i in ${DBS[*]}
do
    echo "- Deleting ${i}"
    mysql -p${PASSW} -e "drop database IF EXISTS ${i}"
    sql_error_check
    echo "- Creating ${i}"
    mysql -p${PASSW} -e "create database ${i}"
    sql_error_check
    echo "- Dumping & restoring ${i}"
    ssh -C [email protected] "mysqldump -p${PASSW} -q --max_allowed_packet=1024M ${i}" | pv | mysql -p${PASSW} ${i}
    sql_error_check 
done

Every thing seems to work perfect, but in some queries (huge alter tables) i get the error:

mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table

mysql mariadb

1 Answers

  1. Best Answer

    The solution was prety easy. This problem happend becouse when the dump is passing throug an ssh canal, and an opperation like "alter" is running, the data transfer needs to be stoped wating for the querie to finish. The time that the connection will wait to new data is defined in my.cnf in a really low time (30 seconds by default).

    For fix this error, the only thing needed is modify this two params on the mysql config:

    net_read_timeout    = 3600 
net_write_timeout   = 3600

    This will prevent the connection to get a timeout and prompt this error.

    Due to the docu, is defined as:

    The number of seconds to wait for more data from a connection before aborting the read. When the server is reading from the client, net_read_timeout is the timeout value controlling when to abort. When the server is writing to the client, net_write_timeout is the timeout value controlling when to abort. See also slave_net_timeout.

    • 2