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.

I need to access mysql from another server using a custom port and restrict access to this other servers IP. How do I get this done?

If I understand correctly I should forward the traffic that comes to lets say port 33306 to 127.0.0.1:3306 by something like this:

iptables -t nat -A PREROUTING ! -i lo -p tcp -m tcp -d 127.0.0.1 --dport 33306 -j REDIRECT --to-port 3306

This is not working and I don't get why.

My iptables look like this:

Chain PREROUTING (policy ACCEPT 149 packets, 8788 bytes)
num   pkts bytes target     prot opt in     out     source               destination         
1        0     0 REDIRECT   tcp  --  !lo    *       0.0.0.0/0            127.0.0.1            tcp dpt:33306 redir ports 3306

Chain INPUT (policy ACCEPT 0 packets, 0 bytes)
num   pkts bytes target     prot opt in     out     source               destination         
1    22271   10M ACCEPT     tcp  --  eth0   *       0.0.0.0/0            0.0.0.0/0            tcp spts:1024:65535 dpt:443 state NEW,RELATED,ESTABLISHED
2     8494 7799K ACCEPT     all  --  lo     *       0.0.0.0/0            0.0.0.0/0           
3    10012 1177K ACCEPT     all  --  *      *       0.0.0.0/0            0.0.0.0/0            ctstate RELATED,ESTABLISHED
4        7   352 ACCEPT     tcp  --  *      *       0.0.0.0/0            0.0.0.0/0            tcp dpt:80
5        3   117 ACCEPT     icmp --  *      *       0.0.0.0/0            0.0.0.0/0            icmptype 8
6        5   772 ACCEPT     all  --  tun+   *       0.0.0.0/0            0.0.0.0/0           
7        2   140 ACCEPT     udp  --  *      *       0.0.0.0/0            0.0.0.0/0            udp dpt:1194
8        1    40 DROP       all  --  *      *       0.0.0.0/0            0.0.0.0/0           

Chain FORWARD (policy ACCEPT 0 packets, 0 bytes)
num   pkts bytes target     prot opt in     out     source               destination         
1        0     0 ACCEPT     all  --  tun+   *       0.0.0.0/0            0.0.0.0/0           

Chain OUTPUT (policy ACCEPT 3338 packets, 1926K bytes)
num   pkts bytes target     prot opt in     out     source               destination

mysql is listening:

netstat -tulpn | grep LISTEN
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      1028/mysqld     
share|improve this question

2 Answers 2

up vote 2 down vote accepted

The most immediate problem is that your NAT rule applies only to traffic destined for 127.0.0.1 but not coming in the loopback interface. Pretty much by definition, there won't be any such traffic. Remove the -d 127.0.0.1 from your PREROUTING rule.

Your second problem is that mysqld is only listening on the loopback interface. You'll need to get it to listen on the external NIC as well, if you want to talk to an external system. I don't, offhand, know how to do that; but you want the output from netstat to look more like

tcp      0    0 0.0.0.0:3306              0.0.0.0:*                 LISTEN      

Your third problem will be that adding a NAT rule for port-forwarding doesn't implicitly punch that forwarded traffic through the firewall. Having redirected incoming traffic on TCP/33306, you'll still need to allow the resulting redirected traffic through the INPUT chain, with something like

iptables -I INPUT 8 -p tcp --dport 33306 -s a.b.c.d -j ACCEPT

where a.b.c.d is the IP address of the server you want to be able to so communicate.

Re-edit: OK, you've decided that mysqld can only bind to localhost, but you're willing to get rid of the 33306/3306 distinction, and have the incoming client connect to port 3306.

That given, I think you have a problem; we can't use SNAT to rewrite the packets as if they came from localhost, because the traffic isn't leaving the box, so the packets won't pass through the nat table's POSTROUTING chain, which is the only place SNAT is a valid target. I don't think you can easily do this with iptables, and that either an SSH connection or a VPN which provides logical interfaces, such as OpenVPN, will be needed; sorry.

share|improve this answer
    
thank you. sure I messed up destination. But isn't there a way to let mysql like it is, I mean bound to 127.0.0.1 and forward the outer port there? –  pauel Apr 19 '13 at 10:48
    
Pauel, forgive me for saying this if you already know it, but local etiquette is that once you're happy with an answer to your question, you accept it by ticking the tick outline next to it. That drives SF's reputation system for the both of us. I note you've been a member for some time, but you've not asked a qustion before, so hopefully my comments not too out-of-place. –  MadHatter Apr 19 '13 at 10:50
    
I'm relatively new to serverfault but I get that. my issue is not really fixed. changing the mysql config to listen to 0.0.0.0 is not an option, so I still need to forward or route the traffic to 127.0.0.1:3306 somehow. I'm not a pro in iptables :-) Thx for your help –  pauel Apr 19 '13 at 10:54
    
I understand you're not fixed yet; acceptance applies only when you're happy. That said, this requirement to have mysqld listen only on localhost is complicating matters; see edit above. –  MadHatter Apr 19 '13 at 10:56
    
Maybe I should reconsider. I have mysql on 127.0.0.1:3306 and can't change that. So what about your suggestion to open 3306. There would still be the problem that mysql is not listening? –  pauel Apr 19 '13 at 11:12

I found out that it's not a good idea to mess up things like I tried before and changed etc/mysql/my.cnf to fit a normal setup like:

# bind to all ip's
bind to 0.0.0.0

I also restricted the connections for port 3306 to an IP:

iptables -I INPUT -i eth0 -p tcp --dport 3306 --src xxx.x.x.xx -j ACCEPT

Drop all traffic to that port that is not allowed:

iptables -I INPUT -i eth0 -p tcp --dport 3306 -j DROP

If someone has a better and more secure way to get it done please let me know.

share|improve this answer

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.