Mysql8.0.42启用SSL安全连接完整步骤(等保整改、docker部署)

[mysqld]
# ssl配置
# 强制所有连接使用ssl
require_secure_transport = on
# 可以自行创建ca证书、服务端证书和keyy，以下三个配置也可以不写，使用默认配置
ssl_ca=ca.pem
ssl_cert=server-cert.pem
ssl_key=server-key.pem
# 其他安全配置
tls_version = tlsv1.2,tlsv1.3  # 限制支持的tls版本
ssl-cipher = high:!anull:!md5  # 限制加密套件

audit-mysql  | 2025-09-29t07:57:32.455742z 0 [warning] [my-013595] [server] failed to initialize tls for channel: mysql_main. see below for the description of exact issue.
audit-mysql  | 2025-09-29t07:57:32.461268z 0 [warning] [my-010069] [server] failed to set up ssl because of the following ssl library error: ssl_ctx_set_default_verify_paths failed

version: '3.8'

services:
  # mysql 8.0.42
  mysql:
    # build: ./mysql
    image: mysql:8.0.42
    container_name: audit-mysql
    restart: always
    environment:
      mysql_root_password: admin@123
      mysql_database: grzsk
      mysql_user: grzsk-user
      mysql_password: admin@123
      tz: asia/shanghai
    volumes:
      - ./mysql/data:/var/lib/mysql
      - ./mysql/conf.d:/etc/mysql/conf.d
      - ./mysql/audit-logs:/var/log/mysql
    ports:
      - "12001:3306"
    networks:
      - backend
    healthcheck:
      test: ["cmd", "mysqladmin", "ping", "-h", "localhost"]
      interval: 5s
      timeout: 10s
      retries: 5

#!/bin/bash
set -e  # 遇到错误立即退出
# ==============================================
# 配置参数
# ==============================================
ssl_dir="./mysql/ssl"          # 证书存储目录
ca_days=3650                   # ca证书有效期（10年）
cert_days=3650                 # 服务器/客户端证书有效期（10年）
country="cn"                   # 国家代码
state="guangzhou"              # 省份/州
city="guangzhou"               # 城市
org="zuzhi"                   # 组织
unit="dba"                     # 部门
common_name="mysql-server"     # 服务器通用名称
client_common_name="mysql-client"  # 客户端通用名称
email="admin@zuzhi.com"       # 联系邮箱
# ==============================================
# 清理旧证书并创建目录
# ==============================================
echo "===== 准备证书目录 ====="
if [ -d "$ssl_dir" ]; then
    echo "删除旧证书目录: $ssl_dir"
    rm -rf "$ssl_dir"
fi
echo "创建新证书目录: $ssl_dir"
mkdir -p "$ssl_dir" || { echo "错误：无法创建目录 $ssl_dir"; exit 1; }
cd "$ssl_dir" || { echo "错误：无法进入目录 $ssl_dir"; exit 1; }
chmod 700 .  # 限制目录访问权限
# ==============================================
# 生成ca根证书
# ==============================================
echo -e "\n===== 生成ca根证书 ====="
echo "生成ca私钥 (ca-key.pem)..."
openssl genrsa -out ca-key.pem 2048 || { echo "错误：ca私钥生成失败"; exit 1; }
echo "生成ca根证书 (ca-cert.pem)..."
openssl req -new -x509 -nodes -days "$ca_days" \
    -key ca-key.pem \
    -out ca.pem \
    -subj "/c=$country/st=$state/l=$city/o=$org/ou=$unit/cn=$common_name/emailaddress=$email" || { 
    echo "错误：ca根证书生成失败"; exit 1; }
# 验证ca私钥格式
echo "验证ca私钥格式..."
if ! grep -- "-----begin rsa private key-----" ca-key.pem >/dev/null 2>&1; then
    echo "错误：ca私钥格式不正确，缺少起始标记"
    exit 1
fi
# ==============================================
# 生成服务器证书（移除pkcs#8转换）
# ==============================================
echo -e "\n===== 生成服务器证书 ====="
echo "生成服务器私钥 (server-key.pem)..."
openssl genrsa -out server-key.pem 2048 || { echo "错误：服务器私钥生成失败"; exit 1; }
# 验证服务器私钥格式
if ! grep -- "-----begin rsa private key-----" server-key.pem >/dev/null 2>&1; then
    echo "错误：服务器私钥格式不正确，缺少起始标记"
    exit 1
fi
echo "生成服务器证书请求 (server-req.pem)..."
openssl req -new -key server-key.pem -out server-req.pem \
    -subj "/c=$country/st=$state/l=$city/o=$org/ou=$unit/cn=$common_name/emailaddress=$email" || {
    echo "错误：服务器证书请求生成失败"; exit 1; }
echo "用ca签名服务器证书 (server-cert.pem)..."
openssl x509 -req -in server-req.pem -days "$cert_days" \
    -ca ca.pem -cakey ca-key.pem -cacreateserial -out server-cert.pem || {
    echo "错误：服务器证书签名失败"; exit 1; }
# ==============================================
# 生成客户端证书（移除pkcs#8转换）
# ==============================================
echo -e "\n===== 生成客户端证书 ====="
echo "生成客户端私钥 (client-key.pem)..."
openssl genrsa -out client-key.pem 2048 || { echo "错误：客户端私钥生成失败"; exit 1; }
# 验证客户端私钥格式
if ! grep -- "-----begin rsa private key-----" client-key.pem >/dev/null 2>&1; then
    echo "错误：客户端私钥格式不正确，缺少起始标记"
    exit 1
fi
echo "生成客户端证书请求 (client-req.pem)..."
openssl req -new -key client-key.pem -out client-req.pem \
    -subj "/c=$country/st=$state/l=$city/o=$org/ou=$unit/cn=$client_common_name/emailaddress=$email" || { 
    echo "错误：客户端证书请求生成失败"; exit 1; }
echo "用ca签名客户端证书 (client-cert.pem)..."
openssl x509 -req -in client-req.pem -days "$cert_days" \
    -ca ca.pem -cakey ca-key.pem -cacreateserial -out client-cert.pem || {
    echo "错误：客户端证书签名失败"; exit 1; }
# ==============================================
# 清理临时文件
# ==============================================
echo -e "\n===== 清理临时文件 ====="
rm -f server-req.pem client-req.pem
# ==============================================
# 生成结果验证
# ==============================================
echo -e "\n===== 证书生成完成 ====="
echo "证书存储目录: $(pwd)"
echo "生成的文件列表:"
ls -l ./*
# 验证关键文件是否存在且非空
required_files=("ca-key.pem" "ca.pem" "server-key.pem" "server-cert.pem" "client-key.pem" "client-cert.pem")
for file in "${required_files[@]}"; do
    if [ ! -s "$file" ]; then
        echo "警告：关键文件 $file 不存在或为空，生成过程可能有问题"
    fi
done

url: jdbc:mysql://192.168.140.14:12001/grzsk?usessl=true&verifyservercertificate=true&sslca=classpath:ssl/ca.pem&sslcert=classpath:ssl/client-cert.pem&sslkey=classpath:ssl/client-key.pem&characterencoding=utf-8&useunicode=true&tinyint1isbit=false&allowpublickeyretrieval=true&servertimezone=asia/shanghai

keytool -importcert -alias myrootca -file /path/to/ca.pem -keystore $java_home/jre/lib/security/cacerts