MySQL :: MySQL 8.4 Reference Manual :: 8.3.3.2 Creating SSL Certificates and Keys Using openssl

version 8.4

8.0 current
5.7

8.0 Japanese

MySQL 8.4 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 所需文件的更简单方法是让服务器自动生成它们；见第 8.3.3.1 节，“使用 MySQL 创建 SSL 和 RSA 证书和密钥”。

Important

无论你使用什么方法生成证书和密钥文件，服务器和客户端证书/密钥的 Common Name 值都必须与 CA 证书的 Common Name 值不同。否则，使用 OpenSSL 编译的服务器上证书和密钥文件不工作。常见错误如下：

ERROR 2026 (HY000): SSL connection error:
error:00000001:lib(0):func(0):reason(1)

Important

如果客户端连接到 MySQL 服务器实例使用带有extendedKeyUsage扩展（X.509 v3 扩展）的 SSL 证书，必须包括客户端身份验证(clientAuth)。如果 SSL 证书仅用于服务器身份验证(serverAuth)和其他非客户端证书用途，证书验证失败，客户端连接到 MySQL 服务器实例失败。使用openssl命令创建的 SSL 证书中没有extendedKeyUsage扩展。如果您使用其他方式创建了客户端证书，请确保extendedKeyUsage扩展包括客户端身份验证。

以下示例展示了创建 MySQL 服务器和客户端证书和密钥文件的命令。您需要使用 openssl 命令响应几个提示。要生成测试文件，可以按 Enter 回车键；要生成生产环境文件，应该提供非空响应。

# 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

生成证书后，请验证它们：

openssl verify -CAfile ca.pem server-cert.pem client-cert.pem

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

server-cert.pem: OK
client-cert.pem: OK

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

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节，“使用加密连接”.

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

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

如果您的系统上未安装 OpenSSL，下载 OpenSSL for Windows。可用的包列表可以在这里看到：

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 位包，请根据需要修改。

在设置过程中出现的消息“...critical component is missing: Microsoft Visual C++ 2019 Redistributables”时，取消设置并下载以下包，取决于您的架构（32位或 64 位）：

Visual C++ 2008 Redistributables (x86)，可在这里找到：

http://www.microsoft.com/downloads/details.aspx?familyid=9B2DA534-3E03-4391-8A4D-074B9F2BC1BF

Visual C++ 2008 Redistributables (x64)，可在这里找到：

http://www.microsoft.com/downloads/details.aspx?familyid=bd2a6171-e2d6-4230-b809-9a8d7548c1b6

安装完毕后，重新启动 OpenSSL 设置过程。

安装时，保持默认的C:\OpenSSL-Win32路径，并且保留默认选项'Copy OpenSSL DLL files to the Windows system directory'。

安装完成后，在服务器上添加C:\OpenSSL-Win32\bin到Windows系统变量（根据您的Windows版本，路径设置指令可能会有所不同）：

在Windows桌面右键单击我的电脑图标，然后选择属性。
从出现的系统属性菜单中，选择高级选项卡，然后点击环境变量按钮。
在系统变量下，选择路径编辑按钮。出现编辑系统变量对话框。
在末尾添加';C:\OpenSSL-Win32\bin'(注意分号）.
单击OK 3次。

通过打开新的命令控制台(开始>运行>cmd.exe)，验证OpenSSL是否正确地集成到路径变量中：

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命令：

# Create clean environment
rm -rf newcerts
mkdir newcerts && cd newcerts

在Windows上，使用这些命令代替：

# Create clean environment
md c:\newcerts
cd c:\newcerts

当命令行末尾出现'\'字符时，这个'\'字符必须被删除，然后将命令行输入到单一行。

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

PREV HOME UP NEXT