如何在Ubuntu上使用SSL连接到远程MySQL服务器

最新推荐文章于 2024-06-14 11:08:31 发布

culuo4781

最新推荐文章于 2024-06-14 11:08:31 发布

阅读量1k

点赞数

文章标签：数据库 linux mysql java python

原文链接：https://www.sqlshack.com/how-to-connect-to-remote-mysql-server-using-ssl-on-ubuntu-18-04/

版权

In this article, the steps to connect to remote MySQL databases using Secure Sockets Layer (SSL) will be shown. MySQL is one of the most popular relational database management systems and by default, is configured to accept only connections from the machine where MySQL is installed. To connect to the MySQL database which sits on another machine, the additional configuration must be set to accept the remote connection with secure SSL encryption.

在本文中，将显示使用安全套接字层（SSL）连接到远程MySQL数据库的步骤。 MySQL是最流行的关系数据库管理系统之一，默认情况下，MySQL被配置为仅接受来自安装MySQL的计算机的连接。要连接到位于另一台计算机上MySQL数据库，必须将其他配置设置为接受具有安全SSL加密的远程连接。

先决条件 (Prerequisite)

For this article, two instances of Linux Ubuntu 18.04 needs to be installed.

对于本文，需要安装两个Linux Ubuntu 18.04实例。

On one of the Ubuntu machines, install MySQL and configure the root user using the guide from the How to install MySQL on Ubuntu 18.04 page.

在其中一台Ubuntu计算机上，使用“ 如何在Ubuntu 18.04上安装MySQL”页面中的指南安装MySQL并配置root用户。

On the second machine install the MySQL client using the following command:

在第二台计算机上，使用以下命令安装MySQL客户端：

sudo apt install mysql-client

sudo apt安装mysql客户端

Otherwise, you will get the following message, when trying to connect to remote MySQL server:

否则，当尝试连接到远程MySQL服务器时，您将收到以下消息：

sudo: mysql: command not found

sudo：mysql：找不到命令

Missing mysql-client to connect to remote MySQL server

In this article, the name of a machine on which MySQL server sits will be called SSLServer1 and the name of a machine from which we will establish a secure SSL connection to a MySQL server will be called SSLServer2.

在本文中，MySQL服务器所在的计算机的名称将称为SSLServer1 ，从中我们将建立与MySQL服务器的安全SSL连接的计算机的名称将称为SSLServer2 。

检查SSL状态 (Checking status of SSL)

So, let’s start configuring SSL for the MySQL server on the SSLServer1 machine. First, let’s check the current status of SSL on the remote MySQL server instance.

因此，让我们开始为SSLServer1计算机上MySQL服务器配置SSL。首先，让我们检查远程MySQL服务器实例上SSL的当前状态。

使用以下命令登录到MySQL实例：

mysql -u root -p -h 127.0.0.1

mysql -u根-p -h 127.0.0.1

Enter a password for the root user and hit the Enter key from the keyboard.

输入root用户的密码，然后按键盘上的Enter键。

Once we are logged in, type and execute the following command:

登录后，键入并执行以下命令：

SHOW VARIABLES LIKE ‘%ssl%’;

显示变量，例如'％ssl％'；

Depending on the installed MySQL version on your machine, you can get different results. If you are running the MySQL server 5.7.27 and below versions, then you will probably get the following result:

根据计算机上安装MySQL版本，您可以获得不同的结果。如果运行的是MySQL服务器5.7.27及以下版本，则可能会得到以下结果：

Status of SSL displayed in the terminal of a remote MySQL Server instance

As can be seen, the have_openssl and have_ssl variables are disabled. To confirm that SSL is not used for the current connection, execute the following:

可以看出，禁用了have_openssl和have_ssl变量。要确认当前连接未使用SSL，请执行以下操作：

\ s

要么

status

状态

Command for checking of MySQL connection

The output result will be similar like one below:

输出结果将类似于以下内容：

————–
mysql Ver 14.14 Distrib 5.7.29, for Linux (x86_64) using EditLine wrapper
Connection id: 3
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ”
Using delimiter: ;
Server version: 5.7.29-0ubuntu0.18.04.1 (Ubuntu)
Protocol version: 10
Connection: 127.0.0.1 via TCP/IP
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
TCP port: 3306
Uptime: 10 min 10 sec
Threads: 1 Questions: 6 Slow queries: 0 Opens: 106 Flush tables: 1 Open tables: 99 Queries per second avg: 0.009
————–

——————
适用于Linux（x86_64）的mysql Ver 14.14 Distrib 5.7.29，使用EditLine包装器
连接ID：3
当前数据库：
当前用户：root @ localhost
SSL：未使用
当前寻呼机：标准输出
使用outfile：”
使用定界符：;
服务器版本：5.7.29-0ubuntu0.18.04.1（Ubuntu）
协议版本：10
连接：通过TCP / IP的127.0.0.1
服务器字符集：latin1
DB字符集：latin1
客户端特征集：utf8
康涅狄格州字符集：utf8
TCP端口：3306
正常运行时间：10分钟10秒
线程：1问题：6慢查询：0打开：106刷新表：1打开表：每秒平均查询99次：0.009
——————

As can be seen from the image above, the SSL is not in use for this connection.

从上图可以看出，该连接未使用SSL。

Another method to determine whether the current connection with the MySQL server uses encryption is to check the session value of the Ssl_cipher status variable:

确定当前与MySQL服务器的连接是否使用加密的另一种方法是检查Ssl_cipher状态变量的会话值：

SHOW SESSION STATUS LIKE ‘Ssl_cipher’;

显示会话状态，例如“ Ssl_cipher”；

If the value is empty, it indicates that the current connection does not use encryption:

如果该值为空，则表明当前连接未使用加密：

In the situation where current connection uses encryption, then the value will not be empty and will look like shown below:

在当前连接使用加密的情况下，该值将不会为空，如下所示：

Note: I use MySQL 5.7.29 version in which by default the SSL is enabled, but for this example, I disabled SSL by modifying the my.cnf file to show the DISABLED status of SSL:
注意：我使用的是MySQL 5.7.29版本，默认情况下启用了SSL，但在此示例中，我通过修改my.cnf文件以显示SSL的禁用状态来禁用 SSL：

In this configuration file, I entered the following command to disable SSL:

在此配置文件中，我输入了以下命令来禁用SSL：

skip_ssl

To enable SSL and generates appropriate certification and key files you can use the mysql_ssl_rsa_setup utility:

要启用SSL并生成适当的证书和密钥文件，可以使用mysql_ssl_rsa_setup实用程序：

sudo mysql_ssl_rsa_setup –uid=mysql

sudo mysql_ssl_rsa_setup –uid = mysql

By default, MySQL stored SSL certification on this location: /var/lib/mysql

默认情况下，MySQL在以下位置存储SSL证书： / var / lib / mysql

Let’s check that by executing this command:

让我们通过执行以下命令进行检查：

sudo find /var/lib/mysql/ -name ‘*.pem’

sudo查找/ var / lib / mysql / -name'* .pem'

Generated SSL certification for connection to remote MySQL server

But if you have installed the MySQL 5.7.28 version and above, then SSL certification will be automatically generated on the /var/lib/mysql location and SSL encryption enabled when starting MySQL:

但是，如果您安装了MySQL 5.7.28及更高版本，则将在/ var / lib / mysql位置上自动生成SSL认证，并在启动MySQL时启用SSL加密：

Configurated SSL certification for connect to remote MySQL server

配置远程MySQL服务器 (Configuring remote MySQL server)

Now that we have enabled SSL, the next step in the configuration of MySQL is to enable (allow) remote connection. By default, MySQL is configured to listen to the connection from the machine on which is installed and only accept connection from the localhost AKA 127.0.0.1 IP address.

既然我们已经启用了SSL，那么MySQL配置的下一步就是启用（允许）远程连接。默认情况下，MySQL配置为侦听来自其安装机器的连接，并且仅接受来自本地主机 AKA 127.0.0.1 IP地址的连接。

If we try now to connect from the client machine (SSLServer2) to MySQL that sit on SSLServer1 machine, we will probably get the following error message:

如果现在尝试从客户端计算机（SSLServer2）连接到位于SSLServer1计算机上MySQL，我们可能会收到以下错误消息：

ERROR 2003 (HY000): Can’t connect to MySQL server on (111)

错误2003（HY000）：无法连接到（111）上MySQL服务器

Connection to remote MySQL server refused

To allow connection to MySQL server from any remote IP address, in the MySQL configuration file /etc/mysql/my.cnf add the following:

要允许从任何远程IP地址连接到MySQL服务器，请在MySQL配置文件/etc/mysql/my.cnf中添加以下内容：

bind-address = 0.0.0.0

绑定地址= 0.0.0.0

Set MySQL to allow connection from any remote IP address

Now, restart the MySQL service for the changes to changes take effect:

现在，重新启动MySQL服务，以使更改生效：

sudo service mysql restart

sudo服务mysql重新启动

When we try again to connect to remote MySQL server, we will get an error message:

当我们再次尝试连接到远程MySQL服务器时，我们将收到一条错误消息：

ERROR 1045 (28000): Access denied for user (using password: YES)

错误1045（28000）：拒绝用户访问（使用密码：是）

Access denied to connect to remote MySQL server

Currently, all MySQL users are configured to connect locally from the MySQL server. To connect to remote MySQL server, you will need to create a new user with the IP address of the client machine, in our case, we need the IP address of the SSLServer2 machine.

当前，所有MySQL用户都配置为从MySQL服务器本地连接。要连接到远程MySQL服务器，您将需要使用客户端计算机的IP地址创建一个新用户，在本例中，我们需要SSLServer2计算机的IP地址。

To view the IP address of the machine (SSLServer2), in terminal type and execute the following command:

要在终端类型中查看机器的IP地址（SSLServer2），然后执行以下命令：

Ifconfig

Now, when we get all necessary information, let’s create a user that will be allowed to connect to remote MySQL with secure connection by adding REQUIRE SSL clause:

现在，当我们获得所有必要的信息时，让我们通过添加REQUIRE SSL子句，创建一个可以通过安全连接连接到远程MySQL的用户：

CREATE USER ‘SSL_USER’@'<SSLServer2_IP>’ IDENTIFIED BY ‘password’ REQUIRE SSL;

CREATE USER'SSL_USER'@'<SSLServer2_IP>'由'password'标识需要SSL；

After we have created the user, let’s gave him all permissions to access all remote MySQL databases.

创建用户后，让我们授予他访问所有远程MySQL数据库的所有权限。

Execute the following command:

执行以下命令：

GRANT ALL ON *.* TO ‘SSL_USER’@'<SSLServer2_IP>’;

全部授予*。* TO'SSL_USER'@'<SSLServer2_IP>';

To apply these settings immediately, execute the following:

要立即应用这些设置，请执行以下操作：

FLUSH PRIVILEGES;

冲洗特权;

One more thing that needs to be done on the SSLServer1 machine is to add the require_secure_transport option to the my.cnf file and set it to ON.

在SSLServer1计算机上还需要完成的另一件事是将require_secure_transport选项添加到my.cnf文件并将其设置为ON 。

Enter require_secure_transport option to my.cnf file

This requires all remote connections to be made with SSL.

这要求使用SSL建立所有远程连接。

After this, restart the MySQL service, to apply the new settings:

之后，重新启动MySQL服务，以应用新设置：

sudo service mysql restart

sudo服务mysql重新启动

With this, we allowed the SSL_USER to connect from a remote location. To confirm that, let’s connect with SSL_USER from the SSLServer2 machine.

这样，我们就允许SSL_USER从远程位置进行连接。为了确认，让我们用SSL_USER连接从SSLServer2机。

On the SSLServer2 machine, open the Terminal and execute the following command:

在SSLServer2计算机上，打开终端并执行以下命令：

mysql -u SSL_USER -p -h <SSLServer1_IP>

Connect to remote MySQL server using SSL_USER

After entering the password of SSL_USER, you’ll be able to connect to remote MySQL server that sits on the SSLServer1 machine:

输入SSL_USER的密码后，您将能够连接到位于SSLServer1机器上的远程MySQL服务器：

Remote MySQL server connection established

Now, type the \s to confirm the status of the server and confirm you are using a secure connection:

现在，键入\ s确认服务器状态并确认您正在使用安全连接：

To confirm that MySQL server only accepts the secure connection and rejects insecure, try to connect with the same credentials but this time in connection string add –ssl-mode=disabled command:

要确认MySQL服务器仅接受安全连接并拒绝不安全，请尝试使用相同的凭据进行连接，但这一次在连接字符串中添加–ssl-mode = disabled命令：

mysql -u SSL_USER -p -h –ssl-mode=disabled

mysql -u SSL_USER -p -h –ssl-mode =禁用

After executing, the following error may appear:

执行后，可能会出现以下错误：

ERROR 1045 (28000): Access denied for user ‘SSL_USER’@’SSLServer2_IP’ (using password: YES)

错误1045（28000）：用户'SSL_USER'@''SSLServer2_IP'的访问被拒绝（使用密码：是）

By now, you have set MySQL server with an SSL certificate signed by a locally generated certificate authority (CA), this is enough to provide secure encryption for incoming connections.

到目前为止，您已经为MySQL服务器设置了由本地生成的证书颁发机构（CA）签名的SSL证书，这足以为传入的连接提供安全的加密。

To provide the fully trusted relationship between the SSLServer1 and SSLServer2 machines, from SSLServer1 machine will be copied the CA certificate with the client certificate and the client key to the SSLServer2 machine, also the SSL_USER will be modified to require a trusted certificate.

为了提供SSLServer1和SSLServer2计算机之间的完全信任关系，将从SSLServer1计算机复制带有客户端证书和客户端密钥的CA证书到SSLServer2计算机，还将SSL_USER修改为需要信任证书。

On the SSLServer2 machine first, create a folder where the client files will be placed:

首先在SSLServer2机器上，创建一个文件夹，将在其中放置客户端文件：

mkdir ~/ssl-client

mkdir〜/ ssl-client

Use the SCP or SFTP to secure transfer files from one machine to another, or just use the cat command to read and copy the content from the CA certificate, client-cert.pem and client-key.pem files on the SSLServer1 machine and create those files and past the content in it on the SSLServer2 machine:

使用SCP或SFTP将传输文件从一台机器安全保护到另一台机器上，或仅使用cat命令从SSLServer1机器上的CA证书 ， client-cert.pem和client-key.pem文件中读取和复制内容，然后创建这些文件并将内容粘贴到SSLServer2计算机上：

Creating SSL files to the ssl-client folder

After the appropriate files are copied, alter the SSL_USER, and put the REQUIRE X509 clause:

复制适当的文件后，更改SSL_USER ，并放置REQUIRE X509子句：

ALTER USER ‘SSL_USER’@'<SSLServer2_IP>’ REQUIRE X509;

ALTER USER'SSL_USER'@'<SSLServer2_IP>'要求X509;

Now, when connecting to remote MySQL server with the SSL_USER user, the –ssl-key and –ssl-cert options must be specified, also it is recommended but not required the –ssl-ca option to be specified. If we connect to remote MySQL server without specifying client certificates:

现在，当使用SSL_USER用户连接到远程MySQL服务器时，必须指定–ssl-key和–ssl-cert选项，也建议这样做，但不需要指定–ssl-ca选项。如果我们在未指定客户端证书的情况下连接到远程MySQL服务器：

mysql -u SSL_USER1 -p -h <SSLServer1_IP>

The following error message may appear:

可能会出现以下错误消息：

ERROR 1045 (28000): Access denied for user ‘SSL_USER’@’SLLServer2_IP’ (using password: YES)

错误1045（28000）：用户'SSL_USER'@''SLLServer2_IP'的访问被拒绝（使用密码：是）

Let’s specify appropriate client certificate by adding –ssl-ca, –ssl-cert, and –ssl-key options to connection string to point to the files under the ~/ssl-client directory:

让我们通过在连接字符串中添加–ssl-ca，–ssl-cert和–ssl-key选项来指定适当的客户端证书，以指向〜/ ssl-client目录下的文件：

mysql -u SSL_USER -p -h SSLServer1_IP –ssl-ca=~/ssl-client/ca.pem –ssl-cert=~/ssl-client/client-cert.pem –ssl-key=~/ssl-client/client-key.pem

mysql -u SSL_USER -p -h SSLServer1_IP –ssl-ca =〜/ ssl-client / ca.pem –ssl-cert =〜/ ssl-client / client-cert.pem –ssl-key =〜/ ssl-client /客户端密钥

After you hit the Enter, you should connect to remote MySQL server successfully:

按下Enter键后 ，您应该成功连接到远程MySQL服务器：

Using SSL files to connect to remote MySQL server

Using the –ssl-ca, –ssl-cert, and –ssl-key options and entering a path to the relevant files every time you want to connect to remote MySQL server is a tidiest and time-consuming job, so let’s optimizing and improve connection usability.

每次要连接到远程MySQL服务器时，使用–ssl-ca，–ssl-cert和–ssl-key选项并输入相关文件的路径是一项整齐且耗时的工作，因此让我们进行优化和改进连接可用性。

In the SSLServer2 machine, if the my.cnf file doesn’t exist under these locations:

在SSLServer2机器中，如果my.cnf文件在以下位置不存在：

/etc/my.cnf
/etc/my.cnf
/etc/mysql/my.cnf
/etc/mysql/my.cnf
/usr/etc/my.cnf
/usr/etc/my.cnf
~/.my.cnf
〜/ .my.cnf

We can create one in the home directory:

我们可以在主目录中创建一个：

~/.my.cnf:

〜/ .my.cnf：

Execute the following command:

执行以下命令：

nano ~/.my.cnf

纳米〜/ .my.cnf

And in the .my.cnf file paste and save the following:

并在.my.cnf文件中粘贴并保存以下内容：

[client]
ssl-ca = ~/ssl-client/ca.pem
ssl-cert = ~/ssl-client/client-cert.pem
ssl-key = ~/ssl-client/client-key.pem

[客户]
ssl-ca =〜/ ssl-client / ca.pem
ssl-cert =〜/ ssl-client / client-cert.pem
ssl-key =〜/ ssl-client / client-key.pem

Set up SSL paths in the client .my.cnf file

Now, you can connect to remote MySQL server without adding the –ssl-ca, –ssl-cert, and –ssl-key options in the connection string:

现在，您可以连接到远程MySQL服务器，而无需在连接字符串中添加–ssl-ca，–ssl-cert和–ssl-key选项：

mysql -u SSL_USER1 -p -h <SSLServer1_IP>

Connect to remote MySQL server without using SSL options in connection string

结论 (Conclusion)

In this article, you’ve seen how to set up a remote MySQL database connection. We kicked off by explaining the prerequisites for this to work, then we checked the status of SSL, made some modifications to allow users to connect to remote MySQL server, and finally tweaked some settings a little bit to make life easier and save some typing every time we connect remotely.

在本文中，您已经了解了如何设置远程MySQL数据库连接。我们首先解释了要实现此目的的先决条件，然后检查了SSL的状态，进行了一些修改以允许用户连接到远程MySQL服务器，最后进行了一些调整以简化工作，并节省了每次输入的费用我们远程连接的时间。

翻译自: https://www.sqlshack.com/how-to-connect-to-remote-mysql-server-using-ssl-on-ubuntu-18-04/

culuo4781

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
如何在Ubuntu上使用SSL连接到远程MySQL服务器

In this article, the steps to connect to remote MySQL databases using Secure Sockets Layer (SSL) will be shown. MySQL is one of the most popular relational database management systems and b...
复制链接

扫一扫