Search code examples
mysqlwindows-10mysql-8.0

Log in problems with MySQL 8.0.16 on Windows 10

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.

Solution

  • 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.