Posts Tagged ‘reset mysql root password’

How to Reset MySQL Root Password

August 18th, 2012 by Admin

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!