?
Solved

iptables probleme to make a connection with mysql

Posted on 2006-05-04
11
Medium Priority
?
471 Views
Last Modified: 2010-04-22
Hi folks,

here is my configuration  :
Internet
                  |
                  |
                  |
            ***** Router******************
            *  Public Ip (x3) on eth0                     *
            * ---------------------------                  *
            *                                                     *
            * Private Ip :                                    *
            *      1: 192.168.10.51                           *
            *      2: 192.168.10.53                      *
            *      3: 192.168.10.54                           *            
              *                                               *
            * Gateway :                                    *
            *      4: 192.168.10.100                    *      
            *******************************
                  |
                  |
                  |
                  --------------------------------------------------...etc ...
                  |                |                  |
                  |                |                  |
                **********            *********          ***************      
                * SRV#1     *         * SRV#2   *               *      Station                *
                * Ip #1       *           * Ip #2     *            *  Ip priv. x            *      
                *                   *            *             *         *                           *            
                **********            *********          ***************

So , I did DNAT and SNAT  with Iptables on the router (a former Debian Sarge machine), so everything is ok :
- every server have ther own DNS server, mysql server, pop and smtp server, web sverver etc ..;
- the router is also (for instance) a secondary dns server
- from outside, i can connect to all services on the servers
- from inside also

But, when I use Dreamweaver , i configure the site (all is ok, ftp etc ...), but when i try to configure the mysql connection, it say to me "2013 lost connection during query" when I specify the mysql server with the public IP (it do that from the LAN and from the internet.

It is ok if I specify the private IP from the station on the lan, it is ok if I specify the FQDN of the site i am working on from inside the LAN and outside (but I must put the FQDN in the hosts file of the server)

This is quite annoying because I must complete the Hosts file for all the site that are on the server.....


any Idea to resolv this prob ?

regards,

Tazman

0
Comment
Question by:Tazman_FR
  • 4
  • 4
9 Comments
 
LVL 19

Expert Comment

by:Gabriel Orozco
ID: 16608470
I think you need to DNAT from each machine so any connection get's back to it.


maybe you will need also to SNAT to appear that request is coming from the router. just check if it is needed or not (i think yes).

iptables -A PREROUTING -t nat -d public.ip -s internal.ip -DNAT --to-destination internal.ip
0
 

Author Comment

by:Tazman_FR
ID: 16608986
Hi,

Here are the rules I have on the router :

#######################################
# eth0 = ethernet card with public IP
# eth1 = ethernet card with private IP
$SRV_Pub_IP = 194.201.180.115
$SRV_Priv_IP = 192.168.10.53
$Router_Pub_IP = 194.201.180.110
$priv_IP_range = 192.168.10.0/24


#Load connection tracking for the ftp (in case of it is not compiled)

modprobe ip_conntrack_ftp
modprobe ip_nat_ftp
modprobe ip_conntrack

#Masquerading all comming out the external IP
iptables -t nat -A POSTROUTING -o eth0 -j MASQUERADE

#Forwarding the connection already established
iptables -A FORWARD -m state --state ESTABLISHED,RELATED -j ACCEPT

iptables -t nat -A POSTROUTING -p tcp --dport 80 -s $priv_IP_range -d $priv_IP_range  -j SNAT --to $Router_Pub_IP

#routing the http connections :
iptables -t nat -A PREROUTING -p tcp -d $SRV_Pub_IP -i eth0 --dport 80 -j DNAT --to $SRV_Priv_IP:80
iptables -t nat -A PREROUTING -p tcp -d $SRV_Pub_IP -i eth1 --dport 80 -j DNAT --to $SRV_Priv_IP:80
iptables -t nat -A OUTPUT --dst $SRV_Pub_IP -p tcp --dport 80 -j DNAT --to-destination $SRV_Priv_IP:80
iptables -A FORWARD -i eth0 -o eth1 -d $SRV_Priv_IP -p tcp --syn --dport 80 -j ACCEPT

#routing the ftp connection
echo "[Routage Corail FTP 230:21/20 -> 39:21/20]"
iptables -t nat -A PREROUTING -p tcp -d $SRV_Pub_IP --dport 21 -i eth0 -j DNAT --to $SRV_Priv_IP:21
iptables -t nat -A PREROUTING -p tcp -d $SRV_Pub_IP --dport 20 -i eth0 -j DNAT --to $SRV_Priv_IP:20
iptables -t nat -A PREROUTING -p tcp -d $SRV_Pub_IP --dport 21 -i eth1 -j DNAT --to $SRV_Priv_IP:21
iptables -t nat -A PREROUTING -p tcp -d $SRV_Pub_IP --dport 20 -i eth1 -j DNAT --to $SRV_Priv_IP:20
iptables -A FORWARD -i eth0 -o eth1 -d $SRV_Priv_IP -p tcp --syn --dport 21 -j ACCEPT
iptables -A FORWARD -i eth0 -o eth1 -d $SRV_Priv_IP -p tcp --syn --dport 20 -j ACCEPT

#routing the mysql connection
echo "[Routage Corail tcp 230:3306 -> 53:3306]"
iptables -t nat -A PREROUTING -p tcp -d 19$SRV_Pub_IP --dport 3306 -i eth0 -j DNAT --to $SRV_Priv_IP:3306
iptables -A FORWARD -i eth0 -o eth1 -d $SRV_Priv_IP -p tcp --syn --dport 3306 -j ACCEPT

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


I do not have any rules on the server 192.168.10.53

so You mean I may have a rule on the server also ?

regards,

tazman

0
 
LVL 19

Expert Comment

by:Gabriel Orozco
ID: 16609819
Looking at your diagram, and then reading your firewall script raises some doubs:

- what is the main purpose of your setup? sharing many internal web servers?
- on your script you take care only on one pc
- if you are sharing many internal webservers, do you have sufficient external ip addressess so you can do a one-by-one NAT?
- I also noted some errors on your iptables script. Did you copy-paste it, or they are finger errors?


- for your mysql problem only: did you check on mysql permissions? the user should have rights from "%" (mean anywhere)
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:Tazman_FR
ID: 16610085

>what is the main purpose of your setup? sharing many internal web servers?
the main purpose of this script is to have one server acting as a router / firewall for other "hidden" server on private IP

>on your script you take care only on one pc
this is the code for only one server (and some services on it ), this router will act for 6 servers, and grant access for the remote desk for XP workstations

>- if you are sharing many internal webservers, do you have sufficient external ip addressess so you can do a one-by-one NAT?
yes, I do , I have dedicated server with one IP for each,

>I also noted some errors on your iptables script. Did you copy-paste it, or they are finger errors?
I copy pasted the script (only a part of it, concerning the rules for this server), and replace the IP with some variables, but if you thing there is some errors, please let me know !

>for your mysql problem only: did you check on mysql permissions? the user should have rights from "%" (mean anywhere)
yes, I checked the my.cnf and commented the "#bind-address            = 127.0.0.1" line, and the user is granted for anywhere (rights from %)

0
 
LVL 19

Expert Comment

by:Gabriel Orozco
ID: 16610355
mmhh... okay
let me study your script so I can propose

but meanwhile: do you have mysqlcc ? can you access from an outside IP to the mysql server for your ip?
0
 

Author Comment

by:Tazman_FR
ID: 16610448
>but meanwhile: do you have mysqlcc ? can you access from an outside IP to the mysql server for your ip?
yes I do, from another server outside (from home) I can access to the Mysql server Mysql with mysqlclient
0
 
LVL 7

Expert Comment

by:XoF
ID: 16672872
> #routing the mysql connection
> echo "[Routage Corail tcp 230:3306 -> 53:3306]"
> iptables -t nat -A PREROUTING -p tcp -d 19$SRV_Pub_IP --dport 3306 -i eth0 -j DNAT --to $SRV_Priv_IP:3306
                                                            ^^^^
Is this just a typo in your posting or is it actually in your ruleset?
0
 

Author Comment

by:Tazman_FR
ID: 16672913
Hi again,

Yes Xof, this is just a typo in the posting, the rule is :
 iptables -t nat -A PREROUTING -p tcp -d $SRV_Pub_IP --dport 3306 -i eth0 -j DNAT --to $SRV_Priv_IP:3306

regards,

Tazman
0
 
LVL 19

Accepted Solution

by:
Gabriel Orozco earned 2000 total points
ID: 16679869
#######################################
# eth0 = ethernet card with public IP
# eth1 = ethernet card with private IP
$SRV_Pub_IP = 194.201.180.115
$SRV_Priv_IP = 192.168.10.53
$Router_Pub_IP = 194.201.180.110
$priv_IP_range = 192.168.10.0/24


#Load connection tracking for the ftp (in case of it is not compiled)

modprobe ip_conntrack_ftp
modprobe ip_nat_ftp
modprobe ip_conntrack

#Masquerading all comming out the external IP
iptables -t nat -A POSTROUTING -o eth0 -j MASQUERADE

#Forwarding the connection already established
iptables -A FORWARD -m state --state ESTABLISHED,RELATED -j ACCEPT


# --> This rule maybe is not being used since from LAN to LAN packets never traverse the firewall:
iptables -t nat -A POSTROUTING -p tcp --dport 80 -s $priv_IP_range -d $priv_IP_range  -j SNAT --to $Router_Pub_IP

#routing the http connections :
iptables -t nat -A PREROUTING -p tcp -d $SRV_Pub_IP -i eth0 --dport 80 -j DNAT --to $SRV_Priv_IP:80
iptables -t nat -A PREROUTING -p tcp -d $SRV_Pub_IP -i eth1 --dport 80 -j DNAT --to $SRV_Priv_IP:80
# --> DNAT does not occur in OUTPUT rule. DNAT can only be done at PREROUTING. delete this line:
iptables -t nat -A OUTPUT --dst $SRV_Pub_IP -p tcp --dport 80 -j DNAT --to-destination $SRV_Priv_IP:80
iptables -A FORWARD -i eth0 -o eth1 -d $SRV_Priv_IP -p tcp --syn --dport 80 -j ACCEPT

#routing the ftp connection
echo "[Routage Corail FTP 230:21/20 -> 39:21/20]"
iptables -t nat -A PREROUTING -p tcp -d $SRV_Pub_IP --dport 21 -i eth0 -j DNAT --to $SRV_Priv_IP:21
iptables -t nat -A PREROUTING -p tcp -d $SRV_Pub_IP --dport 20 -i eth0 -j DNAT --to $SRV_Priv_IP:20
iptables -t nat -A PREROUTING -p tcp -d $SRV_Pub_IP --dport 21 -i eth1 -j DNAT --to $SRV_Priv_IP:21
iptables -t nat -A PREROUTING -p tcp -d $SRV_Pub_IP --dport 20 -i eth1 -j DNAT --to $SRV_Priv_IP:20
iptables -A FORWARD -i eth0 -o eth1 -d $SRV_Priv_IP -p tcp --syn --dport 21 -j ACCEPT
iptables -A FORWARD -i eth0 -o eth1 -d $SRV_Priv_IP -p tcp --syn --dport 20 -j ACCEPT

#routing the mysql connection
echo "[Routage Corail tcp 230:3306 -> 53:3306]"
iptables -t nat -A PREROUTING -p tcp -d 19$SRV_Pub_IP --dport 3306 -i eth0 -j DNAT --to $SRV_Priv_IP:3306
iptables -A FORWARD -i eth0 -o eth1 -d $SRV_Priv_IP -p tcp --syn --dport 3306 -j ACCEPT

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


My suggestion:
#######################################
# eth0 = ethernet card with public IP
# eth1 = ethernet card with private IP
$SRV_Pub_IP = 194.201.180.115
$SRV_Priv_IP = 192.168.10.53
$Router_Pub_IP = 194.201.180.110
$priv_IP_range = 192.168.10.0/24


#Load connection tracking for the ftp (in case of it is not compiled)
modprobe ip_conntrack_ftp
modprobe ip_nat_ftp
modprobe ip_conntrack

#Masquerading all comming out the external IP
iptables -t nat -A POSTROUTING -o eth0 -j MASQUERADE

#Forwarding from inside to outside allowed:
iptables -A FORWARD -i eth1 -o eth0 -j ACCEPT

#Forwarding the connection already established
iptables -A FORWARD -m state --state ESTABLISHED,RELATED -j ACCEPT

# This is for HTTP on SRV_Pub_IP:
iptables -t nat -A PREROUTING -p tcp -d $SRV_Pub_IP --dport 80 -j DNAT --to $SRV_Priv_IP:80
iptables -A FORWARD -i eth0 -o eth1 -d $SRV_Priv_IP -p tcp --syn --dport 80 -j ACCEPT
# (All other things required are managed by the ESTABLISHED,RELATED forward rule)

#routing the ftp connection
echo "[Routage Corail FTP 230:21/20 -> 39:21/20]"
iptables -t nat -A PREROUTING -p tcp -d $SRV_Pub_IP --dport 21 -j DNAT --to $SRV_Priv_IP:21
iptables -A FORWARD -i eth0 -o eth1 -d $SRV_Priv_IP -p tcp --syn --dport 21 -j ACCEPT
# (All other things required are managed by the ESTABLISHED,RELATED forward rule)

#routing the mysql connection
echo "[Routage Corail tcp 230:3306 -> 53:3306]"
iptables -t nat -A PREROUTING -p tcp -d $SRV_Pub_IP --dport 3306 -j DNAT --to $SRV_Priv_IP:3306
iptables -A FORWARD -i eth0 -o eth1 -d $SRV_Priv_IP -p tcp --syn --dport 3306 -j ACCEPT
# (All other things required are managed by the ESTABLISHED,RELATED forward rule)
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

​Being a Managed Services Provider (MSP) has presented you  with challenges in the past— and by meeting those challenges you’ve reaped the rewards of success.  In 2014, challenges and rewards remain; but as the Internet and business environment evol…
Hello EE, Today we will learn how to send all your network traffic through Tor which is useful to get around censorship and being tracked all together to a certain degree. This article assumes you will be using Linux, have a minimal knowledge of …
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question