博客

介绍

(

Introduction

)

MySQL is the

most popular

open-source relational database management system in the world. While modern package managers have reduced some of the friction to getting MySQL up and running, there is still some further configuration that should be performed after you install it. One of the most important aspects to spend some extra time on is security.

MySQL是世界上

最流行

的开源关系数据库管理系统。 尽管现代的软件包管理器减轻了启动和运行MySQL的麻烦，但是在安装MySQL之后仍需要进行一些进一步的配置。 花费一些额外时间的最重要方面之一是安全性。

By default, MySQL is configured to only accept local connections, or connections that originate from the same machine where MySQL is installed. If you need to access your MySQL database from a remote location, it’s important that you do so securely. In this guide, we will demonstrate how to configure MySQL on Ubuntu 18.04 to accept remote connections with SSL/TLS encryption.

默认情况下，MySQL配置为仅接受本地连接或源自安装MySQL的同一台计算机的连接。 如果您需要从远程位置访问MySQL数据库，请务必安全地进行访问。 在本指南中，我们将演示如何在Ubuntu 18.04上配置MySQL以接受具有SSL / TLS加密的远程连接。

先决条件

(

Prerequisites

)

To complete this guide, you will need:

要完成本指南，您将需要：

• 
  Two
  
  Ubuntu 18.04 servers. We will use one of these servers as the MySQL server while we’ll use the other as the client machine. Create a non-root user with
  
sudo

  privileges and enable a firewall with
  
ufw

  on each of these servers. Follow our
  
  Ubuntu 18.04 initial server setup guide
  
  to get both servers into the appropriate initial state.

  
  两台
  
  Ubuntu 18.04服务器。 我们将其中一台服务器用作MySQL服务器，而将另一台服务器用作客户端计算机。 创建具有
  
sudo

  特权的非root用户，
  
ufw

  在每台服务器上使用
  
ufw

  启用防火墙。 请遵循我们的
  
  Ubuntu 18.04初始服务器设置指南
  
  ，将两个服务器都
  
  设置
  
  为适当的初始状态。

• On
  
  one of the machines
  
  , install and configure the MySQL server. Follow
  
  Steps 1 through 3
  
  of our
  
  MySQL installation guide for Ubuntu 18.04
  
  to do this. As you follow this guide, be sure to configure your
  
  root
  
  MySQL user to authenticate with a password, as described in
  
  Step 3
  
  of the guide, as this is necessary to connect to MySQL using TCP rather than the local Unix socket.

  在
  
  其中一台计算机上
  
  ，安装和配置MySQL服务器。 请按照
  
  适用于Ubuntu 18.04MySQL安装指南中的
  
  
  步骤1至3
  
  进行操作。 遵循本指南时，请确保按照指南的
  
  步骤3
  
  所述将MySQL
  
  根
  
  用户配置为使用密码进行身份验证，因为使用TCP而不是本地Unix套接字连接到MySQL是必需的。

Please note that throughout this guide, the server on which you installed MySQL will be referred to as the

MySQL server

and any commands that should be run on this machine will be shown with a blue background, like this:

请注意，在本指南中，安装了

MySQL的服务器

将称为

MySQL服务器，

并且应在该计算机上运行的所有命令均以蓝色背景显示，如下所示：

Similarly, this guide will refer to the other server as the

MySQL client

and any commands that must be run on that machine will be shown with a red background:

同样，本指南将另一台服务器称为

MySQL客户端，

并且必须在该计算机上运行的任何命令将以红色背景显示：

Please keep these in mind as you follow along with this tutorial so as to avoid any confusion.

在学习本教程时，请牢记这些，以免造成任何混淆。

步骤1 —检查MySQL当前的SSL / TLS状态

(

Step 1 — Checking MySQL’s Current SSL/TLS Status

)

Before you make any configuration changes, you can check the current SSL/TLS status on the

MySQL server

instance.

进行任何配置更改之前，您可以检查

MySQL服务器

实例上的当前SSL / TLS状态。

Use the following command to begin a MySQL session as the

root

MySQL user. This command includes the

-p

option, which instructs

mysql

to prompt you for a password in order to log in. It also includes the

-h

option which is used to specify the host to connect to. In this case it points it to

127.0.0.1

, the IPv4 loopback interface also known as

localhost

. This will force the client to connect with

TCP

instead of using the local socket file. MySQL attempts to make connections through a

Unix socket file

by default. This is generally faster and more secure, since these connections can only be made locally and don’t have to go through all the checks and routing operations that TCP connections must perform. Connecting with TCP, however, allows us to check the SSL status of the connection:

使用以下命令以MySQL

根

用户身份开始MySQL会话。 该命令包括

-p

选项，该选项指示

mysql

提示您输入密码以登录。它还包括

-h

选项，该选项用于指定要连接的主机。 在这种情况下，它将指向

127.0.0.1

，这是IPv4环回接口，也称为

localhost

。 这将强制客户端使用

TCP

而不是使用本地套接字文件进行连接。 MySQL默认会尝试通过

Unix套接字文件

建立连接。 这通常更快，更安全，因为这些连接只能在本地进行，而不必经过TCP连接必须执行的所有检查和路由操作。 但是，使用TCP连接可以检查连接的SSL状态：

You will be prompted for the MySQL

root

password that you chose when you installed and configured MySQL. After entering it you’ll be dropped into an interactive MySQL session.

系统将提示您输入在安装和配置MySQL时选择MySQL

根

密码。 输入后，您将进入交互式MySQL会话。

Show the state of the SSL/TLS variables issuing the following command:

显示发出以下命令的SSL / TLS变量的状态：

   
   

    
    Output
   
   
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_openssl  | DISABLED |
| have_ssl      | DISABLED |
| ssl_ca        |          |
| ssl_capath    |          |
| ssl_cert      |          |
| ssl_cipher    |          |
| ssl_crl       |          |
| ssl_crlpath   |          |
| ssl_key       |          |
+---------------+----------+
9 rows in set (0.01 sec)

The

have_openssl

and

have_ssl

variables are both marked as

DISABLED

. This means that SSL functionality has been compiled into the server, but that it is not yet enabled.


have_openssl

和

have_ssl

变量都标记为

DISABLED

。 这意味着SSL功能已被编译到服务器中，但尚未启用。

Check the status of your current connection to confirm this:

检查您当前连接的状态以确认这一点：

   
   

    
    Output
   
   
--------------
mysql  Ver 14.14 Distrib 5.7.26, for Linux (x86_64) using  EditLine wrapper

Connection id:      9
Current database:   
Current user:       root@localhost
SSL:         Not in use
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server version:     5.7.26-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:         40 min 11 sec

Threads: 1  Questions: 33  Slow queries: 0  Opens: 113  Flush tables: 1  Open tables: 106  Queries per second avg: 0.013
--------------

As the above output indicates, SSL is not currently in use for this connection, even though you’re connected over TCP.

如上面的输出所示，即使您通过TCP连接，当前也没有使用SSL进行此连接。

Close the current MySQL session when you are finished:

完成后关闭当前MySQL会话：

Now that you’ve confirmed your MySQL server isn’t using SSL, you can move on to the next step where you will begin the process of enabling SSL by generating some certificates and keys. These will allow your server and client to communicate with one another securely.

既然您已经确认MySQL服务器未使用SSL，则可以继续执行下一步，在此步骤中，通过生成一些证书和密钥来开始启用SSL的过程。 这些将使您的服务器和客户端可以安全地相互通信。

步骤2 —生成SSL / TLS证书和密钥

(

Step 2 — Generating SSL/TLS Certificates and Keys

)

To enable SSL connections to MySQL, you first need to generate the appropriate certificate and key files. MySQL versions 5.7 and above provide a utility called

mysql_ssl_rsa_setup

that helps simplify this process. The version of MySQL you installed by following the

prerequisite MySQL tutorial

includes this utility, so we will use it here to generate the necessary files.

要启用与MySQL的SSL连接，首先需要生成适当的证书和密钥文件。 MySQL 5.7及更高版本提供了一个名为

mysql_ssl_rsa_setup

的实用程序，可帮助简化此过程。 通过遵循

先决条件MySQL教程

安装MySQL版本包括此实用程序，因此我们将在此处使用它来生成必要的文件。

The MySQL process must be able to read the generated files, so use the

--uid

option to declare

mysql

as the system user that should own the generated files:

MySQL进程必须能够读取生成的文件，因此请使用

--uid

选项将

mysql

声明为应拥有生成文件的系统用户：

This will produce output that looks similar to the following:

这将产生类似于以下内容的输出：

   
   

    
    Output
   
   
Generating a 2048 bit RSA private key
.+++
..........+++
writing new private key to 'ca-key.pem'
-----
Generating a 2048 bit RSA private key
........................................+++
............+++
writing new private key to 'server-key.pem'
-----
Generating a 2048 bit RSA private key
.................................+++
............................................................+++
writing new private key to 'client-key.pem'
-----

These new files will be stored in MySQL’s data directory, located by default at

/var/lib/mysql

. Check the generated files by typing:

这些新文件将存储在MySQL的数据目录中，默认情况下位于

/var/lib/mysql

。 通过键入以下内容检查生成的文件：

   
   

    
    Output
   
   
   258930      4 -rw-r--r--   1 mysql    mysql        1107 May  3 16:43 /var/lib/mysql/client-cert.pem
   258919      4 -rw-r--r--   1 mysql    mysql         451 May  3 16:43 /var/lib/mysql/public_key.pem
   258925      4 -rw-------   1 mysql    mysql        1675 May  3 16:43 /var/lib/mysql/server-key.pem
   258927      4 -rw-r--r--   1 mysql    mysql        1107 May  3 16:43 /var/lib/mysql/server-cert.pem
   258922      4 -rw-------   1 mysql    mysql        1675 May  3 16:43 /var/lib/mysql/ca-key.pem
   258928      4 -rw-------   1 mysql    mysql        1675 May  3 16:43 /var/lib/mysql/client-key.pem
   258924      4 -rw-r--r--   1 mysql    mysql        1107 May  3 16:43 /var/lib/mysql/ca.pem
   258918      4 -rw-------   1 mysql    mysql        1679 May  3 16:43 /var/lib/mysql/private_key.pem

These files are the key and certificate pairs for the certificate authority (starting with “ca”), the MySQL server process (starting with “server”), and for MySQL clients (starting with “client”). Additionally, the

private_key.pem

and

public_key.pem

files are used by MySQL to securely transfer passwords when not using SSL.

这些文件是证书颁发机构(以“ ca”开头)，MySQL服务器进程(以“ server”开头)和MySQL客户端(以“ client”开头)的密钥和证书对。 另外，当不使用SSL时，MySQL会使用

private_key.pem

和

public_key.pem

文件来安全地传输密码。

Now that you have the necessary certificate and key files, continue on to enable the use of SSL on your MySQL instance.

现在您已经拥有了必要的证书和密钥文件，继续在MySQL实例上启用SSL。

步骤3 —在MySQL服务器上启用SSL连接

(

Step 3 — Enabling SSL Connections on the MySQL Server

)

Modern versions of MySQL look for the appropriate certificate files within the MySQL data directory whenever the server starts. Because of this, you won’t need to modify MySQL’s configuration to enable SSL.

每当服务器启动时，现代版本MySQL都会在MySQL数据目录中寻找适当的证书文件。 因此，您无需修改​​MySQL的配置即可启用SSL。

Instead, enable SSL by restarting the MySQL service:

而是通过重新启动MySQL服务来启用SSL：

After restarting, open up a new MySQL session using the same command as before. The MySQL client will automatically attempt to connect using SSL if it is supported by the server:

重新启动后，使用与以前相同的命令打开一个新MySQL会话。 如果服务器支持，MySQL客户端将自动尝试使用SSL连接：

Let’s take another look at the same information we requested last time. Check the values of the SSL-related variables:

让我们再看看上次请求的相同信息。 检查与SSL相关的变量的值：

   
   

    
    Output
   
   
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| have_openssl  | YES             |
| have_ssl      | YES             |
| ssl_ca        | ca.pem          |
| ssl_capath    |                 |
| ssl_cert      | server-cert.pem |
| ssl_cipher    |                 |
| ssl_crl       |                 |
| ssl_crlpath   |                 |
| ssl_key       | server-key.pem  |
+---------------+-----------------+
9 rows in set (0.00 sec)

The

have_openssl

and

have_ssl

variables now read

YES

instead of

DISABLED

. Furthermore, the

ssl_ca

,

ssl_cert

, and

ssl_key

variables have been populated with the names of the respective files that we just generated.

现在，

have_openssl

和

have_ssl

变量读取为

YES

而不是

DISABLED

。 此外，

ssl_ca

，

ssl_cert

和

ssl_key

变量已填充有我们刚生成的各个文件的名称。

Next, check the connection details again:

接下来，再次检查连接详细信息：

   
   

    
    Output
   
   
--------------
. . .
SSL:            Cipher in use is DHE-RSA-AES256-SHA
. . .
Connection:      127.0.0.1 via TCP/IP
. . .
--------------

This time, the specific SSL cipher is displayed, indicating that SSL is being used to secure the connection.

这次，将显示特定的SSL密码，表明正在使用SSL来保护连接。

Exit back out to the shell:

退出回到外壳：

Your server is now capable of using encryption, but some additional configuration is required to allow remote access and mandate the use of secure connections.

您的服务器现在可以使用加密功能，但是还需要一些其他配置才能允许远程访问并强制使用安全连接。

步骤4 —为远程客户端配置安全连接

(

Step 4 — Configuring Secure Connections for Remote Clients

)

Now that you’ve enabled SSL on the MySQL server, you can begin configuring secure remote access. To do this, you’ll configure your MySQL server to require that any remote connections be made over SSL, bind MySQL to listen on a public interface, and adjust your system’s firewall rules to allow external connections

既然您已经在MySQL服务器上启用了SSL，则可以开始配置安全的远程访问。 为此，您将配置MySQL服务器以要求通过SSL进行任何远程连接，将MySQL绑定为在公共接口上侦听，并调整系统的防火墙规则以允许外部连接

Currently, the MySQL server is configured to accept SSL connections from clients. However, it will still allow unencrypted connections if requested by the client. We can change this by turning on the

require_secure_transport

option. This requires all connections to be made either with SSL or with a local Unix socket. Since Unix sockets are only accessible from within the server itself, the only connection option available to remote users will be with SSL.

当前，MySQL服务器已配置为接受来自客户端的SSL连接。 但是，如果客户端请求，它将仍然允许未加密的连接。 我们可以通过打开

require_secure_transport

选项来更改此设置。 这要求使用SSL或本地Unix套接字建立所有连接。 由于只能从服务器内部访问Unix套接字，因此远程用户唯一可用的连接选项是SSL。

To enable this setting, open the MySQL configuration file in your preferred text editor. Here, we’ll use

nano

:

要启用此设置，请在首选文本编辑器中打开MySQL配置文件。 在这里，我们将使用

nano

：

Inside there will be two

!includedir

directives which are used to source additional configuration files. You must add your own configuration

beneath

these lines so that it overrides any conflicting settings found in these additional configuration files.

内部有两个

!includedir

指令，用于获取其他配置文件。 您必须

在

这些行

下面

添加自己的配置

，

以便它覆盖在这些其他配置文件中找到的所有冲突设置。

Start by creating a

[mysqld]

section to target the MySQL server process. Under that section header, set

require_secure_transport

to

ON

, which will force MySQL to only allow secure connections:

首先创建一个

[mysqld]

部分以MySQL服务器进程为目标。 在该节标题下，将

require_secure_transport

设置为

ON

，这将强制MySQL仅允许安全连接：

. . .

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

[mysqld]
# Require clients to connect either using SSL
# or through a local socket file
require_secure_transport = ON

By default, MySQL is configured to only listen for connections that originate from

127.0.0.1

, the loopback IP address that represents

localhost

. This means that MySQL is configured to only listen for connections that originate from the machine on which the MySQL server is installed.

默认情况下，MySQL配置为仅侦听源自

127.0.0.1

连接，该连接是代表

localhost

的环回IP地址。 这意味着将MySQL配置为仅侦听来自安装MySQL服务器的计算机的连接。

In order to allow MySQL to listen for external connections, you must configure it to listen for connections on an

external

IP address. To do this, you can add the

bind-address

setting and point it to

0.0.0.0

, a wildcard IP address that represents all IP addresses. Essentially, this will force MySQL to listen for connections on every interface:

为了允许MySQL侦听外部连接，必须将其配置为侦听

外部

IP地址上的连接。 为此，您可以添加

bind-address

设置并将其指向

0.0.0.0

，这是一个表示所有IP地址的通配IP地址。 本质上，这将迫使MySQL监听每个接口上的连接：

. . .

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

[mysqld]
# Require clients to connect either using SSL
# or through a local socket file
require_secure_transport = ON
bind-address = 0.0.0.0

Note:

You could alternatively set

bind-address

to your

MySQL server’s

public IP address. However, you would need to remember to update your

my.cnf

file if you ever migrate your database to another machine.

注意：

您也可以将

bind-address

设置为

MySQL服务器的

公共IP地址。 但是，如果要将数据库迁移到另一台计算机，则需要记住要更新

my.cnf

文件。

After adding these lines, save and close the file. If you used

nano

to edit the file, you can do so by pressing

CTRL+X

,

Y

, then

ENTER

.

添加这些行之后，保存并关闭文件。 如果您使用

nano

来编辑文件，则可以通过按

CTRL+X

，

Y

，然后按

ENTER

。

Next, restart MySQL to apply the new settings:

接下来，重新启动MySQL以应用新设置：

Verify that MySQL is listening on

0.0.0.0

instead of

127.0.0.1

by typing:

通过键入以下内容来验证MySQL正在侦听

0.0.0.0

而不是

127.0.0.1

：

The output of this command will look like this:

该命令的输出将如下所示：

   
   

    
    Output
   
   
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
tcp        0      0 0.0.0.0:3306      0.0.0.0:*               LISTEN      13317/mysqld        
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      1293/sshd           
tcp6       0      0 :::22                   :::*                    LISTEN      1293/sshd           

The

0.0.0.0

highlighted in the above output indicates that MySQL is listening for connections on all available interfaces.

以上输出中突出显示的

0.0.0.0

表示MySQL正在侦听所有可用接口上的连接。

Next, allow MySQL connections through your server’s firewall. Add an exception to your

ufw

rules by typing:

接下来，允许MySQL连接通过服务器的防火墙。 通过输入以下内容为

ufw

规则添加例外：

   
   

    
    Output
   
   
Rule added
Rule added (v6)

With that, remote connection attempts are now able to reach your MySQL server. However, you don’t currently have any users configured that can connect from a remote machine. We’ll create and configure a MySQL user that can connect from your client machine in the next step.

这样，远程连接尝试现在就可以到达您MySQL服务器。 但是，您当前没有配置任何可以从远程计算机连接的用户。 下一步，我们将创建和配置一个可以从您的客户端计算机连接MySQL用户。

步骤5 —创建一个专用MySQL用户

(

Step 5 — Creating a Dedicated MySQL User

)

At this point, your MySQL server will reject any attempt to connect from a remote client machine. This is because the existing MySQL users are all only configured to connect locally from the MySQL server. To resolve this, you will create a dedicated user that will only be able to connect from your client machine.

此时，您MySQL服务器将拒绝任何从远程客户端计算机进行连接的尝试。 这是因为现有MySQL用户都仅配置为从MySQL服务器本地连接。 要解决此问题，您将创建一个专用用户，该用户只能从客户端计算机进行连接。

To create such a user, log back into MySQL as the

root

user:

要创建这样的用户，请以

root

用户身份登录到MySQL：

From the prompt, create a new remote user with the

CREATE USER

command. You can name this user whatever you’d like, but in this guide we name it

mysql_user

. Be sure to specify your client machine’s IP address in the host portion of the user specification to restrict connections to that machine and to replace

password

with a secure password of your choosing. Also, for some redundancy in case the

require_secure_transport

option is turned off in the future, specify that this user requires SSL by including the

REQUIRE SSL

clause, as shown here:

在提示符下，使用

CREATE USER

命令创建一个新的远程用户。 您可以随意命名该用户，但在本指南中，我们将其命名为

mysql_user

。 确保在用户规范的主机部分中指定客户端计算机的IP地址，以限制与该计算机的连接，并用您选择的安全密码替换

password

。 另外，如果将来需要关闭

require_secure_transport

选项以实现某种冗余，请通过包含

REQUIRE SSL

子句来指定此用户需要SSL，如下所示：

Next, grant the new user permissions on whichever databases or tables that they should have access to. To demonstrate, create an

example

database:

接下来，向新用户授予他们应有权访问的任何数据库或表的权限。 为了演示，创建一个

example

数据库：

Then give your new user access to this database and all of its tables:

然后，让您的新用户访问该数据库及其所有表：

Next, flush the privileges to apply those settings immediately:

接下来，清除特权以立即应用这些设置：

Then exit back out to the shell when you are done:

完成后，请退出并退出外壳：

Your MySQL server is now set up to allow connections from your remote user. To test that you can connect to MySQL successfully, you will need to install the

mysql-client

package on the

MySQL client

.

现在，您MySQL服务器已设置为允许来自远程用户的连接。 要测试您是否可以成功连接到MySQL，您需要在

MySQL客户


mysql-client

上安装

mysql-client

软件包。

Log in to your client machine with

ssh


使用

ssh

登录到客户端计算机

Then update the client machine’s package index:

然后更新客户端计算机的软件包索引：

And install

mysql-client

with the following command:

并使用以下命令安装

mysql-client

：

When prompted, confirm the installation by pressing

ENTER

.

出现提示时，请按

ENTER

确认安装。

Once APT finishes installing the package, run the following command to test whether you can connect to the server successfully. This command includes the

-u

user option to specify

mysql_user

and the

-h

option to specify the

MySQL server’s

IP address:

APT完成安装软件包后，运行以下命令以测试是否可以成功连接到服务器。 此命令包括

-u

用户选项，用于指定

mysql_user

和

-h

选项，用于指定

MySQL服务器的

IP地址：

After submitting the password, you will be logged in to the remote server. Use

\s

to check the server’s status and confirm that your connection is secure:

提交密码后，您将登录到远程服务器。 使用

\s

检查服务器的状态并确认您的连接是安全的：

   
   

    
    Output
   
   
--------------
. . .
SSL:         Cipher in use is DHE-RSA-AES256-SHA
. . .
Connection:      your_mysql_server_IP via TCP/IP
. . .
--------------

Exit back out to the shell:

退出回到外壳：

You’ve confirmed that you’re able to connect to MySQL over SSL. However, you’ve not yet confirmed that the MySQL server is rejecting insecure connections. To test this, try connecting once more, but this time append

--ssl-mode=disabled

to the login command. This will instruct

mysql-client

to attempt an unencrypted connection:

您已经确认可以通过SSL连接到MySQL。 但是，您尚未确认MySQL服务器正在拒绝不安全的连接。 要对此进行测试，请尝试再次连接，但是这次将

--ssl-mode=disabled

附加到login命令中。 这将指示

mysql-client

尝试未加密的连接：

After entering your password when prompted, your connection will be refused:

在提示时输入密码后，您的连接将被拒绝：

   
   

    
    Output
   
   
ERROR 1045 (28000): Access denied for user 'mysql_user'@'mysql_server_IP' (using password: YES)

This shows that SSL connections are permitted while unencrypted connections are refused.

这表明允许SSL连接，而拒绝未加密的连接。

At this point, your MySQL server has been configured to accept secure remote connections. You can stop here if this satisfies your security requirements, but there are some additional pieces that you can put into place to enhance security and trust between your two servers.

此时，您MySQL服务器已配置为接受安全的远程连接。 如果满足您的安全性要求，您可以在这里停下来，但是可以添加一些其他步骤来增强两台服务器之间的安全性和信任度。

步骤6 —(可选)为MySQL连接配置验证

(

Step 6 — (Optional) Configuring Validation for MySQL Connections

)

Currently, your MySQL server is configured with an SSL certificate signed by a locally generated certificate authority (CA). The server’s certificate and key pair are enough to provide encryption for incoming connections.

当前，您MySQL服务器配置有由本地生成的证书颁发机构(CA)签名的SSL证书。 服务器的证书和密钥对足以为传入连接提供加密。

However, you aren’t yet fully leveraging the trust relationship that a certificate authority can provide. By distributing the CA certificate to clients — as well as the client certificate and key — both parties can provide proof that their certificates were signed by a mutually trusted certificate authority. This can help prevent spoofed connections from malicious servers.

但是，您尚未完全利用证书颁发机构可以提供的信任关系。 通过将CA证书以及客户端证书和密钥分发给客户端，双方可以提供证明其证书由相互信任的证书颁发机构签名的证据。 这可以帮助防止来自恶意服务器的欺骗连接。

In order to implement this extra, optional safeguard, we will transfer the appropriate SSL files to the client machine, create a client configuration file, and alter the remote MySQL user to require a trusted certificate.

为了实现此额外的可选保护措施，我们将适当的SSL文件传输到客户端计算机，创建客户端配置文件，并更改远程MySQL用户以要求使用受信任的证书。

Note:

The process for transferring the CA certificate, client certificate, and client key to the MySQL client outlined in the following paragraphs involves displaying each file’s contents with

cat

, copying those contents to your clipboard, and pasting them in to a new file on the client machine. While it is possible to copy these files directly with a program like

scp

or

sftp

, this also requires you to

set up SSH keys

for both servers so as to allow them to communicate over SSH.

注意：以下各节

概述了将CA证书，客户端证书和客户端密钥传输到MySQL客户端的过程，其中包括使用

cat

显示每个文件的内容，将这些内容复制到剪贴板，然后将其粘贴到新文件上。客户端计算机。 尽管可以使用诸如

scp

或

sftp

类的程序直接复制这些文件，但这还要求您为两个服务器都

设置SSH密钥

，以允许它们通过SSH进行通信。

Our goal here is to keep the number of potential avenues for connecting to your MySQL server down to a minimum. While this process is slightly more laborious than directly transferring the files, it is equally secure and doesn’t require you to open an SSH connection between the two machines.

我们的目标是使连接到MySQL服务器的潜在途径数量保持最少。 尽管此过程比直接传输文件要更费力，但它同样安全，不需要您在两台计算机之间打开SSH连接。

Begin by making a directory on the

MySQL client

in the home directory of your non-root user. Call this directory

client-ssl

:

首先在非根用户的主目录中的

MySQL客户端

上创建目录。 将此目录

client-ssl

：

Because the certificate key is sensitive, lock down access to this directory so that only the current user can access it:

由于证书密钥是敏感的，因此请锁定对该目录的访问，以便只有当前用户才能访问它：

On the

MySQL server

, display the contents of the CA certificate by typing:

在

MySQL服务器上

，通过键入以下内容显示CA证书的内容：

   
   

    
    Output
   
   
-----BEGIN CERTIFICATE-----

. . .

-----END CERTIFICATE-----

Copy the entire output, including the

BEGIN CERTIFICATE

and

END CERTIFICATE

lines, to your clipboard.

将整个输出(包括

BEGIN CERTIFICATE

和

END CERTIFICATE

行)复制到剪贴板。

On the

MySQL client

, create a file with the same name inside the new directory:

在

MySQL客户端上

，在新目录中创建一个具有相同名称的文件：

Inside, paste the copied certificate contents from your clipboard. Save and close the file when you are finished.

在内部，从剪贴板粘贴复制的证书内容。 完成后保存并关闭文件。

Next, display the client certificate on the

MySQL server

:

接下来，在

MySQL服务器

上显示客户端证书：

   
   

    
    Output
   
   
-----BEGIN CERTIFICATE-----

. . .

-----END CERTIFICATE-----

Copy the file contents to your clipboard. Again, remember to include the first and last line.

将文件内容复制到剪贴板。 同样，请记住包括第一行和最后一行。

Open a file with the same name on the

MySQL client

within the

client-ssl

directory:

在

MySQL客户端上

的

client-ssl

目录中打开一个具有相同名称的文件：

Paste the contents from your clipboard. Save and close the file.

从剪贴板粘贴内容。 保存并关闭文件。

Finally, display the contents of the client key file on the

MySQL server

:

最后，在

MySQL服务器

上显示客户端密钥文件的内容：

   
   

    
    Output
   
   
-----BEGIN RSA PRIVATE KEY-----

. . .

-----END RSA PRIVATE KEY-----

Copy the displayed contents, including the first and last line, to your clipboard.

将显示的内容(包括第一行和最后一行)复制到剪贴板。

On the

MySQL client

, open a file with the same name in the

client-ssl

directory:

在

MySQL客户端上

，在

client-ssl

目录中打开一个具有相同名称的文件：

Paste the contents from your clipboard. Save and close the file.

从剪贴板粘贴内容。 保存并关闭文件。

The client machine now has all of the credentials required to access the MySQL server. However, the MySQL server is still not set up to require trusted certificates for client connections.

客户端计算机现在具有访问MySQL服务器所需的所有凭据。 但是，MySQL服务器仍未设置为要求客户端连接使用受信任的证书。

To change this, log in to the MySQL

root

account again on the

MySQL server

:

要更改此设置，请在

MySQL服务器

上再次登录到MySQL

根

帐户：

From here, change the security requirements for your remote user. Instead of the

REQUIRE SSL

clause, apply the

REQUIRE X509

clause. This implies all of the security provided by the

REQUIRE SSL

clause, but additionally requires the connecting client to present a certificate signed by a certificate authority that the MySQL server trusts.

在这里，更改远程用户的安全要求。 代替

REQUIRE SSL

子句，应用

REQUIRE X509

子句。 这意味着

REQUIRE SSL

子句提供了所有安全性，但是还要求连接客户端出示由MySQL服务器信任的证书颁发机构签名的证书。

To adjust the user requirements, use the

ALTER USER

command:

要调整用户要求，请使用

ALTER USER

命令：

Then flush the changes to ensure that they are applied immediately:

然后刷新更改以确保立即应用它们：

Exit back out to the shell when you are finished:

完成后退出到外壳：

Following that, check whether you can validate both parties when you connect.

之后，检查连接时是否可以验证双方。

On the

MySQL client

, first try to connect without providing the client certificates:

在

MySQL客户端上

，首先尝试在不提供客户端证书的情况下进行连接：

   
   

    
    Output
   
   
ERROR 1045 (28000): Access denied for user 'mysql_user'@'mysql_client_IP' (using password: YES)

As expected, the server rejects the connection when no client certificate is presented.

不出所料，当不提供客户端证书时，服务器将拒绝连接。

Now, connect while using the

--ssl-ca

,

--ssl-cert

, and

--ssl-key

options to point to the relevant files within the

~/client-ssl

directory:

现在，在使用

--ssl-ca

，

~/client-ssl


--ssl-cert

和

--ssl-key

选项指向

~/client-ssl

目录中的相关文件时进行连接：

You’ve provided the client with the appropriate certificates and keys, so this attempt will be successful:

您已为客户端提供了适当的证书和密钥，因此此尝试将成功：

Log back out to regain access to your shell session:

注销以重新访问您的shell会话：

Now that you’ve confirmed access to the server, let’s implement a small usability improvement in order to avoid having to specify the certificate files each time you connect.

既然您已经确认可以访问服务器，那么我们就进行一下可用性方面的改进，以避免每次连接时都必须指定证书文件。

Inside your home directory on the

MySQL client

machine, create a hidden configuration file called

~/.my.cnf

:

在

MySQL客户端

计算机的主目录内，创建一个名为

~/.my.cnf

的隐藏配置文件：

At the top of the file, create a section called

[client]

. Underneath, add the

ssl-ca

,

ssl-cert

, and

ssl-key

options and point them to the respective files you copied over from the server. It will look like this:

在文件的顶部，创建一个名为

[client]

的部分。 在下面，添加

ssl-ca

，

ssl-cert

和

ssl-key

选项，并将它们指向您从服务器复制过来的相应文件。 它看起来像这样：

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

The

ssl-ca

option tells the client to verify that the certificate presented by the MySQL server is signed by the certificate authority you pointed to. This allows the client to trust that it is connecting to a trusted MySQL server. Likewise, the

ssl-cert

and

ssl-key

options point to the files needed to prove to the MySQL server that it too has a certificate that has been signed by the same certificate authority. You’ll need this if you want the MySQL server to verify that the client was trusted by the CA as well.


ssl-ca

选项告诉客户端验证MySQL服务器提供的证书是否由您指向的证书颁发机构签名。 这使客户端可以信任它正在连接到受信任MySQL服务器。 同样，

ssl-cert

和

ssl-key

选项指向向MySQL服务器证明它也具有由同一证书颁发机构签名的证书所需的文件。 如果您希望MySQL服务器也验证客户端是否受到CA的信任，则需要此功能。

Save and close the file when you are finished.

完成后保存并关闭文件。

Now, you can connect to the MySQL server without adding the

--ssl-ca

,

--ssl-cert

, and

--ssl-key

options on the command line:

现在，您可以连接到MySQL服务器，而无需在命令行上添加

--ssl-ca

，–

--ssl-cert

和

--ssl-key

选项：

Your client and server will now each be presenting certificates when negotiating the connection. Each party is configured to verify the remote certificate against the CA certificate it has locally.

现在，您的客户端和服务器将在协商连接时分别提供证书。 各方均配置为对照本地拥有的CA证书来验证远程证书。

结论

(

Conclusion

)

Your MySQL server is now configured to require secure connections from remote clients. Additionally, if you followed the steps to validate connections using the certificate authority, some level of trust is established by both sides that the remote party is legitimate.

现在，您MySQL服务器已配置为要求来自远程客户端的安全连接。 此外，如果按照步骤使用证书颁发机构验证连接，则双方将建立某种程度的信任，即远程方是合法的。

翻译自:

https://www.digitalocean.com/community/tutorials/how-to-configure-ssl-tls-for-mysql-on-ubuntu-18-04