0

I have two IPs (192.168.13.1 and 192.168.13.2) in my Server A and have provided select access to another Server B for secondary IP/virtual IP (192.168.13.2).

mysql> show grants for web_app@192.168.13.2;
+------------------------------------------------------------------------------------------------------------------------------+
| Grants for user@192.168.13.2                                                                                            |
+------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT ON *.* TO 'user'@'192.168.13.2' IDENTIFIED BY PASSWORD '*pass' |
+------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

When I tried to connect mysql of Server A to Server B ( from Linux ), Primary IP is trying to connect to MySQL and access is getting denied

[root@13-1 ~]# mysql -h 192.168.12.1 -u user -p
Enter password:
ERROR 1130 (HY000): Host '192.168.13.1' is not allowed to connect to this MySQL server

Then I give exactly the same grants for 192.168.13.1 and I was able to login. My application running in 192.168.13.2 trying to fetch from remote server was also not working until I give grants for primary IP. My ifconfig details::

[root@13-1 ~]# ifconfig | grep inet
        inet 192.168.13.1  netmask 255.255.254.0  broadcast 192.168.13.255
        inet 192.168.13.2  netmask 255.255.254.0  broadcast 192.168.13.255
        inet 192.168.13.3  netmask 255.255.254.0  broadcast 192.168.13.255

I have two questions:

  1. How to mysql remote login from a specific IP ( in my case, from 192.168.13.2 ) ?
  2. Is it necessary for an application(with MySQL) to have grants provided for Primary IP also, even though I am trying from App in another IP ? Or Am I missing something?
  • You need ask (if not expert personally) network guys for trace Your route. You can choose destination ip, but because both virtual ip on same subnet - You can not mange source ip (at least from mysql). So yes - You must add all to be sure and safe. – a_vlad Mar 7 '17 at 1:16
  • Is it like, no matter my code(which fetch data from remote server) running in 192.168.13.2 alone (like 192.168.13.2:8000/myapp ), I have to provide access to both IPs ? Isn't it suppose to work without other IP intervention? – prado Mar 7 '17 at 1:50
  • I do not know. What I now - this settings, outside of MySQL, may be You can setup it properly in network settings - like define default interface for all outgoing traffic(?), but not from mysql client as in question. Not sure about application - could You manage this or not. MySQL server is stupid - it check incoming IP and compare it. What You can do - use wildcard, like - 192.168.12.% or use DNS name like serverB.damain.com, I personally do not like DNS because it slowdown server and sometime make a lot of troubles when network guys change something :-) – a_vlad Mar 7 '17 at 2:25
  • Unless the client specifies a IP (which is not possible with MySQL client) the OS will pick the source IP address of a outgoing socket. Typically this is done by looking at the source interface of the route to the destination. So you can either use a network namespace or add such a route. However why not use the main IP and avoid complexity? – eckes Sep 9 at 18:24
0

Let me answer in more formatted form:

  • MySQL server do not manage client ip on server, and not manage outgoing on client. Server could select IP address for listen only.

Server just compare client IP with permission tables and allow or reject access. I do not know method how to tell mysql client (including library to use dedicated outgoing ip).

what You can do:

  • use wildcards, like - user@192.168.12.%
  • use domain names, like - user@serverB.yourdomain.com

Wildcard make list of IP addresses wider than necessary, but at the same time using DNS resolving:

  • Slowdown performance under high loading
  • Could completely crash work with may network related issues

You can combine wildcard + firewall rules for reduce number of allowed IP.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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