3

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.

Phrogz
  • 271,922
  • 98
  • 616
  • 693
Kaleidoscope
  • 3,489
  • 1
  • 23
  • 21
  • So, you run that command, and *then* what happens, or what else do you do? You're just setting up a tunnel with that SSH command. – John Flatness Feb 04 '12 at 03:46

1 Answers1

9

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.

John Flatness
  • 29,079
  • 5
  • 71
  • 76
  • How could this be used for creating a database on the server in a bash script? Would be awesome if you would check out my question here: http://stackoverflow.com/questions/12905077/create-mysql-database-through-ssh-in-a-bash-script – Spoeken Oct 15 '12 at 23:06
  • 2
    This worked for me almost exactly but in step 2 I had to specify that the host was 127.0.0.1 like `mysql -u user -h 127.0.0.1 -p` otherwise it wouldn't work. – jkeesh Jan 03 '14 at 06:24