Mysql – can’t connect to thesql via command line but workbench works, ssh tunneling required

MySQL

I am trying to connect to a mysql server running at B.edu. I want to connect via SSH tunnel.

I first set this up in MySQL Workbench:

Connection Method: Standard TCP/IP via SSH
SSH hostname: B.edu
SSH username: me
MySQL hostname: B.edu
MySQL port: 3306 

This works fine. I connect and can do as much as I can through Workbench.

Now I'd like to connect via terminal command line. I try:

ssh -N -L 9999:127.0.0.1:3306 me@B.edu &
mysql -h 127.0.0.1 -u me -P 9999

The ssh connection works fine, but the mysql command fails with the error messages:

channel 2: open failed: connect failed: Connection refused

ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0

I see that I'm not really asking for the same thing through the two methods, because from the command line I'm using 127.0.0.1 as the mysql host name, while from Workbench I'm using B.edu as the hostname. Since the Workbench version works, I'd like to change my command line approach to work similarly, but I don't see how.

My ultimate application, once I have the command line version working, is to automate using Python and PyMySQL (presumably with paramiko to handle the tunneling), but I'd like to cross that bridge later and get the terminal line approach working first.

Best Answer

Wrestled this morning with a very similar issue. This worked for me:

ssh -N -L LOCALPORT:localhost:REMOTEPORT -i MYKEYFILE USER@REMOTE
mysql --port LOCALPORT --protocol=TCP -p