3

I installed MySQL 8.0.16 on Windows 10 using the official online installer from here. I was able to install it successfully but on trying to log in as root I could not get past this error:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

I tried

  • Resetting the password.

  • Changing the authentication plugin using the installer.

  • Changing the authentication type using SQL.

  • Changing the way that MySQL Workbench sends passwords to the server.

  • logging in using the command line

  • un-installing and re-installing

Finally I installed MySQL 5.7.26 which works fine. I think there's a bug with version 8.0.16 on Windows 10. Anyone else having this issue?

Update

This question is not a duplicate of How to reset MySQL root password. I always had the correct password but it was not working.

After multiple trials I think the problem is I had used a single quote in the password. But the installer didn't complain.

AJ Dhaliwal
  • 661
  • 1
  • 8
  • 24
  • Possible duplicate of [How to reset the root password in MySQL 8.0.11?](https://stackoverflow.com/questions/50691977/how-to-reset-the-root-password-in-mysql-8-0-11) – Mike Doe Apr 30 '19 at 08:53
  • I failed to reset the password before because I didn't use the `defaults-file` option. Something to look out for. See answer below for usage. – AJ Dhaliwal May 03 '19 at 09:43
  • A bug report has been opened [here](https://bugs.mysql.com/bug.php?id=95352) – AJ Dhaliwal Jul 21 '19 at 08:37

2 Answers2

3

I just confirmed that if you use single quotes in your root password the installer will not complain but you won't be able to login with the password.

enter image description here

Even if you use the installer's Reconfigure option to check your password the check will be OK. i.e. a green tick will show. But MySQL server will not accept it.

I was able to reset the root password using the official method specified here.

  • Stop the MySQL server service.
  • Create a file named mysql-init.txt with the content:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'yourpasswd';
  • Save it to your User folder.
  • Open a command prompt in admin mode.
  • Execute the following. (replace the locations and user name with your own)
cd "C:\Program Files\MySQL\MySQL Server 8.0\bin"
mysqld --defaults-file="C:\\ProgramData\\MySQL\\MySQL Server 8.0\\my.ini" --init-file=C:\\Users\\UserName\\mysql-init.txt --console
  • You may have to exit out of the command with CTRL+C
  • Delete the mysql-init.txt file.
  • Start the MySQL Server service.
  • Log in with your new password.
AJ Dhaliwal
  • 661
  • 1
  • 8
  • 24
  • I encounter an issue where executing the command does not return any output, despite observing that the mysqld process is active in the background. – Farshad Jun 16 '23 at 05:04
1

Based on the link bellow mysql update the authentication method and your client must be support the caching_sha2_password to connect with this method or you can use the older version of authentication in the mysql installer.

https://dev.mysql.com/doc/refman/8.0/en/caching-sha2-pluggable-authentication.html

Mohammad Samdi
  • 93
  • 1
  • 9
  • He was using the same client version as server. As already mentioned in previous answer the issue was Windows Installer for MySQL messing up the password when it contained the single quote character. You could have better put this as a comment and not an answer as it does not answer the OP's question, but could be maybe useful for somebody coming to this post for a different but similar issue. – Marki555 Aug 25 '21 at 11:31
  • Unfortunately I haven't reputation to add comment :)) – Mohammad Samdi Dec 04 '21 at 14:55