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
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:
ssh -fNg -L <local port>:<local host>:<remote port> <user>@<remote host> -p <ssh port>
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:
mysql -h <local host> -P <local port> -u <mysql_user> -p <db_name>
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:
- create the SSH tunnel between my local machine (127.0.0.1) port 12345 and my remote host (173.194.143.101) port 3306:
ssh -fNg -L 12345:127.0.0.1:3306 dba@173.194.143.101
- connect the MySQL tunnel end that is opened locally on port 12345:
mysql -h 127.0.0.1 -P 12345 -u dba -p wordpress_db Enter password: ******** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3389610 Server version: 5.5.34-cll MySQL Community Server (GPL) Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
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:
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.
Eugen Mihailescu
Latest posts by Eugen Mihailescu (see all)
- Dual monitor setup in Xfce - January 9, 2019
- Gentoo AMD Ryzen stabilizator - April 29, 2018
- Symfony Compile Error Failed opening required Proxies - January 22, 2018