通过ssh隧道访问mysql，包括免密码操作

通过ssh隧道访问mysql：转自：http://blog.csdn.net/qdujunjie/article/details/43701727

sshpass: 用于非交互的ssh 密码验证： 转自：http://blog.csdn.net/gsnumen/article/details/7293274

我的机器可以连接服务器A，A可以访问服务器B上的MySQL，但是我的机器不能访问B，所以我需要建立一个我本地机器到服务器A的ssh隧道来访问服务器B上的mysql。

理一下思路：

1.我要ssh的服务器是111.112.113.114，端口是5122，账户名是wz。

2.然后我ssh到111.112.113.114之后要访问的mysql服务器地址是：192.168.3.51，端口号是3306 。

3.我打算把这个ssh隧道绑定到我本地的3307端口。

好了，搞明白之后我们开始写命令：

<code class="hljs ruby has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: "Source Code Pro", monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;">andy<span class="hljs-variable" style="color: rgb(102, 0, 102); box-sizing: border-box;">@AndyMacBookPro</span><span class="hljs-symbol" style="color: rgb(0, 102, 102); box-sizing: border-box;">:/usr/local/webdata/andy/shop/jd/job</span><span class="hljs-variable" style="color: rgb(102, 0, 102); box-sizing: border-box;">$ </span>ssh -fN -<span class="hljs-constant" style="box-sizing: border-box;">L3307</span><span class="hljs-symbol" style="color: rgb(0, 102, 102); box-sizing: border-box;">:</span><span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">192.168</span>.<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">3.51</span><span class="hljs-symbol" style="color: rgb(0, 102, 102); box-sizing: border-box;">:</span><span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">3306</span> -p5122 wz<span class="hljs-variable" style="color: rgb(102, 0, 102); box-sizing: border-box;">@111</span>.<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">112.113</span>.<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">114</span>
wz<span class="hljs-variable" style="color: rgb(102, 0, 102); box-sizing: border-box;">@111</span>.<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">112.113</span>.<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">114</span><span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'s password:
andy@AndyMacBookPro:/usr/local/webdata/andy/shop/jd/job$ lsof -i:3307
COMMAND  PID USER   FD   TYPE            DEVICE SIZE/OFF NODE NAME
ssh     1114 andy    5u  IPv6 0xaed4b8ceeaf7e51      0t0  TCP localhost:opsession-prxy (LISTEN)
ssh     1114 andy    6u  IPv4 0xaed4b8cfd6dcef1      0t0  TCP localhost:opsession-prxy (LISTEN)
andy@AndyMacBookPro:/usr/local/webdata/andy/shop/jd/job$</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li></ul><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li></ul>

可以看到最后已经建立了两个隧道，命令成功！

其中ssh的语法是这样的：

ssh -fN -L(要绑定到的本地端口):(服务器B的Host):(服务器B上要访问的端口号) -p(服务器A的端口，默认为22) (服务器A的账户):(服务器A的Host)

感谢以下资料： 
http://blog.csdn.net/nrc_douningbo/article/details/18618311

之后我们就可以写php代码来通过ssh隧道来访问那台无法直接连接的mysql服务器了，我这里使用的是medoo这个好东西：

<code class="hljs php has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: "Source Code Pro", monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;">ini_set(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'memory_limit'</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'256M'</span>);

error_reporting(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1</span>);
set_time_limit(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0</span>);

<span class="hljs-variable" style="color: rgb(102, 0, 102); box-sizing: border-box;">$medoo</span> = <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'/usr/local/webdata/github/Medoo/medoo.php'</span>;
<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">require</span> <span class="hljs-variable" style="color: rgb(102, 0, 102); box-sizing: border-box;">$medoo</span>;
<span class="hljs-variable" style="color: rgb(102, 0, 102); box-sizing: border-box;">$database</span> = <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">new</span> medoo([
    <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'database_type'</span> => <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'mysql'</span>,
    <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'database_name'</span> => <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'monitor'</span>,
    <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'server'</span> => <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'127.0.0.1'</span>,
    <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'username'</span> => <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'myname'</span>,
    <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'password'</span> => <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'mypassword'</span>,
    <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'port'</span> => <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'3307'</span>,
]);

<span class="hljs-variable" style="color: rgb(102, 0, 102); box-sizing: border-box;">$database</span>->query(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"set names utf8;"</span>);

<span class="hljs-variable" style="color: rgb(102, 0, 102); box-sizing: border-box;">$sql</span> = <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'show tables'</span>;
<span class="hljs-variable" style="color: rgb(102, 0, 102); box-sizing: border-box;">$data</span> = <span class="hljs-variable" style="color: rgb(102, 0, 102); box-sizing: border-box;">$database</span>->query(<span class="hljs-variable" style="color: rgb(102, 0, 102); box-sizing: border-box;">$sql</span>)->fetchall();
<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">echo</span> <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'<pre>'</span>;var_dump(<span class="hljs-variable" style="color: rgb(102, 0, 102); box-sizing: border-box;">$data</span>);<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">exit</span>;</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li><li style="box-sizing: border-box; padding: 0px 5px;">15</li><li style="box-sizing: border-box; padding: 0px 5px;">16</li><li style="box-sizing: border-box; padding: 0px 5px;">17</li><li style="box-sizing: border-box; padding: 0px 5px;">18</li><li style="box-sizing: border-box; padding: 0px 5px;">19</li><li style="box-sizing: border-box; padding: 0px 5px;">20</li><li style="box-sizing: border-box; padding: 0px 5px;">21</li></ul><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li><li style="box-sizing: border-box; padding: 0px 5px;">15</li><li style="box-sizing: border-box; padding: 0px 5px;">16</li><li style="box-sizing: border-box; padding: 0px 5px;">17</li><li style="box-sizing: border-box; padding: 0px 5px;">18</li><li style="box-sizing: border-box; padding: 0px 5px;">19</li><li style="box-sizing: border-box; padding: 0px 5px;">20</li><li style="box-sizing: border-box; padding: 0px 5px;">21</li></ul>

经验证可以访问成功。另，以上的server如果写成localhost是会报错的，要设置为127.0.0.1才可以。

============================================================================================================

sshpass: 用于非交互的ssh 密码验证

 

ssh登陆不能在命令行中指定密码，也不能以shell中随处可见的，sshpass 的出现，解决了这一问题。它允许你用 -p 参数指定明文密码，然后直接登录远程服务器。 它支持密码从命令行,文件,环境变量中读取

 

$> sshpass -h

 

Usage: sshpass [-f|-d|-p|-e] [-hV] command parameters

   -f filename Take password to use from file

   -d number Use number as file descriptor for getting password

   -p password Provide password as argument (security unwise)

   -e Password is passed as env-var "SSHPASS"

   With no parameters - password will be taken from stdin

 

   -h Show help (this screen)

   -V Print version information

At most one of -f, -d, -p or -e should be used

 

sshpass [-f|-d|-p|-e] [-hV] command parameters 中的 command parameters 和使用交互式密码验证的使用方法相同

 

#从命令行方式传递密码

 

    $> sshpass -p user_password ssh user_name@192.168..1.2

    $> sshpass -p user_password scp -P22 192.168.1.2:/home/test/t . 

 

#从文件读取密码

 

    $>　echo "user_password" > user.passwd

    $> sshpass -f user.passwd ssh user_name@192.168..1.2

 

#从环境变量获取密码

 

    $> export SSHPASS="user_password"

    $> sshpass -e ssh user_name@192.168..1.2 

 

 

源码位置：http://sourceforge.net/projects/sshpass/

 

--------------end--------------
From: GS
-------------------------------