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.)
[client]
section/option group of your VM's/etc/mysql/my.cnf
file is not used/accessible by your local / host machine.