同一个mysql数据库，不同的服务器连接，速度不同

同一个mysql数据库，不同的服务器连接，速度不同

最近业务人员在部署线上业务站点的时发现有一台服务器的站点访问数据库特别慢，但是其他服务器访问缺没有出现慢的情况，得知情况之后首先是考虑源服务器区别，是不是网络有问题 或者是不是执行sql不同导致的，经过排查都排除了这两种可能，然后就想到了mysql的skip-name-resolve参数 是不是因为这个导致的呢？上去mysql一看真的没有配置这个参数，然后就抱着尝试下的心态试了下，结果还真的解决了问题同样你可可以参考MySQL在远程访问时非常慢的解决skip-name-resolve 并且出现 Reading from net，这里因为这个mysql是业务临时安装的 没想到之后会长期使用 所以也没在意去看 ，这里详细说下skip-name-resolve这个参数啥意思：

我们就没必要重新造*，首先看下官方的解释：

How MySQL
uses DNS When a new thread connects to mysqld, mysqld will spawn a new thread to handle the request. This thread will first check if the hostname is in the hostname cache. If not the thread will call gethostbyaddr_r() and gethostbyname_r() to resolve the hostname. If the operating system doesn't support the above thread-safe calls, the thread will lock a mutex and call gethostbyaddr() and gethostbyname() instead. Note that in this case no other thread can resolve other hostnames that is not in the hostname cache until the first thread is ready. You can disable DNS host lookup by starting mysqld with –skip-name-resolve. In this case you can however only use IP names in
the MySQL privilege tables. If you have a very slow DNS and many hosts, you can get more performance by either disabling DNS lookop with –skip-name-resolve or by increasing the HOST_CACHE_SIZE define (default: 128) and recompile mysqld. You can disable the hostname
cache with –skip-host-cache. You can clear the hostname cache with FLUSH HOSTS or mysqladmin flush-hosts. If you don't want to allow connections over TCP/IP, you can do this by starting mysqld with –skip-networking.

接下来直接参考SlowTech的关于skip_name_resolve参数的总结内容，首先在这里感谢SlowTech的分享。
内容如下：

作为MySQL调优的一部分，很多人都推荐开启skip_name_resolve。这个参数是禁止域名解析的（当然，也包括主机名）。很多童鞋会好奇，这背后的原理是什么，什么情况下开启这个参数比较合适。

 

基于以下原因，MySQL服务端会在内存中维护着一份host信息， 包括三部分：IP，主机名和错误信息。主要用于非本地TCP连接。

1. 通过在第一次建立连接时缓存IP和host name的映射关系，同一主机的后续连接将直接查看host cache，而不用再次进行DNS解析。

2. host cache中同样会包含IP登录失败的错误信息。可根据这些信息，对这些IP进行相应的限制。后面将会具体提到。

host cache的信息可通过performance_schema中host_cache表查看。

 

那么，IP和host name的映射关系是如何建立的呢？

1. 当有一个新的客户端连接进来时，MySQL Server会为这个IP在host cache中建立一个新的记录，包括IP，主机名和client lookup validation flag，分别对应host_cache表中的IP，HOST和HOST_VALIDATED这三列。第一次建立连接因为只有IP，没有主机名，所以HOST将设置为NULL，HOST_VALIDATED将设置为FALSE。

2. MySQL Server检测HOST_VALIDATED的值，如果为FALSE，它会试图进行DNS解析，如果解析成功，它将更新HOST的值为主机名，并将HOST_VALIDATED值设为TRUE。如果没有解析成功，判断失败的原因是永久的还是临时的，如果是永久的，则HOST的值依旧为NULL，且将HOST_VALIDATED的值设置为TRUE，后续连接不再进行解析，如果该原因是临时的，则HOST_VALIDATED依旧为FALSE，后续连接会再次进行DNS解析。

 

另，解析成功的标志并不只是通过IP，获取到主机名即可，这只是其中一步，还有一步是通过解析后的主机名来反向解析为IP，判断该IP是否与原IP相同，如果相同，才判断为解析成功，才能更新host cache中的信息。

 

基于上面的总结，下面谈谈 host cache的优缺点：

缺点：当有一个新的客户端连接进来时，MySQL Server都要建立一个新的记录，如果DNS解析很慢，无疑会影响性能。如果被允许访问的主机很多，也会影响性能，这个与host_cache_size有关，这个参数是5.6.5引入的。5.6.8之前默认是128,5.6.8之后默认是-1,基于max_connections的值动态调整。所以如果被允许访问的主机很多，基于LRU算法，先前建立的连接可能会被挤掉，这些主机重新进来时，会再次进行DNS查询。

优点：通常情况下，主机名是不变的，而IP是多变的。如果一个客户端的IP经常变化，那基于IP的授权将是一个繁琐的过程。因为你很难确定IP什么时候变化。而基于主机名，只需一次授权。而且，基于host cache中的失败信息，可在一定程度上阻止外界的暴力**攻击。

 

关于阻止外界的暴力**攻击，涉及到max_connect_errors参数，默认为100，官方的解释如下：

If more than this many successive connection requests from a host are interrupted without a successful connection, the server blocks that host from further connections.
如果某个客户端的连接达到了max_connect_errors的限制，将被禁止访问，并提示以下错误：

Host 'host_name' is blocked because of many connection errors.
Unblock with 'mysqladmin flush-hosts'
 

下面来模拟一下

首先，设置max_connect_errors的值

复制代码
mysql> show variables like 'max_connect_errors';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 100   |
+--------------------+-------+
1 row in set (0.00 sec)

mysql> set global max_connect_errors=2;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'max_connect_errors';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 2     |
+--------------------+-------+
1 row in set (0.00 sec)
复制代码
通过telnet模拟interrupted without a successful connection。

复制代码
[[email protected] ~]# telnet 192.168.244.145 3306
Trying 192.168.244.145...
Connected to 192.168.244.145.
Escape character is '^]'.
N
5.6.26-log
          K]qA1nYT!w|+ZhxF1c#|kmysql_native_password
^]
!#08S01Got packets out of orderConnection closed by foreign host.
[[email protected] ~]# telnet 192.168.244.145 3306
Trying 192.168.244.145...
Connected to 192.168.244.145.
Escape character is '^]'.
N
Y#>PVB(>!Bl}NKnjIj]sMmysql_native_password
^]
!#08S01Got packets out of orderConnection closed by foreign host.
[[email protected] ~]# mysql -h192.168.244.145 -uroot -p123456
Warning: Using a password on the command line interface can be insecure.
ERROR 1129 (HY000): Host '192.168.244.144' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
复制代码
即便后来使用了正确的账号和密码登录，依旧会被阻止。

再来看看host_cache表中的信息，sum_connect_errors为2了。

复制代码
mysql> select ip,host,host_validated,sum_connect_errors,count_authentication_errors from performance_schema.host_cache;
+-----------------+------+----------------+--------------------+-----------------------------+
| ip              | host | host_validated | sum_connect_errors | count_authentication_errors |
+-----------------+------+----------------+--------------------+-----------------------------+
| 192.168.244.144 | NULL | YES            |                  2 |                           0 |
+-----------------+------+----------------+--------------------+-----------------------------+
1 row in set (0.00 sec)
复制代码
 

该阻止会一直生效，直到采取以下操作：

1. mysql> flush hosts;

2. # mysqladmin flush-hosts

3. truncate table performance_schema.host_cache;

4. 或者等待该记录从host cache中被挤掉。

 

如果要禁止DNS解析，可设置skip_name_resolve参数，这样，mysql.user表中基于主机名的授权将无法使用，且错误日志中会提示：

[Warning] 'user' entry '[email protected]' ignored in --skip-name-resolve mode.
这里，通过mysql-slave1访问，将会拒绝访问

[[email protected] ~]# mysql -h192.168.244.145 -uroot -p123
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'192.168.244.144' (using password: YES)
 

host cache是默认开启的，如果要禁掉，可将host_cache_size设置为0，该参数是个动态参数，可在线修改。

 

如果要完全禁掉TCP/IP连接,可在MySQL启动时，设置skip-networking参数。

 

总结：

1. 从原理上看，DNS解析一般只针对客户端的第一次连接，客户端数据量比较小的情况下，开销其实不大，完全不必禁掉skip_name_resolve参数，带来的好处就是，为客户端和多变的IP直接解耦，只需对主机名进行一次授权。

可通过\s查看当前连接使用的是socket还是TCP。

2. 奇怪的是，对于skip_name_resolve参数，虽然官方文档说的是布尔值，

但如果在配置文件中指定了，无论是skip_name_resolve=off或者skip_name_resolve=0。

最后，通过show variables like '%skip_name_resolve%'查看均显示ON。将该参数设置为OFF的唯一办法是不写该参数（因为它默认值即为OFF）。

3. 在skip_name_resolve=ON的情况下，在本地通过-h127.0.0.1没有问题。

复制代码
[[email protected] ~]# mysql -uroot -h127.0.0.1 -p123456
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.31-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

[email protected](none) 09:02:15> \s
--------------
mysql  Ver 14.14 Distrib 5.6.31, for Linux (x86_64) using  EditLine wrapper

Connection id:        4
Current database:    
Current user:        [email protected]
SSL:            Not in use
Current pager:        stdout
Using outfile:        ''
Using delimiter:    ;
Server version:        5.6.31-log MySQL Community Server (GPL)
Protocol version:    10
Connection:        127.0.0.1 via TCP/IP
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:        3306
Uptime:            11 min 10 sec

Threads: 1  Questions: 20  Slow queries: 0  Opens: 70  Flush tables: 1  Open tables: 63  Queries per second avg: 0.029
--------------

[email protected](none) 09:02:18> show variables like '%skip_name_resolve%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| skip_name_resolve | ON    |
+-------------------+-------+
1 row in set (0.06 sec)
复制代码
 

如果该参数设置为OFF，则上述方式就会报错，通过报错信息可以看出，它直接将127.0.0.1转化为localhost了。

[[email protected] ~]# mysql -uroot -h127.0.0.1 -p123456 -P3306
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
注意： 't1'@'%'中包含't1'@'127.0.0.1'，如果开启skip_name_resolve参数，则't1'@'%'中定义的密码可用于't1'@'127.0.0.1'的登录，如果没有开启该参数，则't1'@'127.0.0.1'会转化为't1'@'localhost'登录，此时't1'@'%'定义的密码并不适用。

参考：

1. http://www.tuicool.com/articles/7R7BRb

2. http://dev.mysql.com/doc/refman/5.7/en/host-cache.html

3. http://dev.mysql.com/doc/refman/5.7/en/blocked-host.html

也可以参考horizon~~~的优化MySQL开启skip-name-resolve参数时显示“ignored in --skip-name-resolve mode.”Warning解决方法
内容如下：

参数用途： 
skip-name-resolve #禁止MySQL对外部连接进行DNS解析skip-grant-tables

添加–skip-name-resolve方法：

1
2
3
4
[Mysqld]
 ……
skip-name-resolve
……
修改配置文件添加并需要重启。

参数优化： 
skip-name-resolve 参数的目的是不再进行反解析（ip不反解成域名），这样可以加快数据库的反应时间。 
内部有DNS服务器，对各服务器的IP做了反向解析，但未对内网IP做反向解析，所以使用skip-name-resolve以后用内网地址向mysqlslap请求响应快了一半。

添加“–skip-name-resolve mode”参数后发现错误日志有

1
2
120203 10:21:06 [Warning] 'user' entry '[email protected]\' ignored in --skip-name-resolve mode.
120203 10:21:06 [Warning] 'user' entry '@jimmyli\' ignored in --skip-name-resolve mode.
只需去服务器里边把用户[email protected]和@jimmyli删除即可。 

提示Warning信息：

1
2
130739 11:12:22 [Warning] 'user' entry '[email protected]\' ignored in --skip-name-resolve mode.
130739 11:12:22 [Warning] 'user' entry '@localhost.localdomain\' ignored in --skip-name-resolve mode.
一般在优化MySQL配置参数时，添加“–skip-name-resolve”，然后在重新启动MYSQL时检查启动日志，发现有警告信息。如上。

原因分析： 
“–skip-name-resolve mode”是禁用dns解析，避免网络DNS解析服务引发访问MYSQL的错误，一般应当启用。 
启用“–skip-name-resolve mode”后，在MySQL的授权表中就不能使用主机名了，只能使用IP ，出现此警告是由于mysql 表中已经存在有 localhost.localdomain 帐号信息。

解决方法： 
把Warning的账号删除就解决了。 
MySQL命令行：

1
2
3
mysql>use mysql;
mysql> delete from user where HOST='localhost.localdomain';
Query OK, 2 rows affected (0.00 sec)
最后，重启MySQL，再看错误信息提示，日志发现警告已经没有啦。该方法是mysql启用skip-name-resolve模式时出现Warning的处理办法。