How to retrieve password for mysql 8.0.22 on Mac

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.

You may also be interested in:
  • Introduction to some operations of MySQL secure password input
  • How to change the secure processing password in MySQL 5.6
  • Quick solution for forgetting MySQL8 password
  • Detailed explanation of MySQL 8.0 password expiration policy
  • How to change the root user's password in MySQL
  • MySQL implements an example method of logging in without a password
  • How to reset the root password in Linux mysql-5.6
  • How to safely shut down MySQL
  • How to gracefully and safely shut down the MySQL process
  • It's the end of the year, is your MySQL password safe?

<<:  Solve the problem that images and other resources are automatically deleted after Tomcat is redeployed

>>:  Several ways to submit HTML forms_PowerNode Java Academy

Recommend

Nginx Linux installation and deployment detailed tutorial

1. Introduction to Nginx Nginx is a web server th...

MySQL learning notes help document

View system help help contents mysql> help con...

Detailed explanation of dynamic Christmas tree through JavaScript

Table of contents 1. Animated Christmas Tree Made...

User experience of portal website redesign

<br />From the launch of NetEase's new h...

How to make a List in CocosCreator

CocosCreator version: 2.3.4 Cocos does not have a...

Javascript to achieve the effect of closing advertisements

Here is a case study on how to close ads using Ja...

Detailed explanation of count(), group by, order by in MySQL

I recently encountered a problem when doing IM, a...

Analysis of the methods of visual structure layout design for children's websites

1. Warm and gentle Related address: http://www.web...

Tips for viewing text in Linux (super practical!)

Preface In daily development, we often need to pe...

SQL implementation LeetCode (185. Top three highest salaries in the department)

[LeetCode] 185. Department Top Three Salaries The...

CSS and HTML and front-end technology layer diagram

The relationship between Javascript and DOM is ve...

Steps to install superset under win10 system

Superset is a lightweight self-service BI framewo...

How to install mongodb 4.2 using yum on centos8

1. Make a repo file Refer to the official install...

How to use React forwardRef and what to note

Previously, react.forwardRef could not be applied...

Optimization analysis of Limit query in MySQL optimization techniques

Preface In actual business, paging is a common bu...