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!