MySQL through SSH Tunnel?

I need to connect into a remote MySQL DB that allows only localhost connections.

Is there an elegant, Yii way to create a MySQL connection through an SSH tunnel via the main config file?

Before initializing db component, you need to start tunel as(on Linux servers):

ssh -f user@mysql-server.com -L 3306:mysql-server.com:3306 -N

Once tunel is opened, in main.php config file, for db host use localhost, and enter your mysql server username/password.

Thanks. That’s just what I was looking for.

Can someone clarify where you have to start this ssh tunnel?

For me it seems that this is not possible in the main.php config file? So where is the db component initialized?

Hm, I wonder if MySQL’s built-in SSL transport weren’t the better solution. One could still restrict IP access via iptables.

@Pakotski: Chances are that you cannot do that via PHP. Write a script for initd/systemd/upstart to take care of that.

In continuation to what already answered, while it is possible (with SSH keys and passwordless login…) you would not wish to setup and destroy SSH tunnel with each request since it takes relatively lots of time to establish this tunnel. A few seconds usually.

Its like you’re actually digging up a tunnel from the web server to the MySQL server and you wouldn’t want to destroy and build it again on each request.

Actually, I just remembered that I once used a setup of xinetd+stunnel in order to protect a cvs server of mine. It’s not the fastest solution, but at least for cvs response times were acceptable.

OK, so the best solution seems to setup a SSH tunnel on startup of the server. Will try!

If this tunnel is critical or important enough, be sure to check the availability of the tunnel. It can hung up sometimes.

What’s the story on using MySQL’s built-in SSL connection security with Yii? I’m preparing to separate application and db server in a Yii app into 2 physical servers and would prefer to use MySQL SSL connection instead of SSH tunnels. SSH tunnels are not a good scalable option for enterprise level services. Is there any support for MySQL SSL already in Yii? I haven’t found any information about it anywhere but this thread.

Thanks for any tips you have before I get started with this.

http://www.yiiframework.com/forum/index.php/topic/29094-mysql-ssl-connection/

I’ve got a bit of experience in using SSH this way.

I set it up using keys so I can allow connecting through SSH only to the exact specified service, no shell is available.

To set up the tunnel on demand, I use inetd or xinetd or lately even systemd to fire up SSH when a connection comes to specified port on localhost.

There is a considerable amount of overhead when establishing a connection but otherwise this works flawlessly. You don’t have to monitor the tunnel because it’s set up on demand. Also, you can enable compression.

I guess if you use persistent connections to MySQL in PHP that connection overhead should be bearable. At least I don’t notice it when connecting manually.

If anybody is interested I could post all the details how to do that here or on the Wiki.

Another advantage to this is that you can use this for ANY service, not just databases. That’s why this is sometimes called “poor man’s VPN” :slight_smile: