Regaining access to lost MySQL password for PHPMyAdmin on WAMP

Question

I changed the password for my 'root'@'localhost' account in PHPMyAdmin and like (this person asking here) and locked myself out of PHPMyAdmin on my browser. I am using WAMP 2.5.

The solutions offered on that question don’t work for me because unfortunately I have lost the password that I reassigned root to.

Some solutions ask one to reset the password via command line (including this source), and I have tried that through the Windows command line without success because I now have no privileges:

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('mynewpassword');

ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'mysql'

WAMP comes with a MySQL Console but I am unable to use it since it prompts me for a password which as I stated, no longer have.

This video explains how to change passwords, and it agrees with the answers in the StackExchange answer above, but evidently, I need another method to do this now since I did not know I would lose access as the guy warns.

I would appreciate any advice and apologise if this is a newbie type of thing to ask. I am just getting the hang of using a stack for web development, and configuring access to a LAN site through WAMP was quite an adventure, so if possible, I would prefer something that does not require reinstalling everything again.

Edit 1: Went ahead and tried @RiggsFolly ’s solution. While I can access the my.ini file, make changes without problem, and restart MySQL without issue through WAMP or otherwise, the line skip-grant-tables is not taking effect, leaving me with a password prompt or no access to do anything. (I wrote a new question here to troubleshoot this since I feel like this is a different issue.)

Edit 2: I tried the instructions in the link provided by @t.niese in the comments and when I set the console to return feedback on the instructions, it reported this, and failed to change anything:

C:\wamp\bin\mysql\mysql5.6.17\bin>mysqld --skip-grant-tables --console
[Note] Plugin 'FEDERATED' is disabled.
[Note] InnoDB: Using atomics to ref count buffer pool pages
[Note] InnoDB: The InnoDB memory heap is disabled
[Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
[Note] InnoDB: Compressed tables use zlib 1.2.3
[Note] InnoDB: Not using CPU crc32 instructions
[Note] InnoDB: Initializing buffer pool, size = 128.0M
[Note] InnoDB: Completed initialization of buffer pool
[ERROR] InnoDB: .\ibdatai can't be opened in read-write mode
[ERROR] InnoDB: The system tablespace must be writabe!
[ERROR] Plugin 'InnoDB' init function returned error.
[ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
[ERROR] Unknown/unsupported storage engine: InnoDB
[ERROR] Aborting
[Note] /* List of plugins shutting down */

Solution 1

Here is a method that will allow you to reset the MYSQL 'root' password quite simply.

Stop the mysql service

wampmanager -> MySQL -> Service -> Stop Service

Edit the my.ini file

wampmanager -> MySQL -> my.ini

Find the [wampmysqld](32bit) or [wampmysqld64](64bit) section in the ini file

Add this line directly after that section heading

skip-grant-tables

Restart the mysql service.

wampmanager -> MySQL -> Service -> Start/Resume Service

Open the MySQL console

wampmanager -> MySQL -> MySQL Console

Now we are going to reset the password for the root user, of course this could be used to reset any users password.

Enter the following 2 commands at the mysql> command prompt, each with a semi colon at the end of a line, and press ENTER after each line to issue the command to mysql.

Pre MYSQL version 5.7

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

Post MYSQL version 5.7 the column name changed

UPDATE mysql.user SET authentication_string=PASSWORD('MyNewPass') WHERE User='root';
FLUSH PRIVILEGES;

Note that the update may report that it has updated more than one row, that because there may be more than one user accounts with the userid of 'root' each with a different domain i.e. 127.0.0.1, localhost and ::1

Now enter 'quit' at the mysql command promt to exist mysql.

Stop the mysql service

wampmanager -> MySQL -> Service -> Stop Service

Edit the my.ini file

wampmanager -> MySQL -> my.ini

Find the [wampmysqld](32bit) or [wampmysqld64](64bit) section in the ini file

Remove the skip-grant-tables parameter we added earlier.

DO NOT Leave this parameter in the ini file its a HUGH security hole.

Restart the mysql service.

wampmanager -> MySQL -> Service -> Start/Resume Service

Now if you want you can set the root user account so it does not expire the password automatically

wampmanager -> MySQL -> MySQL Console

and then run this query

ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;

You should now be able to login with phpmyadmin using the userid 'root' and the new password you have just set for that user.

Solution 2

Previous answers didn't work for me, since MySQL Console keep me out after pressing intro. Try this:

Create a file C:\wamp64\mysql-init.txt with this content:

UPDATE mysql.user SET Password=PASSWORD('1234') WHERE User='root';
FLUSH PRIVILEGES;

This will reset password to 1234 for user root. Then, run the next command on Windows Command Line:

C:\wamp64\bin\mysql\mysql5.7.21\bin\mysqld.exe wampmysqld --init-file=C:\\wamp64\\mysql-init.txt

Change paths depending on where is installed WAMP on your file system.

Finally, restart WAMP services.

Comments

[Eamon Bohan]

Hi @RiggsFolly, I appreciate the answer very much. Currently I am trying to figure out why the change I made to the my.ini file as you suggested is not affecting the way the console runs on reload (it still asks for a password). I'll report back, and if you have any ideas I'd be more than glad to try them!

[RiggsFolly]

There was a little bug in WAMPServer 2.5 64bit. Maybe this is your problems. If you installed WAMPServer 2.5 64bit, check that my.ini has the section header [wampmysqld64] and not `[wampmysqld]' Once that is corrected MySQL will respond to changes in that section.

[Eamon Bohan]

That did the trick! Thanks so much. You are a gentleman and a scholar. I can finally log into MySQL and access PHPMyAdmin.

[arunram]

This solved my problem. Only change is the column name in mysql.user table was authentication_string instead of password. Otherwise it worked like a charm! thanks

[RiggsFolly]

@arunram Thanks for the reminder. I have amended the answer. In MYSQL 5.7 the column name was changed

[Pieter De Bie]

@RiggsFolly You've changed the table name instead of the column name, no?

[Pieter De Bie]

Weird, it works in my case when I leave mysql.user alone and change password=... to authentication_string=... SQL update syntax is UPDATE table_name SET columns,... WHERE ...;

[RiggsFolly]

@PieterDeBie Woops You are completely correct. My mistake. I have corrected the error. Silly me. Thanks for reporting that

[Pieter De Bie]

No problem, I thought I was going nuts :D

[The Digital Ninja]

So I have no "wampmanager" I just have "wamp" and it has no mysql under that drop down.

[RiggsFolly]

@TheDigitalNinja Sorry, dont understand. Are you running WAMPServer or something else?

[Rewind]

I tried this but it does not work for me. I get to the line ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER; when it said it could not do that with skip-grant-tables set. So I change the skip-grant-tables setting, rego back to MySQL console and try to finish off, but it tells me I need to have just set my password.

[ikmazameti]

This is what worked for me: # UPDATE mysql.user SET authentication_string='putYourPasswordHere' WHERE User='root';

[RiggsFolly]

@Mikaware So you use a plain text password or the HASHED password from somewhere that you know the original text password for??

[Joel Wiklund]

When clicking WAMPServer icon > MySQL > MySQL console and you're faced with "Enter a valid username", enter root, then hit OK. Then when faced with "Enter password:" in the MySQL console, hit CTRL+C and you enter the MySQL monitor and can follow @RiggsFolly post.