mysqldump匯出報錯"mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `file_storage` at row: 29"

標籤：weight   change   cut   定位在   ase   今天   mat   大量   veh   

今天mysql備份的crontab自動啟動並執行時候,出現了警示,警示內容如下

mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `file_storage` at row: 29mysqldump: 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.00 sec)mysql> set global net_read_timeout = 120; Query OK, 0 rows affected (0.03 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   | | 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)

 

mysqldump匯出報錯"mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `file_storage` at row: 29"