MySQL - root access denied if not 127.0.0.1

Discussion in 'Server Operation' started by SwOsHiE, Oct 28, 2012.

  1. SwOsHiE

    SwOsHiE New Member

    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
     
  2. falko

    falko Super Moderator ISPConfig Developer

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

    SwOsHiE New Member

    Hi falko!

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

    If I'm correct "[email protected]" 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?).
     
  4. falko

    falko Super Moderator ISPConfig Developer

    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?
     
  5. SwOsHiE

    SwOsHiE New Member

    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
     
  6. falko

    falko Super Moderator ISPConfig Developer

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

    SwOsHiE New Member

    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!
     

Share This Page