今天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.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)
===================来自一泽涟漪的博客,转载请标明出处 www.cnblogs.com/ilifeilong===================
分类:
MySQL
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】博客园携手 AI 驱动开发工具商 Chat2DB 推出联合终身会员
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· ASP.NET Core - 日志记录系统(二)
· .NET 依赖注入中的 Captive Dependency
· .NET Core 对象分配(Alloc)底层原理浅谈
· 聊一聊 C#异步 任务延续的三种底层玩法
· 敏捷开发:如何高效开每日站会
· 互联网不景气了那就玩玩嵌入式吧,用纯.NET开发并制作一个智能桌面机器人(一):从.NET IoT入
· .NET 开发的分流抢票软件,不做广告、不收集隐私
· ASP.NET Core - 日志记录系统(二)
· C#实现 Winform 程序在系统托盘显示图标 & 开机自启动
· 实现windows下简单的自动化窗口管理