排查mysqldump 时报错Error 2020

数据库关系型数据库技术服务知识库
问题现象

用户在使用 mysqldump 时报错:

mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table `testtable` 
根因描述

上述报错指向 max_allowed_packet 参数,此参数可以在客户端和服务端来进行设置,而客户端的设置会覆盖服务端配置,例如:

  • MySQL Server 设置的默认值为 1GB
mysql> show global variables like 'max_allowed_packet%';
+--------------------+------------+
| Variable_name      | Value      |
+--------------------+------------+
| max_allowed_packet | 1073741824 |
+--------------------+------------+
1 row in set (0.00 sec)
  • mysql 或 mysqldump 的客户端默认配置分别为 16MB 和 24MB

以下说明来自 MySQL 官方文档[1]

When you change the message buffer size by changing the value of the max_allowed_packet variable, you should also change the buffer size on the client side if your client program permits it. The default max_allowed_packet value built in to the client library is 1GB, but individual client programs might override this. For example, mysql and mysqldump have defaults of 16MB and 24MB, respectively. They also enable you to change the client-side value by setting max_allowed_packet on the command line or in an option file.

因此遇到此类报错时,需要考虑数据库的 max_allowed_packet 值是否设置的太小,还要考虑客户端工具的默认值。

解决方案

在 mysqldump 时,可以显式的添加参数 --max-allowed-packet= xxxx 来避免 Error 2020 的问题

--max-allowed-packet=# The maximum packet length to send to or receive from server.
参考文档
21
0
0
0
相关产品
评论
未登录
看完啦,登录分享一下感受吧~
暂无评论