I sometimes connect to live or staging databases from my development machine. I do this by tunneling the MySQL connection over SSH. It's useful for viewing or testing against real and current data. It's also useful for cleaning up botched data from time to time.

Since this is a frequent occurrence for me, I usually set up my .ssh/config file so the tunnel is easily made. It looks something like this:

Host db-tunnel
HostName db.example.com
User admin
LocalForward 3307 localhost:3306

Running the command ssh db-tunnel will connect to db.example.com as the user admin. The important bit is the LocalForward line. It sets up port 3307 on the machine running the SSH command, my dev machine in this case, to forward all connections to port 3306, the port MySQL usually listens to, on the remote machine. Since tunneling can also be used to forward to a third machine, localhost is used to specify the remote machine and not some third.

I use this all the time, sometimes on a daily basis. It almost always works. But I recently had set up a new dev machine, using a configuration similar to the above. Almost immediately I had to connect to the live server to do some tests. And I couldn't make it work. At all. The SSH connection worked just fine. I could connect properly using the mysql command on the server directly. And of course the live server was still connecting correctly. It was just the SSH tunnel that was problematic. I still had to fix the problem, so I did it the long way around.

It wasn't until later that I figured out the problem. It turns out the MySQL client does some trickery. If you try to connect to localhost it uses a socket connection, regardless of whether you set a port or not. And that won't tunnel. Instead, you have to specify the IP address directly. This applies to PHP and its various method of connecting to MySQL as well as the command line. So with the mysql command, use:

mysql --user=dbuser --port=3307 --host=127.0.0.1 schema-name

I had used, originally, localhost rather than 127.0.0.1. Hence, the lack of connection. As soon as I changed it to 127.0.0.1, all was well.

The article I linked to above mentions the localhost/127.0.0.1 problem, but only in passing and without explanation. Hopefully I've provided a bit more information here. Also, as I do this all the time, I don't usually think about it. So this is post is a bit of a personal reminder.