How to reset MySQL root password under Windows

Today I found that WordPress could not connect to the database. I logged into the window server and found that all services were running normally.

Use the root account to log in to the MySQL database, and the result prompts that the password does not match. I suddenly realized that the server might have been attacked by SQL injection...

As for the cause of the accident and the remedial measures taken afterwards, I will talk about it when I have the chance. Here I will mainly talk about the steps to reset the mysql user password.

Reset root password If you forget the root password, you can enter mysql safe mode and reset the root password.

1. Stop MySQL service

Open a command prompt window and enter net stop mysql to shut down the MySQL service.

C:\Users\Administrator>net stop mysql57
MySQL57 service is stopping..
The MySQL57 service has been stopped successfully.

↑ The service name is not necessarily mysql, for example, mine is mysql57, 57 represents the version number 5.7

Of course, you can also shut down the MySQL service through the Computer Management Panel.

2. Switch to the bin directory

In the command prompt window, use the cd command to switch to the bin directory under the MySQL installation directory.

C:\Users\Administrator>
cd C:\Program Files\MySQL\MySQL Server 5.7\bin
C:\Program Files\MySQL\MySQL Server 5.7\bin>

↑ The default installation directory is C:\Program Files\MySQL\MySQL Server

3. Enter safe mode

Enter mysqld --skip-grant-tables in the bin directory to start mysql by skipping the permission check.

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

[mysqld]

basedir = "C:\ProgramData\MySQL\MySQL Server 5.7"
datadir = "C:\ProgramData\MySQL\MySQL Server 5.7\Data"

↑ I specified the data storage path in the my.ini file. If I do not introduce the configuration file, it will prompt the error No such file or directory.

4. Reset account password

Open another command prompt window (do not close the safe mode window), switch to the mysql\bin directory, and enter mysql to skip permission verification and connect to the database.

C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql
Server version: 5.7.16 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

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

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

mysql> update mysql.user set authentication_string="" where user="root";
Query OK, 1 row affected (0.00 sec)

mysql> select user,authentication_string from mysql.user\G
*************************** 1. row ***************************
         user: root
authentication_string:
*************************** 2. row ***************************
         user:mysql.sys
authentication_string: *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE

2 rows in set (0.00 sec)

↑ The authentication_string field for the root user has been cleared

5. Refresh the permission table

Execute the flush privileges; command to refresh the permission table. The password has been reset. Enter quit to exit.

mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)
mysql> quit
Bye

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

Change root password

For security reasons, the root password should not be empty, so you need to set a new password after resetting the password.

Method 1: 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:

mysql> set password for root@localhost = password("pswd");
Query OK, 0 rows affected, 1 warning (0.00 sec)

Method 2: mysqladmin

mysqladmin -u "username" -p password "new password"

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

C:\Program Files\MySQL\MySQL Server 5.7\bin>mysqladmin -u root -p password pswd
Enter password: ****

mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

Method 3: UPDATE TABLE

UPDATE mysql.user SET authentication_string=PASSWORD("new password") WHERE user="username";


While resetting the root password, you can also set the default password. However, the password cannot be plain text and must be encrypted using the password() function.

mysql> update mysql.user set authentication_string=password("pswd") where user="root";
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

Summarize

The above is all the content of this article about how to reset the root password of MySQL under Windows. I hope it will be helpful to everyone. Interested friends can continue to refer to this site:

MySQL database design: detailed explanation of Schema operation method using Python

Introduction to fuzzy query method using instr in mysql

Examples of using the or statement in MySQL

If there are any deficiencies, please leave a message to point them out. Thank you friends for supporting this site!