Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

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.

share|improve this answer
    
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. Oct 25 '11 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 Oct 26 '11 at 16:44
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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