MySQL - root access denied if not 127.0.0.1

[SwOsHiE]

Hello,

Got a serious problem with our MySQL service a couple of days ago. You cannot login as root unless you type in "-h 127.0.0.1". I've searched and searched on the web and looked up so many things but still not getting it to work.

The things I've tried:

• Checked that /etc/hosts points localhost to 127.0.0.1
• Mysqld is running on port 3306 and with IPv4
• The path to the mysql socket is correct both in my.cnf and debian.cnf
• Checked that "bind adress" is uncommented

Getting really frustrated as ISPConfig is not able to make database changes!

I appreciate all the help I can get!!

Best regards,
Mattias

[falko]

Is this a physical server or a virtual machine? Did you try a reboot?

[SwOsHiE]

Hi falko!

This is a physical machine and just tried to reboot the mirrored server but still same problem..

If I'm correct "root@127.0.0.1" and "root@localhost" have different privileges but this can be solved by NOT using an options that I can't remember right now, but that option was turned off as default so localhost is automatically shown as 127.0.0.1 (or is it the other way around?).

[falko]

Which distribution do you use? Did you install any updates recently? Did you do any other changes right before this problem occurred?

What's in the Host column of the root users in the mysql.user table?

[SwOsHiE]

I use Debian 6.

I did some updates a while ago but no problems at that time, just recently I got a message from one of my clients that they couldn't create new databases, but use and show already made databases.

I think this is what your after, but if you want more columns just tell me!

+---------------------+-------------+
| host | user |
+---------------------+-------------+
| 127.0.0.1 | root
| localhost | debian-sys-maint
| xx.ffffff.net | root
| 192.168.XXX.XXX | root
+---------------------+-------------+

Shouldn't there be some line about root@localhost?

Best regards,
Mat

[falko]

Originally Posted by SwOsHiE

Shouldn't there be some line about root@localhost?

Yes, that's right. Looks like that got lost somehow.

[SwOsHiE]

Phew.. just solved the problem, thanks falko!

What I did:

Login to MySQL:

mysql -u root -p -h 127.0.0.1

Create the root@localhost account:

use mysql;
create user root@localhost;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
commit;
FLUSH PRIVILEGES;
exit;

Restart the MySQL service to fix changes:

service mysql restart

Stop the service and start in safe mode:

service mysql stop
mysqld_safe --skip-grant-tables&

Login as root:

mysql -u root

Change password for root:

use mysql;
UPDATE user SET Password=PASSWORD('YourNewPassword') WHERE User='root';
FLUSH PRIVILEGES;
exit;

Stop the safe mode and restart the service:

service mysql stop
service mysql start

That worked for me Thanks for all the help!