MySQL5.7中mysqldump 查询Error 2013备份报错咋办
发布时间：2021-12-24 06:30:43 所属栏目：MySql教程 来源：互联网
导读：小编给大家分享一下MySQL5.7中mysqldump:查询Error 2013备份报错怎么办，希望大家阅读完这篇文章之后都有所收获，下面让我们一起去探讨吧！ 描述 生产环境使用MySQL5.7.19之前每天正常的备份突然报错，后台日志报错信息如下： 2018-11-01T20:33:05.754602Z
|
小编给大家分享一下MySQL5.7中mysqldump:查询Error 2013备份报错怎么办，希望大家阅读完这篇文章之后都有所收获，下面让我们一起去探讨吧！ 描述 生产环境使用MySQL5.7.19之前每天正常的备份突然报错，后台日志报错信息如下： 2018-11-01T20:33:05.754602Z 57223 [Note] Aborted connection 57223 to db: '***' user: 'root' host: 'localhost' (Got timeout writing communication packets) 2018-11-02T20:32:02.062349Z 57512 [Note] Aborted connection 57512 to db: '***' user: 'root' host: 'localhost' (Got timeout writing communication packets) 2018-11-03T17:02:17.276883Z 57759 [Note] Aborted connection 57759 to db: '***' user: 'root' host: 'localhost' (Got timeout writing communication packets) 2018-11-03T20:33:42.574751Z 57802 [Note] Aborted connection 57802 to db: '***' user: 'root' host: 'localhost' (Got timeout writing communication packets) 2018-11-04T08:57:33.911527Z 57952 [Warning] IP address '***.****.***.***' could not be resolved: Name or service not known 2018-11-04T20:32:41.571757Z 58092 [Note] Aborted connection 58092 to db: '***' user: 'root' host: 'localhost' (Got timeout writing communication packets) 2018-11-05T00:52:24.345151Z 58144 [Note] Aborted connection 58144 to db: '***' user: 'root' host: 'localhost' (Got timeout writing communication packets) 解决思路 一开始认为是包最大值太小，即调大max_allowed_packet参数，将参数调大到200M之后再进行备份发现备份依然报错查看报错信息： mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `***` at row: 325118与后台日志信息： 2018-11-05T01:12:30.211358Z 58154 [Note] Aborted connection 58154 to db: '***' user: 'root' host: 'localhost' (Got timeout writing communication packets) 查看'timeout'参数信息： 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.00 sec) 查阅官方文档发现这两个参数值太小导致mysqldump报错 | net_read_timeout | 30 | | net_write_timeout | 60 | 调大这两个参数： 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) 再执行备份脚本，报错信息消失，备份正常！ （编辑：云计算网_泰州站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
浙公网安备 33038102330476号