MySQL Lost Password: Difference between revisions

From The Uniform Server Wiki
Jump to navigation Jump to search
No edit summary
m (Reverted edits by Upazixorys (Talk); changed back to last version by Ric)
 
Line 1: Line 1:
=[http://akekuqegify.co.cc UNDER COSTRUCTION, PLEASE SEE THIS POST IN RESERVE COPY]=
{{Uc nav mysql}}
{{Uc nav mysql}}
'''Lost Password'''
'''Lost Password'''
Line 6: Line 5:


On refreshing the page received this error message:
On refreshing the page received this error message:
  <nowiki>#</nowiki>1045 - Access denied for user 'root'@'localhost' (using password: YES)
  <nowiki>#</nowiki>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:
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'  
  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&lt;br&gt;Error connecting to mysql
  (using password: YES) in W:\home\admin\www\mqsetup.php on line 46<br>Error connecting to mysql


'''Not a real problem:'''
'''Not a real problem:'''
Line 43: Line 42:
The MySQL server requires restarting (from MySQL console) with skip grant table option set as follows:
The MySQL server requires restarting (from MySQL console) with skip grant table option set as follows:


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


Dialogue in the command prompt looks similar to this:
Dialogue in the command prompt looks similar to this:


&lt;div style=&quot;background:#f0f0f0; border:1px solid #000000; padding-left:10px; width:60%&quot;&gt;
<div style="background:#f0f0f0; border:1px solid #000000; padding-left:10px; width:60%">
Microsoft Windows XP [Version 5.1.2600]&lt;br&gt;
Microsoft Windows XP [Version 5.1.2600]<br>
(C) Copyright 1985-2001 Microsoft Corp.
(C) Copyright 1985-2001 Microsoft Corp.


C:\Documents and Settings\mpg&gt;'''w:'''&lt;br&gt;
C:\Documents and Settings\mpg>'''w:'''<br>
W:\&gt;'''cd usr\local\mysql\bin'''&lt;br&gt;
W:\>'''cd usr\local\mysql\bin'''<br>
W:\usr\local\mysql\bin&gt;'''mysqld-opt --skip-grant-tables --user=root'''&lt;br&gt;
W:\usr\local\mysql\bin>'''mysqld-opt --skip-grant-tables --user=root'''<br>
'''-'''
'''-'''
&lt;/div&gt;
</div>


=== Set Password and Update grant tables ===
=== Set Password and Update grant tables ===
Line 63: Line 62:
Start a second command prompt set new password (root) and update grand tables as follows:
Start a second command prompt set new password (root) and update grand tables as follows:


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


Dialogue in the command prompt looks similar to this:  
Dialogue in the command prompt looks similar to this:  


&lt;div style=&quot;background:#f0f0f0; border:1px solid #000000; padding-left:10px; width:60%&quot;&gt;
<div style="background:#f0f0f0; border:1px solid #000000; padding-left:10px; width:60%">
Microsoft Windows XP [Version 5.1.2600]
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
(C) Copyright 1985-2001 Microsoft Corp.


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


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


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


mysql'''&gt; flush privileges;'''&lt;br&gt;
mysql'''> flush privileges;'''<br>
Query OK, 0 rows affected (0.16 sec)
Query OK, 0 rows affected (0.16 sec)


mysql&gt; '''exit'''&lt;br&gt;
mysql> '''exit'''<br>
Bye
Bye


W:\usr\local\mysql\bin&gt;
W:\usr\local\mysql\bin>
&lt;/div&gt;
</div>


=== Kill process and restart ===
=== Kill process and restart ===
Line 104: Line 103:
# Stop the servers using '''Stop.bat''' close your browser.
# Stop the servers using '''Stop.bat''' close your browser.
# Start the servers using '''Server_Start.bat'''
# Start the servers using '''Server_Start.bat'''
# From apanel run phpMyAdmin.&lt;br&gt;Under Uniform Server check '''Server version: 5.0.41-community-nt'''&lt;br&gt;Confirms the update was '''successful.'''
# From apanel run phpMyAdmin.<br>Under Uniform Server check '''Server version: 5.0.41-community-nt'''<br>Confirms the update was '''successful.'''


=== Conclusion ===
=== Conclusion ===

Latest revision as of 08:36, 24 November 2010

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