mysql 1130本地连接_MySQL ERROR 1130 (HY000): Host ‘127.0.0.1’ is not allowed to connect to this MySQL se...

今天调整了MySQL参数后发现鸡书之家博客访问错误Error establishing a database connection，设置了默认参数 skip_name_resolve = 0 (关闭参数)，坑人！！！

测试root用户连接MySQL发现没有问题啊

[root@web ~]# mysql -u root -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 64

Server version: 5.7.20-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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.

root@localhost:mysql.sock 21:39:24[(none)]>

使用wordpress用户连接下试试，我去，什么情况.......为什么不突然不允许连接了呢 ？

[root@web ~]# mysql -uwordpress -p -h127.0.0.1

Enter password:

ERROR 1130 (HY000): Host '127.0.0.1' is not allowed to connect to this MySQL server

经过排查后发现注释掉 skip_name_resolve 参数就可以了，但是我命名设置的默认值 0=off 啊 ？

设置 skip_name_resolve = 0 检查参数是否开启

root@localhost:mysql.sock 21:50:52[(none)]> show variables like '%skip_name_resolve%';

+-------------------+-------+

| Variable_name | Value |

+-------------------+-------+

| skip_name_resolve | ON |

+-------------------+-------+

1 row in set (0.00 sec)

命名设置的参数=0(off状态)为什么还是ON开启状态呢 ？

看了下MySQL官网对此参数的解释

Whether to resolve host names when checking client connections. If this variable is OFF, mysqld resolves host names when checking client connections. If it is ON, mysqld uses only IP numbers; in this case, all Host column values in the grant tables must be IP addresses. See Section 5.1.12.3, “DNS Lookups and the Host Cache”.

Depending on the network configuration of your system and the Host values for your accounts, clients may need to connect using an explicit --host option, such as --host=127.0.0.1 or --host=::1.

An attempt to connect to the host 127.0.0.1 normally resolves to the localhost account. However, this fails if the server is run with skip_name_resolve enabled. If you plan to do that, make sure an account exists that can accept a connection. For example, to be able to connect as root using --host=127.0.0.1 or --host=::1

大概意思是检查客户端连接时是否解析主机名，如果 skip_name_resolve 参数为 OFF 的话在连接MySQL时会解析主机名，如果是 ON 在用户表Host字段必须是IP地址。

使用本地回环地址 127.0.0.1 通常会解析为 localhost 主机名，所以在上方使用 wordpress用户本地连接是不允许被连接的。

但为什么我设置了 skip_name_resolve = 0 还是开启的状态呢.........我也不知道，我认为应该是 5.7.20 版本的一个小bug吧

因为默认是 OFF 状态，只要在my.cnf中添加了此参数就会开启，那可以注释掉此参数，这样就是 OFF 状态，就可以了。