I can connect to DB through Sequel Pro, but not through command line

Kaleidoscope picture Kaleidoscope · Feb 2, 2012 · Viewed 8.4k times · Source

I can connect to my db with Sequel Pro just fine, but I can't through the command line. I've followed their instructions here: http://www.sequelpro.com/docs/Connecting_to_a_MySQL_Server_on_a_Remote_Host#Do_I_need_to_use_the_Terminal_for_SSH_connections.3F

In their command I substitute the following from the connection window and SSH tab in Sequel Pro:

ssh -L 1234:mysqlhost:3306 sshuser@sshhost

mysqlhost => MySQL Host
sshuser => SSH User
sshhost => SSH Host

And when prompted for a password, I use the one from "SSH Password"

I'm not sure what Sequel Pro is doing differently behind the scenes.

Answer

John Flatness picture John Flatness · Feb 4, 2012

That instruction from the Sequel Pro docs isn't quite the whole story; it's just telling you how to set up a tunnel. You need a second step to actually use it to connect to a MySQL server.

The actual process is two steps:

  1. Create the tunnel.

    ssh -N -L 1234:mysqlhost:3306 sshuser@sshhost
    

    The -N that I added just tells ssh that you're only setting up a tunnel and you don't want to start a shell on sshhost. Running this command will look like it does nothing: that's what it should look like.

    As long as the ssh command is running, connections to port 1234 on your local machine will be tunneled through sshhost to port 3306 (the MySQL port) on mysqlhost.

  2. Connect to MySQL using the tunnel.

    You now need to run the mysql command line client. The ssh command you just ran is still running, so the easiest thing for you to do is open a new Terminal window or tab, and run:

    mysql -P 1234 -u mysqluser -p
    

    to connect to your database. The -P 1234 part is the only out-of-the-ordinary part of this command, and it just makes the mysql client connect using the port you set up in the first command to do the tunneling.

When you're done with the tunnel, either close the original Terminal window or use Ctrl-C to stop the ssh process.