iptables problem with mysql connection from client program
 

I can always connect to my local installation of mysql using the command 'mysql -u root'. However, I can only connect to it from my client program (Navicat) when iptables is disabled or uses the default rules it came with. When I apply the following rules to my iptables, I can still connect using the mysql command, but in Navicat I get the following error message: "1130 Host '192.168.0.44' is not allowed to connect to this MySQL server". The rules are:

This results in the following iptables status.

How can I change the rules I run for iptables to ensure that I can connect to MySQL from the client program? And also, why might this be a problem with the client program and not the command-line client program? I think my MySQL permissions are set up correctly because everything works when iptables is disabled or has the default rules.

Do you mean you CAN connect using the mysql client:
- from localhost (the same host as running the mysql server)
- from another host (the one running Navicat)

You canNOT connect using Navicat
- from the localhost
- from another host

What is the IP of the host running mysql
What is the IP of the client running Navicat

jlinkels

What's weird about this problem is that there is only 1 computer involved. My Linux laptop (localhost) which has a MySQL server running on it, as well as a MySQL command-line client program as well as a GUI client program (Navicat). The internal IP of the local machine is 192.168.0.44.

Something about the way Navicat connects to MySQL causes it to stop working when my iptables is set up the way I've described above (because there's no problem when iptables is disabled or uses the default setup, before I run the iptables commands I showed in my OP.)

Clear.

Try this:
mysql -u root -p localhost
mysql -u root -p 192.168.0.44

(I hope one of the two fails)

What is the IP of wlan0?

Do you see something in /var/log/mysql.log?

Also, find my.cnf (most likely /etc/my.cnf and see what the bind-address is.

I suspect that somehow the Navicat client gets the source address of the other interface (you are routing between eth0 and wlan0) and this source address is used for attempting to connect with mysql.

jlinkels

Very interesting, you're right. I don't know how to solve it though - here's some more information. /etc/my.cnf doesn't have much in it, no bind-address. /var/log/mysql.log has the following after the errors show up:

When I plugged my laptop directly into the modem eth0 was 192.168.1.13 and wlan0 was 192.168.1.22. Now here's what's interesting. Connecting to 'localhost' always works, but whether I entered 192.168.1.22 or 192.168.1.13, the error message was always with '192.168.1.13'. I'm new to this and to be honest I don't know what the implications of 'routing' between the 2 networks are. (All I know is I needed to do so in order for a device directly connected to my computer with a crossover cable to access the internet.)

But when I unplug from the router and only have wireless, wlan0 is still 192.168.1.22, and then I get the error message showing 192.168.1.22:

What I'm hoping to know first of all is how to resolve this, but also if someone could explain on a conceptual level what's going on here so that I understand, it would help my learning process as I'm still relatively new to all this, particularly the networking side of things...

I can't follow exactly what you say about eth0 plugging directly into the modem or not. This doesn't match your previous post telling that your laptop had IP 192.168.0.44.

Let me assume your eth0 has a fixed address on 192.168.0.44 and your wlan0 is 192.168.1.13. The device connected to your laptop must be 192.168.0.12.

(Now when you started talking about device connected to your laptop I see that I answered a post of yours before on routing. :) )

IPtables does masquerading, that means any address not found on the local subnet is being translated to the address of wlan0 and then sent onto the internet. What comes back to wlan0 is catched by IPtables, translated back to 192.168.0.nnn and sent to the device it was originating from.

What most likely happens is that Navicat uses an IP address which is translated first to 192.168.1.xxx before it connects to MySQL.

If you configure Navicat, what address of MYSql do you enter? A name or IP?


111021 19:12:55 [Warning] IP address '192.168.1.22' could not be resolved: Name or service not known

Is this the error which is logged when you start Navicat?

Can you make Navicat go to localhost instead?

Some crude experiments do them one by one, in this sequence:

Change the masquerade statement to this:
(After this UDP might stop working correctly, like DNS. If so, we'll solve that later)

Furthermore if MySQL complains about reverse DNS, try to add this to /etc/hosts:
The 192.168.1.13 must be the wlan0 ip address.

Add this line to /etc/my.cnf:
Or alternatively
I am not too fond of setting the bind address to wlan0 as it might impose a security risk. Be aware of that.

Let's see what happens.

jlinkels

Your first suggestion worked - when I enter the following commands, I can connect to Navicat but the device connected to my laptop can no longer connect to the internet, so the routing seems to have been broken. However, there didn't seem to be any DNS problems (meaning I can still ping google.com from my laptop) or anything similar that you warned could appear. And MySQL doesn't seem to complain about reverse DNS. Thanks for your help, it seems to be getting closer to a solution where I can connect to Navicat and the device can connect to the internet.

And when I enter the following commands that I was using originally, the device connected to my laptop can connect to the internet, but I can't connect to Navicat:

(In Navicat, I enter 'localhost' as the host to connect to, rather than using an IP address or anything else.)

That isn't much use, if it breaks routing, is it?

Can you confirm that Navicat connects to MySQL and that "I can connect to Navicat" is an incorrect expression? If the expression is correct, what/how connects to Navicat?

Does the "device" play any role in the MYSql/Navicat setup or you just want to connect the device and use internet thru that laptop?

Can you restore the IPTables situation where the device can connect to internet and try the bind-address suggestions in the my.cnf file?

jlinkels

You're right, I meant to say 'Navicat connects to MySQL' rather than 'I can connect to Navicat'. The device doesn't play any role in the MySQL/Navicat setup. I restored the iptables setting where the device can connect to the internet, but the bind-address suggestions didn't work.

However, the following did - adding a record to mysql.user with host set to '192.168.%', user set to 'root', and everything else the same as the record with host set to 'localhost' and 'user' set to root. Now I don't know whether this is the right way to go about solving this problem, because it would seem this would just be a symptom of some other misconfiguration on my part. But it works... I'm stumped.

Perhaps in my other thread if I were to explore the option you suggested of using the 'route' command to make the device access the internet, then I wouldn't need to mess with iptables and then I could likely remove this new record from the mysql.user table.

I have to admit I totally forgot about the permissions in MySQL itself. :redface: Maybe because I allow connections from all hosts, and I use the bind-address to control the networks from which connections are allowed.

There should be no reason why Navicat presents itself with a different IP address than localhost. But since it does there is nothing wrong with allowing it to connect to MySQL using that IP address. Just make sure the 3306 port is not allowed to pass through in your internet modem.

Good error finding.

jlinkels