5

Does anyone know what the error could be when I can connect to my MySQL server via localhost but not via 127.0.0.1? AFAIK, these should be routed identically and no additional TCP layer should be created when using the IP.

$ mysql -u root -h localhost -D test # works
$ mysql -u root -h 127.0.0.1 -D test # does not work
(127.0.0.1 does map to localhost/vice versa)

The table permissions are explicitly set to include both methods.

Michael Schubert
  • 720

3 Answers3

7

The MySQL client library overrides localhost to use a Unix socket instead of a TCP/IP connection to 127.0.0.1. If you can connect to localhost but not 127.0.0.1, that suggests that it's either firewalled or not listening on 127.0.0.1.

Mike Scott
  • 4,443
  • Still true for MySQL 8.0 on linux: On Unix and Unix-like systems, a connection to localhost results in a socket-file connection. (doc) – robsch Sep 10 '20 at 09:38
2

The manual at Connecting to the MySQL Server suggests using the following syntax :

shell> mysql --host=127.0.0.1

The explanation is :

On Unix, MySQL programs treat the host name localhost specially, in a way that is likely different from what you expect compared to other network-based programs. For connections to localhost, MySQL programs attempt to connect to the local server by using a Unix socket file. This occurs even if a --port or -P option is given to specify a port number. To ensure that the client makes a TCP/IP connection to the local server, use --host or -h to specify a host name value of 127.0.0.1, or the IP address or name of the local server. You can also specify the connection protocol explicitly, even for localhost, by using the --protocol=TCP option. For example:

harrymc
  • 480,290
1

MySQL client infers the protocol from the provided host.

For a given connection, if the transport protocol is not specified explicitly, it is determined implicitly. For example, connections to localhost result in a socket file connection on Unix and Unix-like systems, and a TCP/IP connection to 127.0.0.1 otherwise.

Source: MySQL Docs

If you are unable to connect to the server using 127.0.0.1 as host, it probably means that the server is not running on a TCP port or port 3306 (which is the default when not provided). In that case you can connect to it only over the socket file.

Omkar Manjrekar
  • 11