How to change the root password if you forget your password in MySQL8.0/8.x

1. Principle Description

1, authentication_stringThis is a new modification made by Mysql8.0. In the old version, the password() function was used.

2. Among the solutions to "mysql forgot password" found on the Internet, most of them will use UPDATE user SET authentication_string="12345" WHERE user="root"; to directly change the password Changing it to 12345 is actually a wrong usage. The authentication_string stores ciphertext. If it is changed directly to plain text such as "12345", it will cause the password to be incorrect and the login cannot be logged in.

The reason is that when the server verifies the identity, it will first convert the plaintext entered by the user into ciphertext and compare it with the ciphertext in the database to verify whether it matches, and it is obvious that it will directly put the plaintext where the ciphertext should be. Won't let them match successfully.

3. In the similar online search result "mysql change password", alter user root@localhost identified by '12345' will be used to modify it. Password, this command cannot be used when "skipping the authorization table". It can only be used in normal mode.

4 has such a solution. First change the authentication_string to empty. After all, the plaintext and ciphertext of empty values ​​are all empty values. Use the empty password to enter the mysql normal mode and then use alter to change the password.

2. Solution steps

1, stop the mysql service

net stop mysql

2, enter the command , enter the "skip authorization table" mode

mysqld --console --skip-grant-tables --shared-memory

3. Put the previous cmd window aside, open another administrator cmd window, and enter mysqlEnter the mysql service

4, enter use mysql to enter the mysql database, and then change authentication_string to empty

USE mysql;
UPDATE user SET authentication_string="" WHERE user="root";

5, refresh permissions, exit

FLUSH privileges;
exit;

6, in the first cmd window, use ctrl c to exit "Skip authorization Table" mode, start the mysql service

net start mysql

7, use an empty password to log in, then use the alter statement to update the password, refresh the permissions, and exit

mysql -uroot -p

alter user root@localhost identified by '12345'
FLUSH privileges;
exit

8, try to log in with password, successful.

The above is the detailed content of How to change the root password if you forget your password in MySQL8.0/8.x. For more information, please follow other related articles on the PHP Chinese website!