Connect to MySQL from WSL2?
I have installed MySQL server on Windows and I would like to connect to it from WSL2. Unfortunately it seems that WSL cannot reach the MySQL server:
$ /mnt/c/Program\ Files/MySQL/MySQL\ Server\ 5.7/bin/mysql.exe -uroot -p
Enter password:
mysql> exit
Bye
$ mysql -uroot -p --protocol=TCP
Enter password:
ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (111)
On ifconfig I only have two interfaces eth0 and lo, so it seems WSL and Windows share the same local interface. To verify this I've started a Python webserver and accessed from Windows on localhost.
How to connect to MySQL from WSL2?
As per the WSL vs WSL2 documentation on how to connect to the host, you can look in WSL2 in /etc/resolv.conf for the nameserver, which happens to be the host:
export WSL_HOST_IP=$(awk '/nameserver/ { print $2 }' /etc/resolv.conf)
As described in this article, you have to use the IP that the WSL ethernet adapter creates.
Create the windows firewall rule if necessary. (Inbound rule for MySQL)
Run ipconfig.exe and look for Ethernet-Adapter vEthernet (WSL). Extract the IP-Address. (If you use the awk-line from the article, I had to change the $14 to $13 for it to work).
Grant the ip-address access in mysql: grant all on *.* to 'root'@'172.0.0.0/255.0.0.0' identified by '<password>' with grant option;
(I found that the IP-Address changes after a Windows restart, therefore I had to use the IP range/mask version.)
Then you can call mysql -uroot -p -h<ip-address> --protocol=tcp
Edit
If you want to skip this -h parameter, you could add the export and an alias to the .bashrc:
export WSL_HOST_IP=$(/c/Windows/system32/ipconfig.exe | awk '/WSL/ {getline; getline; getline; getline; print substr($13, 1, length($13)-1)}')
alias mysql='mysql -h$WSL_HOST_IP'
(If you use /mnt/c/ correct the path for ipconfig accordingly.)
Then you can just type mysql -uroot ...
The above steps work, but there is a slightly easier way to refer to the host.
As of 2021,all you need to do is refer to the Windows host as:
hostname.
or
hostname.local
This returns the same IP address as the brilliant workaround above with the resolve.conf file, and is a little easier.
Note that localhost from wsl does resolve to a different IP, the normal 127.0.0.1
That makes sense, it's a VM, basically.
Unintended consequences: If you're trying to log into say MariaDB, this does not count as localhost - so you have to have the right security setup to allow logging in from a remote machine (the contained VM)