15

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 */
Eamon Bohan
  • 522
  • 2
  • 8
  • 22

2 Answers2

49

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.

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
  • 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! – Eamon Bohan Mar 16 '15 at 19:14
  • 1
    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. – RiggsFolly Mar 16 '15 at 22:36
  • That did the trick! Thanks so much. You are a gentleman and a scholar. I can finally log into MySQL and access PHPMyAdmin. – Eamon Bohan Mar 16 '15 at 22:57
  • 3
    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 – arunram Jan 10 '17 at 09:41
  • @arunram Thanks for the reminder. I have amended the answer. In MYSQL 5.7 the column name was changed – RiggsFolly Jan 10 '17 at 09:53
  • 1
    @RiggsFolly You've changed the table name instead of the column name, no? – Pieter De Bie Jan 27 '17 at 08:46
  • 1
    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 ...; – Pieter De Bie Jan 27 '17 at 09:12
  • @PieterDeBie Woops You are completely correct. My mistake. I have corrected the error. Silly me. Thanks for reporting that – RiggsFolly Jan 27 '17 at 09:19
  • No problem, I thought I was going nuts :D – Pieter De Bie Jan 27 '17 at 09:21
  • So I have no "wampmanager" I just have "wamp" and it has no mysql under that drop down. – The Digital Ninja Jun 06 '17 at 02:19
  • @TheDigitalNinja Sorry, dont understand. Are you running WAMPServer or something else? – RiggsFolly Jun 06 '17 at 07:59
  • 4
    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. – Rewind Sep 23 '17 at 21:45
  • This is what worked for me: # UPDATE mysql.user SET authentication_string='putYourPasswordHere' WHERE User='root'; – ikmazameti Nov 12 '21 at 16:54
  • @Mikaware So you use a plain text password or the HASHED password from somewhere that you know the original text password for?? – RiggsFolly Nov 12 '21 at 18:23
  • This was tremendously helpful and had been haunting me for a while. Only thing I had to do after this was update the password for phpmyadmin in the config.inc.php file and restart the service. – Mike Sep 14 '22 at 05:31
  • Hi @Mike, you shoudl not add the password to the config.inc.php, you shoudl instead leave it as it was set and use the login dialog. That way if your system does get compromised, you have not written the root password in a place everyone knows to look for it :) – RiggsFolly Sep 14 '22 at 07:34
1

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.

Mario Orozco
  • 321
  • 2
  • 5
  • 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. – Joel Wiklund Nov 24 '21 at 03:50