php Slow connections on Remote MySQL servers.

php Slow connections on Remote MySQL servers.

Slow connections on Remote MySQL servers.
Published November 2, 2007 General , My Projects , UNIX & Linux 3 Comments
Tags: connection, db, linux, mysql, mysqld, remote, slow, windows

Now a few of my projects are getting much bigger and the need for having dedicated Database servers are now more appealing than ever.

With MySQL when setting it up I noticed that when trying to access a remote database from an application server, it would takes ages to respond/connect to it.

After searching around the web, I have found what appears to be the solution…

It appears to be that the MySQL server lookups hostnames for authentication EVEN IF you are using for example: remoteuser@231.23.65.23 so to speed up remote connections to your remote database servers try adding the following line…

skip-name-resolve

To your MySQL configuration file (my.ini/my.cnf) and then be sure to reload (restart) the MySQL database server. The ‘[mysqld]‘ section of the my.cnf file now looks like this:

[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
skip-name-resolve

From Microsoft Windows this can be done from the ‘Services’ window in ‘Administrative tools’ or in Debian/Ubuntu entering this command:

/etc/init.d/mysql restart

The above addition will ONLY work if you are using IP Address or ‘%’ for the Database User accounts and will ofcourse disable the ability to use user accounts with the hostname eg. remoteuser@server2.example.com.