今早发现mysql日志中有很多如下的警告:
140724 18:41:25 [Warning] IP address '172.16.18.217' could not be resolved: Temporary failure in name resolution
140724 18:41:25 [Warning] IP address '172.16.18.217' could not be resolved: Temporary failure in name resolution
140724 18:41:26 [Warning] IP address '172.16.18.217' could not be resolved: Temporary failure in name resolution
140724 18:41:26 [Warning] IP address '172.16.18.217' could not be resolved: Temporary failure in name resolution
140724 18:41:27 [Warning] IP address '172.16.18.217' could not be resolved: Temporary failure in name resolution
140724 18:41:28 [Warning] IP address '172.16.18.217' could not be resolved: Temporary failure in name resolution
140724 18:41:28 [Warning] IP address '172.16.18.217' could not be resolved: Temporary failure in name resolution
140724 18:41:28 [Warning] IP address '172.16.18.217' could not be resolved: Temporary failure in name resolution
140724 18:41:28 [Warning] IP address '172.16.18.217' could not be resolved: Temporary failure in name resolution
140724 18:44:54 [Warning] IP address '61.143.209.110' could not be resolved: Temporary failure in name resolution
140724 18:44:54 [Warning] IP address '61.143.209.110' could not be resolved: Temporary failure in name resolution
140724 18:44:54 [Warning] IP address '61.143.209.110' could not be resolved: Temporary failure in name resolution
140724 18:44:54 [Warning] IP address '61.143.209.110' could not be resolved: Temporary failure in name resolution
140724 18:44:54 [Warning] IP address '61.143.209.110' could not be resolved: Temporary failure in name resolution
140724 18:44:55 [Warning] IP address '61.143.209.110' could not be resolved: Temporary failure in name resolution
140724 18:44:55 [Warning] IP address '61.143.209.110' could not be resolved: Temporary failure in name resolution
140724 18:44:55 [Warning] IP address '61.143.209.110' could not be resolved: Temporary failure in name resolution
140724 18:44:55 [Warning] IP address '61.143.209.110' could not be resolved: Temporary failure in name resolution
问题产生的原因:
出现错误的原因是MYSQL Server在本地内存中维护了一个非本地的Client TCP cache,这个cache中包含了远程Client的登录信息,比如IP地址,hostname等信息。
如果Client连接到服务器后,Mysql首先会在本地TCP池中根据IP地址解析客户端的hostname或者反解析,如果解析不到,就会去DNS中进行解析,如果还是解析失败
就是在error log中写入这样的警告信息。
解决的办法:
1.可以通过两个参数来disable这个功能,在MYSQL的配置文件中[mysqld]中加入下面的参数:
[mysqld]
--skip-host-cache
--skip-name-resolve
重新授权,将所有访问数据库服务器的授权方式都改成IP形式的。
grant all on *.* to ‘root’@’172.16.12.68’identified by ‘123456’;
2.添加授权。
将所有访问数据库服务器的授权方式都改成IP形式。
不同的用户用不同的用户名和密码。
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表 下面删除掉那些含有含有主机名字的权限记录。
总结:
1.要么加上
--skip-host-cache
--skip-name-resolve
使得MySQL将不再通过DNS解析地址。
2.要么在赋予权限的时候 直接用ip地址,去掉那些用主机名字的权限。
【数据库|IP address could not be resolved: Temporary failure in name resolution】
推荐阅读
- 数据库|SQL行转列方式优化查询性能实践
- mysql|一文深入理解mysql
- 达梦数据库|DM8表空间备份恢复
- 数据技术|一文了解Gauss数据库(开发历程、OLTP&OLAP特点、行式&列式存储,及与Oracle和AWS对比)
- SqlServer|sql server的UPDLOCK、HOLDLOCK试验
- 谈灾难恢复指标(RTO与RPO是什么鬼())
- RPO与RTO
- 数据库|效率最高的Excel数据导入---(c#调用SSIS Package将数据库数据导入到Excel文件中【附源代码下载】)...