Reset the root password for MySQL under Windows

Today found that WordPress connection is not on the database, log on to the Window Server server view, all services are working properly.

Log in to the MySQL database using the root account and the results indicate that the password does not match. I suddenly realized that the server could have been hit by a SQL injection ...

As to the cause of the accident and the remedial measures that have been made afterwards, I will have a chat after the opportunity. Here I am mainly talking about the reset steps of the MySQL user password.

Reset Root Password

Reset the root password by using MySQL's Safe mode in the case of a forgotten root password.

1. Stop the MySQL service

Open a Command Prompt window and enter net stop MySQL to shut down the MySQL service.

c:\users\administrator>net stop mysql57mysql57 service is stopping: The MYSQL57 service has stopped successfully. 

↑ Service names are not necessarily MySQL, for example, my name is mysql57,57. The version number is 5.7.

Of course you can also turn off the MySQL service from the Computer Management panel.

2. Switch to the bin directory

In the Command prompt window, switch to the bin directory in the MySQL installation directory by using the CD command.

C:\users\administrator>

5.75.7\bin>

↑ default installation directory for C:\Program Files\mysql\mysql Server

3. Enter Safe mode

Enter mysqld--skip-grant-tables in the bin directory, skip permission check to start MySQL.

If you have configured the My.ini file, you need to introduce it: mysqld--defaults-file=". /my.ini" --skip-grant-tables

" C:\ProgramData\MySQL\MySQL Server 5.7 "  "C:\ProgramData\MySQL\MySQL Server 5.7\data"

↑ I specified the data storage path in the My.ini file, and if you do not introduce a configuration file, you will be prompted no such file or directory error.

4. Reset your account password

Open another Command Prompt window (do not close the Safe Mode window), also switch to the MySQL \ Bin directory, enter mysql skip permission to verify the connection database.

C:\Program Files\mysql\mysql Server5.7\bin>Mysqlwelcome to the MySQL Monitor. Commands End With; or \g.your MySQL connectionIDIs7Server Version:5.7. -MySQL Community Server (GPL) Copyright (c) -, ., Oracle and/or its affiliates. All rights reserved.Type'Help ;'Or'\h'  forHelp. Type'\c'ToClearThe current input statement.mysql>

↑ can also specify connection parameters mysql-u < user name >-p < password >-h < connection address >-P < port number >-D < Database >

Execute update mysql.user set authentication_string="" where user="root"; Reset the root user's password (password field before 5.7).

mysql> Update Mysql.user Set authentication_string=""where user="Root"; Query OK,1Row affected (0.00sec) MySQL>Selectuser,authentication_string from Mysql.user\g***************************1. Row ***************************user:rootauthentication_string:***************************2. Row ***************************user:mysql.sysauthentication_string:*Thisisnotavalidpasswordthatcanbeusedhere2RowsinchSet (0.00Sec

↑root User's authentication_string field has been emptied.

5. Refresh the Permissions table

Execute flush privileges; The command refreshes the permissions table, the password has been reset completed, and the input quit quit.

mysql>0 rows affected (0.02  sec) MySQL> quitbye

Close all command prompt windows and end the Mysqld.exe process through Task Manager. Restart the MySQL service before you can log in to the root account directly.

Change root password

For security reasons, the root password should not be empty, so you will need to reset the password after resetting it.

Method One: SET PASSWORD

• SET PASSWORD for "username" =password ("New PASSWORD");

Log in to MySQL as root, and then use the Set Password command to change the password:

 for [email protected] = password ("pswd"01 Warning (0.00 Sec

Method Two: Mysqladmin

• Mysqladmin-u "username"-p password "New password"

After executing the name, you will be prompted to enter the original password, and you can modify it after entering it correctly.

5.7pswd* * * * * in plain text, use SSL connection to ensure password Safety.

Method Three: UPDATE TABLE

• UPDATE Mysql.user
• SET Password=password ("New password")
• WHERE user= "username";

You can also set the default password (the Authentication_string field in 5.7) while resetting the root password.

mysql> Update Mysql.user Set Authentication_string=password ("pswd") where user="  root"11 Warning (0.00  sec) MySQL>   0 rows affected (0.01 sec)

Reset the root password for MySQL under Windows