My *nix world

MySQL SSH connection

OK, so you are a programmer and you have a website/database hosted on a host company/cloud. You want to access your MySQL database remotely but your host company says that they cannot open the 3306 port (MySQL).

My question to you is: do you have at least SSH access to your host? If you do then you can use SSH to open a tunnel through which you can later open a MySQL connection. This is called MySQL over SSH.

MySQL SSH connection

mysql ssh connection

So basically we create a SSH tunnel from the local bind address (like 127.0.0.1) to the remote bind address (your host address). One one end (the local bind address) we open the port 3307 (or any other port you like on your localhost) and at the other end (the remote bind address) we open another port, for instance 3306 which happens to be the MySQL port 🙂

What this means is that anytime when you will connect to local bind address on that local bind port you actually will access the remote bind address on that remote bind port.

On Linux we can use the SSH client like this:


What means all of these parameters:

  • -f : Requests ssh to go to background just before command execution. This is useful if ssh is going to ask for passwords or pass-phrases, but the user wants it in the background
  • -N: Do not execute a remote command. This is useful for just forwarding ports (protocol version 2 only)
  • -g: Allows remote hosts to connect to local forwarded ports
  • -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
  • -p : specify the remote SSH port (eg. 22)
  • <user> your SSH user at the <remotehost>
  • <remotehost> the name/IP of the remote host where you want to connect via SSH

After we have created the SSH tunnel we can access the MySQL instance like this:


What is happening is that although you are connecting to <local host> on <local port> you are actually working on the <remote host> on the <remote port>. Why is that? Because your connection is not just a simple connection to a <service>:<port> that has only one end (like 127.0.0.1:3306). Your connection is a tunnel that at one end is something like 127.0.0.1:12345 and at the other end is something like 173.194.143.101:3306. Whenever you communicate with one end those networking packages travels through the SSH tunnel and are forwarded to the other end (where you can map the MySQL service).

Example:

I want to connect the MySQL service on my remote host (173.194.143.101). I will use a dummy port on my local machine (127.0.0.1), for instance let's say 12345, and I am going to connect the MySQL port (3306) on that remote host. On MySQL I have an user named "dba" and a database called "wordpress_db". What I do:

  1. create the SSH tunnel between my local machine (127.0.0.1) port 12345 and my remote host (173.194.143.101) port 3306:
  1. connect the MySQL tunnel end that is opened locally on port 12345:

Now you can use your MySQL connection via SSH, can't you ?

This "trick" can also be used for any other remote services that you cannot access remotely but via SSH only ;o)

If you have some MySQL tool like MySQL Workbench then you define a remote connection like this:

mysql ssh connection

Now, if you think that this article was interesting don't forget to rate it. It shows me that you care and thus I will continue write about these things.

 
The following two tabs change content below.
MySQL SSH connection

Eugen Mihailescu

Founder/programmer/one-man-show at Cubique Software
Always looking to learn more about *nix world, about the fundamental concepts of math, physics, electronics. I am also passionate about programming, database and systems administration. 16+ yrs experience in software development, designing enterprise systems, IT support and troubleshooting.
MySQL SSH connection

Latest posts by Eugen Mihailescu (see all)

Tagged on: , ,

Leave a Reply

Your email address will not be published. Required fields are marked *

Follow

Get every new post on this blog delivered to your Inbox.

Join other followers: