Thursday 2 July 2009

tunneling mysql over ssh

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

~ping mywork.dyn-alias.com

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 dentist@mywork.dyn-alias.com

that works!

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.
here's how.....

~ssh -p 34567 -N -L 45678:192.168.0.2:3306 dentist@mywork.dyn-alias.com

which means...
-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.

No comments: