4

I have recently reinstalled WAMP on my PC and copied over files from a back-up I had. I am able to access localhost without any problem and my existing sites function fine.

The problem is that I cannot seem to log-in via http://localhost/phpmyadmin/index.php. I receive a #1045 Cannot log in to the MySQL server response.

After doing some reading I have been lead to believe that I can edit phpmyadmin's config.inc.php file to adjust the settings. After setting my files (as outlined below), I just get a Cannot connect: invalid settings. error.

   <?php
/*
 * This is needed for cookie based authentication to encrypt password in
 * cookie
 */
$cfg['blowfish_secret'] = 'xampp'; /* YOU SHOULD CHANGE THIS FOR A MORE SECURE COOKIE AUTH! */

/*
 * Servers configuration
 */
$i = 0;

/*
 * First server
 */
$i++;

/* Authentication type and info */
$cfg['Servers'][$i]['auth_type'] = 'config';
$cfg['Servers'][$i]['user'] = 'root';
$cfg['Servers'][$i]['password'] = '';
$cfg['Servers'][$i]['AllowNoPasswordRoot'] = true;

/* User for advanced features */
$cfg['Servers'][$i]['controluser'] = 'pma';
$cfg['Servers'][$i]['controlpass'] = '';

/* Advanced phpMyAdmin features */
$cfg['Servers'][$i]['pmadb'] = 'phpmyadmin';
$cfg['Servers'][$i]['bookmarktable'] = 'pma_bookmark';
$cfg['Servers'][$i]['relation'] = 'pma_relation';
$cfg['Servers'][$i]['table_info'] = 'pma_table_info';
$cfg['Servers'][$i]['table_coords'] = 'pma_table_coords';
$cfg['Servers'][$i]['pdf_pages'] = 'pma_pdf_pages';
$cfg['Servers'][$i]['column_info'] = 'pma_column_info';
$cfg['Servers'][$i]['history'] = 'pma_history';
$cfg['Servers'][$i]['designer_coords'] = 'pma_designer_coords';

/*
 * End of servers configuration
 */

?>

Can someone point out what I can do to resolve the problem?

I am running WAMP 2 using PHP 5.4.12 and mySQL 5.6.12. I've also attempted to log into the mySQL Console in WAMP but I cannot get past the password request...

Community
  • 1
  • 1
Sheixt
  • 2,546
  • 12
  • 36
  • 65
  • WHat exactly did you copy from you backup to you rnew install? Was the backed up version of wamp ( i.e. apache and mysql) the same as the version you installed and them copy over? – RiggsFolly Mar 31 '14 at 08:44

3 Answers3

19

If the problem is just a forgotten password this will allow you to reset it. However if you have mixed incompatible databases with MySQL Server versions there will be other problems later once you have reset the password.

Stop the mysql service

wampmanager -> MySQL -> Service -> Stop Service

Edit the my.ini file

wampmanager -> MySQL -> my.ini

Find the [wampmysqld] section in the ini file. Add this line directly after the section [wampmysqld]

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.

For MySQL versions prior 5.7.0

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

For MySQL versions after 5.7.0

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

Note that the update should report that it has updated more than one row, that is because there are actually 3 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] 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

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
  • I actually managed to fumble my way through a version of these steps last night & got it working. Thanks for a fantastically details answer however! – Sheixt Apr 02 '14 at 11:45
  • This answer saved my bacon. Tried everything else suggested in dozens of S.O. posts, and this is the only thing that worked. Cheers! – Vince Sep 25 '15 at 16:59
  • Only this helped my accross many answers.. also, if user doesnt exist, use `use mysql; insert into user (Host, User, authentication_string, ssl_cipher, x509_issuer, x509_subject) values ('localhost','root',password('your_password'), 'your_random_string', 'your_random_string', 'your_random_string');` – T.Todua Apr 28 '18 at 09:03
3

I was facing the problem. Unfortunetly my user name root and password = '' was not working. I use following solution and its works for me. You can try your luck..

1) Click on wamp icon (Near the time, Bottom right on your laptop)

2) Go to "MySQL" and click on "MySQL Console".

enter image description here

Once terminal opens, enter these three commands. 

mysql> SET PASSWORD for 'root'@'localhost' = '';
mysql> SET PASSWORD for 'root'@'127.0.0.1' = '';
mysql> SET PASSWORD for 'root'@'::1' = '';

NOTE: You don't need to write mysql> .. It will already there. :)

Now restart your wamp. go to localhost/phpmyadmin , enter username "root" and leave password empty ..

If answer works for you, Don't forget to vote it.

BEST OF LUCK.

Adnan Ahmad
  • 848
  • 1
  • 13
  • 12
2

For above MySQL 5.7 

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

has to be changed to - 

update user set authentication_string=password('MyNewPass') where user='root';
santosh hegde
  • 21
  • 1