Linux – How to allow remote MySQL access to a single, static IP

linuxMySQL

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.

Best Answer

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.

Related Topic