MySQL on Windows: Access Denied for User 'root'@'localhost' (using password: YES)

Question

I'm using MySQL on windows (I wish I was using Mac, but due to some issues I'm using Bootcamp), and I've encountered a problem I think I've encountered before: the error that is written in the title. I think I might have solved this once before by erasing MySQL through the control panel and by erasing everything in the hidden folder, ProgramData, and MySQL from ProgramFiles, then reinstalling it. It is such a hassle and consumes a lot of time. It definitely isn't something I can do repeatedly to solve this problem if I get an error like this again and again.

The problem:

I simply ran this code on CMD after "cd"ing to the bin folder that contains mysql: mysql -u root -p PASSWORD but I get the error written in the title. Thus I can't login to mysql and run any lines to edit the database.

There are so many other topics like this in stackoverflow:

1. MySQL Error: : 'Access denied for user 'root'@'localhost'

2. Access Denied for User 'root'@'localhost' (using password: YES) - No Privileges?

3. https://dev.mysql.com/doc/refman/8.0/en/resetting-permissions.html

I tried implementing the solutions from the three links above. Many other advices advise to use the "sudo" command, which is not a windows command. This is the line I get from implementing the solution given to me by dev.mysql.com (#3):

This is the line of code I ran in CMD:

mysqld --init-file=C:\\mysql-init.txt --defaults-file="C:\ProgramData\MySQL\MySQL Server 8.0\my.ini" --console

This is the code I ran to implement the solution in link #2:

mysqld --skip-grant-tables --skip-networking --console

and the code I get back:

Things to note:

• Each time, I get this line of code back from the console: [Warning]...[Server] CA certificate ca.pem is self signed.
• In the first picture, this is the "ERROR" I get from the console: [ERROR]...[Server] unknown variable 'defaults-file="C:\ProgramData\MySQL\MySQL Server 8.0\my.ini'
• In the second picture, this is the "ERROR" I get from the console: [ERROR] TCP/IP --shared memory, or --named-pipe should be configured on NT OS
• This is the line of code I would put in in mysql-init.txt: ALTER USER 'root'@'localhost' IDENTIFIED BY 'PASSWORDHERE';
• or this: INSERT INTO mysql.user (Host, User, Password) VALUES ('%', 'root', password('YOURPASSWORD')); GRANT ALL ON *.* TO 'root'@'%' WITH GRANT OPTION;
• I installed MySQL through the installation wizard.

Q. How did I encounter this error?:

A. I was experimenting on a project for a back-end Spring Boot project that uses JPA and JDBC combined. I got errors telling me that no datasource could be configured.

Q.How did my root password get reset?

A.I think they got reset because of JPA's datasource autoconfiguration. I didn't touch any of mysql's settings myself. Something I did on Spring Boot and its automatic configuration to localhost must have reset the password.

Answer 1

For anyone encountering this issue after they initialized their mysql server with following command:

mysqld --initialize

Look out for the last output of the process, it should say something like this:

[Note] [MY-010454] [Server] A temporary password is generated for root@localhost: password12345

there is your temporary password to log in.

Answer 2

I will say that uninstall your MySQL and again install it with a new instance but not like just uninstall it from the control panel that's why you are facing the same error every time. I already answered here in detail that how to remove properly so no error will come by creating a new instance.