7

I'm trying to enable remote access to MySQL over port 3306 but having no luck. I've been given root access to a production machine so that I can grab files and databases, work on them locally, and put modified versions back.

My access allows me to SSH into the box, and it allows to FTP over SSH. I'm now trying to get MySQL access over SSH. MySQL is running (listening locally) and accessible on the command line and can access it no problem.

I realise that I could just dump the database, recover the file locally and perform my modification. However, in the future I will probably need to change some stuff, and I'd prefer not to have to do that on the command line, as its quicker through a GUI. Or would be if I could connect like I want to.

6 Answers 6

3

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.

2
  • This is exactly what I wanted to do, but I am using Windows this end. I had a friend look last night and we (he) figured it out for me.
    – Codecraft
    Jan 23, 2012 at 11:21
  • If you are stuck using Windows then you can use the little-known plink command-line tool from the PuTTY suite of tools; it uses the same syntax for options so if you replace ssh in the examples above with plink it should all work.
    – aculich
    Jan 24, 2012 at 4:24
4

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.

6
  • Thats the example from the page I was looking at, i've substituted my own external IP there instead.
    – Codecraft
    Jan 22, 2012 at 18:46
  • If you add the external IP it means that you allow traffic from (-s, as in source) your external IP to port 3306 inbound. To allow any kind of traffic just remove -s 192.168.100.0/24 entirely, just leave: iptables -A INPUT -i eth0 -p tcp --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT Jan 22, 2012 at 19:26
  • I only want it accessible from my network, hence the IP address. I don't want an open MySQL port inviting people to have a go at...
    – Codecraft
    Jan 22, 2012 at 19:28
  • Aha. Entirely different situation, then. You said you wanted to access it remotely. :P I also see that the default policy is ACCEPT, and you don't drop traffic anywhere else, so the firewall isn't the problem in your case after all. What you need to do is probably to configure mySQL to listen to remote connections. Jan 22, 2012 at 19:45
  • 2
    Yeah, it means it just listens to localhost. You can easily tunnel through SSH though, I do it all the time. From your client: ssh [email protected] -L 3305:localhost:3306 and then, in another terminal on the client, do: mysql -h 127.0.0.1 -P 3305 -u username -p and you will connect to the remote mySQL through the SSH tunnel. This assumes that the remote server haven't blocked port forwarding, etc. but usually works great! Jan 22, 2012 at 20:22
1

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.

7
  • There appear to be no other rules, i've edited the question, xx.xx.xx.xx is actually my external IP address, however.
    – Codecraft
    Jan 22, 2012 at 18:47
  • @Codecrat: Have you authorised your remote host to connect to the mysql server ? Try telnet yourserver.tld 3306 and see what it says.
    – user9517
    Jan 22, 2012 at 19:51
  • If I telnet (using PuTTy) to 3306, it just goes 'boing' and exits. I can SSH to it just fine (thats how i'm in the server to modify iptables). I've used my MySQL client (SQLYog) to access MySQL over SSH, but it says it can't authenticate (using the same SSH credentials as I am to access the terminal). FTP over SSH works fine. Something weird is up!
    – Codecraft
    Jan 22, 2012 at 20:05
  • please use telnet from the remote computer you are trying to allow access to.
    – user9517
    Jan 22, 2012 at 20:12
  • connection refused.
    – Codecraft
    Jan 22, 2012 at 21:14
1

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.

3
  • Its running, the website is up and running and I can access MySQL on the command line just fine, but this isn't useful for development. I was a bit suspicious of the emptiness of the iptables as well to be honest, but I don't have much to go on - I don't have access to the previous developer who has moved on, and the project owner is non-technical. I took a stab at IPTables because I had been given a login to the web host's panel, and there was a previous ticket asking for remote access - which was answered by saying it needs to be configured in IPTables on the server itself.
    – Codecraft
    Jan 22, 2012 at 19:26
  • Apache, PHP and the command line client can all communicate with the MySQL server using the socket, without using the network. The netstat command checks that it is listening as well.
    – Ladadadada
    Jan 22, 2012 at 19:46
  • I did run the command and assumed it to be showing that it was up and listening (127.0.0.1:3306)
    – Codecraft
    Jan 22, 2012 at 20:02
1

I found an answer to my own question (I should have clarified that I was using Windows 7 locally, and linux remotely).

Anyway, the answer was to forward a port using PuTTy, as per the other answerer - but this is for Windows users.

In the PuTTy, set up an SSH connection as normal but then go to settings for tunnels, under SSH, choose an arbitrary port (I used 5555) locally to forward to 127.0.0.1:3306

PuTTy Tunneling Options

Open the SSH session and login as normal.

Then in your MySQL client, set the host address to 127.0.0.1 and the port to your selected one (5555).

Hence the MySQL Client is connecting to your local machine, which in turn is being forwarded over SSH to 3306 on the remote machine and allowing the connection.

BIG NOTE: We actually had this set up using the term localhost (as you do) instead of 127.0.0.1, somewhere between PuTTy and the MySQL Client, this didn't resolve - so it seems you must use the loopback address!

5
  • Ah, yes... yet another Windows annoyance that it doesn't have 127.0.0.1 resolve to localhost. It is possible to do that if you add it to %SystemRoot%\system32\drivers\etc\hosts on your system, but yeah, glad to see you figured it out by changing to numeric representation; I will update my answer with that extra info. Also, you can use the plink command-line tool from the PuTTY suite if you don't want to have to open up the PuTTY GUI tool.
    – aculich
    Jan 24, 2012 at 4:27
  • As you're using a Windows client machine you may want to check out HeidiSQL (www.heidisql.com), which allows you to manage your MySQL system over an SSH connection without needing to use additional software. Jan 24, 2012 at 9:52
  • @aculich I accepted your answer; it hardly seems fair to accept my own :)
    – Codecraft
    Jan 24, 2012 at 18:41
  • 1
    @JohnGardeniers the SQL client I use (SQLYog) has options for connecting over an SSH tunnel but for some unknown reason it wouldn't authenticate, though I am not running the latest version - hence the workaround.
    – Codecraft
    Jan 24, 2012 at 18:41
  • @Codecraft, HeidiSQL used to suffer from that same problem and it was most often a case of too short a timeout, so if that's adjustable on SQLYog it may be worth experimenting with. Jan 24, 2012 at 20:59
0

Thank you so much! I was trying all sorts of crap to be able to use MySQL Workbench remotely to work on the tables in my CentOS MySQL implementation. Basically the only solution was to open up port 3306 to everybody (bad for security).

We already use Putty for secure command line access to our server and had no idea we could set up a tunnel like this. Basically we can use our existing secure SSH connection to connect MySQL Workbench with our server. Thank you ! ! !

1
  • 1
    Welcome to Server Fault! This site is a Q&A site, not a forum. You're post doesn't really answer the OP's original question. See the FAQ for more info.
    – slm
    Mar 29, 2013 at 3:23

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .