【Exception】mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table...

解决MySQL备份Error2013

最新推荐文章于 2023-12-19 21:51:54 发布

原创最新推荐文章于 2023-12-19 21:51:54 发布 · 2.7k 阅读

1 ·

CC 4.0 BY-SA版权

本文为【tanpenggood】原创文章，允许转载，但转载必须注明出处并附带首发链接！

Exception 专栏收录该内容

45 篇文章

订阅专栏

本文介绍了解决MySQL备份过程中出现Error2013的详细步骤。问题源于数据量过大导致的timeout超时，通过调整net_read_timeout和net_write_timeout参数，成功解决了这一问题。

今天备份mysql数据时，碰到Error 2013的错误，一起来看一下是什么原因吧！

案发现场

-bash-4.1$ mysqldump -uroot -p1234456 tpcms > ~/mysql-backup/tpcms.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `cms_basic` at row: 237

原因分析

dump的数据量太大，超过了设置的timeout时间

下面执行查询可以看到，net_read_timeout为30秒，net_write_timeout为90秒

mysql> show global variables like '%timeout%';
+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| connect_timeout             | 10       |
| delayed_insert_timeout      | 300      |
| have_statement_timeout      | YES      |
| innodb_flush_log_at_timeout | 1        |
| innodb_lock_wait_timeout    | 50       |
| innodb_rollback_on_timeout  | OFF      |
| interactive_timeout         | 28800    |
| lock_wait_timeout           | 31536000 |
| net_read_timeout            | 30       |
| net_write_timeout           | 60       |
| rpl_stop_slave_timeout      | 31536000 |
| slave_net_timeout           | 60       |
| wait_timeout                | 28800    |
+-----------------------------+----------+
13 rows in set (0.01 sec)

解决方案

将timeout的值调大以后,就可以正常dump了

mysql> set global net_read_timeout = 120; 
Query OK, 0 rows affected (0.00 sec)

mysql> set global net_write_timeout = 900;
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like '%timeout%';
+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| connect_timeout             | 10       |
| delayed_insert_timeout      | 300      |
| have_statement_timeout      | YES      |
| innodb_flush_log_at_timeout | 1        |
| innodb_lock_wait_timeout    | 50       |
| innodb_rollback_on_timeout  | OFF      |
| interactive_timeout         | 28800    |
| lock_wait_timeout           | 31536000 |
| net_read_timeout            | 120      |
| net_write_timeout           | 900      |
| rpl_stop_slave_timeout      | 31536000 |
| slave_net_timeout           | 60       |
| wait_timeout                | 28800    |
+-----------------------------+----------+
13 rows in set (0.01 sec)