How to reset root password MySQL on Windows

If you forget password MySQL server on Windows and have tried all tutorials from mysql.com but it isn’t working. Don’t worry, I am too. It took me 3 hours to find the solution below to recover MySQL root password.

To reset MySQL server password, you need to follow this guide step by step:

1. Stop your MySQL server completely. Start “services.msc” using Run window, and stop the MySQL service there.

2. Open your MS-DOS command prompt using cmd with administrator privilege. Then go to your MySQL bin folder, such as C:\Program File\MySQL\MySQL 5.7\bin.

Execute the following command in the command prompt:

mysqld.exe -u root –skip-grant-tables –datadir=”C:\ProgramData\MySQL\MySQL Server 5.7\Data”

datadir parameter must point to MySQL data folder, without MySQL will create new data folder. If you don’t specific datadir parameter you will get the following error:

D:\Program Files\MySQL\MySQL Server 5.7\bin>mysqld.exe -u root --skip-grant-tables
mysqld: Can't change dir to 'D:\Program Files\MySQL\MySQL Server 5.7\data\'     (Errcode: 2 - No such file or directory)
2021-11-17T08:30:18.822962Z 0 [Warning] TIMESTAMP with implicit DEFAULT     value is deprecated. Please use --explicit_defaults_for_timestamp server option     (see documentation for more details).
2021-11-17T08:30:18.822962Z 0 [Warning] Insecure configuration for --secure-    file    -priv: Current value does not restrict location of generated files.     Consider setting it to a valid, non-empty path.
2021-11-17T08:30:18.822962Z 0 [Note] mysqld (mysqld 5.7.9) starting as     process 1108 ...
2021-11-17T08:30:18.838586Z 0 [Warning] Can't create test file D:\Program     Files\MySQL\MySQL Server 5.7\data\DESKTOP-RNBR3E8.lower-test
2021-11-17T08:30:18.838586Z 0 [Warning] Can't create test file D:\Program     Files\MySQL\MySQL Server 5.7\data\DESKTOP-RNBR3E8.lower-test
2012-11-17T08:30:18.838586Z 0 [ERROR] failed to set datadir to D:\Program     Files\MySQL\MySQL Server 5.7\data\
2021-11-17T08:30:18.838586Z 0 [ERROR] Aborting


2021-11-17T08:30:18.838586Z 0 [Note] Binlog end
2021-11-17T08:30:18.838586Z 0 [Note] mysqld: Shutdown complete

Leave the current MS-DOS command prompt as it is, and open a new MS-DOS command prompt window.

3. Go to your MySQL bin folder again.

Enter mysql -u root and press enter. Next, you need to access MySQL database and reset the root password.

You should now have the MySQL command prompt working. Type “use mysql;” so that we switch to the mysql database.

Execute the following command to update the password:

update user set authentication_string=password(‘your-password’) where user=’root’;
flush privileges;

After you have finished, close the first command prompt, then type “quit;” in the second command prompt then verify the new password with the following command.

mysql -u root -p

If it works, then you successfully reset the MySQL root password.