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.

3 Answers 3

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.

1
  • 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, 2020 at 9: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:

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .