[SOLVED] cannot log in to mysql without allowing remote access

Discussion in 'Installation/Configuration' started by Ovidiu, Apr 27, 2016.

  1. Ovidiu

    Ovidiu Active Member

    Not sure what happened but I noticed I could only log into phpmyadmin with root not with any other DB user.
    Tried the same on the command line with the same results.
    If i edit a DB in ISPCFG3 and allow remote access phpmyadmin works again as does the command line access.

    on my console I see mysql running like this:
    Code:
     netstat -tap | grep mysql
    tcp        0      0 localhost.localdo:50806 localhost.localdo:mysql ESTABLISHED 3329/amavisd-new (c
    tcp        0      0 localhost.localdo:33658 localhost.localdo:mysql ESTABLISHED 10475/amavisd-new (
    tcp       83      0 localhost.localdo:38832 localhost.localdo:mysql CLOSE_WAIT  26061/amavisd-new (
    tcp        1      0 localhost.localdo:48738 localhost.localdo:mysql CLOSE_WAIT  7973/amavisd-new (c
    tcp6       0      0 [::]:mysql              [::]:*                  LISTEN      18853/mysqld
    tcp6       0      0 localhost.localdo:mysql localhost.localdo:33658 ESTABLISHED 18853/mysqld
    tcp6       0      0 localhost.localdo:mysql localhost.localdo:50806 ESTABLISHED 18853/mysqld
    
    does this look right to you?
    This line is commented:
    Code:
    #bind-address           = 127.0.0.1
     
  2. till

    till Super Moderator Staff Member ISPConfig Developer

    Please check the /etc/hosts file, the line 127.0.0.1 should contain "localhost" and "localhost.localdomain". Maybe it is just "localhost.localdomain" at the moment.
     
  3. Ovidiu

    Ovidiu Active Member

    It looks like this:
    Code:
    127.0.0.1       localhost.localdomain localhost myhostname.mydomain.tld
    ::1             localhost ip6-localhost ip6-loopback
    ff02::1         ip6-allnodes
    ff02::2         ip6-allrouters
    51.xxx.xxx.xxx myhostname.mydomain.tld myhostname
    # --- BEGIN PVE ---
    51.xxx.xxx.xxx myhostname.mydomain.tld myhostname
    # --- END PVE ---
     
  4. Ovidiu

    Ovidiu Active Member

    Any other advice?
     
  5. Gwyneth Llewelyn

    Gwyneth Llewelyn New Member

    Hm. What happens if you change manually the password for a DB user (you can change it to the exact same password as before) from within phpMyAdmin (because it's easier ;) )?
    I had lots of issues similar to yours when migrating between servers/MySQL versions/etc. because often different versions of the database server might encode passwords differently. I had this happening to me quite recently; about half the passwords wouldn't work any more, but, strangely enough, the other half or so was fine. After tracing this down, I noticed that only the 'very old' passwords had some problems — i.e., those set some 2 or 3 years ago. The 'newer' users & passwords had no problem whatsoever. The difference between them? Well, different MySQL versions for sure...
     
  6. NdK

    NdK Member

    @Gwyneth Llewelyn : Probably a difference in hashing algorithms. I don't think it's related.

    @Ovidiu : I think problem might be caused by having myhostname.mydomain.tld on multiple rows (that way only the 127.0.0.1 gets used). That might have generated a wrong ACL for the user.
     
  7. Ovidiu

    Ovidiu Active Member

    OK, I see, but do you have any suggestions on how to fix the problem?
     
  8. NdK

    NdK Member

    Try removing "myhostname.mydomain.tld" from the 127.0.0.1 line.
    Shouldn't require a mysql restart.
     
  9. Ovidiu

    Ovidiu Active Member

    seems to have worked, I hope there are no negative side effects involved :)
     
  10. till

    till Super Moderator Staff Member ISPConfig Developer

    The hostname may not be listed in /etc/hosts for the localhost IP, so there should be no side affects. Just check that the hostname is still listed in /etc/hosts, but for the "real" IP of the server (the one that is assigned to the network card). if the server is in a NAT enviroment, then this real IP is the internal IP, not the xeternal one.
     
  11. Ovidiu

    Ovidiu Active Member

    thanks, its not a natted but a bridged environment. my current /etc/hosts looks like this so al should be good.
    Code:
    127.0.0.1       localhost.localdomain localhost
    ::1             localhost ip6-localhost ip6-loopback
    ff02::1         ip6-allnodes
    ff02::2         ip6-allrouters
    51.xxx.xxx.xxx myhostname.mydomain.tld myhostname
    # --- BEGIN PVE ---
    51.xxx.xxx.xxx myhostname.mydomain.tld myhostname
    # --- END PVE ---
     
  12. till

    till Super Moderator Staff Member ISPConfig Developer

    That's ok.
     

Share This Page