Solution to the problem of Access denied for user'root'@'localhost' (using password: YES) in MySQL 8.0 login under win10

I started learning MySQL recently. The installation went smoothly and I was able to install it successfully by following the ready-made tutorials on the Internet.

But, as I happily type

mysql -uroot -p

When I entered the password again, I encountered this situation

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

I have found many solutions online, but there is basically only one solution, which is to add the mysqld entry in the .ini file

skip-grant-tables

Then restart the server and follow the steps again (without entering the password at the end), but there is still a statement that stops me

Access denied for user 'root'@'localhost' (using password: NO)

I combined many methods on the Internet, tried them one by one, and finally succeeded.

The overall idea is to modify the contents of the .ini configuration file, but it is modified through the command line. I don’t know if it is because the version iteration has blocked the previous method of directly modifying the file. In any case, it is possible to modify it through the command line on MySQL 8.0.

Let’s get started.

1. Open cmd as an administrator and switch to the bin directory of MySQL

2. Shut down the MySQL server

net stop mysql

3. Skip the password entry step

mysqld --defaults-file="D:\MySQL\my.ini" --console --skip-grant-tables

( Note: the file name and path must match your own)

If a statement similar to the one circled in the screenshot appears, it means success

4. Restart the server

net start mysql

5. Log in to MySQL server

mysql -uroot -p

After entering the password, you can log in successfully.

(The number 1 is very similar to the letter l)

At this point, it's almost done, but when I type

show databases;

The feedback from MySQL is:

You must reset your password using ALTER USER statement before executing this statement.

My guess is that MySQL thinks the random password provided during installation is insecure and asks the user to reset the password.

I searched online and found a ready-made method, reference article

Password never expires

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new password' PASSWORD EXPIRE NEVER;

Password has a limited time

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new password' PASSWORD EXPIRE;

Then log in to the server again and it will work normally.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.