您的位置: 首页  >  技术问答  >  通过PHP中的SSH连接到MySQL服务器

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

分类: 技术问答 • 2022-03-01 09:22:41

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

问题描述：

我的数据库位于远程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 (preferably, through a Jumpbox for security).


根据您的要求，您可以使用具有SSH隧道支持的GUI MySQL客户端，例如 SequelPro ，使用 Visual Studio代码 PuTTY 设置端口转发

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

有一个名为安全管道的macOS GUI ssh隧道工具，您可能也会发现它很有用.

There is a macOS GUI ssh tunnelling tool called Secure Pipes which you may find useful too.


第1步. 

ssh -fNg -L 3307:10.3.1.55:3306 username@ssh-jumpbox.com

这里的键是'-L'开关，它告诉ssh我们正在请求 local 端口转发.我选择使用上面的端口 3307 .现在，我的 local 计算机上定向到该端口的所有流量都将通过我的 ssh客户端端口转发"到运行在服务器上的 ssh服务器主机位于地址ssh-jumpbox.com.在这种情况下，Jumpbox ssh服务器将代表您解密流量并代表您与MySQL数据库服务器建立网络连接. MySQL数据库服务器会看到来自Jumpbox内部网络地址的连接.

The key here is the '-L' switch which tells ssh we're requesting local port forwarding. 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. The Jumpbox ssh 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/Bastion主机的Internet地址(请参阅步骤1中的示例)，并将数据库目标为远程网络上数据库服务器的内部 IP地址. SSH将完成其余的工作.

NB: I don’t recommend tunnelling 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的精彩文章中，敬请访问 http://chxo.com/be2/20040511_5667.html

Credit to Chris Snyder's great article at http://chxo.com/be2/20040511_5667.html

相关推荐