Alson Kemp

Tunnel MySQL over SSH to remote server issue

There are a million pages about this, but I just bumped into a tricky issue and figured I’d drop a quick note about it.

First off, tunnel MySQL to the server (on a UNIXy box) by doing:

ssh  -L 33306:localhost:3306 your-server.com

Simply, that tells SSH to listen on local port 33306, a port chosen because it’s an obvious variation on MySQL’s default of 3306).  When something connects to that port, SSH will accept the connection and will forward it to the remote host, which will then connect it to the appropriate host and port on the far side.  In this case, we’re asking the server to connect to port 3306 on localhost, but you could connect to any server and any port.

The tricky issue was that on my Debian laptop, MySQL uses socket file for communcation even if you specify a port.  So this will fail and have you talking with your local MySQL instance:

mysql --port=33306  -u your_remote_user -pyour_remote_password

In order to force MySQL to use TCP (instead of sockets), you can force the connection protocol or specify a host (note: you can’t use ‘localhost’; you need to use 127.0.01):

mysql --protocol=tcp --port=33306 \
   -u your_remote_user -pyour_remote_password
mysql -h --port=33306  \
   -u your_remote_user -pyour_remote_password

Written by alson

September 7th, 2012 at 2:29 pm

Posted in Turbinado

without comments

Leave a Reply