Mysql resolving 127.0.0.1 to hostname

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.

Ben

Posted 2014-07-04T04:19:05.583

Reputation: 51

Assuming you're on a Unix/Linux machine - have you got 127.0.0.1 mydomain.com in your /etc/hosts file? – garethTheRed – 2014-07-04T08:51:26.930

I had tried that, but it didn't yield any difference – Ben – 2014-07-04T11:03:47.060

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. – garethTheRed – 2014-07-04T11:11:10.830

No difference as in the error output was exactly the same with or without that in /etc/hosts – Ben – 2014-07-04T11:32:39.190

This answer contains hints as to finding a solution. – Daniel B – 2014-07-04T12:12:22.667

@DanielB I mentioned in the first edit that the issue seemed to be for TCP connections. I can however telnet 127.0.0.1 3306 which shows that mysqld is listening and accessible. I can also explicitly add a 'root'@'mydomain.com' user and login to mysql. – Ben – 2014-07-04T12:23:08.877

@user3603149 : Did you checked whether you have provided user root with proper privilege and password – Renju Chandran chingath – 2014-07-04T12:48:31.717

@RenjuChandranchingath logging in is not the direct issue. The issue is that any attempts to connect using mysql -h 127.0.0.1 are not seen as coming from localhost or 127.0.0.1 but something else (in this case the hostname of (or) the public IP) – Ben – 2014-07-04T12:53:36.360

@garethTheRed I added Edit2 which suggests that rDNS is not the cause. It seems more like an iptables issue but I've tried flushing unnecessary rules etc to no avail. – Ben – 2014-07-04T13:29:35.947

Are you using a password? mysql -h 127.0.0.1 -uroot -pMyRootPassword

actually works as intended. – None – 2014-07-04T06:35:41.337

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 – 2014-07-04T06:41:06.467

You forgot to state your operating system and MySQL version. – Michael Hampton – 2014-07-04T14:31:51.810

Answers

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

Ben

Posted 2014-07-04T04:19:05.583

Reputation: 51

Thank you one million times for sharing this valuable information. – W.M. – 2020-01-14T10:13:54.930

Most likely this is caused by a hosts file entry.

user35787

Posted 2014-07-04T04:19:05.583

Reputation:

No change when I only have 127.0.0.1 localhost in /etc/hosts – Ben – 2014-07-04T06:21:26.353

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

Kazimieras Aliulis

Posted 2014-07-04T04:19:05.583

Reputation: 324

I have bind-address = 0.0.0.0 – Ben – 2014-07-04T06:25:52.057

Check what tcpdump is telling you. – Kazimieras Aliulis – 2014-07-04T06:48:08.503

Running from the command line gives interactions between: mydomain.com.<port> > localhost.mysql – Ben – 2014-07-04T07:04:18.307

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

NooJ

Posted 2014-07-04T04:19:05.583

Reputation: 1

1In 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 – 2014-07-04T06:32:14.063

OK, good to know. – NooJ – 2014-07-04T06:38:52.137

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.

joat

Posted 2014-07-04T04:19:05.583

Reputation: 466