1

I want to map 127.0.0.1 to multiple names instead of the standard way as localhost. so my /etc/hosts file in Ubuntu would have the following entries:

127.0.0.1 localhost
127.0.0.1 localhost:extra1
127.0.0.1 localhost:extra2

Now, in MySQL, I want to have a user named karthick and it should have different privileges. 

select user,host from users;

The above statement shows

 karthick localhost:extra1;
 karthick localhost:extra2;

My question:

I am not able to login using

mysql -u karthick -h localhost:extra1 -p

What would be the problem?

Motivation behind the question

I want to login as below

login 1:

mysql -u karthick -h localhost:extra1 -p

login 2:

mysql -u karthick -h localhost:extra2 -p

I want login 1 and login 2 to have different set of privileges... Hope this is possible

ZygD
  • 22,092
  • 39
  • 79
  • 102
Karthick
  • 2,844
  • 4
  • 34
  • 55
  • http://dev.mysql.com/doc/refman/5.1/en/privilege-system.html Please do find the line: You can grant one set of privileges for connections by joe from office.example.com, and a different set of privileges for connections by joe from home.example.com. – Karthick Aug 22 '10 at 04:13

1 Answers1

0

At the point where mysql receives the connection, all it has to identify the remote system is the IP address. It must then do a "reverse dns" lookup to convert the IP address of the requesting system to a hostname, so that it can look up the hostname in the authorization table. While you can map multiple hostnames to a single IP address, as you have done, there's no way for mysql to know which hostname was originally specified. At that point all it has is 127.0.0.1, and the DNS resolver will return the name 'localhost'. Even if changed that, you could give it only one name, so your scheme won't work.

You can however give your network adapter multiple DIFFERENT IP addresses. I don't know the details of setting this up on Ubuntu, but it should be relatively easy. Then you can set up multiple hostnames each with its own IP that connects back to your system. In your mysql setup specify the IP addresses instead of the hostnames in your GRANT commands to avoid having to mess with rDNS.

Jim Garrison
  • 85,615
  • 20
  • 155
  • 190
  • I tried setting up multiple ip addresses and connecting using new ip address. that is mysql -u root -h 192.168.9.2 -p But it din work..... The Error I get is "Can't connect to MySQL server on '197.168.9.2' " – Karthick Aug 23 '10 at 01:21
  • You have 192.168.9.2 and **197**.168.9.2 -- was that a typo in your comment or was that the real message? – Jim Garrison Aug 23 '10 at 02:02
  • First, can you ping each separate address? Then, check that MySQL is listening on 0.0.0.0 and not 127.0.0.1 (`netstat -nat|grep 3306`). If it's on 127.0.0.1 you'll have to change the MySQL config. – Jim Garrison Aug 24 '10 at 02:11