Mysqldump export Lost connection to MySQL server during query

The following statement is used to export data during mysqldump backup and export.

Mysqldump-uroot-p 'passwd'-opt-default-character-set = utf8-triggers-R-hex-blob-single-transaction-no-autocommit-master-data = 2 test_game mail> mail. SQL

An error similar to the following occurs:

Mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table 'mail' at row: 1754283

Query materials

Probably because mysqldump is too late to accept the data sent from the mysql server, the Server's data will backlog in the memory waiting for sending. This wait is not indefinite, when the Server waits for more than net_write_timeout (60 seconds by default), it loses patience. The connection to mysqldump is disconnected and a Got error: 2013: Lost connection is thrown.

Adding net_write_timeout can solve the above problem. In practice, it is found that after net_write_timeout is increased, the Server consumes more memory, and sometimes even causes swap usage (it is not sure whether net_write_timeout is modified ). We recommend that you change net_write_timeout to a large value (for example, 1800) before mysqldump. After mysqldump ends, change this value to the default value of 60.

Use the following command to set the temporary global effect in the SQL command line:

Set global net_write_timeout = 1800;

After modifying this parameter, back up again and no error will be reported

Note that this parameter is not the mysqldump option, but a configuration parameter of mysql.

Other possible solutions with error: 2013:

Add

[Mysqld]

Skip-name-resolve

Prohibit MySQL from performing DNS resolution on external connections. This option can eliminate the time for MySQL to perform DNS resolution.