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 127.0.0.1 --port=33306 \ -u your_remote_user -pyour_remote_password
