Can't connect to MySql server on remote server

Question

• Connecting to: CentOS v?? with MySql 4.?? in Nashville (example public ip=11.11.11.11)

  Stack: Cable modem as gateway > router with forwarding > CentOs Box

• Connecting from: Windows 2003 R2 in Dallas (example public ip=22.22.22.22)

I'm trying to access my MySql server from a remote server through my public IP. When I try to connect, I get an error along the lines of "could not open a connection to the host" after several seconds. It doesn't have the chance to ask me for login credentials. Here are some of the things I've done toward opening this server up for remote connections:

• Opened port 3306 on my router and forward it to the local IP address of my MySql box.
• Followed the instructions in this blog entry.
• Created a user in MySql with access to my desired database ([email protected]). ** Of course, it doesn't even get this far... just thought I'd include this.

When I try to telnet from the box in Dallas to the box in Nashville at port 3306, I get this:

C:\Documents and Settings\Administrator>telnet 11.11.11.11 3306
Connecting To 11.11.11.11...Could not open connection to the host, on port 3306: Connect failed

However, I can ping 11.11.11.11 from 22.22.22.22 (sorry for the bad formatting... i tried):

C:\Documents and Settings\Administrator>ping 11.11.11.11

Pinging 11.11.11.11 with 32 bytes of data:

Reply from 11.11.11.11: bytes=32 time=57ms TTL=54 Reply from 11.11.11.11: bytes=32 time=61ms TTL=54 Reply from 11.11.11.11: bytes=32 time=56ms TTL=54 Reply from 11.11.11.11: bytes=32 time=57ms TTL=54

Ping statistics for 11.11.11.11: Packets: Sent = 4, Received = 4, Lost = 0 (0% loss), Approximate round trip times in milli-seconds: Minimum = 56ms, Maximum = 61ms, Average = 57ms

The IP tables for 11.11.11.11 read:

[root@centos ~]# /sbin/iptables -L Chain INPUT (policy ACCEPT) target
prot opt source
destination
RH-Firewall-1-INPUT all -- anywhere anywhere
tcp -- 22.22.22.22 anywhere tcp dpt:mysql

Chain FORWARD (policy ACCEPT) target
prot opt source
destination
RH-Firewall-1-INPUT all -- anywhere anywhere

Chain OUTPUT (policy ACCEPT) target
prot opt source
destination

Chain RH-Firewall-1-INPUT (2 references) target prot opt source destination ACCEPT all -- anywhere anywhere ACCEPT icmp -- anywhere
anywhere icmp any ACCEPT
esp -- anywhere anywhere ACCEPT ah -- anywhere
anywhere ACCEPT udp -- anywhere 224.0.0.251 udp dpt:mdns ACCEPT udp -- anywhere anywhere
udp dpt:ipp ACCEPT tcp -- anywhere anywhere
tcp dpt:ipp ACCEPT all -- anywhere anywhere
state RELATED,ESTABLISHED ACCEPT
tcp -- anywhere anywhere state NEW tcp dpt:mysql ACCEPT
tcp -- anywhere anywhere state NEW tcp dpt:ssh REJECT all -- anywhere anywhere reject-with icmp-host-prohibited

@Sunny: Right now, the local ip address is listed in /etc/my.conf. To see what happened, I replaced the local ip address with 11.11.11.11. When I did that, MySql could not start back up. I guess the bind-address has to be an ip address actually in the box.

I CAN connect to MySql from another machine on the same network using local ip address. However, even with local, I cannot connect using 11.11.11.11.

Any ideas? Not being a linux guy, I'm a little blind.

Solution

Check my.conf and what bindings MySql server has.

It may be so, that it listens only on the local network.

You are looking for bind-address setting in /etc/my.conf or /etc/mysql/my.conf.

If this setting is enabled (usually to bind to only localhost 127.0.0.1), comment it out, and it will allow MySql to listen on all available interfaces.

In any case, accessing the database server directly over Internet is not a good idea at all.

I would strongly advice you to use ssh tunneling to access the machine over Internet.

On windows, you can use putty to ssh (creating a tunnel) into the MySql machine. You can forward your local 3306 port to the remote one, and then you can access the MySql server using localhost:3306.

Comments

[Admin]

+1 - very well formed question

[Byron Sommardahl]

@Sunny, I updated the question with my response to your answer. Basically, I don't think this will work since the MySql box is not multi-homed. The public IP resides on a router.