locked myself out of mysql

Submitted by responsiveny on Wed, 09/14/2011 - 23:15

ok, so everything was going great, and i'm loving virtualmin and the support...

anyway, i've done something stupid. I was trying to connect to mysql from external (outside firewall) and nothing I would do would allow me to connect to any of the mysql databases, including root level. I am not a mysql newbie but I used webmin to edit the local host to allow all, but now I seem to have locked myself out of all mysql access thru virtualmin with the error:

DBI connect failed : Access denied for user 'root'@'localhost' (using password: YES)

I even tried stopping the mysql service and restarting with --skip-grant-tables

How do I get mysql back to allowing root user access via localhost if I removed root access via localhost?

Thanks

Status: 
Closed (fixed)

Comments

Submitted by andreychek on Thu, 09/15/2011 - 09:28

If you start the MySQL service with the --skip-grant-tables option, are you then able to access MySQL from the command line?

For example, can you run "mysql -u root", and gain access to MySQL that way?

Submitted by responsiveny on Thu, 09/15/2011 - 10:40

I was able to gain access via command line but I can't quite figure out how to grant root access to connect via localhost again, which totally cripples virtualmin. You cannot run grant commands with the ==skip-grant-tables option.

Submitted by andreychek on Thu, 09/15/2011 - 11:26

That's okay, we'll be getting this straightened up from the command line :-)

First, run this command from the command line to log into Virtualmin and access the MySQL table:

mysql -u root mysql

Then, run this SQL command:

select Host from user where user = 'root';

What output does that produce?

Submitted by responsiveny on Thu, 09/15/2011 - 12:58

Thanks for the assistance. Before I got your most recent reply I already reinstalled since it was a brand new system anyway and quicker to wipe clean and reinstall then troubleshoot further. there was no data on the system anyway.

In any case, i did try to insert 'localhost' back into the user table of mysql because it was missing but that didnt work. i was trying to restore root's ability to login from localhost.

Submitted by andreychek on Thu, 09/15/2011 - 13:07

Okay, I'll mark this as fixed then.

If you have any other questions, feel free to let us know!

Submitted by Issues on Thu, 09/29/2011 - 14:22

Automatically closed -- issue fixed for 2 weeks with no activity.