MySql server not showing proper databases from Ubuntu server

Question

I'm using wsl2 on a windows machine. I want to view my databases that I have on mysql server ubuntu in a GUI such as mysql workbench (on windows) but it seems as the two are not linked. In the pictures provided you can see that when I login to root, it displays different databases, I also use different passwords for root on both servers. When I try to use the root password from the ubuntu server in workbench, I get the error that I cannot connect to the database server.

Ubuntu databases

MySql workbench databases

MySql workbench config

MySql workbench error

Answer 1

UPDATE 2022

I found myself in this same need, and found a good resource that tackles this issue rather nicely. The solution itself predates even this question, funnily enough.

Long story short, check the following GitHub repository. Instructions are available and I can confirm it works on Windows 10.0.19041.1415 and WSL2.

https://github.com/shayne/go-wsl2-host

========================================================

WSL doesn't use the same IP as Windows, meaning you can't access it using localhost. Also, WSL IP changes everytime you boot it, meaning that the credentials for the connection will work only once.

In the sister community SuperUser, this has been discussed and some workarounds are avaliable, but I can't tell if they will work specifically with MySQL Workbench, as they ofter require you to use PowerShell/CMD.

Please, refer to the following discussions, which also provide further sources on the topic (There is one in particular that might be useful if you are running Windows 10 Pro).

Make IP address of WSL2 static

localhost and 127.0.0.1 working but not ip address in wsl windows 10

There are several requests to allow us to set WSL IP statically, so we can register it as a host in Windows DNS Host file and use that alias instead of the IP while setting up a connection (or use the IP itself, since it would be static anyway), but it is not ready yet AFAIK.

Answer 2

After reading the answer from @Jetto, I thought you could create a batchfile like this:

@ECHO OFF
wsl export wsl=$(hostname -I); sed -i -e "s/172.[0-9]*.[0-9]*.[0-9]*/${wsl/ /}/g" /mnt/c/Users/*username*/AppData/Roaming/MySQL/Workbench/connections.xml

This will replace the ip-address to the current ip-address of your wsl instance (relying on the fact that is starts with 172.)

If you start MySQL Workbench after running this script, you should be able to connect to MySQL (or MariaDB) which is running in the WSL2 session.

Disclaimer: I am not responsible for the fact that you did not make a backup of the file connections.xml

P.S. In case you wonder: Yes this instance on my computer uses port 3356. But 3306 should work too if you do not have a local MySQL running.