The best solution for resetting the root password of MySQL 8.0.23

This method was edited on February 7, 2021. The version I use is 8.0.23. The cause of the incident started with Professor Yuan Longping... It started with a textbook. There is a chapter called "MySQL Security Management and Permission Management", which mentions changing the root account

ps: This method is suitable for anyone who has forgotten, modified or messed up the root password

In the spirit of pragmatism, I typed in my MySQL database (currently logged in as root):

update user set authentication_string=MD5("123") where user = "root" and host = "localhost";
flush privileges;

Here I have to talk about the current Baidu Google tutorial. Students can first look at their own user table structure. In the user table, generally speaking, we are most concerned about three fields, namely the host column of localhost, the user column of the username root, and the authentication_string column that saves the password

desc user;

Note: The field for saving password is no longer password (it may be in the old version, and many tutorials for changing root password are still at password). Secondly, my newer version does not apply password function here. The textbook uses MD5 encryption. Currently, many Baidu ones are still like the following. This method is no longer applicable.

...password = password("123")...

After I typed the above code, I logged out and restarted the server and tried to log in with the new password 123, but it gave an error message saying the password was wrong.

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

After that, I spent three hours searching Baidu Guide and trying to retrieve my password, but all ended in failure.
After 24 hours, I accidentally deleted the password saved in the original authentication_string, and then set a new password. Here is some practical information, tested by myself~

1. Password-free login

Set mysql login to password-free login (I don't know whether I can use the debian-sys-maint account in the debian.cnf configuration to log in and modify the root password. I am afraid that the debian permissions are not enough to change the root, so I chose root password-free login. Interested students can try it. This step is mainly to be able to enter the server). The specific method is:
Enter the configuration file, my configuration file is in

/etc/mysql/mysql.conf.d

Use sudo to open it, because you must have su privileges to change the etc configuration. Of course, since I did not customize the configuration in my home directory, all are default. This configuration is likely to be different for each person, and you may have configured it yourself. Then you can use mysql --help on the terminal to see which configuration is in effect at this time. Of course, it is not easy to find it all at once. For example, my current configuration file is in the second /etc/mysql/my.cnf, but there is no configuration option when I open it. There are two paths in it. This path is the real configuration file, just like a "nested"

sudo vim mysqld.cnf

After opening, insert a line below [mysqld] around line 15

Then save and exit vim.

2. Clear the authentication_string password

Actually, I stumbled into this step by mistake, and was led astray by various copied tutorials on the Internet. When I was studying the user table tonight, I saw that the plugin field defaulted to caching_sha2_password, so I thought that maybe I should not have used md5 encryption before, but sha2, so:
Note: This is a critical step

update user set authentication_string=sha2("1234",32) where user = 'root';
flush privileges;

Then I looked at the root data in the user table and found that the value of authentication_string had become null. At this point, I had a hunch that I could actually log in without a password. Return to the configuration file (note that sudo is used) and add the line of code above to the configuration file.

skip-grant-tables

Delete it, restart the MySQL server, and log in

mysql -u root

As expected, I can log in, a real password-free login, because the root account password has been cleared at this time, but I don’t understand why the sha2 digest algorithm does not calculate a password but clears a password. I’m sure someone knows...

3. Reset your password

Resetting the password is easy, just one line of code:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123';

ps: After MySQL is installed for the first time, there is no password. When you log in to the server for the first time, you may need to log in with sudo mysql -uroot. After logging in, set the root password of MySQL yourself. The setting method is exactly the same as the above code.

4. End

After that, you can log in to MySQL again with the new password

mysql -u root -p123

5. Last

• This time I also cleared the root password by mistake. The specific principle is not clear. For example, why did the root password authentication_string field become NULL when using sha2? If your plugin is md5, it may be correct to use MD5. If it is sha1, try sha1. I didn't read the official document, so the whole process was very painful. Therefore, strictly speaking, this is a question post. Waiting for the big guys. Thank you.
• Many places may not be explained in detail, such as database restart, configuration files, how to find the debian-sys-maint account, vim editing, etc. If you have any questions, please feel free to ask. I am often on csdn and am always there.
• The system is Ubuntu, and the Mac method should be the same. The main thing is that the MySQL configuration file of Mac has been optimized, so you must make a my.cnf yourself. Most students should put it in the home directory, so the file location must be different from mine.
• Maybe you are still using an older version, such as 5.x. The methods on the Internet may still be applicable, but this method is definitely not applicable (because the password fields in the user table are different), so pay attention to the version.
  

This is the end of this article about the optimal solution for resetting the root password of MySQL version 8.0.23. For more information about resetting the root password of MySQL 8.0.23, please search for previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope you will support 123WORDPRESS.COM in the future!