mysql dump 2013_mysqldump导出报错"mysqldump: Error 2013: Lost connection to MySQL server during query wh...

今天mysql备份的crontab自动运行的时候,出现了报警,报警内容如下

mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `file_storage` at row: 29

mysqldump: Couldn't execute 'show table status like 'property'': MySQL server has gone away (2006)

mysqldump: Couldn't execute 'SET OPTION SQL_QUOTE_SHOW_CREATE=1': MySQL server has gone away (2006)

mysqldump: Couldn't execute 'show fields from `property`': MySQL server has gone away (2006)

mysqldump: Couldn't execute 'show table status like 'revision'': MySQL server has gone away (2006)

mysqldump: Couldn't execute 'SET OPTION SQL_QUOTE_SHOW_CREATE=1': MySQL server has gone away (2006)

mysqldump: Couldn't execute 'show fields from `revision`': MySQL server has gone away (2006)

mysqldump: Couldn't execute 'show table status like 'revision\_change'': MySQL server has gone away (2006)

mysqldump: Couldn't execute 'SET OPTION SQL_QUOTE_SHOW_CREATE=1': MySQL server has gone away (2006)

mysqldump: Couldn't execute 'show fields from `revision_change`': MySQL server has gone away (2006)

mysqldump: Couldn't execute 'show table status like 'setting'': MySQL server has gone away (2006)

mysqldump: Couldn't execute 'SET OPTION SQL_QUOTE_SHOW_CREATE=1': MySQL server has gone away (2006)

mysqldump: Couldn't execute 'show fields from `setting`': MySQL server has gone away (2006)

mysqldump: Couldn't execute 'show table status like 'setting\_history'': MySQL server has gone away (2006)

mysqldump: Couldn't execute 'SET OPTION SQL_QUOTE_SHOW_CREATE=1': MySQL server has gone away (2006)

mysqldump: Couldn't execute 'show fields from `setting_history`': MySQL server has gone away (2006)

mysqldump: Couldn't execute 'show table status like 'user'': MySQL server has gone away (2006)

mysqldump: Couldn't execute 'SET OPTION SQL_QUOTE_SHOW_CREATE=1': MySQL server has gone away (2006)

mysqldump: Couldn't execute 'show fields from `user`': MySQL server has gone away (2006)

一开始我把故障的错误点定位在"mysqldump: Couldn't execute 'show fields from `revision`': MySQL server has gone away (2006)"这段报错上面,网上的部分建议是检查max_allowed_packet的值,然后改得尽量大一些,我查看当前的值信息如下:

mysql> show global variables like 'max_allowed_packet';+--------------------+-----------+

| Variable_name | Value |

+--------------------+-----------+

| max_allowed_packet | 268435456 |

+--------------------+-----------+

1 row in set (0.00 sec)

可以看到当前的值已经是200M了,说明应该不是这个问题导致的,而且查看报错,这里并没有从select获取大量数据,也没有insert或者update大量数据.

我重新运行了一下脚本,过会儿又出现了相同的报错了.

后来注意到有这么一段

mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `file_storage` at row: 29

在一篇文章里看到了针对这个报错的解决措施

https://ottomatik.groovehq.com/knowledge_base/topics/solving-error-2013-lost-connection-to-mysql-server-during-query-when-dumping-table

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

mysql> show global variables like '%timeout%';+----------------------------+-------+

| Variable_name | Value |

+----------------------------+-------+

| connect_timeout | 10 |

| delayed_insert_timeout | 300 |

| innodb_lock_wait_timeout | 100 |

| innodb_rollback_on_timeout | OFF |

| interactive_timeout | 28800 |

| net_read_timeout | 30 |

| net_write_timeout | 60 |

| slave_net_timeout | 3600 |

| table_lock_wait_timeout | 200 |

| wait_timeout | 28800 |

+----------------------------+-------+

10 rows in set (0.00sec)

mysql> set global net_read_timeout = 120;

Query OK,0 rows affected (0.03sec)

mysql> set global net_write_timeout = 900;

Query OK,0 rows affected (0.00sec)

mysql> show global variables like '%timeout%';+----------------------------+-------+

| Variable_name | Value |

+----------------------------+-------+

| connect_timeout | 10 |

| delayed_insert_timeout | 300 |

| innodb_lock_wait_timeout | 100 |

| innodb_rollback_on_timeout | OFF |

| interactive_timeout | 28800 |

| net_read_timeout | 120 |

| net_write_timeout | 900 |

| slave_net_timeout | 3600 |

| table_lock_wait_timeout | 200 |

| wait_timeout | 28800 |

+----------------------------+-------+

10 rows in set (0.00 sec)