How to retrieve password for mysql 8.0.22 on Mac

Mac latest version of MySQL 8.0.22 password recovery

Problem description:

Yesterday, I suddenly wanted to experience the password change process of the latest version of MySQL on Mac. I changed the plugin by replacing caching_sha2_password with mysql_native_password, and then used UPDATE SET to modify authentication_string. I didn't remember to add the password('new password') function, which led to

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

Cause Analysis:

There are several pitfalls in the whole thing:

1. The password() function has been deprecated in MySQL 8.0
2. The authentication_string field can only contain the 41-bit string password encrypted by MySQL. Others will be reported as format errors. This means that md5('new password') cannot play the role of the original password('new password')
3. The default authentication plugin for MySQL 8.0 database is caching_sha2_password , including the one you use to initialize the database using mysqladmin. So if you want your normal MySQL connection client to connect to the MySQL 8.0 database, you need to modify the default_authentication_plugin , which is the plugin column in mysql.user.

Solution:

Step 1: Shut down the MySQL server

Apple icon in the upper left corner - System Preferences - MySQL - Stop MySQL Server

Step 2: Use system administrator privileges to skip MySQL security authentication and force login

1. Command + Space
2.Terminal
3.sudo -i
4.cd /usr/local/mysql/bin/
5../mysqld_safe --skip-grant-tables &
6.return (click the return key on your keyboard)
7. At this time, MySQL Server becomes running again. You can use ps -ef | grep -v 'grep' | grep 'mysql' to view its PID and the PID of mysqld_safe that started it.
8../mysql

So far, I have successfully logged into MySQL in safe mode.

9. FLUSH PRIVILEGES; (This statement extracts the user information of the current user table and the privilege table permissions into memory to ensure that the permissions can be successfully obtained to modify the user table)
10. First set the authentication_string of root in the user table to an empty string, then use ALTER user to reset the password
11. Exit MySQL, kill mysqld_safe with kill -9, kill mysql, and restart
12. Log in normally with the new password

USE mysql;
UPDATE user SET authentication_string = '' WHERE User = 'root';
ALTER user 'root'@'localhost' IDENTIFIED BY '123456';

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.