TLS Setup in MySQL and Spring Boot Application – 在MySQL和Spring Boot应用程序中设置TLS

最后修改: 2022年 10月 25日

中文/混合/英文(键盘快捷键：t)

1. Overview

1.概述

An unencrypted connection between a MySQL server and client can expose data in transit over the network. For a production-ready application, we should move all communication to a secure connection via TLS (Transport Layer Security) protocol. 

MySQL服务器和客户端之间的未加密连接会暴露网络上传输的数据。对于一个生产就绪的应用程序，我们应该通过TLS（传输层安全）协议将所有通信转移到安全连接。

In this tutorial, we’ll learn how to enable a secure connection on a MySQL server. Also, we’ll configure the Spring Boot application to use this secure connection.

在本教程中，我们将学习如何在MySQL服务器上启用一个安全连接。此外，我们还将配置Spring Boot应用程序以使用这种安全连接。

2. Why Use TLS on MySQL?

2.为什么在MySQL上使用TLS？

First, let’s understand some basics of TLS.

首先，让我们了解一下TLS的一些基本知识。

The TLS protocol uses an encryption algorithm to ensure that data received over the network can be trusted and not tampered with or inspected. It has mechanisms to detect data change, loss, or replay attacks. TLS also incorporates algorithms that provide identity verification using the X.509 standard.

TLS协议使用一种加密算法，以确保通过网络接收的数据可以被信任，不被篡改或检查。它具有检测数据变化、丢失或重放攻击的机制。TLS还结合了使用X.509标准提供身份验证的算法。

An encrypted connection adds a layer of security and makes the data unreadable over the network traffic.

加密连接增加了一个安全层，使数据在网络交通中无法被读取。

Configuring a secure connection between the MySQL server and client enables better authentication, data integrity, and trustworthiness. Additionally, the MySQL server can perform additional checks on the client’s identity.

在MySQL服务器和客户端之间配置一个安全连接，可以实现更好的认证、数据完整性和可信赖性。此外，MySQL服务器可以对客户的身份进行额外的检查。

However, such a secure connection comes with a performance penalty due to encryption. The severity of the performance cost depends on various factors like query size, data load, server hardware, network bandwidth, and other factors.

然而，这样的安全连接由于加密而带来了性能上的损失。性能成本的严重程度取决于各种因素，如查询大小、数据负载、服务器硬件、网络带宽和其他因素。

3. Configure a TLS Connection on MySQL Server

3.在MySQL服务器上配置一个TLS连接

MySQL server performs encryption on a per-connection basis, and this can be made mandatory or optional for the given user. MySQL supports SSL encryption-related operations at runtime with the installed OpenSSL library.

MySQL服务器在每个连接的基础上执行加密，这对给定的用户来说可以是强制性的或可选的。MySQL支持在运行时使用已安装的OpenSSL库进行SSL加密相关操作。

We can use the JDBC Driver Connector/J to encrypt the data between the client and server after the initial handshake. 

我们可以使用JDBC驱动程序Connector/J来在初始握手后对客户端和服务器之间的数据进行加密。

MySQL server v8.0.28 or above supports only TLS v1.2 and TLS v1.3. It no longer supports the earlier versions of TLS (v1 and v1.1).

MySQL服务器v8.0.28或以上版本只支持TLS v1.2和TLS v1.3。它不再支持TLS的早期版本（V1和V1.1）。

Server authentication can be enabled using either a certificate signed by a trusted root certificate authority or a self-signed certificate. Also, it’s common practice to build our own root CA file for MySQL, even in production.

服务器认证可以启用使用由受信任的根证书颁发机构签署的证书或自签证书。此外，为MySQL建立我们自己的根CA文件是常见的做法，即使是在生产中。

Additionally, the server can authenticate and verify the client’s SSL certificate and perform additional checks on the client’s identity.

此外，服务器可以认证和验证客户的SSL证书，并对客户的身份进行额外检查。

3.1. Configure MySQL Server With TLS Certificates

3.1.用TLS证书配置MySQL服务器

We’ll enable secure transport on the MySQL server using the property require_secure_transport and the default-generated certificates.

我们将使用属性 require_secure_transport 和默认生成的证书在 MySQL 服务器上启用安全传输。

Let’s quickly bootstrap the MySQL Server by implementing the settings in a docker-compose.yml:

让我们通过在docker-compose.yml中实现设置，快速启动MySQL服务器。

version: '3.8'

services:
  mysql-service:
    image: "mysql/mysql-server:8.0.30"
    container_name: mysql-db
    command: [ "mysqld",
      "--require_secure_transport=ON",
      "--default_authentication_plugin=mysql_native_password",
      "--general_log=ON" ]
    ports:
      - "3306:3306"
    volumes:
      - type: bind
        source: ./data
        target: /var/lib/mysql
    restart: always
    environment:
      MYSQL_ROOT_HOST: "%"
      MYSQL_ROOT_PASSWORD: "Password2022"
      MYSQL_DATABASE: test_db

We should note that the above MySQL Server uses the default certificates located in path /var/lib/mysql.

我们应该注意到，上述MySQL服务器使用位于路径/var/lib/mysql的默认证书。

Alternatively, we can override the default certificates by including a few mysqld configs in docker-compose.yml:

另外，我们可以通过在docker-compose.yml中加入一些mysqld配置来覆盖默认证书。

command: [ "mysqld",
  "--require_secure_transport=ON",
  "--ssl-ca=/etc/certs/ca.pem",
  "--ssl-cert=/etc/certs/server-cert.pem",
  "--ssl-key=/etc/certs/server-key.pem",
  ....]

Now, let’s start the mysql-service using the docker-compose command:

现在，让我们用docker-compose命令启动mysql-service。

$ docker-compose -p mysql-server up

3.2. Create a User with X509

3.2.用X509创建一个用户

Optionally, we can configure the MySQL server with client identification using the X.509 standard. With X509, a valid client certificate is required. This enables the two-way mutual TLS or mTLS.

可选的是，我们可以使用X.509标准对MySQL服务器进行客户端识别配置。使用X509，需要一个有效的客户端证书。这使得双向相互TLS或mTLS。

Let’s create a user with X509 and grant permission on the test_db database:

让我们创建一个具有X509的用户，并授予test_db数据库的许可。

mysql> CREATE USER 'test_user'@'%' IDENTIFIED BY 'Password2022' require X509;
mysql> GRANT ALL PRIVILEGES ON test_db.* TO 'test_user'@'%';

We can set up a TLS connection without any user certificate identification:

我们可以在没有任何用户证书识别的情况下建立一个TLS连接。

mysql> CREATE USER 'test_user'@'%' IDENTIFIED BY 'Password2022' require SSL;

We should note that the client is required to provide a truststore if SSL is used.

我们应该注意到，如果使用SSL，客户需要提供一个信任仓库。

4. Configure TLS on a Spring Boot Application

4.在Spring Boot应用程序上配置TLS

Spring Boot applications can configure TLS over the JDBC connection by setting the JDBC URL with a few properties.

Spring Boot应用程序可以通过设置具有一些属性的JDBC URL来配置JDBC连接的TLS。

There are various ways of configuring Spring Boot Application to use TLS with MySQL.

配置Spring Boot应用程序以使用TLS与MySQL。

Before that, we’ll need to convert the truststore and client certificates into JKS format.

在此之前，我们需要将信任商店和客户证书转换成JKS格式。

4.1. Convert PEM File to JKS Format

4.1.将PEM文件转换为JKS格式

Let’s convert the MySQL server-generated ca.pem and client-cert.pem files to JKS format:

让我们把MySQL服务器生成的ca.pem和client-cert.pem文件转换成JKS/em>格式。

keytool -importcert -alias MySQLCACert.jks -file ./data/ca.pem \
    -keystore ./certs/truststore.jks -storepass mypassword
openssl pkcs12 -export -in ./data/client-cert.pem -inkey ./data/client-key.pem \
    -out ./certs/certificate.p12 -name "certificate"
keytool -importkeystore -srckeystore ./certs/certificate.p12 -srcstoretype pkcs12 -destkeystore ./certs/client-cert.jks

We should note that, as of Java 9, the default keystore format is PKCS12.

我们应该注意，从Java 9开始，默认的密钥存储格式是PKCS12。

4.2. Configure Using application.yml

4.2.使用application.yml进行配置

TLS can be enabled with the sslMode set as PREFERRED, REQUIRED, VERIFY_CA, or VERIFY_IDENTITY.

可以通过将sslMode设置为PREFERRED、REQUIRED、VERIFY_CA或VERIFY_IDENTITY来启用TLS。

The PREFERRED mode either uses the secure connection, if the server supports it, or otherwise falls back to an unencrypted connection.

PREFERRED模式要么使用安全连接（如果服务器支持），要么退回到未加密的连接。

With REQUIRED mode, the client can only use an encrypted connection. Like REQUIRED, VERIFY_CA mode uses a secure connection but additionally validates the server certificates against the configured Certificate Authority (CA) certificates.

使用REQUIRED模式，客户端只能使用加密连接。与REQUIRED一样，VERIFY_CA模式使用安全连接，但另外根据配置的证书授权（CA）证书验证服务器证书。

The VERIFY_IDENTITY mode does one additional check on the hostname along with certificate validation.

VERIFY_IDENTITY模式在证书验证的同时对主机名进行额外检查。

Also, a few Connector/J properties need to be added to the JDBC URL, such as trustCertufucateKeyStoreUrl, trustCertificateKeyStorePassword, clientCertificateKeyStoreUrl, and clientCertificateKeyStorePassword.

另外，一些Connector/J属性需要被添加到JDBC URL中，例如trustCertufucateKeyStoreUrl、trustCertificateKeyStorePassword、clientCertificateKeyStoreUrl和clientCertificateKeyStorePassword。

Let’s configure the JDBC URL in the application.yml with sslMode set to VERIFY_CA:

让我们在application.yml中配置JDBC URL，将sslMode设置为VERIFY_CA。

spring:
  profiles: "dev2"
  datasource:
    url: >-
         jdbc:mysql://localhost:3306/test_db?
         sslMode=VERIFY_CA&
         trustCertificateKeyStoreUrl=file:/<project-path>/mysql-server/certs/truststore.jks&
         trustCertificateKeyStorePassword=mypassword&
         clientCertificateKeyStoreUrl=file:/<project-path>/mysql-server/certs/client-cert.jks&
         clientCertificateKeyStorePassword=mypassword
    username: test_user
    password: Password2022

We should note that the deprecated properties equivalent to VERIFY_CA are a combination of useSSL=true and verifyServerCertificate=true.

我们应该注意到，相当于VERIFY_CA的废弃属性是useSSL=true和verifyServerCertificate=true的组合。

If the trust certificate files are not provided, we’ll get an error to that effect:

如果没有提供信任证书文件，我们会得到一个这样的错误。

Caused by: java.security.cert.CertPathValidatorException: Path does not chain with any of the trust anchors
	at java.base/sun.security.provider.certpath.PKIXCertPathValidator.validate(PKIXCertPathValidator.java:157) ~[na:na]
	at java.base/sun.security.provider.certpath.PKIXCertPathValidator.engineValidate(PKIXCertPathValidator.java:83) ~[na:na]
	at java.base/java.security.cert.CertPathValidator.validate(CertPathValidator.java:309) ~[na:na]
	at com.mysql.cj.protocol.ExportControlled$X509TrustManagerWrapper.checkServerTrusted(ExportControlled.java:402) ~[mysql-connector-java-8.0.29.jar:8.0.29]

In case the client certificate is missing, we’ll get a different error:

如果客户证书丢失，我们会得到一个不同的错误。

Caused by: java.sql.SQLException: Access denied for user 'test_user'@'172.20.0.1'

4.3. Configure TLS Using Environment Variables

4.3.使用环境变量配置TLS

Alternatively, we can set the above configuration as environment variables and include the SSL-related configs as JVM parameters.

另外，我们可以将上述配置设置为环境变量，并将与SSL相关的配置作为JVM参数。

Let’s add the TLS and Spring-related configs as environment variables:

让我们把TLS和Spring相关的配置作为环境变量加入。

export TRUSTSTORE=./mysql-server/certs/truststore.jks
export TRUSTSTORE_PASSWORD=mypassword
export KEYSTORE=./mysql-server/certs/client-cert.jks
export KEYSTORE_PASSWORD=mypassword
export SPRING_DATASOURCE_URL=jdbc:mysql://localhost:3306/test_db?sslMode=VERIFY_CA
export SPRING_DATASOURCE_USERNAME=test_user
export SPRING_DATASOURCE_PASSWORD=Password2022

Then, let’s run the application with the above SSL configurations:

然后，让我们用上述SSL配置运行该应用程序。

$java -Djavax.net.ssl.keyStore=$KEYSTORE \
 -Djavax.net.ssl.keyStorePassword=$KEYSTORE_PASSWORD \
 -Djavax.net.ssl.trustStore=$TRUSTSTORE \
 -Djavax.net.ssl.trustStorePassword=$TRUSTSTORE_PASSWORD \
 -jar ./target/spring-boot-mysql-0.1.0.jar

5. Verify the TLS Connection

5.验证TLS连接

Let’s now run the application using any of the above methods and verify the TLS connection.

现在让我们使用上述任何一种方法运行该应用程序，并验证TLS连接。

The TLS connection can be verified using the MySQL server general log or by querying the process and sys admin tables.

TLS连接可以使用MySQL服务器一般日志或通过查询process和sys管理表进行核实。

Let’s verify the connections using the log file in its default path /var/lib/mysql/:

让我们使用默认路径/var/lib/mysql/中的日志文件来验证连接情况。

$ cat /var/lib/mysql/7f44397082d7.log
2022-09-17T13:58:25.887830Z        19 Connect   test_user@172.22.0.1 on test_db using SSL/TLS

Alternatively, let’s verify the connections used by test_user:

另外，让我们验证一下test_user使用的连接。

mysql> SELECT process.thd_id,user,db,ssl_version,ssl_cipher FROM sys.processlist process, sys.session_ssl_status session 
where process.user='test_user@172.20.0.1'and process.thd_id=session.thread_id;+--------+----------------------+---------+-------------+------------------------+
| thd_id | user                 | db      | ssl_version | ssl_cipher             |
+--------+----------------------+---------+-------------+------------------------+
|    167 | test_user@172.20.0.1 | test_db | TLSv1.3     | TLS_AES_256_GCM_SHA384 |
|    168 | test_user@172.20.0.1 | test_db | TLSv1.3     | TLS_AES_256_GCM_SHA384 |
|    169 | test_user@172.20.0.1 | test_db | TLSv1.3     | TLS_AES_256_GCM_SHA384 |

6. Conclusion

6.结语

In this article, we’ve learned how a TLS connection to MySQL makes the data secure over the network. Also, we’ve seen how to configure the TLS connection on MySQL Server in a Spring Boot Application.

在这篇文章中，我们已经了解了与MySQL的TLS连接如何使数据在网络上变得安全。此外，我们还看到了如何在Spring Boot应用程序中配置MySQL服务器的TLS连接。

As always, the example code can be found over on GitHub.

一如既往，可以在GitHub上找到示例代码。