01. MySQL connection error: cannot get hostname for your address

01. MySQL connection error: cannot get hostname for your address

Zheng Yi 2010 is affiliated with section 05. Data warehouse receiving

 

The database of a Web application is deployed on MySQL 5.5.5 instance (new machine) of 100. Zzz. yyy. xxx,

However, when PHP or python is used to access the database from a remote machine, the following error message is displayed:

"Can't get hostname for your address".

This prompt is returned by the MySQL server to the client.

That is to say, MySQL server is trying to reverse resolve the Client IP address and try to get the host name. However, the client that initiates the access is either

Because the host name cannot be obtained, MySQL server reports an error and rejects client connection.

 

Error screenshot

For example, python will get an exception like this:

 

Event Log report

At this time, the following error occurs in the Windows event log on the server where the MySQL server is located:

Event Type: Warning
Event Source: MySQL
Event Type: None
Event ID: 100
Date: 2010-01-01
Event: 11:29:13
User: N/
COMPUTER: serverii
Description:
IP address '1970. Zzz. yyy. XXX' cocould not be resolved: getnameinfo () returned error (Code: 100 ).

For more information, see Help and Support Center at http://www.mysql.com.

 

Simple Explanation

MySQL Server received ed a request from you to allow you to connect to the database. So next thing it tried to do is to check what name is bound to your IP address (name resolution) andIt failed to do so. So it just denied you access.

We can understand the process of MySQL processing client parsing in this way:
1. When the mysql client initiates a connection request, the MySQL server takes the initiative to check the client host name.
2. First, find the/etc/hosts file in the Windows System directory and search for the ing between domain names and IP addresses.
3. If the hosts file does not exist, search for DNS settings. If the DNS server is not set, an error is immediately returned. If the DNS server is set, reverse resolution is performed until timeout.

Solution Method 1 modify hosts

On the server where MySQL server is located, modify the Windows Hosts file and add a line of records, such:

100. Zzz. yyy. xxx dummy.ju690.cn

Then in 100. zzz. YYY. the xxx machine initiates a connection request using python. After testing, the connection can be established normally. This means that MySQL server can parse 100 through getnameinfo. zzz. YYY. the Host Name of XXX.

However, this method is very mechanical, so the following method is generally used.

Second, modify the MySQL configuration file my. ini.

The solution:
Just add skip-name-resolve option to your MySQL configuration file (My. INI ).

Add the following two lines to the MySQL server configuration file my. ini:

[Mysqld]

Skip-name-resolve

It will prohibit MySQL server from performing DNS resolution on external connections. Using this option can eliminate the time for MySQL to perform DNS resolution.

However, if this option is enabled, IP addresses are required for all remote host connection authorizations. Otherwise, MySQL cannot process connection requests normally.

Refer:
Http://www.ixdba.net/article/89/2127.html
Http://blog.chinaunix.net/u/25264/showart_1936561.html

 

Possible Consequences

If the skip-name-resolve option is enabled, make sure that MySQL has used host name authorization,
Run the following command in MySQL:

Mysql> Select User, host from mysql. User where host <> 'localhost ';

Generally, you will get a record with "%" authorization (that is, any address:

+ ------------------ + ------------- +
| User | host |
+ ------------------ + ------------- +
| Root | % |
| User_sync | 192.168.0.113 |

If a host name is "db1" "DB2", delete the hostanme record in the authorization table and restart mysqld.