问题描述:

在执行从库A执行mysqldump并sql导入到目标数据库B的时候报了Got a packet bigger than‘max_allowed_packet’bytes的错误.

原因分析:

图中所示,at line 729830, 为sql文件中失败的记录行数,插入该条sql语句时,出现packet大于mysql系统参数值.
Mysql中有一个global变量max_allowed_packet,当传输的一个packet超过该值则会报错,判断是否数据库中该全局参数设置过小引起的不合理,抑或是整个表的值过大,也有可能其中的某条记录过大.

解决方案:

方案一: 调大max_allowed_packet值

通过判断目标数据库中global参数max_allowed_packet是否合理,进而调大改值:
1.查看max_allowed_packet值的大小。
show VARIABLES like ‘%max_allowed_packet%’;
显示的结果为：

以上数据说明目前的配置是：64M

执行以下命令可以将max_allowed_packet的值设置大一点：
set global max_allowed_packet = 2147483648

退出mysql命令行，然后重新登录,进行查看(必须重新登录):
show VARIABLES like ‘%max_allowed_packet%’;

设置值为2G,但是查看显示只能设置到1G,设置过大容易导致丢包.

Warning该方法需要有Super User

方案二: 优化数据源的方式

按照skip-extended-insert方法换成多条insert语句
在上述方案仍然不可行时,通过将源数据库的sql文件中insert语句进行拆分,再进行导入.
方式:在源库表sql文件备份时加入参数:
mysqldump --skip-extended-insert ...

缺点:容易导致导入时长延长.

⚠️方案三: 优化数据的方式

错误日志显示line 729830为错误行数,通过命令:
head -n 729830 ***.sql | tail -n 1 > tmp.dat
将该条记录内容暂时存储,查看文件大小,查出发现该行已有160M.判断是哪个字段出现超长不合理的长度.
使用length() 查看大于5M的字段column,进行优化判断处理,再尝试方案一/方案二.
SELECT count(1) FROM table_name WHERE LENGTH( column ) > 5\*1024\*1024;

总结:

设计库表时,合理优化,尽可能满足第三范式原则,同时尽可能设置字段的长度,避免过长.