[SOLVED] iptables problem with mysql connection from client program
Linux - NewbieThis Linux forum is for members that are new to Linux.
Just starting out and have a question?
If it is not in the man pages or the how-to's this is the place!
Notices
Welcome to LinuxQuestions.org, a friendly and active Linux Community.
You are currently viewing LQ as a guest. By joining our community you will have the ability to post topics, receive our newsletter, use the advanced search, subscribe to threads and access many other special features. Registration is quick, simple and absolutely free. Join our community today!
Note that registered members see fewer ads, and ContentLink is completely disabled once you log in.
If you have any problems with the registration process or your account login, please contact us. If you need to reset your password, click here.
Having a problem logging in? Please visit this page to clear all LQ-related cookies.
Get a virtual cloud desktop with the Linux distro that you want in less than five minutes with Shells! With over 10 pre-installed distros to choose from, the worry-free installation life is here! Whether you are a digital nomad or just looking for flexibility, Shells can put your Linux machine on the device that you want to use.
Exclusive for LQ members, get up to 45% off per month. Click here for more info.
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:
$ sudo service iptables status
Table: nat
Chain PREROUTING (policy ACCEPT)
num target prot opt source destination
Chain POSTROUTING (policy ACCEPT)
num target prot opt source destination
1 MASQUERADE all -- 0.0.0.0/0 0.0.0.0/0
Chain OUTPUT (policy ACCEPT)
num target prot opt source destination
Table: filter
Chain INPUT (policy ACCEPT)
num target prot opt source destination
1 ACCEPT all -- 0.0.0.0/0 0.0.0.0/0 state RELATED,ESTABLISHED
2 ACCEPT icmp -- 0.0.0.0/0 0.0.0.0/0
3 ACCEPT all -- 0.0.0.0/0 0.0.0.0/0
4 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 state NEW tcp dpt:22
5 REJECT all -- 0.0.0.0/0 0.0.0.0/0 reject-with icmp-host-prohibited
Chain FORWARD (policy ACCEPT)
num target prot opt source destination
1 ACCEPT all -- 0.0.0.0/0 0.0.0.0/0
Chain OUTPUT (policy ACCEPT)
num target prot opt source destination
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.
Distribution: Debian /Jessie/Stretch/Sid, Linux Mint DE
Posts: 5,195
Rep:
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
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.)
Distribution: Debian /Jessie/Stretch/Sid, Linux Mint DE
Posts: 5,195
Rep:
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.
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:
Quote:
111021 19:12:55 [Warning] IP address '192.168.1.22' could not be resolved: Name or service not known
111021 19:25:24 [Warning] IP address '192.168.1.13' could not be resolved: Name or service not known
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.)
Quote:
$ mysql -u root -h 192.168.1.22
ERROR 1130 (HY000): Host '192.168.1.13' is not allowed to connect to this MySQL server
$ mysql -u root -h 192.168.1.13
ERROR 1130 (HY000): Host '192.168.1.13' is not allowed to connect to this MySQL server
$ mysql -u root -h localhost
Welcome to the MySQL monitor. Commands end with ; or \g.
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:
Quote:
$ mysql -u root -h 192.168.1.22
ERROR 1130 (HY000): Host '192.168.1.22' is not allowed to connect to this MySQL server
$ mysql -u root -h localhost
Welcome to the MySQL monitor. Commands end with ; or \g.
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...
Distribution: Debian /Jessie/Stretch/Sid, Linux Mint DE
Posts: 5,195
Rep:
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:
Code:
sudo iptables -t nat -F #flush old rules
sudo iptables -t nat -A POSTROUTING -p tcp !--dport 3306 -j MASQUERADE
(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:
Code:
192.168.1.13 hostname.of.yourlaptop
The 192.168.1.13 must be the wlan0 ip address.
Add this line to /etc/my.cnf:
Code:
# skip-networking <<< comment out this line if it is present
bind-address= <IP of your wlan0>
Or alternatively
Code:
# skip-networking <<< comment out this line if it is present
bind-address= <IP of your eth0>
I am not too fond of setting the bind address to wlan0 as it might impose a security risk. Be aware of that.
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:
Distribution: Debian /Jessie/Stretch/Sid, Linux Mint DE
Posts: 5,195
Rep:
Quote:
Originally Posted by veeruk101
Your first suggestion worked -
That isn't much use, if it breaks routing, is it?
Quote:
Originally Posted by veeruk101
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
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?
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.
Distribution: Debian /Jessie/Stretch/Sid, Linux Mint DE
Posts: 5,195
Rep:
I have to admit I totally forgot about the permissions in MySQL itself. 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.
LinuxQuestions.org is looking for people interested in writing
Editorials, Articles, Reviews, and more. If you'd like to contribute
content, let us know.