Link to home
Create AccountLog in
Avatar of Jonah11
Jonah11

asked on

Changed root password, got 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
Avatar of arnold
arnold
Flag of United States of America image

Make sure you saved a copy of the config.example.inc.php as config.inc.php and you configured it for cookies.
At this point the main page will be a form with username/password fields without which access will not be granted.
Avatar of Jonah11
Jonah11

ASKER

arnold,

thanks for your reply.  changing auth_type to "cookie" did bring me to a login screen.  however when i enter the uname root and password i still get error 1130 displayed on the login page.

my config.inc.php looks like this:

<?php

/* Servers configuration */
$i = 0;

/* Server: localhost [1] */
$i++;
$cfg['Servers'][$i]['verbose'] = 'localhost';
$cfg['Servers'][$i]['host'] = 'localhost';
$cfg['Servers'][$i]['port'] = '';
$cfg['Servers'][$i]['socket'] = '';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['extension'] = 'mysqli';
$cfg['Servers'][$i]['auth_type'] = 'cookie';
$cfg['Servers'][$i]['user'] = 'root';
$cfg['Servers'][$i]['password'] = '888888';
$cfg['Servers'][$i]['AllowNoPassword'] = true;

/* End of servers configuration */

$cfg['DefaultLang'] = 'en-utf-8';
$cfg['ServerDefault'] = 1;
$cfg['UploadDir'] = '';
$cfg['SaveDir'] = '';

?>
SOLUTION
Avatar of arnold
arnold
Flag of United States of America image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Avatar of Jonah11

ASKER

changing the connect_type to socket did not fix the problem.

the only thing I did to create the problem was change the password on the 2 versions of root (1 version listed for localhost and one for 127.0.0.1) from blank to a real password.  those 2 versions of root are the only admin accts, in fact the only entries at all in the users table.  what else should check?
Do you have access to the shell?
Did you flush privileges i.e. told mysql to reload such that the change will take effect?
Are you able to run mysql -u root -p and provide the password when prompted.
Are you able to gain access?
Did you set the same password for both?

The method to restore is to shutdown mysql. rename the /var/lib/mysql/mysql and then rerun mysql_install_db.
Then you should be able to login and you can query the renamed for settings you may have set in host,db.
IMHO, before making changes to the administrative accounts, make sure you always have another account with admin rights that you know works.
Avatar of Jonah11

ASKER

arnold, remember from the OP that I can gain access anytime I want my adding "skip-grant_tables" to my.ini.  so i can get into to phpmyadmin and change anything at all you want me to troubleshoot.

to answer your questions,

yes, it's my local laptop so i have shell access, if i run "mysql -u root -p" it prompts me for a password, but when i enter it i get the same error message that I do from phpmyadmin
yes, i have restarted mysql and do so anytime i make a change for troubleshooting.
yes, same password for both
You should then use the skip-grant and look at the mysql.host mysql.db and mysql.user tables to see what is there that causes the mismatch.
Instead of restarting which effectively locks unless you use the skip option, you can issue a flush privileges; when changes in mysql table are made.
Then using a separate window you can see whether you have access with the new inforamtion.
phpmyadmin I think issues the flush upon a change.

 
Avatar of Jonah11

ASKER

in the user table, everything is marked as "Y" for both entries, except

max_questions       max_updates       max_connections       max_user_connections

which are all 0.  Everything else matches except the host which is "localhost" for one and 127.0.0.1 for the other.  

the host table has no rows.

the db table has 2 rows that match for everything except the "Db" column, which is "test" in one and "test\_%" in the other.  in both rows the "host" colum is "%".  

does this give you any clues?
Add an administrative user for access from any host (%) and see whether you can log into phpmyadmin using this new user.
Then try to access the system as mysql -h <private_ip> -u <newusername> -p
You may have removed the rights for connection on localhost by anyone.
http://friendsofed.infopop.net/2/OpenTopic?a=tpc&s=989094322&f=8033053165&m=4581078141
Seems to be the remedy I suggested.
There is a change you made that may have locked localhost as a valid entry point into mysql.

I can not think of how, and I tried to see if I can make a change that will have the same result.

Avatar of Jonah11

ASKER

Now I don't get any error message when I try to login, but instead I just get returned to a blank login screen.  This is true whether I login as root or my new user, "test".  I did not try:

mysql -h <private_ip> -u <newusername> -p

since I didn't know what to put for <private_ip>

I didn't see anything in that link that I could try.

Thanks again.
<private_ip> is the IP your system has other than the 127.0.0.1 and 0.0.0.0.

Did you remove the skip grant option?
Avatar of Jonah11

ASKER

I am removing the skip-grant option for all the troubleshoting i am doing, since with that option i can always login.  

how would i determine my private ip?
netstat -rn will return the routing table which will list the IP your system has.
ifconfig (linux) ipconfig(windows)
will list the IPs and active network cards/interfaces.
Avatar of Jonah11

ASKER

ok i have tried connecting with a new user named test whose host entry is '%' as you described a few posts up, but it does not work either:

C:\wamp\bin\mysql\mysql5.1.36\bin>mysql -h 192.168.1.67 -u test -p
Enter password: **********
ERROR 1130 (HY000): Host 'toshiba.domain_not_set.invalid' is not allowed to connect to this MySQL server
While the skip option is on, what do you have in the columns_priv, tables_priv,procs_rpiv, userinfo.

I think you should reset the mysql database since a change you made somehow altered rights and is causing this issue.
ie. stop mysql, rename the mysql folder. run the mysql_install_db binary and start mysql or use the mysql configuration wizard to reconfigure it.
Did you make any changes to the c:\windows\system32\drivers\etc\hosts?

Avatar of Jonah11

ASKER

columns_priv, tables_priv, procs_priv are all empty.

userinfo -- i cant find that table

in hosts, only entry maps 127.0.0.1 to localhost

this was a wamp install btw.  i am reluctant to just reinstall, since i think this will just happen again.  remember the only thing i did when this started happening was to add passwords to my root users.... AND what's more, i just tested by removing the password, and now i can login just fine again even with skip-grant-tables removed.  so somehow the existence of the password seems to be causing the problem.....

Avatar of Jonah11

ASKER

as an update arnold, i think the problem may have been that i was setting the password field directly.  i just tried this:

update user set password = PASSWORD('mypass') where user='test'; flush privileges;

and now i can login using test....
ASKER CERTIFIED SOLUTION
Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
Avatar of Jonah11

ASKER

I was putting it in with no encryption.
The root account is superpowerful. The grant tables will give you the option of allowing access with and with out a password.... so for example, you could tell it to allow access to root at 127.0.0.1 with no password, and yet "overload" it to only allow access to root with a password from somewhere else (like 192.168.1.*

If you can get back into phpmyadmin, from the main page, click privileges and look at the lines for root:
specifically host and password.... If you set the password for root and it wasnt set before, and the priviledges table is saying "root,127.0.0.1,password=NO" then using ANY password would prevent you from logging in to that host -- since NO password != any password.


As additional procaution, I would recommend making an additional user with administrative rights, and then secure the root account -- don't use root except in an emergency!!!