[adsense id=”0514458240″ width=”468″ height=”60″]
When a MySQL client is attempting to connect to a MySQL server typically it will attempt to determine the hostname of the server it is attempting to connect to. This is done through the DNS server (whether local in your own LAN or remote on the internet).
Sometimes, the act of getting a hostname can be very slow depending on the load of the DNS, that all connections to the database appear to be slow. This conversion from IP address to hostname can be removed by using the skip_name_resolve parameter.
Here is a typical my.cnf file
# # In this file, you can use all long options that a program supports. # If you want to know which options a program supports, run the program # with the "--help" option. # The following options will be passed to all MySQL clients [client] port = 3306 socket = /var/lib/mysql/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] port = 3306 socket = /var/lib/mysql/mysql.sock skip-locking key_buffer = 16M max_allowed_packet = 1M table_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M query_cache_size = 32777216 # The safe_mysqld script [safe_mysqld] err-log=/var/lib/mysql/mysqld.log [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] key_buffer = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout
By default resolution of ip addresses to hostnames is active. Here is an example:
shell> mysql -h 127.0.0.1 -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.0.45 SUSE MySQL RPM Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> select current_user(); +----------------+ | current_user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec)
In the above example the IP address 127.0.0.1 got resolved to localhost. This is particularly important as it meant that our privileges are determined by the user root@localhost. If on the other had we add in the parameter configured in the my.cnf file and restarted the server we see:
mysql -h 127.0.0.1 -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.0.45 SUSE MySQL RPM Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> select current_user(); +----------------+ | current_user() | +----------------+ | root@% | +----------------+ 1 row in set (0.01 sec)
The user account root@% will typically have fewer permissions associated with it than the user root@localhost. If you get strange errors such as this:
ERROR 1044 (42000): Access denied for user 'root'@'%' to database 'databasename'
then check whether the host name is resolved from the IP address.