IP address '172.172.200.88' could not be resolved: Temporary failure in name resolution

最新推荐文章于 2024-08-08 17:40:48 发布

渔夫数据库笔记

最新推荐文章于 2024-08-08 17:40:48 发布

阅读量3.5k

点赞数

本文链接：https://blog.csdn.net/shaochenshuo/article/details/50727885

版权

Mysql 专栏收录该内容

220 篇文章 9 订阅

订阅专栏

1.版本

1)操作系统

cat /etc/issue
Red Hat Enterprise Linux Server release 5.5 (Tikanga)
Kernel \r on an \m

cat /proc/version
Linux version 2.6.32-504.el6.x86_64 (mockbuild@c6b9.bsys.dev.centos.org) (gcc version 4.4.7 20120313 (Red Hat 4.4.7-11) (GCC) ) #1 SMP Wed Oct 15 04:27:16 UTC 2014

2)mysql数据库版本

mysql --version
mysql Ver 14.14 Distrib 5.6.26, for linux-glibc2.5 (x86_64) using EditLine wrapper

2.问题描述

2.1 发现问题

今天应用反应发现某个应用第一次响应很慢，当第一次响应成功后，后面所做的处理速度就正常了。我看了一下mysql的errorlog，发现有如warning：

2016-02-24 09:16:05 19114 [Warning] IP address '172.172.200.86' could not be resolved: Temporary failure in name resolution
2016-02-24 09:16:10 19114 [Warning] IP address '172.172.200.88' could not be resolved: Temporary failure in name resolution
2016-02-24 09:16:15 19114 [Warning] IP address '172.172.200.86' could not be resolved: Temporary failure in name resolution

##86和88正好是有问题应用的ip地址，那么此时问题就很清楚了，是因为mysql在对登录的ip地址进行ip地址到域名的反解析时失败了，导致该ip地址过来的登录数据库都很慢(我们内网域名服务器没有配置ip地址到域名的反向解析功能)

我们查看mysql的 performance_schema.host_cache表中可以发现如下信息：

<span style="color:#333333;">*************************** 1. row ***************************
                                        IP: 172.172.200.86
                                      HOST: NULL
                            HOST_VALIDATED: NO  >>>注意此处标识为NO，这时从88过来的登录请求就不会使用host_cache缓存，需要重新解析

                        SUM_CONNECT_ERRORS: 0
                 COUNT_HOST_BLOCKED_ERRORS: 0
           COUNT_NAMEINFO_TRANSIENT_ERRORS: 163
           COUNT_NAMEINFO_PERMANENT_ERRORS: 0
                       COUNT_FORMAT_ERRORS: 0
           COUNT_ADDRINFO_TRANSIENT_ERRORS: 0
           COUNT_ADDRINFO_PERMANENT_ERRORS: 0
                       COUNT_FCRDNS_ERRORS: 0
                     COUNT_HOST_ACL_ERRORS: 0
               COUNT_NO_AUTH_PLUGIN_ERRORS: 0
                  COUNT_AUTH_PLUGIN_ERRORS: 0
                    COUNT_HANDSHAKE_ERRORS: 0
                   COUNT_PROXY_USER_ERRORS: 0
               COUNT_PROXY_USER_ACL_ERRORS: 0
               COUNT_AUTHENTICATION_ERRORS: 0
                          COUNT_SSL_ERRORS: 0
         COUNT_MAX_USER_CONNECTIONS_ERRORS: 0
COUNT_MAX_USER_CONNECTIONS_PER_HOUR_ERRORS: 0
             COUNT_DEFAULT_DATABASE_ERRORS: 0
                 COUNT_INIT_CONNECT_ERRORS: 0
                        COUNT_LOCAL_ERRORS: 0
                      COUNT_UNKNOWN_ERRORS: 0
                                FIRST_SEEN: 2016-02-13 08:03:54
                                 LAST_SEEN: 2016-02-21 09:16:55
                          FIRST_ERROR_SEEN: 2016-02-13 08:03:54
                           LAST_ERROR_SEEN: 2016-02-21 09:16:55
*************************** 2. row ***************************
                                        IP: 172.172.200.88
                                      HOST: NULL
                            HOST_VALIDATED: NO >>>注意此处标识为NO，这时从88过来的登录请求就不会使用host_cache缓存，需要重新解析
                        SUM_CONNECT_ERRORS: 0
                 COUNT_HOST_BLOCKED_ERRORS: 0
           COUNT_NAMEINFO_TRANSIENT_ERRORS: 139
           COUNT_NAMEINFO_PERMANENT_ERRORS: 0
                       COUNT_FORMAT_ERRORS: 0
           COUNT_ADDRINFO_TRANSIENT_ERRORS: 0
           COUNT_ADDRINFO_PERMANENT_ERRORS: 0
                       COUNT_FCRDNS_ERRORS: 0
                     COUNT_HOST_ACL_ERRORS: 0
               COUNT_NO_AUTH_PLUGIN_ERRORS: 0
                  COUNT_AUTH_PLUGIN_ERRORS: 0
                    COUNT_HANDSHAKE_ERRORS: 0
                   COUNT_PROXY_USER_ERRORS: 0
               COUNT_PROXY_USER_ACL_ERRORS: 0
               COUNT_AUTHENTICATION_ERRORS: 0
                          COUNT_SSL_ERRORS: 0
         COUNT_MAX_USER_CONNECTIONS_ERRORS: 0
COUNT_MAX_USER_CONNECTIONS_PER_HOUR_ERRORS: 0
             COUNT_DEFAULT_DATABASE_ERRORS: 0
                 COUNT_INIT_CONNECT_ERRORS: 0
                        COUNT_LOCAL_ERRORS: 0
                      COUNT_UNKNOWN_ERRORS: 0
                                FIRST_SEEN: 2016-02-13 08:00:30
                                 LAST_SEEN: 2016-02-21 09:16:11
                          FIRST_ERROR_SEEN: 2016-02-13 08:00:30
                           LAST_ERROR_SEEN: 2016-02-21 09:16:11

2.2 问题分析

请见官方文档中 8.12.6.2 DNS Lookup Optimization and the Host Cache，或者我的博客

http://blog.csdn.net/shaochenshuo/article/details/50727975

2.3 问题解决

2.3.1 方法1

为了彻底解决该问题，我们可以在mysql 的cnf文件中加入如下参数skip_name_resolve = 1，因为该参数为非动态参数，需要重启实例才能生效。（建议在安装mysql的时候就加上该参数）

2.3.2 方法2

1)利用/etc/host 进行解析

当然生成数据库可能并不能轻易重启，那么我们可以临时在/etc/hosts文件中写入有问题的ip地址，等mysql解析成功后(此时会被记录在host_cache中)，再注释掉。如下是我在商用环境/etc/hosts中加入内容：

172.172.200.86 test1
172.172.200.88 test2

2)从86和88重新发起数据库连接请求

此时连接会很快，并且我们查询performance_schema.host_cache会发现：

<span style="color:#333333;">*************************** 1. row ***************************
                                        IP: 172.172.200.86
                                      HOST: test1  >>注意ip成功被解析为域名
                            HOST_VALIDATED: YES    >>此处被标记为YES
                        SUM_CONNECT_ERRORS: 0
                 COUNT_HOST_BLOCKED_ERRORS: 0
           COUNT_NAMEINFO_TRANSIENT_ERRORS: 163
           COUNT_NAMEINFO_PERMANENT_ERRORS: 0
                       COUNT_FORMAT_ERRORS: 0
           COUNT_ADDRINFO_TRANSIENT_ERRORS: 0
           COUNT_ADDRINFO_PERMANENT_ERRORS: 0
                       COUNT_FCRDNS_ERRORS: 0
                     COUNT_HOST_ACL_ERRORS: 0
               COUNT_NO_AUTH_PLUGIN_ERRORS: 0
                  COUNT_AUTH_PLUGIN_ERRORS: 0
                    COUNT_HANDSHAKE_ERRORS: 0
                   COUNT_PROXY_USER_ERRORS: 0
               COUNT_PROXY_USER_ACL_ERRORS: 0
               COUNT_AUTHENTICATION_ERRORS: 0
                          COUNT_SSL_ERRORS: 0
         COUNT_MAX_USER_CONNECTIONS_ERRORS: 0
COUNT_MAX_USER_CONNECTIONS_PER_HOUR_ERRORS: 0
             COUNT_DEFAULT_DATABASE_ERRORS: 0
                 COUNT_INIT_CONNECT_ERRORS: 0
                        COUNT_LOCAL_ERRORS: 0
                      COUNT_UNKNOWN_ERRORS: 0
                                FIRST_SEEN: 2016-02-13 08:03:54
                                 LAST_SEEN: 2016-02-21 09:30:55
                          FIRST_ERROR_SEEN: 2016-02-13 08:03:54
                           LAST_ERROR_SEEN: 2016-02-21 09:16:55
*************************** 2. row ***************************
                                        IP: 172.172.200.88
                                      HOST: TEST2
                            HOST_VALIDATED: YES
                        SUM_CONNECT_ERRORS: 0
                 COUNT_HOST_BLOCKED_ERRORS: 0
           COUNT_NAMEINFO_TRANSIENT_ERRORS: 139
           COUNT_NAMEINFO_PERMANENT_ERRORS: 0
                       COUNT_FORMAT_ERRORS: 0
           COUNT_ADDRINFO_TRANSIENT_ERRORS: 0
           COUNT_ADDRINFO_PERMANENT_ERRORS: 0
                       COUNT_FCRDNS_ERRORS: 0
                     COUNT_HOST_ACL_ERRORS: 0
               COUNT_NO_AUTH_PLUGIN_ERRORS: 0
                  COUNT_AUTH_PLUGIN_ERRORS: 0
                    COUNT_HANDSHAKE_ERRORS: 0
                   COUNT_PROXY_USER_ERRORS: 0
               COUNT_PROXY_USER_ACL_ERRORS: 0
               COUNT_AUTHENTICATION_ERRORS: 0
                          COUNT_SSL_ERRORS: 0
         COUNT_MAX_USER_CONNECTIONS_ERRORS: 0
COUNT_MAX_USER_CONNECTIONS_PER_HOUR_ERRORS: 0
             COUNT_DEFAULT_DATABASE_ERRORS: 0
                 COUNT_INIT_CONNECT_ERRORS: 0
                        COUNT_LOCAL_ERRORS: 0
                      COUNT_UNKNOWN_ERRORS: 0
                                FIRST_SEEN: 2016-02-13 08:00:30
                                 LAST_SEEN: 2016-02-21 09:30:11
                          FIRST_ERROR_SEEN: 2016-02-13 08:00:30
                           LAST_ERROR_SEEN: 2016-02-21 09:16:11

##当然我们还是要记得在my.cnf文件中加入skip_name_resolve = 1，这样如果实例重启后，该问题才不会重现。