I'm having a difficult time calibrating my server to allow me to remote connect to mysql.
My server is a in house box connected to the internal network of our office.
I am attempting to connect to it using Heidisql.
Here are the steps I've followed:
Under the Webmin->Servers->Mysql Database Server menu
->User Permissions
The user has an entry for localhost as a host I've attempted to change it from localhost to any Result: Via ssh shell I was unable to log into mysql when it was set to any, gave me a Access denied for user @ localhost
I reverted the user back to localhost and added a new user with the same credentials with the host: 192.168.1.% Result: HeidiSql error
->Database Permissions Added two entries for database and username, first host = 'localhost' second one = '192.168.1.%' Result: HeidiSql error
-> Host Permissions Nothing
->Server Configurations With all the above in effect I changed the listnen address to 0.0.0.0. Result HeidiSql Error Reverted change
Webmin->Networking->Linux Firewall
Added Accept If protocol is TCP and destination port is 3306 and state of connection is NEW
So far I am still unable to remotly connect to mysql. Any ideas on how to configure this. I've tried several other forum topics on the issue but nothing brought home the prize.
HeidiSql error: SQL Error (2003): Can't connect to MySQL server on '192.168.1.200'
Disabled IPTABLES... Oh... it works now. >.<
Problem solved
Hi
Where and how did you disable "IPTABLES"?
Hi again
I just looked around the "IPTABLES". Why would you disable the FireWall on the VirtualMin setup?? It should be able to solve it without disabling "IPTABLES".
I am having the same problem, and it works when I stop "IPTABLES" but I would rather solve it without stopping the service.
To disable the IPTables I used the linux command (as root):
/etc/init.d/iptables stop
There are several reasons as to why I would disable the firewall on a virtualmin. Primarily I will limit it to my top two.
1st. The reason I nonchalantly disabled my firewall is because I'm on an internal network, theoretically there should be no way for the outside world to have access to my computer. Of course the computers on my internal network do though. So disabling my firewall for a few minutes is not mission critical, so to speak.
The second reason is that Many, many, many, many, MANY, many, Many, most Times. Whenever I've had difficulties setting up software on a server my issues 9 out of 10 times is that a necessary port is not open where it needs to be open. aka mis-configured firewall setting..
So in terms of troubleshooting, turning IPTABLES off gives you lots of information. If whatever you are trying to do still doesn't work with the firewall off, then you know that application is not working properly. If however it turns out it works with the firewall off, then you just saved hours of needless reinstalltion and reconfiguration of something that already works as it should. It is simply an unopen port that is causing you issues.
In the end, for me, it makes the difference between wasting hours trying to reconfigure something that works. Why for the likes of me it is still the last thing I think of when troubleshooting, I'll never know.
SO all that said and done.... If you are having the same issue I had and it works when IPTABLES is off then you know the issue lies in your IPTABLE Rules. Now that you've identified the issue, turn your iptables back on please.
Again since I'm on an internal network I simply opened port 3306 to everyone. However you will probably wish to open this port to the specific ip address of the machine you wish to have mysql remote access.
On my virtualmin control panel the rule I added looks like:
Accept If protocol is TCP and destination port is 3306 and state of connection is NEW On the actual IPTable file it looks like:
-A INPUT -p tcp -m tcp -m state --dport 3306 --state NEW -j ACCEPT
But remember you will most likely want to limit it to a specific port. And move the rule above any rejects just incase some of them cause any issue... YOURS SHOULD LOOK MORE LIKE THIS:
If protocol is TCP and source is 192.168.1.125 and destination port is 3306 and state of connection is NEW
and
-A INPUT -p tcp -m tcp -m state -s 192.168.1.125 --dport 3306 --state NEW -j ACCEPT
Remember to restart IPtables after making the new rule.
/etc/init.d/iptables start
or alternatively
/etc/init.d/iptables restart
Hi Again
Thanks for your explanation :) I agree that it's an easy why to troubleshoot on, by disabling the firewall.
I solved the issue by going through the firewall rules, and I noticed that I had placed the MySQL accept rule below the last incoming rule called "Run chain RH-Firewall-1-INPUT Always". I moved the rule above this one, and now it works when "IPTABLES" (firewall) is running :)
Note that you can also simply use VirtualMin/Webmin to easily control/shut down iptables via SYSTEM / Bootup and Shutdown