Added password to root, locked out of phpmyadmin

问题:

Using phpmyadmin, I changed the password for user root (both the localhost entry and the 127.0.0.1 entry) from blank to "password". I was then locked out of phpmyadmin, getting the 1130 error: "Host ‘localhost’ is not allowed to connect to this MySQL server"

I did a bunch of searching on stackoverflow and found a solution that gets me back into phpmyadmin:

I simply add the line "skip-grant-tables" to the [mysqld] section of "my.ini".

But that seems like a bad solution, like I am just circumventing the problem rather than fixing it. I also tried another solution in which I add my username and password to "config.inc.php" in this section:

$cfg['Servers'][$i]['user'] = 'root';
$cfg['Servers'][$i]['password'] = 'mypass';

but that has no effect. So my questions are:

1. Why would adding a password to root lock me out of phpmyadmin in the first place? Shouldn't it just bring me to a login screen?

2. What is the proper way to fix this?

Thanks Jonah

回答1:

1. depending on the configuration you chose, it can lock you out completely.
2. delete the configuration files and reupload the config wizard and create a new configuration.

回答2:

config.inc.php in the root of your phpma install contains all the configurations.

For the auth type, you could probably get away with cookies on a public server, but not if there's a risk of the computer being stolen or accessed by a potentially harmful person. On my localhost, I just use config for the auth type and place my login credentials in there so I can skip logins entirely. Not ideal for internet use.

Good luck, and merry Christmas! :D

-Tom

回答3:

I tried several of the suggested changes to get back into phpMyAdmin, and they did not work. However, while going through the config file, I came across this statement:

$cfg['Servers'][$i]['user'] = 'root'; 
$cfg['Servers'][$i]['password'] = '';

Simply insert the password for the root user between the single quotes, then save the file. You should be able to get back in.

回答4:

The line in the config file will look like the following and it should solve the problem.

//$cfg['Servers'][$i]['auth_type'] = 'config';

All I did was putting two forward slashes in the beginning of the line. This restricts the line from being compiled and work.

回答5:

unfortunately, when you comment out the code below as suggested, //$cfg['Servers'][$i]['auth_type'] = 'config'

the result is that the authentication is automatically disabled and it is a security risk. I prefer uninstalling and re-installation.

回答6:

I had the same problem, after getting the security warnings in PMA I decided to change the password for the root account(s).

I noticed the root is displayed 3 times once with the IPV4 loop-back address (127.0.0.1), once with the IPV6 loop-back address (:::1) and once with the localhost address...

So I started changing passwords on all three, but after the third I got locked out.

Password setting in my.ini (through xampp) didn't do it, neither did setting the pwd in config.inc.php entry $cfg['Servers'][$i]['password'] (though it is part of the solution).

And then I changed the $cfg['Servers'][$i]['host'] setting from 127.0.0.1 to localhost and voilà! I got back in!

I don't have a clue why this worked, as localhost is supposed to get translated to 127.0.0.1, but I hope this also helps some others.