I had a tricky time getting this to work correctly, so I thought I’d publish how I was successful.

I need to make a MySQL connection on a remote server.  Opening up the MySQL port of 3306 is simply not an option since it would make it vulnerable to crackers plus the queries involve credit application data such as Social Security Numbers, which we definately don’t want passed around the internet in clear text.  The solution I’ve come up with is to use ssh to tunnel to the remote server and do some port forwarding to get things working correctly.

I use this to fire up ssh and put it in the background:

ssh -fNL 3307:127.0.0.1:3306 remoteserver.com

Don’t be tempted to use “localhost” in place of “127.0.0.1″.  I tried doing that and it simply didn’t work.

Now fire up MySQL with this command line:

mysql -h 127.0.0.1 -P 3307 -u username -p

Again, don’t use “localhost” and don’t leave out the host option, it won’t work.

The final key to the puzzle was the privileges on the remote server.  When directly connected the authentication was done as ‘username@localhost’.  With this pseudo-local connection it is authenticated as ‘username@domain.com’, so be sure to grant the privileges to both if you will be working both locally and remotely with a database.

Once you have things working from the command line, it’s a simple matter of modifying your scripts and programs to use the correct server (127.0.0.1) and port (3307).

Addendum

It looks like $! is useless for retrieving the PID of ssh when you put it in the background because of forking.  I’ll have to use some hack like “ps –no-headers -fC ssh –ppid $$ | head -1 | awk ‘{print $2}’” to obtain it – kind of messy.

This entry was posted on Saturday, September 17th, 2005 at 11:42 pm and is filed under Geek Stuff. You can follow any responses to this entry through the RSS 2.0 feed. You can skip to the end and leave a response. Pinging is currently not allowed.