Access denied for root

[franklin97355]

How do I regain access to databases that log you on as root? I'm trying to manage several applications that were working but now I'm told I don't have access. Access denied for user: '@localhost'

 

[sleipnir214]

MySQL user credentials are not just a 2-tuple of username and password. They are a 3-tuple of username, password, and address from which the user is trying to connect.

Were you previously accessing MySQL from localhost?

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[franklin97355]

I've connected to this from localhost, 127.0.0.1 and Moya (the servername of the box) the apps that are denying me access are webmin and a PHP forum site on my website. I haven't changed anything there. I have tried upgrading MySQL since this happened but I'm so confused now I've probably done more damage than good. Where can I go to see the users and how MySQL thinks they should log on?

 

[sleipnir214]

It's all stored in the mysql database. But, of course, you have to log on before you can access it.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[rzs0502]

Access denied for user: '@localhost' indicates that it is login on with a username.
On the server, use `mysql -h localhost -u root -p` <and supply root's password.>

The check the mysql.user table to check that your username has access to the database from `%` any host (or the hostname of the remote web server)

 

[franklin97355]

OK I can get to the prompt of mysql but if I try to check the mysql database I get access denied. What next?

 

[sleipnir214]

Does the user you've connected as have access to the mysql database?

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[rzs0502]

Are you logging on as root?
Only root and admin users should have access to the mysql table as all your database privileges are stored in there.

 

[franklin97355]

I am logged on to the system as root, I log on to mysql with &quot;mysql -h 127.0.0.1 -u root&quot; I then get the mysql> prompt.

 

[rzs0502]

If this is on Linux/UNIX, stop the mysqld process

Then start it using 'safe_mysqld --skip-grant-table &'

then go into mysql (no user name or password)

You can then reset the permissions on your database

then use mysqladmin flush-privileges to activate permissions

You can restart the database and test...

 

[franklin97355]

I'll try that tonight. I'm new to this so thanks for the help and the patience.

 

[franklin97355]

After following rzs0502s suggestion I did the following:

mysql> delete from user where user = '';
Query OK, 2 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

This seems to have fixed the problem and I hope to never see this one again.

Thanks all for the help.

 

[rzs0502]

That means you have no access control on your database...
You should re-create the root user will full access firstly, and then any other users you require for your applications.

If you are not certain on how to create them, use the MySQL Control Centre to create your users.