How to Reset MySQL Root Password

August 18th, 2012 by Admin Leave a reply »

Forgot your MySQL root user password? Don’t worry. We are here for rescue.

When you tried to login to root without entering a password, you may get ‘Access Denied’ message, as MySQL is expecting a password. This article explains how to reset MySQL root password when you don’t remember your old one.

How to Reset MySQL Root Password:

Step 1: Stop the MySQL service by going to Control Panel, Administrative Tools and Services. Right-click on the service and choose Stop.

Step 2: Create a new text file and copy and paste the following lines into it:

UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
FLUSH PRIVILEGES;

Make sure to change the password “MyNewPass” to whatever password you want to replace the current one with. Now save the file and give it a name like C:\mysql-init.txt or whatever you like.

Step 3: Now go to the command prompt and type in the following command:

C:\> C:\mysql\bin\mysqld-nt --init-file=C:\mysql-init.txt

In my case, I had to change the path to something different than C:\mysql\bin. If you installed MySQL using the installation wizard, which I did, you have to use a different command:

C:\> "C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt.exe"
         --defaults-file="C:\Program Files\MySQL\MySQL Server 5.0\my.ini"
         --init-file=C:\mysql-init.txt

So what is the path for your defaults-file? You can get the exact value by going to the Services dialog again and right-clicking on MySQL and choosing Properties. The box that says “Path to executable” has the value for defaults-file.

At this step, I ran into an error. Every time I would try to execute the command, I would end up getting an error message and the password would not be reset.

InnoDB: Operating system error number 32 in a file operation.
InnoDB: The error means that another program is using InnoDB’s files.
InnoDB: This might be a backup or antivirus software or another instance
InnoDB: of MySQL. Please close it to get rid of this error.

In my case, I have to go to the Task Manager and click on the Processes tab. Here I found that I already had several instances of MySQL running! I don’t know why or how they were started, but even with the service stopped, there were processes running.

I killed off all the processes and ran the command again, which worked perfectly.

Step 4: Go back to the Services window and restart the MySQL service. You should now be able to log into the MySQL database! Enjoy!