Slow MySQL remote connection

Many of my friends may have encountered remote connection to mysql, or connection failure or slowness. Today I also met the following to share my solution to slow connection speed.

I want to connect to the database on my colleague's machine. When the account password is correct, several problems still occur.

1. Firewall
Telnet a colleague's machine IP address of 3306, and the result is that it cannot be connected. I have handled similar problems. As a result, telnet is enabled when the firewall on the machine of the colleague is directly disabled.

2. Slow database connection
Connecting to the MySQL database in PHP is very slow. I thought there was an exception with the PHP extension. Then the cmd command is used to perform the operation, and the connection is still slow. This makes me eliminate the reason for PHP. Baidu, I learned that the MySQL connection involves the following steps:
1. DNS resolution connection HOST
2. If the resolution fails, the current IP address is connected directly.

Therefore, a line of code is added under [mysqld] In the mysql configuration file:

Skip-name-resolve directly skips MySQL DNS resolution when connecting to a remote database

2. When the mysqld server is started, append the-skip-name-resolve option, in the same principle as the first one.

3. Increase the value of HOST_CACHE_SIZE. The default value of HOST_CACHE_SIZE is 128, which is suitable for multiple remote hosts.

Because I use an IP address to connect, DNS resolution is impossible. Therefore, the parsing process takes a long time. During this time, DNS resolution times out. Through the above Code, MYSQL should not directly go through DNS resolution.

Note: If you use the-skip-grant-tables system, no access control is required for any user's access. However, you can use mysqladmin flush-privileges or mysqladmin reload to enable access control; by default, the show databases statement is open to all users,
If the mysql server does not have a remote account, add skip-grant-tables to my. ini.