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.