Slow MySQL remote connection

MySQL remote connection is slow. When mysqlclient initiates a connection request, MySqlServer will take the initiative to check the client host name.

Slow MySQL remote connection. When the mysql client initiates a connection request, the MySql Server takes the initiative to check the client host name.

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.

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 the host name is "DB1" "DB2", delete the hostanme record in the authorization table and restart mysqld.