0

At work, my team have a MySQL database on a Linux 14.04 box. My team is working on changing the permissions to use hostnames rather than IPs (so that the IT team can do what they want to the network and not worry about breaking our access).

For example, there would be a user admin@10.10.xxx.xxx that we will want to change to admin@my-ws.co.local. I've been playing around with my user (since I have root and can still log in). After changing the user profile's host from the IP address to the Hostname, Workbench will give the error...

HOST '10.10.xxx.xxx' is not allowed to connect to this MySQL server

When I log in to the Linux box and use nslookup my-ws.co.local, the response gives me the correct IP address, so I know there no connection issue between the box and the network. And since the response is correct, I am assuming that the internal DNS is working correctly.

Checking the performance_schema.host_cache table, I can see that the IP 10.10.xxx.xxx was not resolved into a host (the HOST column is NULL and the HOST_VALIDATED column is YES).

Why is MySQL unable to resolve the IP address into the correct hostname, while nslookup works? Is there some setting in MySQL that I need to fix? Do there need to be records in the internal DNS for each workstation of my team?

Peter
  • 11
  • 4
  • Stack Overflow is a site for programming and development questions. This question appears to be off-topic because it is not about programming or development. See [What topics can I ask about here](http://stackoverflow.com/help/on-topic) in the Help Center. Perhaps [Super User](http://superuser.com/) or [Unix & Linux Stack Exchange](http://unix.stackexchange.com/) would be a better place to ask. – jww Jul 08 '18 at 02:32

2 Answers2

1

There's forward lookups, like resolving an A record, and there's reverse lookups, which involve finding a PTR record. They're often paired together on public networks, but remember that one or more A records can point to the same IP but there should be only one reverse PTR record. Additionally, while the PTR record is supposed to be a resolvable address, this may not be the case, the reverse entry might be invalid or resolve to an entirely different address.

Unless you have a resolver for 10.10.in-addr.arpa, which is not normally the case since that's a reserved address space, you cannot reverse lookup the IP back to the hostname.

With nslookup you can test reverse mapping:

nslookup 10.10.1.1

Where that address is whatever you're trying to reverse.

tadman
  • 208,517
  • 23
  • 234
  • 262
  • 1
    Ah, this was part of the missing link. Thank you! It looks like our network isn't currently set up to do reverse lookups to workstations. I'm conferring with my IT team, and we'll get it figured out. – Peter Jul 06 '18 at 20:19
  • 1
    PTR records are not required to be unique, while rare, it can happen (and most applications dealing with PTR records have no idea on how to handle that) – Patrick Mevzek Jul 06 '18 at 22:25
  • The only way you can reliably do reverse lookups on private networks is if everyone uses the same private nameservers that have the right configuration. Public servers will not, obviously, be aware of the internals of your private network. – tadman Jul 09 '18 at 16:46
1

It happened to me as well.

The reason is skip_name_resolve was turned on in my.cnf configure. It disable reverse DNS query form IP address and use IP address only for logging time.

if nslookup works, it means reverse DNS query works.

Especially, if 'resolveip' command works, that means MySQL server is able to resolve IP address to hostname. Because resolveip is a command coming with MySQL Server, MySQL uses the same way to resolve IP and hostname.

user1812597
  • 61
  • 1
  • 3