How to reset a MySQL database root password

Recently, I had to take over a website that was previously being handled by third-party consultants. The site was completely data-driven and after I installed phpMyAdmin to manage the MySQL database, I realized I could not gain access because I did not know the root password.

At first, I thought there could possibly be no way to get the password from the database because it’s hashed before being stored. Luckily, though, I came across a useful article from my official MySQL site on how to reset the root password.

The article pretty much explains what you have to do in order to reset the root password, but I will explain it in a bit more detail in this post. Also, I got an error along the way that is not mentioned in their article.

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 use phpMyAdmin to log into the MySQL database! If you have any issues, feel free to post a comment and I will help! Enjoy!