iptables to forward mysql traffic to another server

mycoolwater · 02-12-2009, 07:46 PM

My current script is:
iptables -F
iptables -t nat -F
iptables -t mangle -F
iptables -t nat -A PREROUTING -p tcp -i eth0 --dport 3306 -j DNAT --to-destination x.x.x.x:3306
iptables -t nat -A POSTROUTING -o eth0 -j MASQUERADE

It is slim, clean, and working nicely. But the problem is that this is using NAT, and the actual mysql server can't determine where the traffic actually coming of - it only knows that the traffic comes from. Any walk around with iptables that allow a server pass mysql traffic to another mysql server with detail of ip address being passed on?

maas187 · 02-13-2009, 04:38 PM

Quote:

Originally Posted by mycoolwater

My current script is:
iptables -F
iptables -t nat -F
iptables -t mangle -F
iptables -t nat -A PREROUTING -p tcp -i eth0 --dport 3306 -j DNAT --to-destination x.x.x.x:3306
iptables -t nat -A POSTROUTING -o eth0 -j MASQUERADE

It is slim, clean, and working nicely. But the problem is that this is using NAT, and the actual mysql server can't determine where the traffic actually coming of - it only knows that the traffic comes from. Any walk around with iptables that allow a server pass mysql traffic to another mysql server with detail of ip address being passed on?

Try this.

### " ALL MYSQL TRAFFIC, 'Local' PORT (3306) TO 'Remote' PORT (3306) ...."

iptables -t nat -A PREROUTING -p tcp -i eth0 -d X.X.X.X --dport 3306 -j DNAT --to x.x.x.x:3306

iptables -A FORWARD -p tcp -i eth0 -d x.x.x.x --dport 3306 -j ACCEPT

Hope it works with you .

Regards,
MaaS

maas187 · 02-13-2009, 04:39 PM

[QUOTE=maas187;3442715]Try this.

### " ALL MYSQL TRAFFIC, 'Local' PORT (3306) TO 'Remote' PORT (3306) ...."

iptables -t nat -A PREROUTING -p tcp -i eth0 -d X.X.X.X --dport 3306 -j DNAT --to x.x.x.x:3306

iptables -A FORWARD -p tcp -i eth0 -d x.x.x.x --dport 3306 -j ACCEPT

Sorry forgot this.

iptables -t nat -A POSTROUTING -o eth0 -j MASQUERADE

take care.

mycoolwater · 02-15-2009, 05:07 PM

Thanks maas187, i tried exactly what you suggested, but no luck.

So maybe my original post is not clear enough... here is the scenario:

x.x.x.x (client X) --> y.y.y.y (app server Y) --> z.z.z.z (mysql server Z)

I tried to pass ip of client, X (can be any external IP in the world), to Z machine via iptables inside Y.

After maas187 suggestion, my iptables command:

Code:

iptables -F
iptables -t nat -F
iptables -t mangle -F
iptables -t nat -A PREROUTING -p tcp -i eth0 -d y.y.y.y --dport 3306 -j DNAT --to z.z.z.z:3306
iptables -A FORWARD -p tcp -i eth0 -d y.y.y.y --dport 3306 -j ACCEPT
iptables -t nat -A POSTROUTING -o eth0 -j MASQUERADE

This is the result of

Code:

tcpdump -l -n -i eth0 port 3306

in Z:

Code:

08:29:54.364275 IP y.y.y.y.41510 > z.z.z.z.mysql: S 224958559:224958559(0) win 5840 <mss 1460,sackOK,timestamp 1071510064 0,nop,wscale 0>
08:29:54.364297 IP z.z.z.z.mysql > y.y.y.y.41510: S 1243310729:1243310729(0) ack 224958560 win 5792 <mss 1460,sackOK,timestamp 1345070385 1071510064,nop,wscale 7>
08:29:54.365444 IP y.y.y.y.41510 > z.z.z.z.mysql: . ack 1 win 5840 <nop,nop,timestamp 1071510064 1345070385>
08:29:54.365572 IP z.z.z.z.mysql > y.y.y.y.41510: P 1:79(78) ack 1 win 46 <nop,nop,timestamp 1345070386 1071510064>
08:29:54.366540 IP y.y.y.y.41510 > z.z.z.z.mysql: . ack 79 win 5840 <nop,nop,timestamp 1071510064 1345070386>
08:29:55.833628 IP y.y.y.y.41510 > z.z.z.z.mysql: P 1:3(2) ack 79 win 5840 <nop,nop,timestamp 1071510211 1345070386>
08:29:55.833655 IP z.z.z.z.mysql > y.y.y.y.41510: . ack 3 win 46 <nop,nop,timestamp 1345071854 1071510211>
08:29:57.998865 IP y.y.y.y.41510 > z.z.z.z.mysql: P 3:9(6) ack 79 win 5840 <nop,nop,timestamp 1071510428 1345071854>
08:29:57.998904 IP z.z.z.z.mysql > y.y.y.y.41510: . ack 9 win 46 <nop,nop,timestamp 1345074020 1071510428>
08:29:57.998942 IP z.z.z.z.mysql > y.y.y.y.41510: P 79:99(20) ack 9 win 46 <nop,nop,timestamp 1345074020 1071510428>
08:29:57.998975 IP z.z.z.z.mysql > y.y.y.y.41510: F 99:99(0) ack 9 win 46 <nop,nop,timestamp 1345074020 1071510428>
08:29:57.998990 IP z.z.z.z.mysql > y.y.y.y.41510: R 100:100(0) ack 9 win 46 <nop,nop,timestamp 1345074020 1071510428>
08:29:58.000014 IP y.y.y.y.41510 > z.z.z.z.mysql: . ack 99 win 5840 <nop,nop,timestamp 1071510428 1345074020>
08:29:58.000023 IP z.z.z.z.mysql > y.y.y.y.41510: R 1243310828:1243310828(0) win 0

As above, the ip address of X is masked to Y (because of NAT/PREROUTING rule???) and Z only knows of Y ip address. So the question, is possible to modify the iptables in Y so mysql server in Z can understand the traffic coming from X?

maas187 · 02-16-2009, 10:32 PM

Quote:

Originally Posted by mycoolwater

Thanks maas187, i tried exactly what you suggested, but no luck.

So maybe my original post is not clear enough... here is the scenario:

x.x.x.x (client X) --> y.y.y.y (app server Y) --> z.z.z.z (mysql server Z)

I tried to pass ip of client, X (can be any external IP in the world), to Z machine via iptables inside Y.

After maas187 suggestion, my iptables command:

Code:

iptables -F
iptables -t nat -F
iptables -t mangle -F
iptables -t nat -A PREROUTING -p tcp -i eth0 -d y.y.y.y --dport 3306 -j DNAT --to z.z.z.z:3306
iptables -A FORWARD -p tcp -i eth0 -d y.y.y.y --dport 3306 -j ACCEPT
iptables -t nat -A POSTROUTING -o eth0 -j MASQUERADE

This is the result of

Code:

tcpdump -l -n -i eth0 port 3306

in Z:

Code:

08:29:54.364275 IP y.y.y.y.41510 > z.z.z.z.mysql: S 224958559:224958559(0) win 5840 <mss 1460,sackOK,timestamp 1071510064 0,nop,wscale 0>
08:29:54.364297 IP z.z.z.z.mysql > y.y.y.y.41510: S 1243310729:1243310729(0) ack 224958560 win 5792 <mss 1460,sackOK,timestamp 1345070385 1071510064,nop,wscale 7>
08:29:54.365444 IP y.y.y.y.41510 > z.z.z.z.mysql: . ack 1 win 5840 <nop,nop,timestamp 1071510064 1345070385>
08:29:54.365572 IP z.z.z.z.mysql > y.y.y.y.41510: P 1:79(78) ack 1 win 46 <nop,nop,timestamp 1345070386 1071510064>
08:29:54.366540 IP y.y.y.y.41510 > z.z.z.z.mysql: . ack 79 win 5840 <nop,nop,timestamp 1071510064 1345070386>
08:29:55.833628 IP y.y.y.y.41510 > z.z.z.z.mysql: P 1:3(2) ack 79 win 5840 <nop,nop,timestamp 1071510211 1345070386>
08:29:55.833655 IP z.z.z.z.mysql > y.y.y.y.41510: . ack 3 win 46 <nop,nop,timestamp 1345071854 1071510211>
08:29:57.998865 IP y.y.y.y.41510 > z.z.z.z.mysql: P 3:9(6) ack 79 win 5840 <nop,nop,timestamp 1071510428 1345071854>
08:29:57.998904 IP z.z.z.z.mysql > y.y.y.y.41510: . ack 9 win 46 <nop,nop,timestamp 1345074020 1071510428>
08:29:57.998942 IP z.z.z.z.mysql > y.y.y.y.41510: P 79:99(20) ack 9 win 46 <nop,nop,timestamp 1345074020 1071510428>
08:29:57.998975 IP z.z.z.z.mysql > y.y.y.y.41510: F 99:99(0) ack 9 win 46 <nop,nop,timestamp 1345074020 1071510428>
08:29:57.998990 IP z.z.z.z.mysql > y.y.y.y.41510: R 100:100(0) ack 9 win 46 <nop,nop,timestamp 1345074020 1071510428>
08:29:58.000014 IP y.y.y.y.41510 > z.z.z.z.mysql: . ack 99 win 5840 <nop,nop,timestamp 1071510428 1345074020>
08:29:58.000023 IP z.z.z.z.mysql > y.y.y.y.41510: R 1243310828:1243310828(0) win 0

As above, the ip address of X is masked to Y (because of NAT/PREROUTING rule???) and Z only knows of Y ip address. So the question, is possible to modify the iptables in Y so mysql server in Z can understand the traffic coming from X?

Ok . i get it now. this is my configuration iptables list the i used for one of my friends to forward any HTTP,MYSQL and other services to an internal server. example as you said.
X = any IP
Y = iptables gateway and firewall. that has 2 NIC eth0=External and eth1=Internal
Z = internal Server, which hosts http and mysql.

feel free to modify and play around with this script. and tell me how is gose.

#################################################################

############### GATEWAY SETTINGS ...

### Flush and Delete Old Settings ...

service iptables restart
iptables -X
iptables -F

iptables -P INPUT DROP
iptables -P OUTPUT DROP

### " ALL HTTP TRAFFIC FROM Y, PORT (80) TO Z PORT (80) ...."
iptables -t nat -A PREROUTING -p tcp -i eth0 -d Y.Y.Y.Y --dport 80 -j DNAT --to Z.Z.Z.Z:80
iptables -A FORWARD -p tcp -i eth0 -d Z.Z.Z.Z --dport 80 -j ACCEPT

### " ALL MYSQL TRAFFIC FROM Y, PORT (3306) TO Z PORT (3306) ...."
iptables -t nat -A PREROUTING -p tcp -i eth0 -d Y.Y.Y.Y --dport 3306 -j DNAT --to Z.Z.Z.Z:3306
iptables -A FORWARD -p tcp -i eth0 -d Z.Z.Z.Z --dport 3306 -j ACCEPT

### "FORWARD PACKETS ON EH0 AND EH1 ...."
echo 1 > /proc/sys/net/ipv4/ip_forward

### "MASQUERADE ON EH0 FOR NAT ...."
iptables -t nat -A POSTROUING -o eth3 -j MASQUERADE

# DONE . let me know

# Take care
### Regards,
### MaaS

fotoguy · 02-20-2009, 02:22 AM

Try dropping the masquerading rule, this will always change the ipaddress to the gateway, which you obviously don't want.