Cannot connect to MySQL database (running on WSL2) from Windows Desktop Application "MySQL Workbench"

Question

I set up MySQL on Windows Subsystem for Linux (WSL2 version). I'm relatively new to MySQL, but I have confirmed the following:

• It is running (ps ax | grep mysqld returns a value)
• It is running on default host 127.0.0.1
• It is running on default port 3306

To login to the mysql shell, I use the command sudo mysql -u root -p. Without sudo, I am unable to login to the shell.

I assume that this issue has something to do with the host that the MySQL service is running on, but I have no idea how to change that and properly connect. Below is a screenshot of the connection setup in MySQL Workbench.

And below is the error that I get when I use the settings shown and my root user password.

Answer 1

Turns out, this had nothing to do with WSL at all, but rather the authentication method for the MySQL user.

As of MySQL version 5.5.10, users have the ability to use auth_socket authentication. In my case, I used the Linux apt repository to configure and install MySQL, and this was set as the default authentication method, as shown by the output of the following command:

SELECT user,authentication_string,plugin,host FROM mysql.user;

MySQL Workbench does not support this type of authentication, and therefore, you must revert back to the old method of authentication, mysql_native_password.

To do this, run the following command while logged in as root, or whatever user you are trying to connect to MySQL Workbench with:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new-password';

After doing that, MySQL workbench connects fine.

Answer 2

I have had issues connecting to Mysql running in WSL2 at first but I am now able to connect to the WSL2 MySQL instance via localhost. You just have to make sure that MySQL is listening on all ports which it does by default in MySQL 8.

[mysql]
port=3307 # or any port you like
bind-address=0.0.0.0

[

Answer 3

I think to connect from a SQL client (SQLYog, HeidiSQL, etc) to a mariaDB (MySQL) database installed on WSL2 (Windows 10) it is not necessary to change SGDBR config, here is what I did to solve this problem:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

This command will set a password to the root user, that's it... You can now connect to your DB with root/[new_password]

I hope it can help and works for you

More info: https://mariadb.com/kb/en/authentication-plugin-unix-socket/

Answer 4

Besides creating a user and granting access to localhost and % I had to use the internal IP address for eth0 in WSL. I follow the instructions in How to connect to WSL mysql from Host Windows. It worked for me.

Answer 5

I'm on WSL 1, and unknowingly I have also been using MySQL 8 rather than 5.7.

I tried answers from all across the StackExchange sites for hours, and none of them worked.

Then this answer gave me the idea of checking my MySQL Workbench version. It was 6.

Once I upgraded to MySQL Workbench 8, I was then able to use it to connect to the MySQL 8 server that was running within WSL:

Note that I'd previously run (in MySQL, in WSL):

CREATE USER 'my_local'@'%' IDENTIFIED BY 'xxxxiuyiuyiuyxxx';
GRANT ALL PRIVILEGES ON * . * TO 'my_local'@'%';
FLUSH PRIVILEGES;

Answer 6

If anybody else has tried everything above but the workbench still won't connect to the wsl2 database, for me it was the fact that I had mysql installed on windows too (not wsl2) and it's service was running (MYSQL80). Stopping it immediately allowed it to connect to the wsl2 localhost.