How to reset password after forgetting password in MySQL8 (MySQL old method doesn't work)

The command line mysqld –skip-grant-tables cannot be successfully started in mysql8, and the parameter cannot be started in the ini file.

The MySQL password is stored in the user table. Changing the password actually means changing the record in the table.

The idea of ​​resetting is to find a way to enter the system without a password, and then use database commands to modify the password record in the user table.

After checking, the method recommended by MySQL5 system on the Internet is to start the MySQL service with the --skip-grant-tables parameter, which indicates that the authorization table is not loaded at startup, so after successful startup, the root user can log in with an empty password

mysqld --skip-grant-tables

After logging in, you can use

UPDATE user SET authentication_string=” WHERE user='root';

These commands set the password or leave it blank.

However, the command line such as mysqld –skip-grant-tables cannot be successfully started in mysql8, and the parameter cannot be started in the ini file.

Two ideas for resetting the MySQL8 system password

There are two ways of thinking. You can use the --init-file parameter to load and run the command file for changing the password when the service starts. Once the command is executed, the password will be cleared or reset after the service starts. After starting the service, you can log in with an empty password or a specified password.

Or continue to study the reason why the service cannot be started under the –skip-grant-tables command line parameter, solve the problem, then start the service and log in with an empty password, manually enter the command to clear or reset the password record field in the mysql.user table.

The former is recommended.

The specific operation process is as follows:

Method 1: Use the --init-file parameter to solve

This parameter specifies that a SQL command file is executed when the service is started. Therefore, you only need to write the command to reset the password in the file and use this parameter to specify that the command is executed at startup. After the startup is complete, you can reset the system password.

The first step is to shut down the system service

net stop mysql

The second step is to create a text file containing a password modification command

ALTER USER 'root'@'localhost' IDENTIFIED BY ';

Step 3: Start the server in command line mode and specify the password modification command file to be executed at startup

mysqld –init-file=d:mysqlc.txt –console

Specific operation screenshots

Method 2: Find a way to use the --skip-grant-tables parameter

Same as method 1, turn off the system service first

In actual testing, under the mysql8 system, using mysqld –console –skip-grant-tables –shared-memory can start the service without a password

After the service is started, log in to the system with an empty password

mysql.exe -u root

Then execute the sql command to set the root user password to empty

UPDATE mysql.user SET authentication_string=” WHERE user='root' and host='localhost';

Specific operation screenshots

Due to some features of MySQL 8, the old reset method is not very effective. It is recommended to use the –init-file parameter to solve the problem. It has been tested to be safe and reliable.

Losing your database management password is a real headache. If you cannot retrieve it successfully, you will be in a lot of trouble. Most of the solutions found online are out of date. It is recommended that you save this article and pay attention to it in case you need it.