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?

Improve this question
4
  • 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, 2011 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, 2011 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, 2011 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, 2011 at 23:23

2 Answers 2

Reset to default
1

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

Improve this answer
2
  • 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, 2011 at 15:47
  • 1
    Why on earth the down vote? Jeez, some people.
    – Coops
    Feb 17, 2011 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.

Improve this answer

You must log in to answer this question.

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