Connecting MySQL Server in Windows Machine From WSL

Connecting MySQL Server in Windows Machine from WSL

Originally published at dataqoil.com.

What does this mean? In simple sentence, how do we connect to a MySQL server which is hosted in Windows from WSL. It might sound easy but let me tell you, IT IS NOT!!!!

I was trying to connect (from WSL) to my local MySQL which was installed on Windows Machine while using Airflow because my Airflow was installed in WSL. But it took me long to figure out the best way to do it. I hope it helps you too.

MySQL Client in WSL

First install MySQL client in WSL using below command which can be seen once we type mysql in WSL terminal.

sudo apt install mysql-client-core-8.0     # version 8.0.27-0ubuntu0.20.04.1, or
sudo apt install mariadb-client-core-10.3  # version 1:10.3.31-0ubuntu0.20.04.1

For me, I did first one.

Find IPv4 Address of WSL

• Go to Settings -> Network and Internet -> Status -> View Hardware and connection properties. Look for the name vEthernet (WSL). It will usually be on the bottom.
• My looks like below. But I've shaded the addresses.

Now try to connect to MySQL from WSL using below command:

mysql -u wsl_root -p -h 172.24.xxx.xxx

Please remember that in above command xxx is just a placeholder. Also, root is just a username that we tried to login with. We will get an error right now with above command and we will fix it.

Making New User in MySQL to make a Call from WSL

CREATE USER 'wsl_root'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'wsl_root'@'localhost' WITH GRANT OPTION;
CREATE USER 'wsl_root'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'wsl_root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

In above query,

• wsl_root is a name of user that we will use from WSL. localhost is a address where MySQL is and password is password. :)
• We have granted all privileges to that user and it will be just another admin.

From WSL

Now running the command mysql -u wsl_root -p -h 172.24.xxx.xxx and giving password after it asked, we could connect to the MySQL server.

References

• https://stackoverflow.com/questions/1559955/host-xxx-xx-xxx-xxx-is-not-allowed-to-connect-to-this-mysql-server

Comments

[Alvin Cousins]

It wasn't so clear to me which IPv4 address was attached to WSL- so ended up trying all of the ones listed. I guess it should start 192.168...
Eventually got it to connect. Thanks!

[Viper]

Yes. Thank you for pointing out. I have updated this blog. I suppose the one that worked in your case must be the address with name WSL in it.

[Alvin Cousins]

Well that was part of the confusion: the address with “WSL” in its name didn’t work for me, but instead one starting “192.168…” did.

[Alexandru I. Bujdei]

Hi,
Thanks a lot for this, it helped me since I was not able to start a project in WSL.
I have one question.
Why did we have to create a new user?
Could we not have given . priviledges to the default root user?
Thanks

[panayot-zhi]

Wouldn't the IPv4 address of the WSL change upon let's say restart?