Wednesday, July 13, 2011

How to Reset root's Password of MySQL

We've got a problem accessing the MySQL database when someone changes the password of 'root' user and we cannot retrieve it. So, I decided to reset it's password.

This is my setup:
  1. Using Windows Server 2008
  2. MySQL Server 5.1 installed in the default directory, inside the Program Files folder.

If your installation directory is different, just make the necessary adjustments to the steps below.

Before doing the next step, create first the init file. This will reset the password of the user 'root'.
  1. Go to C:\ drive.
  2. Create the mysql-init.txt file and open it.
  3. Copy and paste the following to the file and save it.

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

Note: Replace MyNewPass with your password.

A. If MySQL is running as a Windows Service:

Stop the service:
  1. Log in as Administrator in the server.
  2. Go to Control Panel > Administrative Tools > Services.
  3. Find the MySQL service and stop it.

Start the service:
  1. Go the Services window again and find MySQL service.
  2. Right click it and select Properties.
  3. Put the following in the Start parameters text box.
  4. Now, click the Start button. Click OK once the service has started.

B. If MySQL was executed as a process:

Stop mysqld.exe:
  1. Log in as Administrator in the server.
  2. Open the Task Manager.
  3. Go to Processes tab.
  4. Select mysqld.exe and click the End Process button.

Start the mysqld.exe:
  1. Open the C:\Windows\system32 folder.
  2. Find the cmd.exe.
  3. Right click it and select Run as administrator.
  4. Type the following on the command prompt:
    C:\>"C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld.exe"
    --defaults-file="C:\Program Files\MySQL\MySQL Server 5.1\my.ini"
  5. Now, click the Start button. Click OK once started.

If you got the error "InnoDB: Operating system error number 5 in a file operation"

you have to give Admin priviledge to mysqld.exe.
  1. Go to MySQL installation directory. In my case C:\Program Files\MySQL\MySQL Server 5.1\bin.
  2. Right click mysqld.exe and click Properties.
  3. Go to Compatibility tab and check out the Run this program as administrator. Click OK.
  4. Try again to start the mysqld.exe with the steps above.

MySQL should be running again. Try to connect using the username 'root' and your new password.

No comments:

Post a Comment