Access MySQL Localhost on Windows Host from WSL2

It’s common to have a MySQL server running on localhost for development. But when using WSL2, it’s not that easy to access the localhost on Windows host. This post will show how to do it.

Check Private Network

Run command ip route on WSL2 to get the IP address of the private network.

$ ip route
default via 172.23.144.1 dev eth0
172.23.144.0/20 dev eth0 proto kernel scope link src 172.23.151.37 

The first line represents the default route, the IP address 172.23.144.1 is the gateway or next-hop IP address. Actually, this is also the IP address of the host machine, where our MySQL server is running.

The second line represents a specific route for the network 172.23.144.0/20, which is a subnet for WSL2. The source src 172.23.151.37 is the IP address that will be used when sending traffic to the network, i.e., the IP address of WSL2.

Run following command can also validate the IP address of the host machine.

echo $(hostname).local
ping -c 2 $(hostname).local

Actually, the WSL2 and the Windows host machine are all on a shared Hyper-V switch, which is a virtual network switch.

However, the IP address of the host machine is not static, it will change after rebooting. So it’s not a good idea to have the IP address hard-coded in the configuration.

And the solution is to use the hostname of the host machine, which is also the name of the host machine. The hostname can be found by running hostname on Windows PowerShell, or echo $(hostname) on WSL2. The .local suffix is also needed. See the python script for connection testing.

It is also noteworthy that the private network IPv4 addresses all begin with 172. This would be helpful when we configure MySQL user.

Create MySQL User

Since all users on MySQL server are on localhost by default, it is required to create a new user for WSL2.

Note that both user name user and host name host need to match to log in, otherwise, it will be denied. For example, if we try to log in as root in Python from WSL2, it will raise an an mysql.connector.errors.ProgrammingError, with an error message that 1045 (28000): Access denied for user 'root'@'172.23.151.37'.

mysql> SELECT user, host FROM mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
4 rows in set (0.01 sec)

Log in MySQL Command Line Client with root privilege, and create a user for WSL2, say, wsl2.

CREATE USER 'wsl2'@'172.%';

or, if password is needed,

CREATE USER 'wsl2'@'172.%' IDENTIFIED BY 'password';

Here % percentage sign is a wildcard which means any IP address that begins with 172.

We could see at this stage the user wsl2 has no privilege.

mysql> SELECT user, host, select_priv, create_priv, insert_priv FROM mysql.user WHERE user='wsl2';
+------+-------+-------------+-------------+-------------+
| user | host  | select_priv | create_priv | insert_priv |
+------+-------+-------------+-------------+-------------+
| wsl2 | 172.% | N           | N           | N           |
+------+-------+-------------+-------------+-------------+
1 row in set (0.01 sec)

Grant Privilege

Grant privilege to the user wsl2 as per requirement.

GRANT CREATE ON *.* TO 'wsl2'@'172.%';
GRANT INSERT ON *.* TO 'wsl2'@'172.%';
GRANT SELECT ON *.* TO 'wsl2'@'172.%';
FLUSH PRIVILEGES;

Rerun the query, we could see the user wsl2 has the privilege now.

SELECT user, host, select_priv, create_priv, insert_priv FROM mysql.user WHERE user='wsl2';

Test Connection

Here is a simple python script. Run it on WSL2 to test the connection.

Remember to replace the user, password and host with your own. The host should be exactly the same as what echo $(hostname).local returns on WSL2.

#!/usr/bin/python3

from mysql import connector

connection = connector.connect(
    user="wsl2",
    password="password",
    host="DESKTOP-4T7NV3Q.local"
)

cursor = connection.cursor()
cursor.execute("SHOW DATABASES;")

for db in cursor.fetchall():
    print(db[0])

cursor.close()
connection.close()

References

1. ip(8) - Linux manual page
2. Accessing network applications with WSL | Microsoft Learn
3. networking - Connect to host machine from WSL2 - Unix & Linux Stack Exchange
4. MySQL :: MySQL 8.0 Reference Manual :: 6.2.1 Account User Names and Passwords
5. MySQL :: MySQL 8.0 Reference Manual :: 6.2.3 Grant Tables
6. MySQL :: MySQL 8.0 Reference Manual :: 13.7.1.3 CREATE USER Statement
7. How to grant all privileges to root user in MySQL 8.0 - Stack Overflow
8. mysql - Not Allowed to Create User with GRANT - Ask Ubuntu
9. How to Modify User Privileges in MySQL Databases :: DigitalOcean Documentation