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 */
Improve this question
5
  • So you've done the trick where you store the credentials in text file and then call that through mysqld? Commented Mar 16, 2015 at 6:14
  • Did you follow this instruction B.5.4.1 How to Reset the Root Password? Commented Mar 16, 2015 at 6:14
  • Hi @Strawberry, I admit I don’t know of that trick you mention. Commented Mar 16, 2015 at 6:20
  • @t.niese, I will report back and edit the question details as I try all of the suggestions there. Thanks. Commented Mar 16, 2015 at 6:58
  • @Strawberry, I actually did try that, but I had no access granted. Commented Mar 16, 2015 at 21:04

2 Answers 2

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

16 Comments

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.
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 You've changed the table name instead of the column name, no?
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 ...;
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.
|
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.

Improve this answer

1 Comment

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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.