How do I allow remote MySQL access to a single, static IP?

Solution 1:

EDIT: For the instructions below if you are using Windows then there are two things to keep in mind:

1) Change localhost to 127.0.0.1 since Windows doesn't have that set up automatically in %SystemRoot%\system32\drivers\etc\hosts.

2) You can use the little-known plink.exe command-line tool from the PuTTY suite of tools; it uses the same syntax for options as the ssh command so if you replace ssh in the examples below with plink.exe it should all work.

You'll want to use an SSH tunnel to forward a local port on your client to the mysql port on the server. You can do that with:

% ssh -f -N -L3306:localhost:3306 username@remoteserver
% mysql -h remoteserver -u mysqluser -p

The options to ssh mean:

-f Requests ssh to go to background just before command execution.
-N Do not execute a remote command.
-L [bind_address:]port:host:hostport
   Specifies that the given port on the local (client) host is to be 
   forwarded to the given host and port on the remote side.

Using -f -N also means that you will have forked an ssh process into the background rather than staying connected to the remote host the way you usually would when logged into a remote shell. If you want to tear down the tunnel you can kill the ssh process on your client like this:

% pgrep -fl ssh
11145 ssh -f -N -L3306:localhost:3306 username@remoteserver
% kill -9 11145

Of course in this case 11145 is the PID of the ssh process which will be different each time you start a new ssh process to open a tunnel.

Also, this assume that you do not have the mysql server also running on your client. If so you'll need to change the local port that you bind to like this:

% ssh -f -N -L3333:localhost:3306 username@remoteserver
% mysql -P 3333 -h remoteserver -u mysqluser -p

The port 3333 is arbitrary; you can pick any free port number that your client has.

Solution 2:

Using -s 192.168.100.0/24 means that you only allow access to port 3306 remotely for IP ranges matching 192.168.100.0/24, which is a private internal network. Is this really what you intend to do? Otherwise this is the problem why remote connections from other IPs doesn't work.

You also don't usually need the outgoing rule.

If this doesn't help, please provide a little bit more information on where you're testing from, IPs/interfaces of the machine, what happens and perhaps a full output of iptables -vnL for us?

Edit 1:
Based on more information it shows that the example used as template was misunderstood, you have to remove the source IP range (because you want to allow everyone remotely). Just type this, and only this, and it should work:

iptables -A INPUT -i eth0 -p tcp --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT

If you want to include your external IP it has to be the destination IP, such as:

    iptables -A INPUT -i eth0 -p tcp -d xx.xx.xx.xx --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT

-s, as in source, is only used if you want to limit access from specific IP ranges.

Edit 2:
Doesn't seem to be an iptables question at all, but more of a mySQL one. To allow mySQL to listen to remote connections at all, make sure to configure it to listen to your external address. Edit /etc/mysql/my.cnf and check for the bind-address statement and change it to:

bind-address = xx.xxx.xx.xx

Either you chose to replace xx.xx.xx.xx with your external IP address, or you can set it to listen to 0.0.0.0 which means it will listen to all interfaces.

After that the question for you is how to setup the entire firewall for your server in the first place. Either you manually block all specific ports that no one else should be able to get to, or you have to set a default policy to reject traffic and then manually open port by port (as your initial question indicated) for services you want to allow. Be VERY careful with this though, if its a remote machine its very easy to lock yourself out if you put things the wrong order or the wrong way.

Solution 3:

The -A switch to iptables adds the new rules to then end of the chain. It's likely that you have an earlier rule that is denying access and with iptables the first match wins. Try using the -I switch to insert the rule at the beginning of the chain.

If that doesn't work please show us the output of iptables -L -v -n as an edit to your question.

Solution 4:

Your default policy for both INPUT and OUTPUT is ACCEPT. You have not defined any rules to drop or reject packets. This means your firewall is not blocking any connections.

The problem is somehere else.

Use netstat -tan | grep LISTEN to make sure you are running MySQL and that it is listening.

If that works, there is probably another firewall somewhere that is blocking the connection.