How to reset the root password of mysql in windows: mysqlroot

How to reset the root password of mysql in windows: mysqlroot

Today, we found that WordPress cannot connect to the database. log on to the window server and check that all services are running normally.

Log on to the mysql database with the root account, and the system prompts that the password does not match. I suddenly realized that the server may be attacked by SQL injection ......

As for the cause of the accident and subsequent remedial measures, I will have a chat later. Here I will focus on the steps for resetting the mysql user password.

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

1. Stop MySQL Service

Open the Command Prompt window and enter net stop mysql to close the MySQL service.

C: \ Users \ Administrator> net stop mysql57
MySQL57 service is stopping ...
The MySQL57 service has been stopped successfully.
↑ The service name may not always be mysql, for example, mine is mysql57, 57 means the version number is 5.7

Of course, you can also shut down the MySQL service through the computer management panel.

2. Change to the bin directory

In a command prompt window, use the cd command to change 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, skip the permission check and start mysql.

If you configured the my.ini file, you need to include 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 no configuration file is introduced, a No such file or directory error will be prompted.

4. Reset account password

Open another command prompt window (don't close the safe mode window), also switch to the mysql \ bin directory, enter mysql to skip the 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 <username> -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 (previously 5.7 password field).

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 root user's authentication_string field has been cleared

5. Refresh permission table

Run the flush privileges; command to refresh the privilege 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 the task manager. Restart the MySQL service, and then you can log in to the root account directly.

Change root password
For security reasons, the root password should not be blank, so you need to set a new password after the password is reset.

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 you enter 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";

When you reset the root password, you can also set a default password. However, the password cannot be clear 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)
to sum up

The above is all the content of this article about resetting the root password of mysql under windows, I hope it will be helpful to everyone. Interested friends can continue to refer to this site:

Detailed MySQL database design using Python operation Schema method

Introduction to fuzzy query using instr in mysql

Or statement usage example in MySQL

If there are deficiencies, please leave a message to point out. Thank you friends for your support!