利用SSH隧道連接遠程服務器

想必大家都有這樣的經歷，本機可以通過SSH訪問遠程一個私有網絡中的一臺對外的主機，但在該網絡中的服務器確只對該網絡開放，像你這樣在外網中的人無法訪問，其實我們可以通過建立SSH隧道的來完成網絡訪問的目的。

如SQLylog就提供了SSH Tunneling連接的能力，下面是它的幫助：

Connecting using SSH Tunneling

What Is SSH?

The Acronym SSH stands for Secure Shell Host. There are a couple of ways that you can access most systems. The most common way is to use a telnet program such as the one called telnet (for Unix and Windows). Accessing a shell account in this fashion though poses a danger in that everything that you do over that telnet session is visible in plain text on your local network, and the local network of the machine you are connecting to.

As one solution SSH was created to encrypt the data being sent over the network as well as provide an option that prevents passwords from being ever passed over the network. SSH can use several different forms of encryption, anywhere from 56 to 1024 bit. SSH has been ported to Operating Systems on several platforms including Linux, Microsoft Windows and Macintosh.

What Is SSH Tunneling?

SSH can be used to encrypt communications between SQLyog and your remote MySQL server. This is known as SSH tunneling and is actually quite simple to use.

Benefit of SSH Tunneling

Many hosting companies that provide MySQL hosting will block access to the MySQL server from outside the hosting company's network, and only grant access to users connecting from localhost or from a machine within the network. The major benefit of SSH tunneling is that it allows us to connect to a MySQL server from behind a firewall when the MySQL server port is blocked. As long as the hosting company provides SSH access, you can still connect through SQLyog SSH Tunneling option.

Setting up SQLyog for SSH Tunneling

Select Tunnel tab in the connection window.

SQLyog gives you the option of using either password authentication or private/public key authentication with SSH tunneling. Most users use password authentication. The details for this option are:

Username	Username to access the SSH server (Note: not the MySQL server).
Password	Password to access the SSH server (Note: not the MySQL server).
SSH Host	Address of the machine on which SSH server is running.
SSH Port	Port on which SSH server is listening. By default, it is 22.
Local Port	The local port on which SQLyog will listen locally for correctly port forwarding MySQL requests. Note: If you are connecting to two or more different MySQL servers, then you need to provide two different local port values in the respective connections.

To understand the setup better, let us connect to a MySQL server using a real life example.

E.g. Your website is hosted on 234.56.65.78. This server also has SSH server running and listening on port 22. Your MySQL server is located on server 234.56.65.79. This machine only allows connection from localhost or machine within the same network (e.g. 234.56.65.78). You publish your website on 234.56.65.78 using the username and password provided by your ISP. Ex. the username and password provided is ssh_user and ssh_pwd respectively.

In this particular case you have to put in the following value for various options:

Server Tab
MySQL Host Address	localhost
Username	Your mysql user name.
Password	Your mysql password.
Database	The database that you want to connect to. You can leave this field blank.
Port	The port MySQL is listening. By default it is 3306.
Tunnel Tab
Username	ssh_user
Password	ssh_pwd
SSH Host	234.56.65.78
SSH Port	22
Local Port	Any port that is unused on your local machine. If you don't have a MySQL running on your machine then you can provide 3306.

Note that the MySQL Host Address is entered as 'localhost'. That will most often be the case. The MySQL server and the SSH daemon then runs on the same machine or at least the same protected network and some routing system on the network takes care of the rest. You could in principle enter '234.56.65.78' for the MySQL server as well but in some cases the SSH configuration will not allow for the use of a 'global ip'. Finally you could need to enter '234.56.65.79' if the SSH daemon runs on '234.56.65.78' and the MySQL server on '234.56.65.79'. But note then that all communication between the machines '234.56.65.78' and '234.56.65.79' is not encrypted. As long as they both are on the same protected network it is normally not a problem. But connection from the SSH daemon running on one server to a MySQL server outside the protected network can be. That will however also only be needed in very special situations. And if you need to do so, you probably need not read this!

The public/private key authentication adds an additional layer of security. If you check this option you must still provide a password (what is now named a 'passphrase') and you must enter the path and name of the file that contains your private key. To have your account set up for public/private key authentication you should contact your System Administrator. Key files to be used with SQLyog must be in .ppk -format (same format as used by the SSH shell program 'Putty').

但是我們普通的客戶端如何利用這種Tunnel進行連接呢？別急，看下面：

1、簡介：
大多數人知道SSH是用來替代R命令集，用于加密的遠程登錄，文件傳輸，甚至加密的FTP(SSH2內置)，因此SSH成為使用極廣的服務之一，不僅如此，SSH還有另一項非常有用的功能，就是它的端口轉發隧道功能，利用此功能，讓一些不安全的服務象TCP、POP3、SMTP、FTP，LDAP等等通過SSH的加密隧道傳輸，然后，既然這些服務本身是不安全的，密碼和內容是明文傳送的，通過使用SSH隧道傳輸的話再想在其中間監聽也是徒勞無功的了。本文主要介紹通過SSH隧道連接遠程Mysql服務器，SSH隧道更詳細的文章參見：官方關于SSH隧道的介紹文章

2、使用Windows客戶端連接

下面介紹使用windows系統下比較流行的MySQL終端工具SQLyogEnterprise，通過SSH隧道連接Mysql服務器。如下圖：

新建連接

MySQL標簽頁

SSH標簽頁

3、linux命令行下使用ssh命令建立SSH隧道

mysql服務器地址為:192.168.99.52
本機地址為:192.168.99.91

前提
將本機的ssh publickey復制到mysql服務器中，也就是將本機的id_rsa.pub內容添加到mysql服務器的~/.ssh/authorized_keys文件中。我這里用命令實現。

利用 ssh-keygen 命令生成本機id_rsa.pub文件
# ssh-keygen 連續回車生成id_rsa.pub 文件

將id_rsa.pub文件復制到mysql服務器中
# ssh-copy-id -i ~/.ssh/id_rsa.pub 192.168.99.52

開始挖隧道
利用ssh命令在本機開個3388的端口，這個端口為隧道的入口端口，也就是說我一會兒通過在本機連接這個端口來達到連接mysql服務器3306端口的目的。使用命令如下：

ssh -NCPfroot@192.168.99.52 -L 3388:192.168.99.52:3306

參數解釋

-C 使用壓縮功能，是可選的，加快速度。
-P 用一個非特權端口進行出去的連接。
-f 一旦SSH完成認證并建立port forwarding，則轉入后臺運行。
-N 不執行遠程命令。該參數在只打開轉發端口時很有用（V2版本SSH支持）

這里的root@192.168.99.52 是登陸mysql服務器的SSH用戶名和IP地址-L 3388:192.168.99.52:3306 這個參數的意思是說在本機開放3388端口到192.168.99.52:3306端口的映射，也就是說隧道的入口為3388出口為mysql服務器的3306

執行完后查看本地連接情況
# netstat -tulnp | grep 3388
tcp 0 0127.0.0.1:3388 0.0.0.0:* LISTEN 14273/ssh
tcp 0 0::1:3388 :::* LISTEN 14273/ssh

查看本機與mysql服務器的ssh連接情況
# netstat -an | grep 192.168.99.52
tcp 0 0 192.168.99.91:7612 192.168.99.52:9698 ESTABLISHED

通過這兩條命令可知，執行完命令之后，本機與mysql服務器就建立起了ssh連接，且開放了3388端口。

通過隧道連接MySQL服務器

# mysql -udbname -P 3388 -h 127.0.0.1 -pdbpwd
Enter password:
Welcome to the MySQL monitor. Commands end with ;or \g.
Your MySQL connection id is 52562
Server version: 5.1.31-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the currentinput statement.

dbname@192.168.99.52 [(none)] >showdatabases;

4 rows in set (0.00 sec)

連接成功！

原文出自：http://salogs.com/2009/11/%E5%88%A9%E7%94%A8ssh%E9%9A%A7%E9%81%93%E6%96%B9%E5%BC%8F%E8%BF%9E%E6%8E%A5%E8%BF%9C%E7%A8%8Bmysql%E6%9C%8D%E5%8A%A1%E5%99%A8/