全国统一24小时服务热线:400-0000-889400-0000-889  / 13999887799

IT技术分享

当前位置:主页 > IT技术分享 >

MySQL5.7 mysqldump: Error 2013备份报错解决办法

发布时间:2018-11-07 17:59 作者:admin 来源: 点击: 字号:

  • 描绘

出产环境运用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)

再履行备份脚本,报错信息消失,备份正常!

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31362743/viewspace-2218694/,如需转载,请注明出处,否则将追查法律责任。

本文源自: 环亚娱乐游戏

相关文章Related Articles

如果您觉得本站还不错,就请分享给身边的好友吧


分享成功还有机会获得精美礼品哦