mysql报错IP address could not be resolved解决方法
这个警告不会影响数据库的访问 但是当有大量的这种日志产生的时候,数据库之前的错误信息 就会很难去查询了。连接数越多,产生报警日志的频率越高。
一、错误描述
数据库的alert.log中,我们经常会出现下面的警告:文章源自飞鸟学习网-https://www.pk88.net/2022-0408-6.html
IP address '172.16.12.195' could not be resolved: Temporary failure in name resolution IP address '172.16.12.67' could not be resolved: Temporary failure in name resolution IP address '172.16.12.67' could not be resolved: Temporary failure in name resolution IP address '172.16.12.195' could not be resolved: Temporary failure in name resolution IP address '172.16.12.195' could not be resolved: Temporary failure in name resolution IP address '172.16.12.195' could not be resolved: Temporary failure in name resolution IP address '172.16.12.196' could not be resolved: Temporary failure in name resolution IP address '172.16.12.196' could not be resolved: Temporary failure in name resolution IP address '172.16.12.195' could not be resolved: Temporary failure in name resolution IP address '172.16.12.195' could not be resolved: Temporary failure in name resolution IP address '172.16.12.68' could not be resolved: Temporary failure in name resolution
二、问题产生的原因
出现错误的原因是MYSQL Server在本地内存中维护了一个非本地的Client TCP cache,这个cache中包含了远程Client的登录信息,比如IP地址,hostname等信息。文章源自飞鸟学习网-https://www.pk88.net/2022-0408-6.html
如果Client连接到服务器后,Mysql首先会在本地TCP池中根据IP地址解析客户端的hostname或者反解析,如果解析不到,就会去DNS中进行解析,如果还是解析失败文章源自飞鸟学习网-https://www.pk88.net/2022-0408-6.html
就是在error log中写入这样的警告信息。文章源自飞鸟学习网-https://www.pk88.net/2022-0408-6.html
文章源自飞鸟学习网-https://www.pk88.net/2022-0408-6.html
三、解决的办法:
1、修改配置文件
可以通过两个参数来disable这个功能,在MYSQL的配置文件中[mysqld]中加入下面的参数:文章源自飞鸟学习网-https://www.pk88.net/2022-0408-6.html
[mysqld] --skip-host-cache --skip-name-resolve
重新授权,将所有访问数据库服务器的授权方式都改成IP形式的。文章源自飞鸟学习网-https://www.pk88.net/2022-0408-6.html
grant all on *.* to ‘root’@’172.16.12.68’identified by ‘123456’;
2、添加授权。
将所有访问数据库服务器的授权方式都改成IP形式。
不同的用户用不同的用户名和密码。文章源自飞鸟学习网-https://www.pk88.net/2022-0408-6.html
grant all on *.* to ‘user_68’@’172.16.12.68’identified by ‘pwd_68’; grant all on *.* to ‘user_67’@’172.16.12.67’identified by ‘pwd_67’; ....
然后去 mysql数据库下面的 user表 和db表 下面删除掉那些含有含有主机名字的权限记录。文章源自飞鸟学习网-https://www.pk88.net/2022-0408-6.html
四、总结
1、要么加上
--skip-host-cache --skip-name-resolve
使得MySQL将不再通过DNS解析地址。文章源自飞鸟学习网-https://www.pk88.net/2022-0408-6.html
2、赋予权限
要么在赋予权限的时候 直接用ip地址,去掉那些用主机名字的权限。
全站资源源于网络整合,仅供学习研究,不可商用,不提供技术指导,如有侵权请告知删除,可以投稿,广告合作联系V: pk88net
评论