MySQL 8.4 版本資訊
本節說明如何使用 openssl 命令，設定 MySQL 伺服器和用戶端使用的 SSL 憑證與金鑰檔案。第一個範例顯示簡化的程序，例如您可能會從命令列使用的程序。第二個範例顯示包含更多細節的腳本。前兩個範例適用於 Unix，且都使用屬於 OpenSSL 一部分的 openssl 命令。第三個範例說明如何在 Windows 上設定 SSL 檔案。
注意
與此處描述的程序相比，產生 SSL 所需檔案的更簡單替代方法是讓伺服器自動產生它們；請參閱第 8.3.3.1 節，「使用 MySQL 建立 SSL 與 RSA 憑證與金鑰」。
重要
無論您使用何種方法產生憑證與金鑰檔案，用於伺服器和用戶端憑證/金鑰的「通用名稱」值都必須與用於 CA 憑證的「通用名稱」值不同。否則，憑證與金鑰檔案不適用於使用 OpenSSL 編譯的伺服器。這種情況下的典型錯誤是
ERROR 2026 (HY000): SSL connection error:
error:00000001:lib(0):func(0):reason(1)重要
如果連線到 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現在您有一組檔案可以使用，如下所示
如需其他使用說明，請參閱 第 8.3.1 節，「設定 MySQL 以使用加密連線」。
以下是一個範例指令碼，說明如何為 MySQL 設定 SSL 憑證和金鑰檔案。執行指令碼後，請依照 第 8.3.1 節，「設定 MySQL 以使用加密連線」 中的說明，使用檔案進行 SSL 連線。
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 for Windows，請下載它。可以在此處查看可用套件的概觀
http://www.slproweb.com/products/Win32OpenSSL.html根據您的架構 (32 位元或 64 位元)，選擇 Win32 OpenSSL Light 或 Win64 OpenSSL Light 套件。預設安裝位置為 C:\OpenSSL-Win32 或 C:\OpenSSL-Win64，具體取決於您下載的套件。以下指示假設預設位置為 C:\OpenSSL-Win32。如果您使用的是 64 位元套件，請根據需要修改此位置。
如果在設定期間出現訊息，指出 '...遺失重要元件：Microsoft Visual C++ 2019 Redistributables'，請取消設定並下載以下其中一個套件，同樣地取決於您的架構 (32 位元或 64 位元)
Visual C++ 2008 Redistributables (x86)，可於以下網址取得
http://www.microsoft.com/downloads/details.aspx?familyid=9B2DA534-3E03-4391-8A4D-074B9F2BC1BFVisual C++ 2008 Redistributables (x64)，可於以下網址取得
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 系統路徑變數 (根據您的 Windows 版本，以下路徑設定指示可能略有不同)
在 Windows 桌面上，按一下滑鼠右鍵「我的電腦」圖示，然後選取「」。
從出現的「」功能表中，選取「」索引標籤，然後按一下「」按鈕。
在「系統變數」下，選取「」，然後按一下「」按鈕。應該會出現「」對話方塊。
在結尾處新增
';C:\OpenSSL-Win32\bin'(請注意分號)。按 OK 3 次。
開啟新的命令主控台 (開始>執行>cmd.exe)，並確認 OpenSSL 是否可用，以檢查 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 以使用加密連線」。