Here's my setup for remote access to my mysql server at work.
My work has a dynamically assigned ip from the local provider. I use the free service from dyndns.com to make that ip available. So let's say the hostname I chose from dyndns was mywork.dyn-alias.com.
I could ping that address using
I set up a machine at work (machine A) to listen for ssh connections on port 34567
so I can connect to that machine using.
~ssh -p 34567 mywork.dyn-alias.com
oh hang on.. my home user "neil" is not allowed, silly me, so let's say I have a known user called "dentist"
~ssh -p 34567 firstname.lastname@example.org
Now A separate mysql server (machine B) resides on that network. It's LAN IP is 192.168.0.2.
This machine does not allow any other type of connection. What if I want to lever that database remotely? The easiest way is to ssh into machine A (as above) and use the mysql-client command line tool on that machine to connect to machine B.
that command is
~mysql -h 192.168.0.2 -u databaseUser -p
which will prompt for a password. or
~mysql -h 192.168.0.2 -u databaseUser -pPASSWORD mydatabase
which will pass the password "PASSWORD" automatically, and start using database "mydatabase" automatically.
but what if I want to use an application - like openmolar - to connect to that database.
the answer here is to forward the mysql port from machine B through machine A, so that it appears as a service running on my local machine.
~ssh -p 34567 -N -L 45678:192.168.0.2:3306 email@example.com
-p 34567 use "port" 34567 (isn't port a silly term... surely this should be "channel")
-N from the ssh manual - "Do not execute a remote command" I don't fully understand this, but do know the connection is refused if I ommit this.
-L 45678:192.168.0.2:3306 let's break this down. -L is the command to "bind an address"
45678 is a random port on mylocal machine.
192.168.0.2:3306 is the address of the mysql service on machine B. machine A has permission to connect to this. Also note - 3306 is mysql's standard port.
with that command running, I can now point the app to 127.0.0.1:45678 and I'm golden.