Host Name Resolution in mysql.db and mysql.user tables

Question

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?

Answer 1

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.

• KM.: 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 ... ?
• RolandoMySQLDBA: @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.
• Michael - sqlbot: 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.