Mysqldump export Error "Mysqldump:error 2013:lost connection to MySQL server during query when dumping table ' file_storage ' at row : 29 "

Source: Internet
Author: User
Tags mysql backup



Today, MySQL backup crontab automatically run when the alarm appears, the contents of the alarm are as follows


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)


At the beginning, I located the fault point of the fault in the "mysqldump: Couldn't execute 'show fields from `revision`': MySQL server has gone away (2006)" error. The online recommendation is to check the value of max_allowed_packet. Then change it to be as large as possible. I check the current value information as follows:


mysql> show global variables like ‘max_allowed_packet‘;
+--------------------+-----------+
| Variable_name      | Value     |
+--------------------+-----------+
| max_allowed_packet | 268435456 | 
+--------------------+-----------+
1 row in set (0.00 sec)


You can see that the current value is already 200M, the explanation should not be caused by this problem, and look at the error, here is not a lot of data from the Select, nor insert or update a large amount of data.



I rerun the script, and later the same error occurred.



Later, I noticed that there was this passage


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


In an article to see the solution to this error



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



Later, when the value of timeout is turned up, you can dump it normally.


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 export Error "Mysqldump:error 2013:lost connection to MySQL server during query when dumping table ' file_storage ' at row : 29 "


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.