MySQL password reset procedure

MySQL > alter table user; MySQL > alter table user;

The reset idea is to find a way to enter the system without a password, and then use the database command to modify the password record in the user table.

–skip-grant-tables is the recommended method to start the mysql service. This parameter indicates that the authorization table is not loaded at startup, so root can log in with a blank password after successful startup

Mysqld - skip - grant - tablesCopy the code

You can use it after you log in

UPDATE user SET authentication_string=' ' WHERE user='root';Copy the code

These commands set the password or leave it blank.

However, the actual mysqld — skip-grant-tables command line does not start successfully in mysql8, and it also does not start when tested in the INI file

MySQL8 system password reset two ideas

The –init-file parameter is used to load and run the command file to change the password when the service is started. Once this command is executed, the password will be cleared or reset after the service is started. After the service is started, you can log in with empty password or specified password.

Or continue with the — skip-grant-tables command line argument and 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 fields 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 the problem

This parameter specifies that a file containing the SQL command is executed when the service is started. Therefore, you only need to write the password reset command in the file to specify that the command is executed when the service is started. After the service is started, the system password can be reset. + q group: 834962734 can obtain a Java architecture advanced learning resources (high concurrency +Spring source code +JVM principle analysis + distributed architecture + microservice architecture + multi-threaded concurrency principle… These become essential for architects) and the Java progressive learning roadmap.

Step one, turn off system services

net stop mysqlCopy the code

Second, create a text file with a password change command

ALTER USER 'root'@'localhost' IDENTIFIED BY ' ';Copy the code

Step 3: Start the server in command line mode, and specify the password change command file to be executed when the server is started

mysqld --init-file=d:mysqlc.txt --consoleCopy the code

Screenshot of specific operation

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

Same method one, first shut down the system service

Mysql8 –console –skip-grant-tables –shared-memory — mysqld –console –skip-grant-tables –shared-memory

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

mysql.exe -u rootCopy the code

Then run the SQL command to set the password of user root to null

UPDATE mysql.user SET authentication_string=' ' WHERE user='root' and host='localhost';Copy the code

Screenshot of specific operation

MySQL8 has some features that cause the old method to not work, so it is recommended to use –init-file.