MySQL SSL 加密傳輸保障數據在網路傳輸過程中的機密性與完整性。本文涵蓋 OpenSSL 手動簽發 CA/服務端/客戶端憑證、MySQL 5.7+ mysql_ssl_rsa_setup 自動化工具、服務端與客戶端配置、驗證方法,以及 Docker 容器化部署完整實戰。
mysql> SHOW VARIABLES LIKE 'have_ssl';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_ssl | YES |
+---------------+-------+
YES 表示編譯支援;DISABLED 需重編譯或加載 SSL 插件。
mkdir -p ~/temp/cert && cd ~/temp/cert
openssl genrsa 2048 > ca-key.pem
openssl req -sha1 -new -x509 -nodes -days 3650 -key ca-key.pem > ca-cert.pem
# 填寫主體資訊 (CN、O、OU 等)
# 私鑰 + CSR
openssl req -sha1 -newkey rsa:2048 -days 3650 -nodes -keyout server-key.pem > server-req.pem
# 轉換為 RSA 格式
openssl rsa -in server-key.pem -out server-key.pem
# CA 簽發
openssl x509 -sha1 -req -in server-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem
openssl req -sha1 -newkey rsa:2048 -days 3650 -nodes -keyout client-key.pem > client-req.pem
openssl rsa -in client-key.pem -out client-key.pem
openssl x509 -sha1 -req -in client-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem
mkdir ~/temp/cert && cd ~/temp/cert
mysql_ssl_rsa_setup --datadir ./
自動生成 8 個檔案:ca.pem, ca-key.pem, server-cert.pem, server-key.pem, client-cert.pem, client-key.pem, public_key.pem, private_key.pem。
[mysqld]
ssl-ca=/etc/mysql/ca-cert.pem
ssl-cert=/etc/mysql/server-cert.pem
ssl-key=/etc/mysql/server-key.pem
bind-address = * # 允許所有 IP 連接
重啟 MySQL,驗證:
mysql> SHOW VARIABLES LIKE '%ssl%';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| have_ssl | YES |
| have_openssl | YES |
| ssl_ca | ca.pem |
| ssl_cert | server-cert.pem |
| ssl_key | server-key.pem |
+---------------+-----------------+
GRANT ALL PRIVILEGES ON *.* TO 'ssl_test'@'%' IDENTIFIED BY 'ssl_test' REQUIRE SSL;
FLUSH PRIVILEGES;
命令列參數:
mysql --ssl-ca=/path/ca-cert.pem --ssl-cert=/path/client-cert.pem --ssl-key=/path/client-key.pem -h host -u ssl_test -p
配置文件 (~/.my.cnf):
[client]
ssl-ca=/path/ca-cert.pem
ssl-cert=/path/client-cert.pem
ssl-key=/path/client-key.pem
驗證:\s 輸出含 SSL: Cipher in use is DHE-RSA-AES256-SHA 即成功。
temp/
├── cert/ # 8 個憑證檔案
├── config/
│ └── my.cnf # 含上述 [mysqld] SSL 配置
└── db/ # 數據目錄
docker run --rm --name test_db -p 10000:3306 \
-e MYSQL_ROOT_PASSWORD=root \
-v /host/temp/db:/var/lib/mysql \
-v /host/temp/config:/etc/mysql/conf.d \
-v /host/temp/cert:/etc/mysql/cert \
mysql:latest
docker run -it --link test_db:test_db --rm mysql \
sh -c 'exec mysql -u root -p -h test_db -e "SHOW VARIABLES LIKE '\''%ssl%'\'';"'
docker run -it --link test_db:test_db --rm \
-v /host/temp/cert:/etc/mysql/cert \
mysql sh -c 'exec mysql --ssl-ca=/etc/mysql/cert/ca-cert.pem \
--ssl-cert=/etc/mysql/cert/client-cert.pem \
--ssl-key=/etc/mysql/cert/client-key.pem \
-h test_db -u ssl_test -p'
執行 \s 確認 SSL: Cipher in use is DHE-RSA-AES256-SHA。
require_secure_transport=ON 強制所有連接使用 SSLSsl_accepts, Ssl_accept_renegotiates 等狀態變數