Resetting MySQL root password in Windows

This tutorial describes how to reset the MySQL root user password if the root password is forgotten. The procedure in windows involves creating a new file which contains a query to UPDATE the MySQL root user’s password from the table named ‘user’ and then start the MySQL process again. Before starting check whether MySQL is running as a service or as a process. To check this got to start -> run or [windows] + R and type services.msc. If you find a service named MySQL or something similar to wampmysql then MySQL is running as a service right click it and stop it. Else open task manager and locate a process named mysqld and “End” the process. If you’re using Linux read this article.

Open a text editor and type the following two queries.

UPDATE mysql.user SET password=PASSWORD('newpassword') WHERE user='root';
FLUSH privileges;

Replace newpassword with your desired password to be reset. Save the file with preferably a .sql extension on the local hard drive.

After MySQL is stopped open the command-line by typing cmd in run. Navigate to your MySQL installation bin folder by using the cd command

cd "C:\MySQL Server 5.1\bin"

if you don’t know where you installed MySQL open the MySQL service properties window and locate it.

Now issue the command to start the MySQL server with the initialization file.

mysqld.exe --defaults-file="C:\MySQL Server 5.1\my.ini" --init-file="C:\file.sql" --console

The defaults-file contains the configuration for MySQL if you don’t know where this file is see the MySQL service properties window. The init-file option is the path to the queries file created in the previous step. The console option displays the output in the command line.

After you see the above screen close the command line and start the MySQL service or process. Try connecting using the updated root password through the command line from inside the bin folder.

mysql.exe -u root -p

You’ll be prompted for the new password enter it and login. To change the MySQL root password type these queries.

UPDATE mysql.user SET password=PASSWORD('secure_password') WHERE user='root';
FLUSH privileges;

Replace secure_password with a new very very secure password. Do the necessary changes in your scripts. After the completion of all the steps remove the .sql file containing queries to change the MySQL root password immediately.