为phpMyAdmin修复用户'root'@'localhost'的访问被拒绝

Question

I'm using WAMP Server 2.2 on a PC. In phpMyAdmin (version 5.5.24) I edited the 'root' user (with 'localhost' host) and gave it a password of 'root'. This turned out to be a big mistake that I'm trying to revert. Now when I go to localhost/phpmyadmin/ I get the menu of databases on the left, but the main frame has an error that reads:

#1045 - Access denied for user 'root'@'localhost' (using password: NO)
phpMyAdmin tried to connect to the MySQL server, and the server rejected the 
connection. You should check the host, username and password in your 
configuration and make sure that they correspond to the information given 
by the administrator of the MySQL server.

If I go to 127.0.0.1/phpmyadmin, I do not get the error and everything works fine.

I tried changing the user's password back to no password; I tried modifying config.inc.php file to add the new password (but that made phpMyAdmin error-out completely); I tried removing and recreating the root/localhost user. Nothing seems to work. The root/localhost user appears to have no password and all privileges, but the error remains.

Any ideas or how I can get this user's access back to normal without having to reinstall WAMP?

Answer 1

@Dagon had the right idea but was a little short on details.

If you use the access 127.0.0.1/phpmyadmin and have a close look at the Users that are setup in MysQL you should see 3 versions of the root username.

This is because in MySQL each userid is associated with a HOST, i.e. The PC ( ip address ) that the user is allowed to sign in from. So your 3 `'root' userids are in fact the same userid which is allowed to signin from 3 seperate HOSTS. Normally out of the box the 'root' has 3 HOSTS setup, 127.0.0.1, localhost and ::1

These are :-

localhost is an alias for THIS MACHINE created from your HOSTS file ( c:\windows\system32\drivers\etc\hosts )

127.0.0.1 is the IPv4 loopback ip address of THIS MACHINE

::1 is the IPv6 loopback ip address of THIS MACHINE

What is probably happening to you is that you have set a password for root@127.0.0.1 but not any of the others. Now your browser will 'arbtrarily' decide to use ::1 or 127.0.0.1 as the host ip so when it uses ::1 your login will fail.

So login using 127.0.0.1/phpmyadmin and change all 3 'root' usernames to have the same password you should no longer see this problem.

Also make sure that your HOSTS file has these 2 entries

127.0.0.1  localhost
::1        localhost