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.