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.