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