远程mysql用ssh连接_使用SSH密钥连接到远程MySQL服务器

远程mysql用ssh连接

In this article, it will be described how to connect to remote MySQL Server on Linux OS using SSH key pairs. Windows OS will be used as a host from which will be established a connection using PuTTY as a client. Besides using PuTTY as a client for establishing the connection to remote machines, it will be used to generate and use secure shell – SSH keys as well.

在本文中，将描述如何使用SSH密钥对连接到Linux OS上的远程MySQL Server。 Windows操作系统将用作主机，使用PuTTY作为客户端从中建立连接。 除了将PuTTY用作建立与远程计算机的连接的客户端外，它还将用于生成和使用安全Shell – SSH密钥。

Before starting, some prerequisites must be fulfilled. On the host machine from which a remote machine will be accessed, the Windows 10 OS should be installed and on the remote machine the Linux Ubuntu 18.04 OS.

开始之前，必须满足一些先决条件。 在将要从其访问远程计算机的主机上，应安装Windows 10 OS ，在远程计算机上应安装Linux Ubuntu 18.04 OS。

Also, on a remote machine MySQL Server needs to be installed, too. For more information about how to install MySQL on Linux refer to How To Install MySQL on Ubuntu 18.04 page.

另外，在远程计算机上，也需要安装MySQL Server 。 有关如何在Linux上安装MySQL的更多信息，请参阅如何在Ubuntu 18.04页面上安装MySQL 。

To create SSH keys, the PuTTYgen key generator needs to be installed first. Normally, PuTTYgen is installed as a part of the PuTTY.msi installation package.

要创建SSH密钥，首先需要安装PuTTYgen密钥生成器。 通常，PuTTYgen是PuTTY.msi安装软件包的一部分安装。

安装PuTTY客户端 (Installation of PuTTY client)

Installation of the PuTTY is straightforward, double-click on the executable installer, the welcome screen appears:

PuTTY的安装非常简单，双击可执行安装程序，出现欢迎屏幕：

Click the Next button to continue with the installation. Details about the installation of the PuTTY client can be found on the How to Connect to a Remote MySQL Server Using SSH page.

单击下一步按钮继续安装。 有关PuTTY客户端安装的详细信息，可以在“ 如何使用SSH连接到远程MySQL服务器”页面上找到。

After installation, in the start menu, find and start PuTTYgen:

安装后，在开始菜单中，找到并启动PuTTYgen：

When the PuTTY Key Generator window appears, click the Generate button:

当出现“ PuTTY密钥生成器”窗口时，单击“ 生成”按钮：

After you click the Generate button, in the blank area of the Key section, move the mouse around to provide randomness for the key:

单击“ 生成”按钮后，在“ 密钥”部分的空白区域中，四处移动鼠标以提供密钥的随机性：

By default, a 2048-bit SSH-2 RSA key will be generated. If you want, an additional security layer can be provided by entering the passphrase in the Key passphrase and Confirm passphrase box. When authenticating to remote machines with a private SSH key, you’ll be prompted for the passphrase. This is good in situations when someone obtains your private key and try to connect to the remote machine. Without a passphrase, they will be prevented, but in case of forgetting the passphrase, there is no way to recover/reset it. In this example, we will go without the passphrase:

默认情况下，将生成一个2048位SSH-2 RSA密钥。 如果需要，可以通过在“ 密钥密码”和“ 确认密码”框中输入密码来提供附加的安全层。 使用私有SSH密钥对远程计算机进行身份验证时，系统会提示您输入密码。 在有人获取您的私钥并尝试连接到远程计算机的情况下，这非常有用。 如果没有密码短语，则会阻止它们，但是如果忘记了密码短语，将无法恢复/重置它。 在此示例中，我们将不使用密码短语：

To save SSH keys to your computer, for later usage, click Save private key to save the private key as .ppk file:

要将SSH密钥保存到您的计算机中，以供以后使用，请单击“ 保存私钥”将私钥另存为.ppk文件：

As for public SSH key, just select all and copy from the Public key section:

至于公共SSH密钥，只需选择全部并从“公共密钥”部分复制：

Open a text editor, paste copied code and save it:

打开文本编辑器，粘贴复制的代码并保存：

Now, when SSH keys are saved, start PuTTY client to connect to the remote machine. In the Host Name (or IP address) box, enter a name or IP address of the remote machine.

现在，保存SSH密钥后，启动PuTTY客户端以连接到远程计算机。 在“ 主机名（或IP地址）”框中，输入远程计算机的名称或IP地址。

To find the address of a remote machine, go to the remote machine; in our case, that is Linux Ubuntu 18.04, in the Terminal type ifconfig command and press the Enter key. Under the inet information about IP address will be shown:

要查找远程计算机的地址，请转到远程计算机； 在我们的示例中，即Linux Ubuntu 18.04，在终端中键入ifconfig命令，然后按Enter键。 在inet下，将显示有关IP地址的信息：

Now type that IP address in the Host Name (or IP address) box of PuTTY Configuration window and click the Open button:

现在，在“ PuTTY配置”窗口的“ 主机名”（或IP地址）框中键入该IP地址，然后单击“ 打开”按钮：

On the PuTTY Security Alert window, click the No button to continue:

在“ PuTTY安全警报”窗口上，单击“ 否”按钮继续：

In the PuTTY terminal, enter the credentials that are used to connect to the remote machine:

在PuTTY终端中，输入用于连接到远程计算机的凭据：

When the connection is successfully established, the PuTTY terminal will look like this:

成功建立连接后，PuTTY终端将如下所示：

Now, when we are connected, the first thing that needs to be done is to create a .ssh directory in which will be placed public SSH key that was generated by the PuTTY Key Generator.

现在，当我们建立连接时，首先要做的是创建一个.ssh目录，该目录中将放置由PuTTY密钥生成器生成的公共SSH密钥。

To do that, in the terminal, type the mkdir .ssh command and press the Enter key:

为此，在终端中，键入mkdir .ssh命令，然后按Enter键：

Navigate to the .ssh directory and, in it, create the authorized_keys file. In the terminal type cd .ssh to navigate to the directory and to create a file type the touch authorized_keys command and press the Enter key:

导航到.ssh目录，然后在其中创建authorized_keys文件。 在终端中，输入cd .ssh以导航到目录并创建文件，请输入touchauthorized_keys命令，然后按Enter键：

In the authorized_keys file that is created, the public SSH key will be pasted. Open the authorized_keys file by executing the sudo nano authorized_keys command:

在创建的authorized_keys文件中，将粘贴公共SSH密钥。 通过执行sudo的打开authorized_keys文件纳米 authorized_keys的命令：

Now when the authorized_keys file is opened, paste public SSH key in it and save the document:

现在，当打开authorized_keys文件时，在其中粘贴公共SSH密钥并保存文档：

To save the changes, press the Ctrl+X and in the prompted dialog type Yes or Y and hit the Enter key:

要保存更改，请按Ctrl + X，然后在提示的对话框中键入Yes或Y，然后按Enter键：

Next thing that needs to be done is to edit SSH Server configuration file. To do that, type the sudo vim /etc/ssh/sshd_config command:

接下来需要做的是编辑SSH服务器配置文件。 为此，请键入sudo vim / etc / ssh / sshd_config命令：

In this file, set the PermitRootLogin value to prohibit-password and for the PasswordAuthentication set the value to no:

在此文件中，将PermitRootLogin值设置为禁止密码 ，对于PasswordAuthentication，将值设置为no：

Type :wq to save changes and exit from the file:

输入：wq保存更改并退出文件：

In order for changes to take effect, the SSH Server needs to be restarted. To do this, type the sudo systemctl restart sshd.service command and press Enter:

为了使更改生效，需要重新启动SSH服务器。 为此，请键入sudo systemctl restart sshd.service命令，然后按Enter键 ：

Exit from the current session, by executing the logout command:

通过执行注销命令退出当前会话：

Now, as the SSH public is set on the remote machine, start PuTTY client and in the Host Name (or IP address) box, type the username followed by the at (@) symbol and the IP address of the remote machine:

现在，在远程计算机上设置了SSH公用程序后，启动PuTTY客户端，然后在“ 主机名（或IP地址）”框中，键入用户名，后跟at符号（ @ ）和远程计算机的IP地址 ：

One more thing that needs to be set before we hit the Open button is to load the private SSH key for authentication. To do that, in the Category tree, find and expand the SSH tab and select the Auth tab. On the right side, click the Browser button and find the previously saved private SSH key, that is generated by PuTTY Key Generator and click the Open button:

在单击“ 打开”按钮之前，还需要设置另一件事，就是加载专用的SSH密钥以进行身份​​验证。 为此，在“ 类别”树中，找到并展开“ SSH”选项卡，然后选择“ 身份验证”选项卡。 在右侧，单击“ 浏览器”按钮，找到先前保存的由PuTTY密钥生成器生成的SSH专用密钥，然后单击“ 打开”按钮：

As can be seen, the user will not be prompted for login credentials and we are automatically connected to the remote machine:

可以看出，将不会提示用户输入登录凭据，并且我们会自动连接到远程计算机：

Now, all that needs to be done in order to connect to remote MySQL server is to type the MySQL user credentials after executing the mysql -u root -p command:

现在，要连接到远程MySQL服务器，需要做的就是在执行mysql -u root -p命令后键入MySQL用户凭证：

More useful information about the connection to remote MySQL Server can be found on the How to Connect to a Remote MySQL Server Using SSH page.

有关与远程MySQL Server的连接的更多有用信息，可以在“ 如何使用SSH连接到远程MySQL Server”页面上找到。

翻译自: https://www.sqlshack.com/using-ssh-keys-to-connect-to-a-remote-mysql-server/

远程mysql用ssh连接