MySQL 9.0 版本資訊
本節說明如何使用 openssl 命令來設定 SSL 憑證和金鑰檔案，供 MySQL 伺服器和用戶端使用。第一個範例顯示簡化的程序，例如您可能會從命令列使用。第二個範例顯示包含更多詳細資訊的指令碼。前兩個範例適用於 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 憑證，則 extended key usage 必須包含用戶端驗證 (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 位元套件，請根據需要修改此設定。
如果在設定期間出現一則訊息，指出 '...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-074B9F2BC1BFVisual 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'（注意分號）。按一下「確定」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 使用加密連線」。