Mysql database remote connection very slow solution _mysql

One time colleagues suggested that the MySQL database connection used for development is slow because our MySQL development database is deployed on a separate machine, so we think it might be caused by a network connectivity problem.

After pinging and route, the network traffic is found to be normal, and the local connection on the MySQL machine is found to be very fast, so the network problem is basically excluded. I have encountered such a problem before, but then I do not know how suddenly good, this time again encountered such a problem, so want to see whether the MySQL configuration problem. After querying MySQL related documents and web searches, a configuration appears to solve the problem of adding the following configuration parameters to the MySQL configuration file:

[Mysqld]

Skip-name-resolve

In Linux, the profile is/ETC/MY.CNF, and the configuration file under Windows is the My.ini file under the MySQL installation directory. Note that the configuration is added under [mysqld], after you change the configuration and save, then restart MySQL and connect to the test remotely, and everything is back to the same. The official explanation information for this parameter is as follows:

 code as follows:

How MySQL uses DNS

When a new thread connects to Mysqld, Mysqld'll spawn a new thread to handle the request. This thread would be the hostname is in the hostname cache. If not the thread would call Gethostbyaddr_r () and Gethostbyname_r () to resolve the hostname.

If the operating system doesn ' t support the above Thread-safe calls, the thread would lock a mutex and call gethostbyaddr () and gethostbyname () instead. Note ' In ' This case no other thread can resolve the other hostnames so is isn't in the hostname cache until the D 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 are have a very slow DNS and many hosts, you can have more performance by either disabling DNS Lookop Solve 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.

According to the documentation, if your MySQL host query DNS slow or a lot of client host will cause the connection is slow, because our development machine is not able to connect the extranet, so DNS parsing is impossible to complete, thus also understand why the connection so slow. Also, note that after adding this configuration parameter, the Host field in the MySQL authorization table will not be able to use the domain name and only use the IP address, because this is the result of a domain name resolution that is forbidden.

ApsaraDB RDS for MySQL - A fully hosted online database service that supports MySQL 5.5, 5.6, 5.7, and 8.0.

Alibaba Cloud Hot Products
Elastic Compute Service (ECS)	Dedicated Host (DDH)	ApsaraDB RDS for MySQL (RDS)	ApsaraDB for PolarDB(PolarDB)	AnalyticDB for PostgreSQL (ADB for PG)
AnalyticDB for MySQL(ADB for MySQL)	Data Transmission Service (DTS)	Server Load Balancer (SLB)	Global Accelerator (GA)	Cloud Enterprise Network (CEN)
Object Storage Service (OSS)	Content Delivery Network (CDN)	Short Message Service (SMS)	Container Service for Kubernetes (ACK)	Data Lake Analytics (DLA)
ApsaraDB for Redis (Redis)	ApsaraDB for MongoDB (MongoDB)	NAT Gateway	VPN Gateway	Cloud Firewall
Anti-DDoS	Web Application Firewall (WAF)	Log Service	DataWorks	MaxCompute
Elastic MapReduce (EMR)	Elasticsearch