2

I'm trying to connect to mysql using the command

mysql -h 127.0.0.1

It comes up with the error

ERROR 1045 (28000): Access denied for user 'root'@'mydomain.com' (using password: NO)

Why is 127.0.0.1 being converted into my domain name, and how can I fix it?

However, it does work if I don't specify a host (and by extension, if 'localhost' is specified).

Edit: It seems it resolves to the domain name when using TCP, so it also fails when using

mysql -h localhost --protocol=TCP

Edit2: When I use skip-name-resolve I get a similar output except mydomain.com is replaced with x.x.x.x which is the public IP of mydomain.com.

12
  • Are you using a password? mysql -h 127.0.0.1 -uroot -pMyRootPassword actually works as intended.
    – Deer Hunter
    Jul 4, 2014 at 6:35
  • The password is irrelevant. If I added 'root'@'mydomain.com' as a user it would work, but I want to connect using 'root'@'127.0.0.1'. The problem is that any mysql TCP connection made to either 127.0.0.1 or localhost gets changed into mydomain.com
    – Ben
    Jul 4, 2014 at 6:41
  • Assuming you're on a Unix/Linux machine - have you got 127.0.0.1 mydomain.com in your /etc/hosts file? Jul 4, 2014 at 8:51
  • I had tried that, but it didn't yield any difference
    – Ben
    Jul 4, 2014 at 11:03
  • No difference as in it still said Access denied or it still has mydomain.com in the user name? If it's the latter and you definitely have removed your hostname from /etc/hosts then DNS must be misconfigured for mysql to be able to reverse lookup 127.0.0.1 to your hostname. Jul 4, 2014 at 11:11

5 Answers 5

3

I had masquerading for all external packets (including on lo). Removing/editing the iptable -t nat POSTROUTING rules fixed the issue.

1
  • Thank you one million times for sharing this valuable information.
    – W.M.
    Jan 14, 2020 at 10:13
0

Most likely this is caused by a hosts file entry.

1
  • No change when I only have 127.0.0.1 localhost in /etc/hosts
    – Ben
    Jul 4, 2014 at 6:21
0

Maybe mysql is listening only on your public ip and not on 0.0.0.0?

3
  • I have bind-address = 0.0.0.0
    – Ben
    Jul 4, 2014 at 6:25
  • Check what tcpdump is telling you. Jul 4, 2014 at 6:48
  • Running from the command line gives interactions between: mydomain.com.<port> > localhost.mysql
    – Ben
    Jul 4, 2014 at 7:04
0

I'm not sure to understand your question but 127.0.0.1 is the standard loopback address. SO if your host have this IP : 192.168.0.1 Then 192.168.0.1 = 127.0.0.1 = localhost

2
  • 1
    In mysql, these things are not the same. User accounts (and permissions) are maintained individually for each host, so 'root'@'localhost' can have a different password to 'root'@'127.0.0.1'
    – Ben
    Jul 4, 2014 at 6:32
  • OK, good to know.
    – NooJ
    Jul 4, 2014 at 6:38
0

1) Check /etc/hosts and make sure that 127.0.0.1 points only to localhost and not mydomain.com. mydomain.com should be associated with the actual IP address for the server and not with localhost.localdomain.

2) It also looks like you've set the password for root on your mysql instance (actually this is a good thing). Recommend adding the "-p" switch to your login so that it looks like:

mysql -h localhost -p

You will then be asked to enter the password for root. Hope this helps.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .