描述

生产环境使用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>showglobalvariableslike'%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|+-------------------------------+----------+13rowsinset(0.00sec)

查阅官方文档发现这两个参数值太小导致mysqldump报错|net_read_timeout|30||net_write_timeout|60|

调大这两个参数：

mysql>setglobalnet_read_timeout=120;QueryOK,0rowsaffected(0.00sec)mysql>setglobalnet_write_timeout=900;QueryOK,0rowsaffected(0.00sec)

再执行备份脚本，报错信息消失，备份正常！