HowtoForge Forums | HowtoForge - Linux Howtos and Tutorials

HowtoForge Forums | HowtoForge - Linux Howtos and Tutorials (http://www.howtoforge.com/forums/index.php)
-   Server Operation (http://www.howtoforge.com/forums/forumdisplay.php?f=5)
-   -   MySQL - root access denied if not 127.0.0.1 (http://www.howtoforge.com/forums/showthread.php?t=59289)

SwOsHiE 28th October 2012 22:31

MySQL - root access denied if not 127.0.0.1
 
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! :confused:

I appreciate all the help I can get!!

Best regards,
Mattias

falko 29th October 2012 23:05

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

SwOsHiE 30th October 2012 11:20

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 1st November 2012 08:08

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 1st November 2012 21:16

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 3rd November 2012 00:28

Quote:

Originally Posted by SwOsHiE (Post 287660)
Shouldn't there be some line about root@localhost?

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

SwOsHiE 3rd November 2012 11:06

Phew.. just solved the problem, thanks falko!

What I did:

Login to MySQL:
Code:

mysql -u root -p -h 127.0.0.1
Create the root@localhost account:
Code:

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:
Code:

service mysql restart
Stop the service and start in safe mode:
Code:

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

Login as root:
Code:

mysql -u root
Change password for root:
Code:

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

Stop the safe mode and restart the service:
Code:

service mysql stop
service mysql start

That worked for me :) Thanks for all the help!


All times are GMT +2. The time now is 23:40.

Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.