在 PHP 中通过 SSH 连接到 MySQL 服务器 [英] Connect to a MySQL server over SSH in PHP

问题描述

我在远程 Linux 机器上有我的数据库，我想使用 SSH 和 PHP 函数进行连接(我目前为此使用 ssh2 库).我尝试使用 mysql_connect，但它使我无法访问(虽然我已授予权限)当我尝试使用此功能时:

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:

$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());

我收到错误mysqli_connect() 期望参数 6 为字符串，资源给定".我该如何解决?

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

推荐答案

SSH隧道解决方案

设置到您的 MySQL 数据库服务器的 SSH 隧道(通过 Jumpbox 代理以确保安全).

SSH Tunnel Solution

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

根据您的要求，您可以使用带有内置 SSH 隧道支持的 GUI MySQL 客户端，例如 Visual Studio Code 转发端口/创建 SSH 隧道, TablePlus 或使用 PuTTY 设置本地em> 端口转发.

Depending on your requirements, you can use a GUI MySQL client with SSH Tunnelling support built-in such as Visual Studio Code Forwarding a port / creating SSH tunnel, TablePlus or use PuTTY to setup local port forwarding.

在 macOS 上，我喜欢 Secure Pipes 或 TablePlus.

On macOS, I like Secure Pipes or TablePlus.

第一步.

ssh -fNg -L 3307:10.3.1.55:3306 [e-mail] 

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

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

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

I've chosen to use port 3307 above. All traffic on my local machine directed to this port will now be 'port-forwarded' via my ssh client to the ssh server running on the host at address ssh-jumpbox.com.

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

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.

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

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(转到后台)
-N(不执行远程命令)
-g(允许远程主机连接到本地转发端口)

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

私钥认证，在上面添加(-i)开关:

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

-i/path/to/private-key

-i /path/to/private-key

第 2 步.

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

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 -h 127.0.0.1 -P 3307 -u dbuser -p passphrase

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

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

安全说明
不要直接隧道到您的数据库服务器.拥有可从 Internet 直接访问的数据库服务器是一项巨大的安全责任.将隧道目标地址设为您的 Jumpbox/堡垒主机的互联网地址(参见步骤 1 中的示例)，并将您的数据库设为远程网络上数据库服务器的内部 IP 地址.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 internal IP address of your database server on the remote network. SSH will do the rest.

第 3 步.

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

Now connect up your PHP application with:

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

感谢 Chris Snyder 出色的文章，详细介绍了 ssh 命令行隧道用于 MySQL 连接.

Credit to Chris Snyder's great article detailing ssh command line tunnelling for MySQL connectivity.

这篇关于在 PHP 中通过 SSH 连接到 MySQL 服务器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！