2

We're seeing something peculiar about the way MySQL does name resolution -- or peculiar to us at least (-:

We have two data centers, A and B. A is primary, B is used in case of failover. We have two MySQL servers in A, db1 and db2, sitting behind a load balancer in active(db1)-passive(db2) configuration. The MySQL "service" is accessed as db.domain.com, which is c-named to the load balancer, lb.domain.com.

When looking at the mysql.db table (and similarly in mysql.user), we see entries like:

Host                    Db                User
lb.domain.com         app1              user1
lb.domain.com         app2              user2
lb.domain.com         app3              user3

How does one get db.domain.com in the Host column instead of the lb.domain.com?

Improve this question

1 Answer 1

Reset to default
2

If you want to grant access to the app databases to the same users, do this:

GRANT ALL PRIVILEGES ON app1.* TO 'user'@'db.domain.com';
GRANT ALL PRIVILEGES ON app2.* TO 'user'@'db.domain.com';
GRANT ALL PRIVILEGES ON app3.* TO 'user'@'db.domain.com';

If you want to replace 'lb.domain.com' with 'db.domain.com' do this:

UPDATE mysql.user SET host='db.domain.com'
WHERE user='user' AND host='lb.domain.com';
UPDATE mysql.db   SET host='db.domain.com'
WHERE user='user' AND host='lb.domain.com' AND db in ('app1','app2','app3');
FLUSH PRIVILEGES;

Give it a Try !!!

UPDATE 2011-10-26 12:43 EDT

Perhaps you may want to try masking the domain as follows:

GRANT ALL PRIVILEGES ON app1.* TO 'user'@'%.domain.com';
GRANT ALL PRIVILEGES ON app2.* TO 'user'@'%.domain.com';
GRANT ALL PRIVILEGES ON app3.* TO 'user'@'%.domain.com';

or replacing the domain:

UPDATE mysql.user SET host='%.domain.com'
WHERE user='user' AND host='lb.domain.com';
UPDATE mysql.db   SET host='%.domain.com'
WHERE user='user' AND host='lb.domain.com' AND db in ('app1','app2','app3');
FLUSH PRIVILEGES;

That way, any authentication of user from the domain.com domain would be acceptable.

UPDATE 2011-10-26 18:05 EDT

Personally, I hate using DNS names in mysql.user and mysql.db

You can actually get mysqld to bypass having to use DNS as follows

First replace all DNS names with hard IP addresses. Also, replace domain names with IP netblocks (instead of *.domain.com using 10.20.30.%)

Then, add the following to /etc/my.cnf and restart mysql

[mysqld]
skip-name-resolve
skip-host-cache

DNS resolution should, then, become a thing of the past.

Improve this answer
3
  • Ah. Thanks @Rolando. I'm aware of the update option. We're using an application to manage the above applications, app1, app2, app3, etc., and would like to avoid using update if possible. We enter db.domain.com in the manager application, but the MySQL picks up lb.domain.com. Looks like we have to revisit the manager application, but is there anything on MySQL side that would cause it to go from db.domain.com to lb.domain.com ... ?
    – KM.
    Commented Oct 25, 2011 at 22:40
  • @KM01 : No. MySQL can only report on the IP/DNS-Name it sees in the processlist. I updated my answer to use a masked domain.
    – RolandoMySQLDBA
    Commented Oct 26, 2011 at 16:44
  • You probably don't want to update the mysql.user table like this, as a matter of practice, since it will invalidate the permissions on any stored programs where the affected user is the DEFINER.
    – Michael - sqlbot
    Commented Jan 28, 2015 at 12:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.