Regaining access to lost MySQL password for PHPMyAdmin on WAMP

by

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.

More Related Contents:

  1. #1045 – Access denied for user ‘root’@’localhost’ (using password: YES)
  2. where does MySQL store database files?
  3. If two columns are 1 then make new column
  4. MySQL: How to reset or change the MySQL root password?
  5. MySQL Server has gone away when importing large sql file
  6. How to display UTF-8 characters in phpMyAdmin?
  7. #1071 – Specified key was too long; max key length is 1000 bytes
  8. MySQL : isn’t in GROUP BY
  9. MySQL: Can’t create table (errno: 150)
  10. What is maximum query size for mysql?
  11. Why is the estimated rows count very different in phpmyadmin results?
  12. Find and Replace text in the entire table using a MySQL query
  13. How to access remote server with local phpMyAdmin client?
  14. Is there a simple way to convert MySQL data into Title Case?
  15. Bug? #1146 – Table ‘xxx.xxxxx’ doesn’t exist
  16. Creating functions in phpMyAdmin – Error: access denied you need the super privilege for this operation
  17. #1273 – Unknown collation: ‘utf8mb4_unicode_ci’ cPanel
  18. Error Dropping Database (Can’t rmdir ‘.test\’, errno: 17)
  19. phpMyAdmin is throwing a #2002 cannot log in to the mysql server phpmyadmin
  20. How to Import Multiple csv files into a MySQL Database
  21. Composite key as foreign key (sql)
  22. phpMyAdmin – can’t connect – invalid setings – ever since I added a root password – locked out
  23. how to delete duplicate rows from a table in mysql
  24. SQL: deleting tables with prefix
  25. #1292 – Incorrect date value: ‘0000-00-00’ [duplicate]
  26. #1130 – Host ‘localhost’ is not allowed to connect to this MySQL server
  27. How do I write an SP in phpMyAdmin (MySQL)?
  28. How to import large sql file in phpmyadmin
  29. Import CSV to Update rows in table
  30. mySQL query to search all tables within a database for a string?

Leave a Comment