Solve MySQL 8.0 input error and still prompt access denied

Time:2019-11-7

Recently, I started to learn mysql. The installation is very smooth. I can install it successfully according to the online ready-made tutorials.
However, in the input

mysql -uroot -p

When entering the password again, I encountered this situation

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

Many solutions have been found on the Internet, but there is only one. Add mysqld entry in the. INI file

skip-grant-tables

Then restart the server, and then follow the steps just now (do not enter the password at last), but a statement stopped me

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

Integrated many methods on the Internet, and tried one by one, and finally succeeded
The general idea is to modify the content of the. Ini configuration file, but it is modified in the form of a command line. I don’t know if it is because of the version iteration that the previous method of directly modifying the file is shielded. In any case, it is possible to modify the content of the. Ini configuration file through the command line on MySQL 8.0.

Let’s start.

  • Open CMD as administrator and switch to the bin directory of MySQL
  • Shut down MySQL server
net stop mysql
  • Skip the steps of entering password (Note: the file name and path should match their own)
mysqld --defaults-file="D:\MySQL\my.ini" --console --skip-grant-tables
  • If there are statements like screenshots circled, it indicates success

Solve MySQL 8.0 input error and still prompt access denied

  • Restart the server
net start mysql
  • 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)

  • It’s almost finished by this step, but when I input
show databases;

My feedback from MySQL is:

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

My guess is that MySQL thinks that the random password provided during the installation is not secure, so the user can reset the password.
Find it on the Internet and find out the ready-made way
Reference article: https://dev.mysql.com/doc/ref

  • Password never expires
Alter user 'root' @ 'localhost' identified by 'new password' password express new;
  • Cryptographically finite
Alter user 'root' @ 'localhost' identified by 'new password' password exit;

Then log in again and the server will operate normally.