Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Can someone plz tell me how to set DB proxy for the below case.

Lets consider,

  1. Three machines in same network - 172.16.1.10, 172.16.1.11 and 172.16.1.12
  2. 172.16.1.12 have mysql instance running on 3306 port
  3. Users from 172.16.1.10 machine want to connect to above mysql instance by using 172.16.1.11 machine and one port number(say 9999) as pass-through.

    $> mysql -h 172.16.1.11 -P 9999 -u user1 -p pwd1'

what all iptables rules need to be created to achieve this?

Thanks

share|improve this question
    
What are you trying to do? Why not just connecting directly to IP: 172.16.1.12. For port change, you can do it by configuring mysql server to listen on different port. –  Khaled Jul 18 '13 at 10:53
    
I need to connect multiple mysql instances on 12 box using dynamic random ports for some type of load balancing –  Murali Jul 18 '13 at 12:28
    
why not use a (software)loadbalancer? –  Pascal Schmiel Jul 18 '13 at 12:55
    
I have a datamart to which we load data into it on hourly basis, but loading data also takes hours time bcoz of huge volume. Till the time loading data is not complete, datamart should not serve reports bcoz of data consistency and integrity reasons. So that's why i've created one more datamart with same schema on one more different mysql instance - means one active and one passive datamarts, one for reading and other for loading data parallely.After every load completion, i'll set proxy rules such that reports always get data from datamart which have loaded data recently –  Murali Jul 19 '13 at 6:14
    
Otherside i'll load data into other datamart - means switching between datamarts continously after every load. i'm creating/deleting iptables rules dynamically as like below. iptables -t nat -A PREROUTING -p tcp -d 172.16.1.11 --dport 9034 -j DNAT --to-destination 172.16.1.12:3306 iptables -t nat -A OUTPUT -p tcp -d 172.16.1.11 --dport 9034 -j DNAT --to-destination 172.16.1.12:3306 iptables -t nat -A PREROUTING -p tcp -d 172.16.1.11 --dport 9035 -j DNAT --to-destination 172.16.1.12:3307 iptables -t nat -A OUTPUT -p tcp -d 172.16.1.11 --dport 9035 -j DNAT --to-destination 172.16.1.12:3307 –  Murali Jul 19 '13 at 6:19
show 1 more comment

1 Answer

up vote 0 down vote accepted

You can use NAT on 172.17.1.11:

sysctl net.ipv4.ip_forward=1
iptables -t nat -A POSTROUTING -j MASQUERADE
iptables -t nat -A PREROUTING -p tcp --dport 9999 -j DNAT --to 172.16.1.12:3306
iptables -A FORWARD -d 172.16.1.12 -p tcp --dport 3306 -j ACCEPT
share|improve this answer
    
Can u plz tel me with out MASQUERADE'ing POSTROUTING plz? and do we need FORWARD also in 11 box? –  Murali Jul 18 '13 at 12:35
    
if you want to use iptables, masquerading is the only way I am aware of. all this stuff - including the ip forward - has to be done on the host .11. –  Pascal Schmiel Jul 18 '13 at 12:51
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.