MySQL connections work only with localhost and not 127.0.0.1

Recently encountered a strange issue on client VPS that MySQL connections were working only when using localhost and not with 127.0.0.1 This was due to the fact that the client had enabled skip_name_resolve in his my.cnf file which prevents MySQL to do (DNS) host lookups.

You can find if skip_name_resolve is enabled by the following command:

mysql> SHOW GLOBAL VARIABLES LIKE 'skip_name_resolve';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| skip_name_resolve | ON    |
+-------------------+-------+

To disable skip-name-resolve simply comment it out or delete it from the my.cnf file.

nano -w /etc/my.cnf

Locate the mysqld block and comment or remove:

# Skip reverse DNS lookup 
skip-name-resolve

Save the file and restart MySQL or MariaDB:

service mysql restart

Unfortunately at the time of writing this article (Nov 2021) skip-name-resolve can’t be disabled from the terminal but only directly from the my.cnf file.

To enable it back on, just run:

mysql --disable-skip-name-resolve

Note that there is an open feature request for mysqld to support values on skip-name-resolve, eg.

mysqld --skip-name-resolve=false

So hopefully in the future we might be able to disable it from the terminal as well.