Database Tutorials

How to reset root password MySQL on Windows

Pinterest LinkedIn Tumblr

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 -uroot” 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, and 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.

I'm a full stack developer. I have experiences with Java, Android, PHP, Python, C#, Web development...I hope website https://learncode24h.com will help everyone can learn code within 24h and apply it in working easily.

Comments are closed.

%d bloggers like this: