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

Connect to a MySQL server over SSH in PHP

提问by Tony Barganski

I have my database on remote Linux machine, and I want to connect using SSH and PHP functions (I am currently using ssh2 library for that). I tried using mysql_connect, but it gives me can't access (although I have granted permission) when I tried using this function:

我在远程 Linux 机器上有我的数据库，我想使用 SSH 和 PHP 函数进行连接（我目前为此使用 ssh2 库）。我尝试使用 mysql_connect，但是当我尝试使用此功能时，它使我无法访问（尽管我已授予权限）：

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

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

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

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

I got error "mysqli_connect() expects parameter 6 to be string, resource given". How can I resolve this?

我收到错误“mysqli_connect() 期望参数 6 是字符串，资源给定”。我该如何解决这个问题？

回答by Tony Barganski

SSH Tunnel Solution

SSH隧道解决方案

Set up an SSH tunnel to your MySQL database server (through a Jumpbox proxy for security).

设置到 MySQL 数据库服务器的 SSH 隧道（通过 Jumpbox 代理以确保安全）。

(A) GUI Tools

(A) 图形用户界面工具

Depending on your requirements, you can use a GUI MySQL client with SSH Tunnelling support built-in such as Visual Studio CodeForwarding a port / creating SSH tunnel, SequelProor use PuTTYto setup localport forwarding.

根据您的要求，您可以使用内置 SSH 隧道支持的 GUI MySQL 客户端，例如Visual Studio Code转发端口/创建 SSH 隧道、SequelPro或使用PuTTY设置本地端口转发。

On macOS, I like Secure Pipes.

在 macOS 上，我喜欢Secure Pipes。

(B) Command Line

(B) 命令行

Step 1.

第1步。

ssh -fNg -L 3307:10.3.1.55:3306 [email protected] 

The key here is the '-L'switch which tells ssh we're requesting localport forwarding.

这里的关键是“-L”开关，它告诉 ssh 我们正在请求本地端口转发。

I've chosen to use port 3307above. All traffic on my localmachine directed to this port will now be 'port-forwarded' via my ssh clientto the ssh serverrunning on the host at address ssh-jumpbox.com.

我选择使用上面的端口3307。我本地机器上指向此端口的所有流量现在都将通过我的ssh 客户端“端口转发”到在地址为 的主机上运行的ssh 服务器ssh-jumpbox.com。

The Jumpbox ssh proxy server will decrypt the traffic and establish a network connection to your MySQL database server on your behalf, 10.3.1.55:3306, in this case. The MySQL database server sees the connection coming in from your Jumpbox' internal network address.

10.3.1.55:3306在这种情况下，Jumpbox ssh 代理服务器将解密流量并代表您建立与 MySQL 数据库服务器的网络连接。MySQL 数据库服务器会看到来自 Jumpbox 内部网络地址的连接。

Local Port Forwarding Syntax
The syntax is a little tricky but can be seen as:

本地端口转发语法
语法有点棘手，但可以看作：

<local_workstation_port>:<database_server_addr_remote_end_of_tunnel>:<database_server_port_remote_end> username@ssh_proxy_host.com

If you're interested in the other switches, they are:

如果您对其他开关感兴趣，它们是：

-f (go to background)
-N (do not execute a remote command)
-g (allow remote hosts to connect to local forwarded ports)

-f (进入后台)
-N (不执行远程命令)
-g (允许远程主机连接到本地转发端口)

Private Key Authentication, add (-i) switch to above:

私钥认证，在上面添加（-i）开关：

-i /path/to/private-key

-i /path/to/private-key

Step 2.

第2步。

Tell your local MySQL client to connect through your SSH tunnel via the local port 3307 on your machine (-h 127.0.0.1) which now forwards all traffic sent to it through the SSH tunnel you established in step 1.

告诉您的本地 MySQL 客户端通过您机器上的本地端口 3307 (-h 127.0.0.1) 通过 SSH 隧道进行连接，该端口现在通过您在步骤 1 中建立的 SSH 隧道转发发送给它的所有流量。

mysql -h 127.0.0.1 -P 3307 -u dbuser -p passphrase

Data exchange between client and server is now sent over the encrypted SSH connection and is secure.

客户端和服务器之间的数据交换现在通过加密的 SSH 连接发送并且是安全的。

Security note
Don't tunnel directly to your database server. Having a database server directly accessible from the internet is a huge security liability. Make the tunnel target address the internet address of your Jumpbox/Bastion Host (see example in step 1) and your database target the internalIP address of your database server on the remote network. SSH will do the rest.

安全说明
不要直接隧道到您的数据库服务器。拥有可从 Internet 直接访问的数据库服务器是一项巨大的安全责任。将隧道目标地址设为 Jumpbox/堡垒主机的 Internet 地址（参见步骤 1 中的示例），并将数据库设为远程网络上数据库服务器的内部IP 地址。SSH 将完成剩下的工作。

Step 3.

第 3 步。

Now connect up your PHP application with:

现在连接您的 PHP 应用程序：

<?php
      $smysql = mysql_connect( "127.0.0.1:3307", "dbuser", "passphrase" );
      mysql_select_db( "db", $smysql ); 
?>

Credit to Chris Snyder'sgreat articledetailing ssh command line tunnelling for MySQL connectivity.

感谢克里斯·斯奈德的大文章详细介绍了MySQL的连接SSH命令行隧道。

回答by billynoah

Unfortunately, the ssh2 tunnel offered by php doesn't seem able to handle a remote mysql connection as you cannot specify the local port to tunnel (it only works with port 22 or whatever ssh port your remote server is running on). My solution to this is to just open the tunnel via exec()operator and connect as usual from there:

不幸的是，php 提供的 ssh2 隧道似乎无法处理远程 mysql 连接，因为您无法指定隧道的本地端口（它仅适用于端口 22 或远程服务器运行的任何 ssh 端口）。我对此的解决方案是通过exec()操作员打开隧道并从那里照常连接：

exec('ssh -f -L 3307:127.0.0.1:3306 [email protected] sleep 10 > /dev/null');
$mysqli = new mysqli('127.0.0.1', 'user', 'password', 'database', '3307');

回答by Ivo Smits

I was looking for the same thing, but I prefer not to need external commands and manage external processes. So at some point I thought, how hard can it be to write a pure PHP MySQL client which can operate on any PHP stream? It took me about half a day, based on the MySQL protocol documentation.

我一直在寻找同样的东西，但我更喜欢不需要外部命令和管理外部进程。所以在某些时候我想，编写一个可以在任何 PHP 流上运行的纯 PHP MySQL 客户端有多难？根据MySQL协议文档，我花了大约半天时间。

https://gist.github.com/UCIS/4e509915ed221660e58f5169267da004

https://gist.github.com/UCIS/4e509915ed221660e58f5169267da004

You can use this with the SSH2 library or any other stream:

您可以将其与 SSH2 库或任何其他流一起使用：

    $ssh = ssh2_connect('ssh.host.com');
    ssh2_auth_password($ssh, 'username', 'password');
    $stream = ssh2_tunnel($ssh, 'localhost', 3306);
    $link = new MysqlStreamDriver($stream, 'SQLusername', 'SQLpassword', 'database');
    $link->query('SELECT * FROM ...')->fetch_assoc();

It does not implement the complete mysqli API, but it should work with all plain-text queries. Please be careful if you decide to use this, I haven't thoroughly tested the code yet and the string escaping code has not been reviewed.

它没有实现完整的 mysqli API，但它应该适用于所有纯文本查询。如果您决定使用它，请小心，我还没有彻底测试代码，也没有字符串转义代码。

回答by David Z

According to the docs, that last parameter is supposed to be a socket or pipe name, something like '/var/run/mysql/mysql.sock'. Since you're not connecting using a UNIX socket, that doesn't apply to you... so try just leaving it out.

根据文档，最后一个参数应该是套接字或管道名称，例如“/var/run/mysql/mysql.sock”。由于您没有使用 UNIX 套接字进行连接，因此这不适用于您……所以请尝试将其排除在外。

回答by David Z

even i tried it by doing ssh both by root credentials and and public private key pair, but it allows me to conect through command line but not through php code. I tried by creating tunnel also(by using ssh2 functions),ans running shell commands from php code(system,exec etc), nothing worked. Finally i tried ssh2 function to execute shell command and it finally worked :) Here is code, if it helps you:----

甚至我通过 root 凭据和公共私钥对执行 ssh 来尝试它，但它允许我通过命令行而不是通过 php 代码进行连接。我也尝试过创建隧道（通过使用 ssh2 函数），从 php 代码（系统、执行等）运行 shell 命令，但没有任何效果。最后我尝试了 ssh2 函数来执行 shell 命令，它终于奏效了:) 这是代码，如果它对你有帮助：----

$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";
}

it worked for me try this if want to use php functions specifically.

它对我有用，如果想专门使用 php 函数，试试这个。

回答by robert thornton

Make sure that your username and password that you are connecting with has the right hostname permissions. I believe you can use '%' for a wildcard. Also if you are connecting to remote machine (which I would assume you are if you are trying to ssh into it) that is not on your local network you will have to forward the ports on your router where the server is for outside traffic to be able to connect to it.

确保您连接的用户名和密码具有正确的主机名权限。我相信您可以使用 '%' 作为通配符。此外，如果您要连接到不在本地网络上的远程机器（如果您尝试通过 ssh 连接到它，我会假设您是这样），您将必须转发路由器上的端口，该服务器用于外部流量能够连接到它。

http://www.lanexa.net/2011/08/create-a-mysql-database-username-password-and-permissions-from-the-command-line/

http://www.lanexa.net/2011/08/create-a-mysql-database-username-password-and-permissions-from-the-command-line/

回答by Dhiraj Gupta

I believe that the reason I (and I suppose most people) have a problem getting this to work is because the user in the mysql server is set to only allow from "localhost" and not 127.0.0.1, the IP address of localhost.

我相信我（我想大多数人）在让它工作时遇到问题的原因是因为 mysql 服务器中的用户设置为只允许来自“localhost”而不是 127.0.0.1，即 localhost 的 IP 地址。

I got this to work by doing the following steps:

我通过执行以下步骤使其工作：

Step 1: Allow 127.0.0.1 host for target user

步骤 1：允许目标用户使用 127.0.0.1 主机

SSH normally into your server, and log in with the mysql root user, then issue the command:

SSH 正常连接到您的服务器，并使用 mysql root 用户登录，然后发出命令：

GRANT ALL ON yourdbname.* TO [email protected] IDENTIFIED BY 'yourdbpassword';

The key of course, is specifying 127.0.0.1 above.

当然，关键是在上面指定 127.0.0.1。

Step 2: Start local SSH tunnel to MySQL

第 2 步：启动到 MySQL 的本地 SSH 隧道

You can now start your local SSH tunnel to the remote MySQL server, like so:

您现在可以启动到远程 MySQL 服务器的本地 SSH 隧道，如下所示：

ssh -vNg -L 33306:127.0.0.1:3306 [email protected]

-vmakes ssh operate in verbose mode, which kind of helps to see what's happening. For example, you'll see debugging output like this in your terminal console when you attempt a connection:

-v使 ssh 以详细模式运行，这有助于了解发生了什么。例如，当您尝试连接时，您将在终端控制台中看到如下调试输出：

debug1: client_input_global_request: rtype [email protected] want_reply 0
debug1: Connection to port 33306 forwarding to 127.0.0.1 port 3306 requested.

and output like this when you close the connection:

并在关闭连接时输出如下：

debug2: channel 2: is dead
debug2: channel 2: garbage collecting
debug1: channel 2: free: direct-tcpip: listening port 33306 for 127.0.0.1 port 3306, connect from 127.0.0.1 port 52112 to 127.0.0.1 port 33306, nchannels 3

-Nmakes ssh issue no commands, and just wait instead after establishing connection.

-N使 ssh 不发出任何命令，而是在建立连接后等待。

-gallows remote hosts to connect to local forwarded ports. Not completely sure if this is necessary but it might be useful for multiplexing multiple connections through the same SSH tunnel.

-g允许远程主机连接到本地转发端口。不完全确定这是否有必要，但对于通过同一 SSH 隧道多路复用多个连接可能很有用。

-LThis is the main parameter that specifies the local port 33306to connect to the remote host's local IP address 127.0.0.1and the remote host's mysql port, usually 3306.

-L这是主要参数，指定33306连接远程主机的本地IP地址127.0.0.1和远程主机的mysql端口的本地端口，通常是3306.

You can use whatever mechanisms / other parameters needed after this to connect through SSH to your remote host. In my case, I use key files configured in my ~/.ssh/configso I just need to specify user@hostto get in.

您可以使用此后所需的任何机制/其他参数通过 SSH 连接到您的远程主机。就我而言，我使用在我的配置中配置的密钥文件，~/.ssh/config因此我只需要指定user@host即可进入。

Issuing the command like this runs SSH in the foreground, so I can easily close it with Ctrl + C. If you want to run this tunnel in a background process you can add -fto do this.

发出这样的命令会在前台运行 SSH，因此我可以轻松地使用Ctrl + C. 如果您想在后台进程中运行此隧道，您可以添加-f来执行此操作。

Step 3: Connect from PHP / other mysql compatible methods

第 3 步：从 PHP/其他 mysql 兼容方法连接

The SSH tunnel running from above on your localhost will behave exactly like as if your mysql was running on 127.0.0.1. I use port 33306(note the triple 3) which lets me run my local sql server at its normal port. You can now connect as you would normally do. The mysqlcommand on the terminal looks like this:

在您的本地主机上从上方运行的 SSH 隧道的行为与您的 mysql 在127.0.0.1. 我使用端口33306（注意三重 3），它让我在其正常端口上运行我的本地 sql 服务器。您现在可以像往常一样进行连接。在mysql对终端看起来像这样的命令：

mysql -h 127.0.0.1 -P 33306 -u yourmysqluser -p

where -P(capital P) specifies the port where your SSH tunnel's local end is accepting connections. It's important to use the 127.0.0.1IP address instead of localhostbecause the mysql cli will try to possibly use the linux socket to connect.

其中-P（大写 P）指定 SSH 隧道的本地端接受连接的端口。重要的是使用127.0.0.1IP 地址而不是localhost因为 mysql cli 将尝试可能使用 linux 套接字进行连接。

For PHP connection strings, my data source name string (for my Yii2 config) looks like this:

对于 PHP 连接字符串，我的数据源名称字符串（对于我的 Yii2 配置）如下所示：

'dsn' => 'mysql:host=127.0.0.1;dbname=yourdbname;port=33306',

Passwords, and usernames are specified as normal.

密码和用户名被指定为正常。