Can connect to virtual MySql instance using 127.0.0.1, but not localhost

Question

Running MySql 5.7.33 on a local VirtualBox instance. I have forwarded port 3306 from the virtual server to be accessible locally. On my virtual host, I have this in my /etc/mysql/my.cnf file ...

[client]
#password = your_password
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld]
port = 3306
bind-address = 0.0.0.0
datadir = /var/lib/mysql
socket = /var/run/mysqld/mysqld.sock
pid-file = /var/run/mysqld/mysqld.pid

From my local machine, I can connect to the MySql instance using

mysql -h 127.0.0.1 -u my_user my_db -p

But once I change to “localhost,” I can no longer connect

$ mysql -h localhost -u my_user my_db -p
Enter password: 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

Not sure what the issue is. From my local machine (i.e. not the virtual server), this is returned for “localhost” …

$ host localhost
localhost has address 127.0.0.1
localhost has IPv6 address ::1

Answer 1

When you specify -h localhost the mysql client will try to use the socket (i.e. UNIX domain socket) rather than TCP. This is a special behaviour in MySQL and its forks.

However, since the MySQL server is running in a VM, the server's socket is not directly accessible to the mysql client. UNIX domain sockets can only be shared between processes sharing the same kernel, and this is not the case with a host OS (with the mysql client) running a guest OS in a VM (with the MySQL server).

By the way, if running the MySQL server instead in a container (docker, podman etc), then you would be sharing the same kernel, and it would also be possible to share a UNIX domain socket.

Solution 1: SSH tunnel

If you must use the socket, then run this from your host OS to create an SSH tunnel (enter password if prompted):

ssh -L /tmp/mysql.sock:/var/run/mysqld/mysqld.sock $VM_USER@$VM_IPADDR

Then, in a separate shell on the host OS:

mysql -h localhost --protocol=socket -u my_user -p -S /tmp/mysql.sock my_db

From the client you can issue the status command to verify that you are indeed connected through the socket.

Note that you need to manually delete the local socket file after you close the SSH tunnel. Otherwise you will get an error next time when you try to connect mysql through a new tunnel:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (111) 

Solution 2: "localhost", but TCP

If you just want to use -h localhost, but don't care about Unix sockets:

$ mysql -h localhost --protocol=tcp -u my_user my_db -p

By simply adding the parameter --protocol=tcp you force the mysql client to connect over TCP instead of the UNIX domain socket. (This works for me when connecting to a podman container whose port 3306 has been forwarded to my host OS's 127.0.0.1:3306.)

By the way, if you don't want to write these long mysql commands every time you connect, you can of course put some or all of the parameters inside a [client] section of a .my.cnf file (note the leading '.') in the root of your $HOME directory. (Same syntax as in your /etc/mysql/my.cnf on the VM.)

Answer 2

Tell MySQL about that "user" can connect via a socket:

CREATE USER "user"@"localhost" ...;
GRANT ... ON ... TO "user"@"localhost";

However, that will only let "user" connect from within that same virtual host.

Think of it as a separate machine.

Answer 3

I think the issue is understanding what localhost is.

In computer networking, localhost is a hostname that refers to the current device used to access it. It is used to access the network services that are running on the host via the loopback network interface. Using the loopback interface bypasses any local network interface hardware.

^{Reference: localhost (Wikipedia)}

Your mysql -h localhost -u my_user my_db -p will fail, because a localhost connection via mysql will be looking for a /tmp/mysql.sock file, which it can't access, because it is inside your MySQL host in VirtualBox.

The default location for the Unix socket file that the server uses for communication with local clients is /tmp/mysql.sock. (For some distribution formats, the directory might be different, such as /var/lib/mysql for RPMs.)

^{Reference: (MySQL Documentation)}

What You Are Observing

When you connect with 127.0.0.1 then you trigger a TCP connection which seems to work with your port forwarding.

When you (try to) connect with localhost then you trigger the SOCKET connection, which is looking for the /tmp/mysql.sock file. This is not available on your localhost, because it is inside the MySQL instance's localhost.