0

I'm going crazy to solve this. I have a mysql installation (on machine db.example.org) which doesn't resolve a given hostname. I gave privileges using hostnames i.e. 

GRANT USAGE ON *.* TO 'user'@'host1.example.org' IDENTIFIED BY PASSWORD 'secret'
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, INDEX ON `my_database`.* TO 'user'@'host1.example.org'

However when I try to connect using mysql -u user -p -h db.example.org I obtain

ERROR 1045 (28000): Access denied for user 'user'@'192.168.11.244' (using password: YES)

I already checked for correct name resolution in the dns system:

$ dig -x 192.168.11.244
;; ANSWER SECTION:
244.11.168.192.in-addr.arpa. 68900 IN   PTR host1.example.org.

I've also checked for skip-name-resolve option in mysql variables in fact if I can access from another machine on the same subnet using hostname privileges.

The only difference is that host1.example.org and db.example.org point the same ip on the same machine i.e. both db.example.org and host1.example.org have ip 192.168.11.244. In this way all the applications using that database can use the name db.example.org and we can move the data on other hosts (if needed) just by changing the dns record, leaving the application code unchanged.

What should I do to solve this or at least to understand what's happening?

Fabio
  • 1,299
  • 2
  • 13
  • 18
  • What does the query: `SELECT User, Host FROM `mysql`.user;` . Also, you might want more privileges than 'USAGE' for your user. Is your user you're trying to connect 'root' (as indicated by the error message)? Granting USAGE to root seems a bit off. – Derek Downey Feb 16 '11 at 15:13
  • I make some typo using cut and paste is not root but user. I actually have more privileges for that user, I didn't include them for brevity. I edit the post to reflect your point. – Fabio Feb 16 '11 at 15:22
  • 2
    I **highly recommend** you do not use hostname based users for MySQL and be sure the skip-name-resolve option is set. You're exposing yourself to DNS cache poisoning, as well as DoS if your DNS ever goes offline. We had a MySQL server in production that did not have `skip-name-resolve` set. One day our provider's DNS server went down, which should have had no effect on us. However, MySQL ended up consuming ridiculous amounts of resources and open connections trying to perform the reverse DNS lookups for connections. It eventually swapped to disk and was killed by the OOM manager. – hobodave Feb 16 '11 at 17:11
  • @hobodave we're using our internal dns servers to resolve names and we have a couple of them in the network plus another secondary dns outside our network, so we should be safe in regarding of what you're saying. – Fabio Feb 16 '11 at 23:23

2 Answers2

1

Just to make sure, have you tried running FLUSH PRIVILEGES ?

Coops
  • 6,055
  • 1
  • 34
  • 54
  • Yes I did, the problem is I'd expect the message `Access denied for user 'user'@'host1.example.org'` instead of the current one. – Fabio Feb 16 '11 at 15:47
  • 1
    Why on earth the down vote? Jeez, some people. – Coops Feb 17 '11 at 14:03
0

After a couple of days the problem is gone... I tried again, and now it does work. Maybe it was a temporary dns failure or a cache problem (I executed the flush hosts command more than once). Anyway the problem is solved.

Fabio
  • 1,299
  • 2
  • 13
  • 18