I develop on Windows, but I don't like installing MySQL for Windows because the setup experience is quite terrible. The last time I tried to install an update it asked me to locate an MSI file who knows where for the old installation. So, I finally had it, and installed MySQL on my WSL2 setup.

Why not Docker?

Put it simply, it sounded easier to me to just type sudo apt install mysql on my WSL2 prompt than looking for the right container, right command-line switches at the time.

That was a big mistake.

But, I pushed through and succeeded, and wanted to share my story. Some of the information here might be relevant for other software too, so leaving it here.

There are mainly two problems accessing a MySQL instance in a container over native:

1. MySQL itself doesn't listen beyond its own localhost by default. So, basically, anyone outside the container becomes an unwanted visitor.
2. WSL2 instances have their own IP address, not localhost. If your development environment setup uses localhost, you'll have to change all the localhost definitions with your WSL2 IP address which might change in the future too.

Let's solve both problems.

Make MySQL listen to external networks

Define a password for root

By default, root user on MySQL doesn't have a password, but it's bound to localhost. That prevents it from being accessed remotely. In order to make it accessible from Windows, you need to set a password to it, and change the auth mechanism by using this command in MySQL shell:

ALTER USER 'root'@'localhost' 
IDENTIFIED WITH caching_sha2_password 
BY '<NEW_ROOT_PASSWORD>';

This will change the auth mechanism, and change the root password at the same time. Remember to use a random, strong password, and save it to your favorite password manager. Now, try connecting to your MySQL instance from a WSL2 shell with your new password and make sure that it succeeds.

Change root's host to wildcard

The root user in MySQL has a host of localhost by default. This makes it impossible to login as root from any other host. Run this command in MySQL to change that:

RENAME USER 'root'@'localhost' TO 'root'@'%';

'%' means a wildcard here, and it allows logins to root from any host.

Allow external access to MySQL

Even when you allow external root access, you still can't connect to MySQL container from your Windows machine because MySQL only binds to its localhost, the container instance. You need to make MySQL allow external connections.

Edit /etc/mysql/mysql.conf.d/mysqld.conf and comment out this line:

bind-address            = 127.0.0.1

by prefixing it with "#", so it looks like this:

#bind-address            = 127.0.0.1

Make Windows forward connections to WSL2

This would let any setup for localhost keep working for MySQL.

First, learn your WSL2 IP address with ip addr show eth0 on your WSL2 shell. You need to forward any request to localhost to that address.

Create port forwarding from Windows to WSL2

To do that, run this command on an Administrator Command Prompt on Windows:

netsh interface portproxy add v4tov4 listenport=3306 listenaddress=0.0.0.0 connectport=3306 connectaddress=<YOUR_WSL_IP_ADDRESS>

Test port forwarding

Now, test if you can connect to the MySQL port using localhost by running this on a PowerShell prompt:

Test-NetConnection 127.0.0.1 -p 3306

The output should show TcpTestSucceeded as True:

ComputerName     : 127.0.0.1
RemoteAddress    : 127.0.0.1
RemotePort       : 3306
InterfaceAlias   : Loopback Pseudo-Interface 1
SourceAddress    : 127.0.0.1
TcpTestSucceeded : True

Block external access to MySQL port on Windows

Just to make sure that you don't accidentally expose your MySQL instance to other network peers, you can add rules to let only your localhost connect to your MySQL port for good measure.

Run the commands below on an Administrator command-prompt:

netsh advfirewall firewall add rule name="Allow MySQL from localhost" dir=in action=allow protocol=TCP localport=3306 remoteip=127.0.0.1
netsh advfirewall firewall add rule name="Block MySQL from all hosts" dir=in action=block protocol=TCP localport=3306

The first rule you add will take precedence over the second one.

Et voila!

Now you finally have this weird setup because you didn't care to use a container. Congratulations.