Trying to connect to remote MySQL host (error 2003)

Question

I have a MySQL instance running on a Debian server and I can connect to it locally with no problems. However, I am unable to connect to it remotely. When I try this from my command line I get the following error:

ERROR 2003 (HY000): Can't connect to MySQL server on '<server-ip>' (110)

I've added the user to mysql as 'user'@'*' and 'user'@'localhost'. skip-networking in this server is set to false and the bind-address is commented out in my.cnf. I've also attempted to open port 3306 in iptables using the following command:

/sbin/iptables -A INPUT -i eth0 -p tcp --destination-port 3306 -j ACCEPT

Here is a listing of all my iptable firewall rules, which I retreived using iptables -L:

Chain INPUT (policy DROP)
target     prot opt source               destination         
ACCEPT     all  --  anywhere             anywhere            
REJECT     tcp  --  anywhere             anywhere            tcp dpt:auth reject-with icmp-port-unreachable 
ACCEPT     icmp --  anywhere             anywhere            icmp type 8 code 0 state NEW,RELATED,ESTABLISHED,UNTRACKED 
ACCEPT     all  --  anywhere             anywhere            state RELATED,ESTABLISHED 
ACCEPT     tcp  --  anywhere             anywhere            tcp spts:1024:65535 dpt:ftp state NEW 
ACCEPT     tcp  --  anywhere             anywhere            tcp spts:1024:65535 dpt:ssh state NEW 
ACCEPT     tcp  --  anywhere             anywhere            tcp spts:1024:65535 dpt:www state NEW 
ACCEPT     tcp  --  <my-server>          anywhere            tcp spts:1024:65535 dpt:mysql state NEW 
ACCEPT     tcp  --  anywhere             anywhere            tcp dpts:49152:65534 state NEW 
ACCEPT     tcp  --  anywhere             anywhere            tcp dpt:mysql 
LOG        all  --  anywhere             anywhere            limit: avg 5/min burst 5 LOG level debug prefix `iptables denied: ' 
ACCEPT     tcp  --  anywhere             anywhere            tcp dpt:mysql 
LOG        tcp  --  anywhere             anywhere            tcp dpt:mysql LOG level debug 

Chain FORWARD (policy ACCEPT)
target     prot opt source               destination

Chain OUTPUT (policy ACCEPT)
target     prot opt source               destination

Does anyone have any idea where I should go from here?

Grayback

what happens when you telnet to <server-ip> on 3306? – Quickel

@Mike: I get the message "telnet: unable to connect to remote host: Connection timed out". – Grayback

Error 2003 is detailed here: webyog.com/faq/content/23/15/en/… – Ingrain

+1 I love when you learn more from the question than the answers. Thanks – Sod

Answer 1

Based on your response, you need to find if there is a device between you and the server that blocks your connection. You should also ensure that you can telnet to 3306 on that server's ethernet address when logged into the server... if not, you probably have not got the service bound to the ethernet in my.cnf... see the bind-address parameter.

Quickel

Thanks for this. I think there is a device between me and the server. I've asked the question of the place where the server resides and am awaiting a response at the moment. I can telnet to 3306 when loged into the server, although the connection is closed pretty quickly. I'll post more as I find it out. – Grayback

I managed to get access through the intermediate device and I'm now able to connect remotely. Cheers! – Grayback

Answer 2

You should check your MySQL Server configuration to see if it bind to 127.0.0.1, then you can only connect to mysql if your app is on same server. There's an easy way, to have webamin installed and you can control via web interface.

Alliaceous

From the question "the bind-address is commented out" – Grayback

Answer 3

This may due to 3306 port is closed , check your server port status

http://www.yougetsignal.com/tools/open-ports/

If it shows closed, that means you can't access it from out side the machine , to open the port

ufw - Uncomplicated Firewall

The default firewall configuration tool for Ubuntu is ufw. The following are some examples of how to use ufw:

First, ufw needs to be enabled. From a terminal prompt enter:

sudo ufw enable

To open a port (mysql):

sudo ufw allow 3306

To see the firewall status, enter:

sudo ufw status

Immune

Answer 4

If you use VPN connection to connect remote database server and you got this kind of error then just check it out two simple steps...

1) In windows machine, please go to "Control Panel\Network and Internet\Network Connections" path and right click on local area network and go to properties. Click on TCP/IPv4 and go the properties. Make sure the IP and DNS server(if you use DHCP or not?).

2) After this please restart the Machine and check it out again.

Hope this will work. In my case it works.

Note: Please make sure all your settings are correct. This answer is funny but it works in my case :)

Thanks!

Lithiasis

Answer 5

for me this was to enable the port 3306 in /etc/default/iptables file to allow the incoming traffic from any host by uncommenting the below given line:

#-A INPUT -p tcp --dport 3306 -j ACCEPT

Hyacinthe