Documentation Home
MySQL 8.3 Reference Manual
Related Documentation
MySQL 8.3 Release Notes
Download this Manual
PDF (US Ltr) - 40.8Mb
PDF (A4) - 40.9Mb
Man Pages (TGZ) - 294.0Kb
Man Pages (Zip) - 409.0Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb
Excerpts from this Manual
MySQL Backup and Recovery
MySQL NDB Cluster 8.3
MySQL Globalization
MySQL Information Schema
MySQL Installation Guide
MySQL and Linux/Unix
MySQL and macOS
MySQL Partitioning
MySQL Performance Schema
MySQL Replication
Using the MySQL Yum Repository
MySQL Restrictions and Limitations
Security in MySQL
MySQL and Solaris
Building MySQL from Source
Starting and Stopping MySQL
MySQL Tutorial
MySQL and Windows


MySQL 8.3 Reference Manual  /  ...  /  Creating SSL Certificates and Keys Using openssl

8.3.3.2 使用 openssl 创建 SSL 证书和密钥

本节描述了如何使用 openssl 命令来设置 MySQL 服务器和客户端的 SSL 证书和密钥文件。第一个示例显示了从命令行使用简化的过程。第二个示例显示了包含更多详细信息的脚本。前两个示例旨在 Unix 上使用，并且都使用了 OpenSSL 的一部分 openssl 命令。第三个示例描述了如何在 Windows 上设置 SSL 文件。

Note

生成所需的 SSL 文件有比这里描述的过程更简单的替代方法：让服务器自动生成它们或使用 mysql_ssl_rsa_setup 程序（已弃用）。见 第 8.3.3.1 节，“使用 MySQL 创建 SSL 和 RSA 证书和密钥”。

Important

无论您使用哪种方法生成证书和密钥文件，服务器和客户端证书/密钥的 Common Name 值必须各不相同，否则证书和密钥文件将无法用于使用 OpenSSL 编译的服务器。在这种情况下，典型的错误是：

Press CTRL+C to copy
 
ERROR 2026 (HY000): SSL connection error:
error:00000001:lib(0):func(0):reason(1)
Important

如果客户端连接到 MySQL 服务器实例时使用了扩展密钥使用（X.509 v3 扩展）SSL 证书，扩展密钥使用必须包括客户端身份验证（clientAuth）。如果 SSL 证书仅指定了服务器身份验证（serverAuth）和其他非客户端证书目的，证书验证将失败，客户端连接到 MySQL 服务器实例将失败。在使用 openssl 命令按照本主题中的说明创建的 SSL 证书中没有 extendedKeyUsage 扩展。如果您使用自己的客户端证书创建于其他方式，请确保任何 extendedKeyUsage 扩展包括客户端身份验证。

示例 1：从 Unix 命令行创建 SSL 文件

以下示例显示了一组命令，以创建 MySQL 服务器和客户端证书和密钥文件。您必须响应 openssl 命令的多个提示。要生成测试文件，可以对所有提示按 Enter 键。要生成生产用文件，应该提供非空响应。

Press CTRL+C to copy
 
# Create clean environment
rm -rf newcerts
mkdir newcerts && cd newcerts

# Create CA certificate
openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 3600 \
        -key ca-key.pem -out ca.pem

# Create server certificate, remove passphrase, and sign it
# server-cert.pem = public key, server-key.pem = private key
openssl req -newkey rsa:2048 -days 3600 \
        -nodes -keyout server-key.pem -out server-req.pem
openssl rsa -in server-key.pem -out server-key.pem
openssl x509 -req -in server-req.pem -days 3600 \
        -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

# Create client certificate, remove passphrase, and sign it
# client-cert.pem = public key, client-key.pem = private key
openssl req -newkey rsa:2048 -days 3600 \
        -nodes -keyout client-key.pem -out client-req.pem
openssl rsa -in client-key.pem -out client-key.pem
openssl x509 -req -in client-req.pem -days 3600 \
        -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

生成证书后，验证它们：

Press CTRL+C to copy
 
openssl verify -CAfile ca.pem server-cert.pem client-cert.pem

您应该看到类似这样的响应：

Press CTRL+C to copy
 
server-cert.pem: OK
client-cert.pem: OK

要查看证书的内容（例如，检查证书的有效期），可以像这样调用 openssl：

Press CTRL+C to copy
 
openssl x509 -text -in ca.pem
openssl x509 -text -in server-cert.pem
openssl x509 -text -in client-cert.pem

现在您拥有了一组可以用于以下目的的文件：

  • ca.pem：使用它来设置服务器端的 ssl_ca 系统变量和客户端的 --ssl-ca 选项。（CA 证书，如果使用，必须在两端相同。）

  • server-cert.pem, server-key.pem：使用它们来设置服务器端的 ssl_cert 和 ssl_key 系统变量。

  • client-cert.pem, client-key.pem：使用它们作为客户端的 --ssl-cert 和 --ssl-key 选项的参数。

有关其他使用说明，请参阅 第 8.3.1 节，“配置 MySQL 使用加密连接”。

示例 2：使用 Unix 脚本创建 SSL 文件

以下是一个示例脚本，显示如何设置 MySQL 的 SSL 证书和密钥文件。执行脚本后，使用文件进行 SSL 连接，如 第 8.3.1 节，“配置 MySQL 使用加密连接” 所述。

Press CTRL+C to copy
 
DIR=`pwd`/openssl
PRIV=$DIR/private

mkdir $DIR $PRIV $DIR/newcerts
cp /usr/share/ssl/openssl.cnf $DIR
replace ./demoCA $DIR -- $DIR/openssl.cnf

# Create necessary files: $database, $serial and $new_certs_dir
# directory (optional)

touch $DIR/index.txt
echo "01" > $DIR/serial

#
# Generation of Certificate Authority(CA)
#

openssl req -new -x509 -keyout $PRIV/cakey.pem -out $DIR/ca.pem \
    -days 3600 -config $DIR/openssl.cnf

# Sample output:
# Using configuration from /home/jones/openssl/openssl.cnf
# Generating a 1024 bit RSA private key
# ................++++++
# .........++++++
# writing new private key to '/home/jones/openssl/private/cakey.pem'
# Enter PEM pass phrase:
# Verifying password - Enter PEM pass phrase:
# -----
# You are about to be asked to enter information to be
# incorporated into your certificate request.
# What you are about to enter is what is called a Distinguished Name
# or a DN.
# There are quite a few fields but you can leave some blank
# For some fields there will be a default value,
# If you enter '.', the field will be left blank.
# -----
# Country Name (2 letter code) [AU]:FI
# State or Province Name (full name) [Some-State]:.
# Locality Name (eg, city) []:
# Organization Name (eg, company) [Internet Widgits Pty Ltd]:MySQL AB
# Organizational Unit Name (eg, section) []:
# Common Name (eg, YOUR name) []:MySQL admin
# Email Address []:

#
# Create server request and key
#
openssl req -new -keyout $DIR/server-key.pem -out \
    $DIR/server-req.pem -days 3600 -config $DIR/openssl.cnf

# Sample output:
# Using configuration from /home/jones/openssl/openssl.cnf
# Generating a 1024 bit RSA private key
# ..++++++
# ..........++++++
# writing new private key to '/home/jones/openssl/server-key.pem'
# Enter PEM pass phrase:
# Verifying password - Enter PEM pass phrase:
# -----
# You are about to be asked to enter information that will be
# incorporated into your certificate request.
# What you are about to enter is what is called a Distinguished Name
# or a DN.
# There are quite a few fields but you can leave some blank
# For some fields there will be a default value,
# If you enter '.', the field will be left blank.
# -----
# Country Name (2 letter code) [AU]:FI
# State or Province Name (full name) [Some-State]:.
# Locality Name (eg, city) []:
# Organization Name (eg, company) [Internet Widgits Pty Ltd]:MySQL AB
# Organizational Unit Name (eg, section) []:
# Common Name (eg, YOUR name) []:MySQL server
# Email Address []:
#
# Please enter the following 'extra' attributes
# to be sent with your certificate request
# A challenge password []:
# An optional company name []:

#
# Remove the passphrase from the key
#
openssl rsa -in $DIR/server-key.pem -out $DIR/server-key.pem

#
# Sign server cert
#
openssl ca -cert $DIR/ca.pem -policy policy_anything \
    -out $DIR/server-cert.pem -config $DIR/openssl.cnf \
    -infiles $DIR/server-req.pem

# Sample output:
# Using configuration from /home/jones/openssl/openssl.cnf
# Enter PEM pass phrase:
# Check that the request matches the signature
# Signature ok
# The Subjects Distinguished Name is as follows
# countryName           :PRINTABLE:'FI'
# organizationName      :PRINTABLE:'MySQL AB'
# commonName            :PRINTABLE:'MySQL admin'
# Certificate is to be certified until Sep 13 14:22:46 2003 GMT
# (365 days)
# Sign the certificate? [y/n]:y
#
#
# 1 out of 1 certificate requests certified, commit? [y/n]y
# Write out database with 1 new entries
# Data Base Updated

#
# Create client request and key
#
openssl req -new -keyout $DIR/client-key.pem -out \
    $DIR/client-req.pem -days 3600 -config $DIR/openssl.cnf

# Sample output:
# Using configuration from /home/jones/openssl/openssl.cnf
# Generating a 1024 bit RSA private key
# .....................................++++++
# .............................................++++++
# writing new private key to '/home/jones/openssl/client-key.pem'
# Enter PEM pass phrase:
# Verifying password - Enter PEM pass phrase:
# -----
# You are about to be asked to enter information that will be
# incorporated into your certificate request.
# What you are about to enter is what is called a Distinguished Name
# or a DN.
# There are quite a few fields but you can leave some blank
# For some fields there will be a default value,
# If you enter '.', the field will be left blank.
# -----
# Country Name (2 letter code) [AU]:FI
# State or Province Name (full name) [Some-State]:.
# Locality Name (eg, city) []:
# Organization Name (eg, company) [Internet Widgits Pty Ltd]:MySQL AB
# Organizational Unit Name (eg, section) []:
# Common Name (eg, YOUR name) []:MySQL user
# Email Address []:
#
# Please enter the following 'extra' attributes
# to be sent with your certificate request
# A challenge password []:
# An optional company name []:

#
# Remove the passphrase from the key
#
openssl rsa -in $DIR/client-key.pem -out $DIR/client-key.pem

#
# Sign client cert
#

openssl ca -cert $DIR/ca.pem -policy policy_anything \
    -out $DIR/client-cert.pem -config $DIR/openssl.cnf \
    -infiles $DIR/client-req.pem

# Sample output:
# Using configuration from /home/jones/openssl/openssl.cnf
# Enter PEM pass phrase:
# Check that the request matches the signature
# Signature ok
# The Subjects Distinguished Name is as follows
# countryName           :PRINTABLE:'FI'
# organizationName      :PRINTABLE:'MySQL AB'
# commonName            :PRINTABLE:'MySQL user'
# Certificate is to be certified until Sep 13 16:45:17 2003 GMT
# (365 days)
# Sign the certificate? [y/n]:y
#
#
# 1 out of 1 certificate requests certified, commit? [y/n]y
# Write out database with 1 new entries
# Data Base Updated

#
# Create a my.cnf file that you can use to test the certificates
#

cat <<EOF > $DIR/my.cnf
[client]
ssl-ca=$DIR/ca.pem
ssl-cert=$DIR/client-cert.pem
ssl-key=$DIR/client-key.pem
[mysqld]
ssl_ca=$DIR/ca.pem
ssl_cert=$DIR/server-cert.pem
ssl_key=$DIR/server-key.pem
EOF
示例 3：在 Windows 上创建 SSL 文件

如果 OpenSSL尚未安装在您的系统上，请下载 Windows 版本的 OpenSSL。可在此处查看可用的软件包概述：

Press CTRL+C to copy
 
http://www.slproweb.com/products/Win32OpenSSL.html

选择 Win32 OpenSSL Light 或 Win64 OpenSSL Light 软件包，具体取决于您的架构（32 位或 64 位）。默认安装位置是 C:\OpenSSL-Win32 或 C:\OpenSSL-Win64，具体取决于您下载的软件包。以下说明假设默认位置为 C:\OpenSSL-Win32。如果您使用 64 位软件包，请根据需要修改。

如果在设置过程中出现消息，指示 '... 临界组件缺失：Microsoft Visual C++ 2019 Redistributables'，请取消设置并下载以下软件包之一，具体取决于您的架构（32 位或 64 位）：

  • Visual C++ 2008 Redistributables（x86），可在以下位置获取：

    Press CTRL+C to copy
     
    http://www.microsoft.com/downloads/details.aspx?familyid=9B2DA534-3E03-4391-8A4D-074B9F2BC1BF

  • Visual C++ 2008 Redistributables（x64），可在以下位置获取：

    Press CTRL+C to copy
     
    http://www.microsoft.com/downloads/details.aspx?familyid=bd2a6171-e2d6-4230-b809-9a8d7548c1b6

安装附加软件包后，重新启动 OpenSSL 设置过程。

在安装过程中，请将默认 C:\OpenSSL-Win32 保持为安装路径，并保留默认选项 '将 OpenSSL DLL 文件复制到 Windows 系统目录' 选中。

安装完成后，将 C:\OpenSSL-Win32\bin 添加到服务器的 Windows 系统 Path 变量中（具体取决于您的 Windows 版本，以下路径设置说明可能略有不同）：

  1. 在 Windows 桌面上，右键单击 我的电脑 图标，然后选择 属性。

  2. 从 系统属性 菜单中选择 高级 选项卡，然后单击 环境变量 按钮。

  3. 在 系统变量 下，选择 Path，然后单击 编辑 按钮。编辑系统变量 对话框将出现。

  4. 在结尾添加 ';C:\OpenSSL-Win32\bin'（注意分号）。

  5. 按 OK 3 次。

  6. 通过打开一个新的命令控制台 (开始>运行>cmd.exe) 并验证 OpenSSL 是否可用来检查 OpenSSL 是否正确集成到 Path 变量中：

    Press CTRL+C to copy
     
    Microsoft Windows [Version ...]
Copyright (c) 2006 Microsoft Corporation. All rights reserved.

C:\Windows\system32>cd \

C:\>openssl
OpenSSL> exit <<< If you see the OpenSSL prompt, installation was successful.

C:\>

安装 OpenSSL 后，使用类似于示例 1（在本节前面显示）的说明，但进行以下更改：

  • 更改以下 Unix 命令：

    Press CTRL+C to copy
     
    # Create clean environment
rm -rf newcerts
mkdir newcerts && cd newcerts

    在 Windows 上，使用以下命令代替：

    Press CTRL+C to copy
     
    # Create clean environment
md c:\newcerts
cd c:\newcerts

  • 当命令行末尾显示 '\' 字符时，必须删除该字符并将命令行输入到单行中。

生成证书和密钥文件后，使用它们进行 SSL 连接，请参阅 第 8.3.1 节，“配置 MySQL 使用加密连接”。


PREV   HOME   UP   NEXT
Related Documentation
MySQL 8.3 Release Notes
Download this Manual
PDF (US Ltr) - 40.8Mb
PDF (A4) - 40.9Mb
Man Pages (TGZ) - 294.0Kb
Man Pages (Zip) - 409.0Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb
Excerpts from this Manual
MySQL Backup and Recovery
MySQL NDB Cluster 8.3
MySQL Globalization
MySQL Information Schema
MySQL Installation Guide
MySQL and Linux/Unix
MySQL and macOS
MySQL Partitioning
MySQL Performance Schema
MySQL Replication
Using the MySQL Yum Repository
MySQL Restrictions and Limitations
Security in MySQL
MySQL and Solaris
Building MySQL from Source
Starting and Stopping MySQL
MySQL Tutorial
MySQL and Windows