Connect to mysql / oracle database with ssh

Question

I want to connect to my database with the tool SquirreL in ssh. Is it possible? Sadly I have no other options for this server, so I ll have to change of tool if it is not working.

Answer 1

There's no way to do it directly through Squirrel, but it's ridiculously easy (when you know how) to set up the ssh tunnel that Squirrel can use.

I'd forgotten how, and came here looking for something to refresh my memory... but I had no luck on StackOverflow, so I did the research and I'm leaving the solution here for future-me... and hopefully it'll help someone else, too.

1. Create the ssh tunnel:

   $ ssh -v -N -L3307:localhost:3306 remotehostname

   I chose to use port 3307, since I also have MySQL running locally on port 3306.

   Details (or you can just skip to step 2):

   3307 is the port you want to use locally to refer to the remote system.

   I believe that localhost here is referring to the remote system, from its own point of view, so localhost:3306 references the the standard MySQL port on the remote system.

   -v is optional; it just makes ssh's output more verbose, which can help with troubleshooting. You're not going to be using the window running ssh for anything else, so might as well let it be chatty.

   -N says you're not interested in actually opening a shell on the remote host. We're here for a database connection only.

   If you have the verbose option turned on, you should see a message like this:

   debug1: Local connections to LOCALHOST:3307 forwarded to remote address localhost:3306

   If you don't have verbose output turned on, you'll only see something like Authenticated to 10.0.1.234 (via proxy).

2. Set up an "alias" in Squirrel that looks something like this:

   Click Test and then Connect to test your connection.

   If you ran ssh with the -v option, you can watch the window while you attempt to connect via Squirrel. You should see something like this for a successful connection:

   debug1: Connection to port 3307 forwarding to localhost port 3306 requested. debug1: channel 2: new [direct-tcpip] debug1: channel 2: free: direct-tcpip: listening port 3307 for localhost port 3306, connect from 127.0.0.1 port 54536 to 127.0.0.1 port 3307, nchannels 3

3. When you're done with the database, just hit CtrlC in the ssh window to kill the tunnel (if you left off the -N option, I guess you'd have to hit CtrlD to close the shell).

Answer 2

Sadly squirrelSQL does not support ssh tunneling. Fortunately, MySQL Workbench does support ssh tunneling so I would recommend using that instead if you are connecting to a MySQL database.

An alternative to using a different tool is to use ssh port forwarding. Essentially you can forward port 3306 (or whatever port your MySQL DB uses) from the remote box to your local box. There are a number of guides on the web on how to do this. Once the tunnel with port forwarding is established you can use a local connection string like 'localhost:3306'. to connect to the remote db. It's good idea to put the ssh connection string in a .sh or .bat file because you'll have to run when you need to connect to the DB for the first time.