Shulou(Shulou.com)05/31 Report--

Editor to share with you about MySQL5.7 mysqldump: query Error 2013 backup error report how to do, I hope you will learn something after reading this article, let's discuss it together!

Description

Before the production environment uses MySQL5.7.19, the daily normal backup suddenly reports an error. The error message in the backend log is as follows:

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)

Solution idea

At first, it is considered that the maximum value of the package is too small, that is, adjust the max_allowed_packet parameter to 200m, and then make a backup and find that the backup still reports an error and check the error message:

Mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `* *` at row: 325118 and backend log information:

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

View the 'timeout' parameter information:

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) check the official documents and find that the values of these two parameters are too small to cause mysqldump to report an error | net_read_timeout | 30 | | net_write_timeout | 60 |

Increase these two parameters:

Mysql > set global net_read_timeout = 120th query OK, 0 rows affected (0.00 sec) mysql > set global net_write_timeout = 900th query OK, 0 rows affected (0.00 sec)

Then execute the backup script, the error message disappears and the backup is normal!

After reading this article, I believe you have a certain understanding of "mysqldump in MySQL5.7: how to query Error 2013 backup error report". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.