在 PHP 中通过 SSH 连接到 MySQL 服务器

Connect to a MySQL server over SSH in PHP

提问by Eric Goodwin

I'd like to establish an ssh tunnel over ssh to my mysql server.

我想通过 ssh 建立一个到我的 mysql 服务器的 ssh 隧道。

Ideally I'd return a mysqli db pointer just like I was connecting directly.

理想情况下，我会返回一个 mysqli db 指针，就像我直接连接一样。

I'm on a shared host that doesn't have the SSH2libraries but I might be able to get them installed locally using PECL.

我在一个没有SSH2库的共享主机上，但我也许可以使用 PECL 在本地安装它们。

If there's a way that uses native commands that would be great.

如果有一种使用本机命令的方法会很棒。

I was thinking something like this, but without those libraries it won't work.

我在想这样的事情，但没有这些库它就行不通。

$connection = ssh2_connect('SERVER IP', 22); 

ssh2_auth_password($connection, 'username', 'password');

$tunnel = ssh2_tunnel($connection, 'DESTINATION IP', 3307);

$db = new mysqli_connect('127.0.0.1', 'DB_USERNAME', 'DB_PASSWORD', 
                         'dbname', 3307, $tunnel)
    or die ('Fail: ' . mysql_error());  

Anyone have any ideas? I'm running a shared CentOS linux host at liquidweb.

谁有想法？我在 Liquidweb 上运行一个共享的 CentOS linux 主机。

Any thoughts on making the tunnel persistent? Is it possible to establish it with another script and just take advantage of it in PHP?

关于使隧道持久化的任何想法？是否可以用另一个脚本建立它并在PHP.

Thanks.

谢谢。

采纳答案by jpm

Eric,

埃里克，

I think you are out of luck on this one. You can either use the ssh extension in your PHP code, or if you have access to the server, you could try to create a ssh tunnel on the command-line.

我认为你在这一点上不走运。您可以在 PHP 代码中使用 ssh 扩展，或者如果您有权访问服务器，则可以尝试在命令行上创建 ssh 隧道。

You probably need special permissions to do that, though. It also looks like you don't have ssh access to this hosting account.

不过，您可能需要特殊权限才能做到这一点。您似乎也没有此主机帐户的 ssh 访问权限。

--Joao

--若昂

回答by Brian C. Lane

I would use the autosshtool to create a persistent ssh tunnel to your mysql database. Then all you have to do in your PHP code is point it to localhost.

我会使用autossh工具为您的 mysql 数据库创建一个持久的 ssh 隧道。然后你在 PHP 代码中要做的就是将它指向 localhost。

You can test this (without automatic restart) by doing:

您可以通过执行以下操作来测试（不自动重启）：

ssh -L 3306:localhost:3306 [email protected]

Setup a ssh key so that you don't need to use passwords, and tweak the .ssh/authorized_keys file on the mysql system to only allow it to be used for port forwarding.

设置一个 ssh 密钥，以便您不需要使用密码，并调整 mysql 系统上的 .ssh/authorized_keys 文件，使其仅用于端口转发。

For more info on ssh tricks see Brian Hatch'sexcellent series on SSH and port forwarding.

有关 ssh 技巧的更多信息，请参阅Brian Hatch关于 SSH 和端口转发的优秀系列。

回答by Sosy

The tunnel must be keep open during the course of the SQL action(s). The following example from RJMetricsexplains:

在 SQL 操作过程中，隧道必须保持打开状态。以下来自RJMetrics 的示例解释了：

Here's the generic SSH command syntax:

这是通用的 SSH 命令语法：

ssh -f -L bind-ip-address:bind-port:remote-ip-address:remote-port \
username@remote-server [command] >> /path/to/logfile

Here's how to securely establish a remote database connection in just 2 lines of PHP code:

以下是仅用两行 PHP 代码安全地建立远程数据库连接的方法：

shell_exec("ssh -f -L 127.0.0.1:3307:127.0.0.1:3306 [email protected] sleep 60 >> logfile");  
$db = mysqli_connect("127.0.0.1", "sqluser", "sqlpassword", "rjmadmin", 3307);

We use the shell_exec()function to create the tunnel with a 60 second opening window, and then use the mysqli_connect()function to open a database connection using the forwarded port. Note that we must use the "mysqli" library here because mysql_connect()does not allow us to specify a port and mysql_*functions are deprecated.

我们使用该shell_exec()函数创建一个60秒开启窗口的隧道，然后使用该mysqli_connect()函数使用转发的端口打开一个数据库连接。请注意，我们必须在这里使用“mysqli”库，因为mysql_connect()它不允许我们指定端口并且mysql_*不推荐使用函数。

sleep 60: When it comes to tunnel connections, we basically have two options: leave the connection open all the time or open it and close it as needed. We prefer the latter, and as such we don't specify the -Noption when establishing a tunnel, which would leave it open until the process is manually killed (bad for automation). Since -Nis not specified, our tunnel will close itself as soon as its SSH session isn't being used for anything. This is ideal behavior, except for the few seconds between when we create the tunnel and when we get a MySQL connection up and running via the tunnel. To buy us some time during this period, we issue the harmless sleep 60command when the tunnel is created, which basically buys us 60 seconds to get something else going through the tunnel before it closes itself. As long as a MySQL connection is established in that timeframe, we are all set.

sleep 60：谈到隧道连接，我们基本上有两种选择：保持连接一直打开或根据需要打开并关闭它。我们更喜欢后者，因此我们-N在建立隧道时不指定选项，这将使其保持打开状态，直到进程被手动终止（不利于自动化）。由于-N未指定，我们的隧道将在其 SSH 会话未用于任何用途时自行关闭。这是理想的行为，除了我们创建隧道和我们通过隧道建立并运行 MySQL 连接之间的几秒钟。在此期间为我们争取一些时间，我们发出无害的sleep 60在隧道创建时发出命令，这基本上为我们争取了 60 秒的时间让其他东西在隧道关闭之前通过隧道。只要在那个时间范围内建立了 MySQL 连接，我们就都准备好了。

回答by Sosy

I tried it by doing SSH both by root credentials and and public private key pair. It allows me to conect through command line but not through PHP code.

我通过根凭据和公共私钥对执行 SSH 来尝试它。它允许我通过命令行连接，但不能通过 PHP 代码连接。

I also tried by creating a tunnel (by using SSH2 functions), and running shell commands from PHP code (system, exec, etc.); nothing worked.

我还试图通过创建隧道（通过使用SSH2函数），并运行shell命令从PHP代码（system，exec等）; 没有任何效果。

Finally I tried SSH2 function to execute shell command and it finally worked :)

最后我尝试了 SSH2 函数来执行 shell 命令，它终于奏效了 :)

Here is my code, if it helps you:

这是我的代码，如果它对您有帮助：

$connection = ssh2_connect($remotehost, '22');
if (ssh2_auth_password($connection, $user,$pass)) {
    echo "Authentication Successful!\n";
} else {
    die('Authentication Failed...');
}


$stream=ssh2_exec($connection,'echo "select * from zingaya.users where id=\"1606\";"  | mysql');
stream_set_blocking($stream, true);
while($line = fgets($stream)) {
    flush();
    echo $line."\n";
}

Try this if want to use PHP functions specifically.

如果想专门使用 PHP 函数，试试这个。

回答by dbr

It ispossible, but why? It's more complicated than it needs to be, and error prone.

这是可能的，但为什么呢？它比它需要的更复杂，并且容易出错。

Can you not run the database locally? If not, can you not use something like SQLite, XML files or something else that doesn't require a separate server daemon?

不能在本地运行数据库吗？如果没有，您是否可以不使用 SQLite、XML 文件或其他不需要单独服务器守护程序的文件？

You reallydo not want to initialise the tunnel inside the PHP scripts. Initialising an SSH tunnel takes a long time (can easily be a second or two), so that will mean every page that connects to the database will have a 2 seconds delay while loading..

您真的不想在 PHP 脚本中初始化隧道。初始化 SSH 隧道需要很长时间（很容易是一两秒），因此这意味着连接到数据库的每个页面在加载时都会有 2 秒的延迟。

If you haveto do this (which I strongly recommend against), the best method would be to have a script that maintains the connection in the background..

如果您必须这样做（我强烈建议不要这样做），最好的方法是拥有一个在后台维护连接的脚本。

Setup a SSH keypair. Then using autossh, or a simple script which would execute the required SSH command, wait till the process died and start it again. It could be more intelligent and try and run a command every 10-20 seconds, reconnecting if it fails.

设置SSH 密钥对。然后使用autossh或一个简单的脚本来执行所需的 SSH 命令，等到进程终止并重新启动它。它可以更智能，每 10-20 秒尝试运行一次命令，如果失败则重新连接。

Using the SSH keypair, you wouldn't have to hardcode the remote users password in the script. I would recommend limiting what the remote user can do (by using a dedicated tunnel user, and giving it a restricted shell, see thisquestion)

使用 SSH 密钥对，您不必在脚本中对远程用户密码进行硬编码。我建议限制远程用户可以做什么（通过使用专用的隧道用户，并给它一个受限的 shell，请参阅这个问题）

In short, I would strongly suggest trying SQLite first, then see how feasible it to store data using XML files, then try bugging your web-host about getting a local MySQL server, thenlook into the SSH tunnelling option.

简而言之，我强烈建议先尝试 SQLite，然后看看它使用 XML 文件存储数据的可行性，然后尝试窃听您的网络主机关于获取本地 MySQL 服务器的问题，然后查看 SSH 隧道选项。

回答by Nathan Strong

I think your best bet is to find a different hosting provider; I suggest a VPS solution (Virtual Private Server) which gives you full control over your web host. THat way, if you don't like the default configuration, you can upgrade it yourself.

我认为最好的办法是找到不同的托管服务提供商；我建议使用 VPS 解决方案（虚拟专用服务器），它可以让您完全控制您的网络主机。这样，如果您不喜欢默认配置，可以自行升级。