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

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

 

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

 

Any other advice?

 

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

 

@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.

 

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

 

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

 

seems to have worked, I hope there are no negative side effects involved

 

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