通过 SSH PHP 连接 MySQL 数据库 [英] Connecting to a MySQL database through SSH PHP

问题描述

对于我的大学项目，我要连接到 MySQL 数据库.从 covid 19 开始，我不能去大学，所以必须远程进行，有人可以帮助处理 PHP 连接吗?

SSH 主机名:ssh.University.uk:2223SSH 用户名:s4905304SHH 密码:....SSH 密钥文件:空白

MySQL 主机名:127.0.0.1MySQL 服务器端口:3306用户名:s4905304密码:....

解决方案

试试这个:

第 1 步.

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

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

这里的关键是 -L 表示我们正在进行本地端口转发.

本地端口转发语法

::username@ssh_proxy_host.com

其他开关是:

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

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

-i/path/to/private-key

第 2 步.

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

mysql -h 127.0.0.1 -P 3307 -u dbuser -p 密码

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

不建议直接通过隧道连接到您的数据库服务器 - 直接从 Internet 访问数据库服务器是一项巨大的安全责任.将隧道目标地址设为 Jumpbox/堡垒主机的 Internet 地址(参见步骤 1 中的示例)，并将数据库设为远程网络上数据库服务器的内部 IP 地址.SSH 将完成剩下的工作.

感谢 Chris Snyder 在 http://chxo.com/be2/20040511_5667.html

第 3 步.

<块引用>

连接.DSN

PDO 有一种叫做 DSN 的奇特连接方法.没什么虽然很复杂 - 而不是一个简单明了的选项列表，PDO 要求你输入三个不同的配置指令不同的地方:

• database driver、host、db (schema) name 和 charset，以及不太常用的port 和 unix_socket 进入 DSN；
• username 和 password 去构造函数；
• 所有其他选项进入选项数组.

其中 DSN 是以分号分隔的字符串，由 param=value 组成对，从驱动程序名称和冒号开始:

 mysql:host=localhost;dbname=test;port=3306;charset=utf8mb4驱动程序^ ^冒号^参数=值对^分号

请注意，遵循正确的格式很重要 - 没有空格或在 DSN 中必须使用引号或其他装饰，但仅参数、值和分隔符，如手册中所示.>

这里有一个 MySQL 的例子:

 $host = '127.0.0.1';$db = '测试';$user = 'root';$pass = '';$charset = 'utf8mb4';$dsn = "mysql:host=$host;dbname=$db;charset=$charset";$选项= [PDO::ATTR_ERRMODE =>PDO::ERRMODE_EXCEPTION,PDO::ATTR_DEFAULT_FETCH_MODE =>PDO::FETCH_ASSOC,PDO::ATTR_EMULATE_PREPARES =>错误的，];尝试 {$pdo = new PDO($dsn, $user, $pass, $options);} catch (\PDOException $e) {throw new \PDOException($e->getMessage(), (int)$e->getCode());}

引自:https://phpdelusions.net/pdo#dsn

For my University project I am connecting to a MySQL database. As of covid 19 I can't go to uni so have to do it remotely can anyone help with the PHP connection?

SSH Hostname: ssh.University.uk:2223 SSH Username: s4905304 SHH Password: .... SSH Key File:blank

MySQL Hostname: 127.0.0.1 MySQL Server Port:3306 Username:s4905304 Password:.....

解决方案

Try this:

Step 1.

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

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

The key here is -L which says we're doing local port forwarding.

Local Port Forwarding Syntax

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

Other switches are:

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

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

-i /path/to/private-key

Step 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 -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.

Tunneling directly to your database server is not recommended - 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.

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

Step 3.

Connecting. DSN

PDO has a fancy connection method called DSN. It's nothing complicated though - instead of one plain and simple list of options, PDO asks you to input different configuration directives in three different places:

• database driver, host, db (schema) name and charset, as well as less frequently used port and unix_socket go into DSN;
• username and password go to constructor;
• all other options go into options array.

where DSN is a semicolon-delimited string, consists of param=value pairs, that begins from the driver name and a colon:

       mysql:host=localhost;dbname=test;port=3306;charset=utf8mb4
driver^    ^ colon         ^param=value pair    ^semicolon  

Note that it's important to follow the proper format - no spaces or quotes or other decorations have to be used in DSN, but only parameters, values and delimiters, as shown in the manual.

Here goes an example for MySQL:

 $host = '127.0.0.1';
$db   = 'test';
$user = 'root';
$pass = '';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];
try {
     $pdo = new PDO($dsn, $user, $pass, $options);
} catch (\PDOException $e) {
     throw new \PDOException($e->getMessage(), (int)$e->getCode());
}

Quoted from: https://phpdelusions.net/pdo#dsn

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