微信号:gh_a2125bded21f

介绍:关注Python开发,运维自动化,把运维做到更好~

Mysql max_allowed_packet 被修改设置为1GB或者1024B原因

2016-09-08 10:04 卓朗

问题:最近开发有台mysql数据库的max_allowed_packet经常会被重置为1GB或者1KB,导致Java报错。


问题日志:

10:04:43.296 [http-bio-8080-exec-18] ERROR o.h.e.j.s.SqlExceptionHelper [147] - Packet for query is too large (1862 > 1024). You can change this value on the serv

er by setting the max_allowed_packet' variable. 


解决思路:

  1. Mysql  bug 我们用的是mysql5.6版本,用的人很多,如果这是个bug,网上会有很多的资料,可是官方或者网上很少有说mysql5.6有这个bug,暂时排除。

  2. 代码bug,检查代码数据库设置,检查mysql的my.cnf配置文件。



具体实施:

  1. 我们公司有mysql的zabbix监控,查看mysql max_allowed_packet参数重置的时间点 ,在12:05左右


2.查看mysql日志,在12:05左右有ip地址试图连接数据库,由此可以怀疑可能是数据库权限或者代码的数据库设置权限有问题。

2016-03-30 10:34:54 24124 [Warning] IP address '122.114.47.25' could not be resolved: Temporary failure in name resolution

2016-03-30 10:34:55 24124 [Warning] IP address '122.114.47.25' could not be resolved: Temporary failure in name resolution

2016-03-30 10:34:55 24124 [Warning] IP address '122.114.47.25' could not be resolved: Temporary failure in name resolution

2016-03-30 10:34:55 24124 [Warning] IP address '122.114.47.25' could not be resolved: Temporary failure in name resolution

2016-03-30 10:34:56 24124 [Warning] IP address '122.114.47.25' could not be resolved: Temporary failure in name resolution

2016-03-30 10:34:56 24124 [Warning] IP address '122.114.47.25' could not be resolved: Temporary failure in name resolution

2016-03-30 10:34:57 24124 [Warning] IP address '122.114.47.25' could not be resolved: Temporary failure in name resolution

2016-03-30 10:35:00 24124 [Warning] IP address '122.114.47.25' could not be resolved: Temporary failure in name resolution

2016-03-30 10:59:10 24124 [Warning] IP address '124.114.152.26' could not be resolved: Name or service not known

2016-03-30 12:05:51 24124 [Warning] IP address '14.29.49.156' could not be resolved: Name or service not known

2016-03-30 13:30:36 24124 [Warning] IP address '222.186.50.172' could not be resolved: Name or service not known

2016-03-30 13:59:55 24124 [Warning] IP address '59.175.137.36' has been resolved to the host name '36.137.175.59.broad.wh.hb.dynamic.163data.com.cn', which resemb

les IPv4-address itself.                                                                                                          

3. 检查代码,开发自己配置数据库代码权限,用户是root,密码也是简单密码,my.cnf的配置bind-address=0.0.0.0,这台服务器mysql和代码放在一起,有外网对外,没有任何防火墙设置。


解决方法:

  1. 修改数据库权限,重新设置代码的数据库权限配置。2.bind-address设置为127.0.0.1。




总结:

其实许多问题都是工作不规范不够专业,往往开发或者运维为了一时方便,忘掉了代码和数据安全导致的。






 
devops平台 更多文章 Hadoop启动报Error: JAVA_HOME is not set and could not be found解决办法 Ceph管理节点故障mds迁移到存储节点 CDH大数据平台搭建
猜您喜欢 全程揭秘!从零开始做一个 App需要多少钱? Docker Swarm系列第一部:利用Flocker部署多节点Cassandra集群 ABP系列之4、ABP模块系统 Siri要变得更善解人意,需要这六大「营养元素」 软件架构和鸡窝