MySQL5.7中mysqldump:查詢Error 2013備份報(bào)錯(cuò)怎么辦

小編給大家分享一下MySQL5.7中mysqldump:查詢Error 2013備份報(bào)錯(cuò)怎么辦，希望大家閱讀完這篇文章之后都有所收獲，下面讓我們一起去探討吧！

• 描述

生產(chǎn)環(huán)境使用MySQL5.7.19之前每天正常的備份突然報(bào)錯(cuò)，后臺(tái)日志報(bào)錯(cuò)信息如下：

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)

• 解決思路

    一開始認(rèn)為是包最大值太小，即調(diào)大max_allowed_packet參數(shù)，將參數(shù)調(diào)大到200M之后再進(jìn)行備份發(fā)現(xiàn)備份依然報(bào)錯(cuò)查看報(bào)錯(cuò)信息：

mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `***` at row: 325118與后臺(tái)日志信息：

2018-11-05T01:12:30.211358Z 58154 [Note] Aborted connection 58154 to db: '***' user: 'root' host: 'localhost' (Got timeout writing communication packets)

查看'timeout'參數(shù)信息：

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)

查閱官方文檔發(fā)現(xiàn)這兩個(gè)參數(shù)值太小導(dǎo)致mysqldump報(bào)錯(cuò)
| net_read_timeout       | 30   |
| net_write_timeout      | 60   |

調(diào)大這兩個(gè)參數(shù)：

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)

再執(zhí)行備份腳本，報(bào)錯(cuò)信息消失，備份正常！

看完了這篇文章，相信你對(duì)“MySQL5.7中mysqldump:查詢Error 2013備份報(bào)錯(cuò)怎么辦”有了一定的了解，如果想了解更多相關(guān)知識(shí)，歡迎關(guān)注億速云行業(yè)資訊頻道，感謝各位的閱讀！