I have been trying to allow a remote computer to connect to MySQL but to be allowed by hostname instead of IP address – as the ADSL connection does not have a static address assigned to it, I thought it would be easy to get MySQL to valid by host. Oh no!
So the connection has IP address of w.x.y.z
The reverse lookup resolves as w-x-y-z.static-adsl.isp.co.uk
So by adding a user to MySQL and granting privileges it should be nice and easy, right?
MySQL:
use users; grant all on *.* to 'user'@'w-x-y-z.static-adsl.isp.co.uk' identified by 'password'; flush privileges;
Won’t connect -get the usual “remote host w.x.y.z is not allowed to connect to this mysql server”
A bit more digging, troubleshooting, trying different combinations of hostnames etc I stumbled upon this link which explains a bit about how MySQL uses DNS:
Reverse Lookup
When a network connection is made to MySQL, all MySQL knows is the remote end (peer) IP. First MySQL resolves the peer IP to a hostname by calling the standard C library gethostbyaddr() [197]. Naturally, there are two possible return values: Either the lookup succeeds and returns a hostname, or it fails. If the reverse lookup succeeds MySQL goes onto the next step. If the reverse lookup fails, the failure is cached (remembered) [201], and MySQL will not try to lookup this IP address again until either MySQL is restarted or issued a FLUSH HOSTS; command. Failure can mean, basically, one of two things: Either DNS doesn’t respond at all, or it responds with NXDomain (non-existent domain). In the first case MySQL waits at least 10 seconds for a response. Since MySQL uses standard C library resolver functions (gethostbyaddr() and gethostbyname()) the operation and limitations of DNS lookups is a factor of the C library, not MySQL. Therefore we have to look at how these resolver functions work.
The other most common kind of failure is a return value of NXDomain, which means the DNS server has no matching resource record, which would be a PTR record at this point. Each nameserver is queried twice in order, waiting 5 seconds for each one, and fails once any nameserver returns NXDomain.
If the reverse lookup succeeds by returning a hostname, MySQL then does a forward lookup on this hostname. (Technically, MySQL doesn’t allow an empty hostname [203]. If the reverse lookup returns an empty hostname this is considered a failure.)
Forward Lookup
For added security, MySQL does a forward lookup on the hostname it obtained from the reverse lookup. MySQL is checking that the IP address this hostname resolves to matches the peer IP address [239]. MySQL resolves the IP address to a hostname by calling the standard C library gethostbyname() [214]. If the lookup succeeds its always cached; if the lookup fails for any reason it is never cached. The forward lookup can fail for the same reasons the reverse look can fail: Non-responsive DNS or an NXDomain response. The standard C library gethostbyname() works a little differently than gethostbyaddr().
Standard C library gethostbyname() uses /etc/resolv.conf, queries each listed nameserver twice in order, and waits 5 seconds for each one just like gethostbyaddr() except it goes one step further if the lookup fails. If gethostbyname() receives no response from any nameserver, it tries all over again but with a modified hostname: It takes the domain of the server’s hostname and appends it to the hostname in question. For example, if your server’s hostname is my.server.com, and the hostname in question is someone.else.com, gethostbyname() will try to resolve someone.else.com.server.com. The maximum wait time for gethostbyname() is 20 seconds times the number of nameservers.
If gethostbyname() receives an NXDomain response from a nameserver, it always tries again with the modified hostname (e.g. someone.else.com.server.com.). Interestingly enough, it may or may not retry with all nameservers. If the first nameserver returns NXDomain, gethostbyname() retries only that nameserver with the modified hostname. If the first nameserver doesn’t respond, and the second one does, gethostbyname() retries the first nameserver, then retries the second nameserver.
In either case, should gethostbyname() fail the failure is never cached by MySQL [220]. Only successful forward lookups are cached. While MySQL is waiting for forward resolution the status of this connection in the process list is shown the same as above.
Additional Checks
After reverse and forward lookups complete successfully MySQL makes two additional checks. First it checks the hostname obtained from the reverse lookup does not start with a series of numbers followed by a dot [226]. “MySQL disallows matching on hostnames that start with digits and a dot. Thus, if you have a host named something like 1.2.foo.com, its name never matches the Host column of the grant tables” (5.5.5.). Finally, MySQL checks that the IP address obtained from the forward lookup matches the peer IP address [239]. If the two match, the function (ip_to_hostname()) returns the hostname [244]. If the two do not match this failure is cached [250]. (And if you’re curious, ip_to_hostname() returns to sql/sql_parse.cc line 525.)