mysqldump report error-Error 2013 Lost connection to MySQL server

created at 07-07-2021 views: 1

Specific error information

error message:

mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table xxx at row: 258609

reason

I looked at it online and probably said that during mysqldump, because the amount of data is too large to receive so much data at one time, the data sent from the server will be backlogged in memory waiting to be sent. This waiting time is the time of net_write_timeout. When this time is exceeded, mysqldump will be disconnected and an error will be thrown: error 2013: Lost connection.

Solution

Most of the Internet say that you can modify the net_write_timeout time. For example, change to 1200.

Operation process:

  1. Log in to mysql first: mysql -u root -p
  2. First check the default time: show global variables like '%timeout%'
  3. Modification time: set global net_write_timeout = 1200
  4. You can check again whether it has changed: show global variables like '%timeout%'
  5. Exit mysql and execute mysqldump again: mysqldump -uroot -p -B monitor> /var/www/mysqlbackup/db.xxx.sql

During the above operation, there is no need to restart mysql. It may be that this value will not be reset to the default value within a certain period of time after exiting mysql. If it doesn't work, try restarting: service mysqld restart.

In addition, this net_write_timeout time, it is best to change back to the default value after the operation of mysqldump.

Please log in to leave a comment.