Through the intranet connection to the mysql service of another machine, I did find that the speed n was slow! Waited for tens of seconds before waiting for the password.Very anxiousIs there a way to solve the slow mysql database connection in the LAN?Here we lead everyone to solve this problem,Let's take a look with interested friends
Through the intranet connection to the mysql service of another machine, I did find that the speed n was slow! Waited for tens of seconds before waiting for the password. But ping mysql's server is fast! Thinking of having a similar experience a long time ago, Some services, such as telnet,Will do some reverse domain name resolution (if reverse resolution is not smooth, It ’s estimated that it will be delayed a lot, The feeling is that the connection speed n is slow! )
I searched the solution online, Two types were found:
1. The resolve.conf configured on both sides of the machine is different. It is ok to configure resolve.conf to a faster dns.
2. Turn off reverse DNS resolution in the mysql configuration. Also ok
I use the first method, After modifying the resolve.conf configuration, the speed is up!
Appendix 1:Excerpt from
Two machines on the same network segment, Connect to the same database server, One is 100 times faster than the other, and the database host is represented by the domain name. I don't understand why
Later, I checked the /etc/resolv.conf of the two machines and found that the configuration was different. Change the configuration of the fast machine, Speed came up.
Domain name resolution can also cause network programs to be slow, Another gain. In addition, mysql has the problem of reverse DNS resolution, May also cause slow speed, Can be in the mysql configuration file, Turn off DNS reverse analysis.
Appendix 2:Excerpt from
When remotely accessing mysql, mysql resolves the domain name,Will cause slow access,Add the following configuration to solve this problem
#Disable mysql to do domain name resolution [mysqld] skip-name-resolve
The php remote connection to mysql is slow. Sometimes it takes 4-20 seconds to connect to mysql remotely. The local connection to mysql is normal.The mysql installed by default has reverse DNS resolution enabled.Add the sentence skip-name-resolve under [mysqld] in the file my.ini (under Windows) or my.cnf (Unix or Linux)
However, this will cause a problem:when connecting to mysql, you cannot use localhost to connect, but use the ip address;if you grant permissions to users by localhost,The user login permissions should also be modified.
Workaround for slow connection to mysql.
2 servers,One runs iis + php, one runs mysql, and configures the environment as usual.Test page everything ok
When running the application, I found that PHP is very slow to access mysql.This situation has never been seen before,Although the 2 servers are not on the same network segment,However, the ping value is basically between 1,2ms. TCP connection should not be a problem.Google found the answer later,Add:skip-name-resolve to the [mysqld] section of the my.ini file, save the file,Restart mysql, everything is OK, the speed is like flying
The new version of mysql is not configured like the previous fool.This problem tossed me all morning,Finally came back at night to solve it,Hey, learn something again.
mysql 5 server under windows 2003, this machine is very fast to connect to the mysql service,There are two linux machines in the local area network, and one has a fast connection.The other one has to wait for several seconds after entering the password before connecting.
Adding the following configuration to the mysql server configuration is fast.
Appendix:(how mysql uses dns)
when a new thread connects to mysqld, mysqld will spawn a new thread to handle the request. this thread will first check if the hostname is in the hostname cache. if not the thread will call gethostbyaddr_r () and gethostbyname_r () to resolve the hostname .
if the operating system doesn "t support the above thread-safe calls, the thread will lock a mutex and call gethostbyaddr () and gethostbyname () instead. note that in this case no other thread can resolve other hostnames that is not in the hostname cache until the first thread 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 have a very slow dns and many hosts, you can get more performance by either disabling dns lookop with --skip-name-resolve 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.
The php remote connection to mysql is slow. Sometimes it takes 4-20 seconds to connect to mysql remotely. The local connection to mysql is normal. The main reason for this problem is thatThe mysql installed by default has reverse DNS resolution enabled.Add the sentence skip-name-resolve under [mysqld] in the file my.ini (under Windows) or my.cnf (Unix or Linux).
Mysql remote connection is slow
Connected to mysql of other machines in the local area network and found that the speed is very slow.It's unknown for what reason,There is always a few seconds of delay.
Later found a solution online,add in my.ini
[mysqld] skip-name-resolve skip-grant-tables
The option can disable DNS resolution, and the connection speed will be much faster.However, in this case, you cannot use the host name in the mysql authorization table, but only use the ip format.
If the –skip-grant-tables system is used, no access control will be performed on the access of any user.But 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,Just add skip-grant-tables to my.ini
- Java connection mysql database code example program
- django connection to the mysql database and table creation examples
- mysql - database connection error with local third xampp, wordpress installation
- php: i want to test the connection to mysql error: fatal error: uncaught error: call to undefined function mysql_connect ()
- i want to make mysql connection with java from cloud9
- php - wp database connection error while moving from local to server
- MySQL scheduled backup database operation example
- Solve the problem of Android10 automatic connection WiFi
- mysql - about database field names
- pycharm tool fails to connect to the mysql database
- mysql - about database columns (beginner)
- How to get data from mysql database to echarts in Django
- java - regarding connection to aws database
- Summary of two methods of connecting C ++ to mysql database
- i want to know the difference between oracle database and mysql in the number of pv
- mysql connection query, joint query, sub query principle and usage examples in detail
- php - my mysql connection doesn't work (using xampp)
- Simple understanding of MYSQL database optimization stage
- php - about database connection in conohawing
- cannot create database (mysql)
- python - you may need to restart the kernel to use updated packages error
- php - coincheck api authentication doesn't work
- php - i would like to introduce the coincheck api so that i can make payments with bitcoin on my ec site
- [php] i want to get account information using coincheck api
- the emulator process for avd pixel_2_api_29 was killed occurred when the android studio emulator was started, so i would like to
- python 3x - typeerror: 'method' object is not subscriptable
- i want to call a child component method from a parent in vuejs
- xcode - pod install [!] no `podfile 'found in the project directory
- sh - 'apt-get' is not recognized as an internal or external command, operable program or batch file