MySQL Lost Password

MySQL: General information.

Lost Password

Recently I had a friend, who lost his MySQL password. What he had done was to use phpMyAdmin and changed the root user password.

On refreshing the page received this error message:

#1045 - Access denied for user 'root'@'localhost' (using password: YES)

He then went into apanel used MySQL Server Configuration to set this new password and received the following error message:

Warning: mysql_connect() [function.mysql-connect]: Access denied for user 'root'@'localhost' 
(using password: YES) in W:\home\admin\www\mqsetup.php on line 46
Error connecting to mysql

Not a real problem:

• Close servers
• Open file mysql_password located in folder *\Uniform Server\udrive\home\admin\www
• Delete the old password and enter your new password.
• Start the servers. From apanel select MySQL Server Configuration the new password will be picked up if not enter your new password
• Finally click the change button and all should be OK.

Note: The same password must be used in each of the above steps.

Unfortunately the above failed because he could not remember the password he set using phpMyAdmin.

One way to recover from this situation is to reset Uniform Server to a know state. The remainder of this page provides a step-by-step guide on how to set the root user password either back to root (default) or to a new password.

phpMyAdmin - Set root password

To access the MySQL server phpMyAmin uses the root password this it picks up from a file. Before doing anything else edit the file to contain the new password (root) as follows.

1. Stop the servers
2. Open the file mysql_password in a text editor
3. File is located in folder: *\Uniform Server\udrive\home\admin\www
4. Delete the contents and enter new password: root (do not press enter otherwise you add a line feed)
5. Save file

MySQL - Set root password

The MySQL server requires restarting with skip grant table option set, this bypasses any security allowing access to the server. A new password is set and grant tables updated. The following method uses two command windows and task manger before following each step start the servers using Server_Start.bat I will assume the server is running on drive W.

Kill process

Open windows task manager by pressing ctrl, alt and del keys together. In the window that opens click on the Process tab (default) scroll up or down and locate the file mysqld-opt.exe right click on this and select End Process click Yes to confirm kill process.

Restart MySQL server

The MySQL server requires restarting (from MySQL console) with skip grant table option set as follows:

1. Start a command prompt: (Start > click on Run > type cmd click OK)
2. Type following lines into the command prompt:
   W:
   cd usr\local\mysql\bin
   mysqld-opt --skip-grant-tables --user=root Note: Do not copy and paste you must type it in.
3. Note the flashing cursor indicating server is running. Minimise this window (do not close it).

Dialogue in the command prompt looks similar to this:

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\mpg>w:
W:\>cd usr\local\mysql\bin
W:\usr\local\mysql\bin>mysqld-opt --skip-grant-tables --user=root
-

Set Password and Update grant tables

Start a second command prompt set new password (root) and update grand tables as follows:

1. Start a command prompt: (Start > click on Run > type cmd click OK)
2. Type following lines into the command prompt:
   W:
   cd usr\local\mysql\bin
   mysql
   USE mysql;
   UPDATE user SET password=password("root") WHERE user="root";
   flush privileges;
   exit
   
3. That's it finished.

Dialogue in the command prompt looks similar to this:

Microsoft Windows XP [Version 5.1.2600] (C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\mpg>w:
W:\>cd usr\local\mysql\bin
W:\usr\local\mysql\bin>mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.41-community-nt MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> USE mysql;
Database changed
mysql> UPDATE user SET password=password("root") WHERE user="root";
Query OK, 1 row affected (0.11 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.16 sec)

mysql> exit
Bye

W:\usr\local\mysql\bin>

Kill process and restart

Final steps:

1. Kill the process mysqld-opt.exe see section above, close command prompt windows.
2. Stop the servers using Stop.bat close your browser.
3. Start the servers using Server_Start.bat
4. From apanel run phpMyAdmin.
   Under Uniform Server check Server version: 5.0.41-community-nt
   Confirms the update was successful.

Conclusion

Resetting the server to a known condition requires a large number of steps if you do make a mistake repeat all steps from the beginning.

Command-line tools are very useful if you do find you are locked out from phpMyAdmin remember these tools are there to be used.

Top

---

Ric