7

I have a brand new WSL2, running on the latest Windows 10 (2004) with Ubuntu 20.04, MySQL installed, I can access it from Bash and get the status --

mysql> status
--------------
mysql  Ver 8.0.20-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))

Connection id:          10
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         8.0.20-0ubuntu0.20.04.1 (Ubuntu)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:            /var/run/mysqld/mysqld.sock
Binary data as:         Hexadecimal
Uptime:                 13 sec

Next I want to connect with MySQL Workbench. All I ever get is

Your connection failed for user 'root' to the MySQL server at 127.0.0.1:3306
Access denied for user 'root'@'localhost'

My WSL2 list is ---

PS C:\Users\gymdo> wsl -l -v
  NAME            STATE           VERSION
* Ubuntu-20.04    Running         2

My WSL2 info is ---

Installation successful!
To run a command as administrator (user "root"), use "sudo <command>".
See "man sudo_root" for details.

Welcome to Ubuntu 20.04 LTS (GNU/Linux 4.19.104-microsoft-standard x86_64)

 * Documentation:  https://help.ubuntu.com
 * Management:     https://landscape.canonical.com
 * Support:        https://ubuntu.com/advantage

  System information as of Sat Jun 13 02:35:32 EDT 2020

  System load:  1.97               Processes:             8
  Usage of /:   0.4% of 250.98GB   Users logged in:       0
  Memory usage: 0%                 IPv4 address for eth0: 172.20.109.28
  Swap usage:   0%

0 updates can be installed immediately.
0 of these updates are security updates.

I have tried using the 172.20.109.28 from above --- same error

Looking at it from the Windows side with ---

Ethernet adapter vEthernet (WSL):

   Connection-specific DNS Suffix  . :
   Link-local IPv6 Address . . . . . : fe80::c54a:f83c:a5f8:40b2%43
   IPv4 Address. . . . . . . . . . . : 172.20.96.1
   Subnet Mask . . . . . . . . . . . : 255.255.240.0
   Default Gateway . . . . . . . . . :

I have tried using the 172.20.96.1 from above --- same error

One strange thing is --- when I try user name of root@localhost instead of simply root, the workbench pops up the "enter password" popup. So excited I was, but when I leave the password field blank it still fails with password: NO

I'm a beaten down man --- I need help.

4
  • How do you start MySQL in Ubuntu? Do you start with sudo mysql or mysql -u root -p? – Kulfy Jun 13 '20 at 19:04
  • From inside WSL2 box -- I start it with: $ service mysql start then I access it with: $ mysql -uroot – Jim Dunn Jun 13 '20 at 21:26
  • Do you use sudo before mysql -uroot? Is your password blank? – Kulfy Jun 14 '20 at 6:35
  • I start up WSL2 fromPowerShell using WSL -u root without any password, so I am logged in as root so I don't need sudo – Jim Dunn Jun 15 '20 at 3:03
5

I had the same problem with WSL 2 MySQL-Server; I fixed it by replacing 127.0.0.1 in the config of workbench connection with localhost.

127.0.0.1 config

Failed to connect error with Hostname: 127.0.0.1

Localhost config

successful connection with Hostname: localhost

I hope it will be useful for someone!

4

After some more searching I found hints that I may need to GRANT access. From inside the WSL2 box I added a new user and GRANTed access.

mysql> CREATE USER 'newguy'@'localhost' IDENTIFIED BY 'ratfink';
Query OK, 0 rows affected (0.02 sec)

mysql> GRANT ALL ON mysql.* TO 'newguy'@'localhost';
Query OK, 0 rows affected (0.01 sec)

Check the examples down the page here.

I am now able to connect via MySQL Workbench with user: newguy and password: ratfink from MySQL Workbench. I have not been able to GRANT access to root. I even tried removing root as a user and re-adding root without success. Shouldn't mess with Mother Nature I guess.

1

The problem is how the root user is authenticated. By default this is set to use auth_socket instead of mysql_native_password.

Log into the mysql server in wsl as root user and run:

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

The output will list the authentication method for each user.

Now change the method for the root user with the following command:

mysql > ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

Make sure to change 'password' to the correct password for the root user.

Now run command to update changes:

mysql > FLUSH PRIVILEGES;

Now run the command again to make sure the authentication method updated:

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

Now the output should show that the authentication method for the root user is with mysql_native_password. You should now be able to log into workbench as root user using the password.

1
  • WORKED PERFECTLY!!! THANK YOU!!! – DaveyJake Mar 30 at 23:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.