Solution to forgetting MySQL root password in MACOS

MySQL is a relational database management system developed by Swedish company MySQL AB and currently a product of Oracle. MySQL is one of the most popular relational database management systems. In terms of WEB applications, MySQL is the best RDBMS (Relational Database Management System) application software.

MySQL is a relational database management system that stores data in different tables instead of putting all the data in one large warehouse, which increases speed and flexibility.

The SQL language used by MySQL is the most commonly used standardized language for accessing databases. MySQL software adopts a dual licensing policy and is divided into community edition and commercial edition. Due to its small size, fast speed, low total cost of ownership, and especially its open source nature, MySQL is generally chosen as the website database for the development of small and medium-sized websites.

After installing MySQL on Mac, the software will generate a default password for us. However, when I used Navicat to establish a connection, it prompted that the password was invalid. I had no choice but to change the default database password.

Next, record the entire root password modification process.

The mysql service must be stopped before starting the following steps!

 cd /usr/local/mysql/bin/
 sudo su
 ./mysqld_safe --skip-grant-tables & //This step is used to bypass permission verification./mysql -uroot //Log in as root. No password is required because of the third step. There is no need to add ./mysql in front of the command after this use mysql;
 update user set authentication_string='123456' where User='root';

The versions circulating on the Internet are all set password = ''. This way of writing always gives an error saying that the 'password' column does not exist!

Finally, using the SQL command to check, it was found that there was only an authentication_string field, but no password field.

After executing the previous step, I thought I could log in, but when I tested the connection with navicat, the following error occurred:

ERROR 1862 (HY000): Your password has expired. To log in you must
change it using a client that supports expired passwords.

So the following steps are needed

 cd /usr/local/mysql/bin/
 sudo su
 ./mysql -uroot -p123456
 set password = password('123456')

Username: root, Password: 12345

Modification successful

Supplement: Although the above modification was successful, there were still many detours. The above is just a record of the whole process. Let's summarize the simplest and most effective method below.

This process was heartbreaking. There was a ton of information on the Internet, but everyone had their own wrong methods. I tried for a long time but couldn't find the right one. Just when I was about to break through my psychological defenses and look up the MySQL documentation, I succeeded. There is no article that tells me the complete answer. I referred to several guides and built the car behind closed doors. Give yourself a thumbs up. Without further ado, follow me step by step.

1. Shut down the MySQL server

sudo /usr/local/mysql/support-files/mysql.server stop

You can also turn it off in MySQL in System Preferences.

2.cd /usr/local/mysql/bin to enter the directory

3. sudo su to obtain permissions

4. ./mysqld_safe --skip-grant-tables & restart the server

5. Reopen the terminal,

Configuration short command:

alias mysql=/usr/local/mysql/bin/mysql

6. Enter mysql to enter mysql command mode

7. Use mysql to enter the mysql database

8. flush privileges; probably means to obtain permissions, otherwise he won't let you change it.

9. set password for 'root'@'localhost'=password('新密碼'); complete the modification

10. Damn, I finally finished the revision.

Okay, I have taught you the method, I hope it will be helpful to you.