您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

Linux 下基於 OpenSSL 實現 MySQL 主從 SSL 加密復制

編輯：MySQL 資料庫相關知識

背景與價值

默認 MySQL 復制明文傳輸，敏感數據經網絡傳輸存在洩露風險。基於 OpenSSL 的 SSL 加密復制，提供傳輸加密、雙向認證、數據完整性三重保障，滿足等保合規要求。

一、CA 證書體系構建

1.1 創建 CA 私鑰與自簽證書


mkdir -p /etc/pki/CA/{private,certs,newcerts,crl}
echo "01" > /etc/pki/CA/serial
touch /etc/pki/CA/index.txt

# CA 私鑰 (4096 位更安全)
(umask 066; openssl genrsa -out /etc/pki/CA/private/cakey.pem 4096)

# CA 自簽證書 (有效期 10 年)
openssl req -new -x509 -key /etc/pki/CA/private/cakey.pem \
  -days 3650 -out /etc/pki/CA/cacert.pem \
  -subj "/CN=MySQL-CA/O=MyOrg/OU=DB/C=CN/ST=Beijing/L=Beijing"

1.2 服務端 (Master) 證書申請與簽發


# Master 私鑰
(umask 066; openssl genrsa -out /var/lib/mysql/ssl/server-key.pem 2048)

# 申請文件 (CSR)
openssl req -new -key /var/lib/mysql/ssl/server-key.pem \
  -out /var/lib/mysql/ssl/server-csr.pem \
  -subj "/CN=mysql-master/O=MyOrg/OU=DB/C=CN/ST=Beijing/L=Beijing"

# CA 簽發服務端證書 (有效期 1 年)
openssl ca -in /var/lib/mysql/ssl/server-csr.pem \
  -out /var/lib/mysql/ssl/server-cert.pem -days 365 \
  -batch -config /etc/pki/tls/openssl.cnf

1.3 客戶端 (Slave) 證書申請與簽發


# Slave 私鑰
(umask 066; openssl genrsa -out /var/lib/mysql/ssl/client-key.pem 2048)

# 申請文件
openssl req -new -key /var/lib/mysql/ssl/client-key.pem \
  -out /var/lib/mysql/ssl/client-csr.pem \
  -subj "/CN=mysql-slave/O=MyOrg/OU=DB/C=CN/ST=Beijing/L=Beijing"

# CA 簽發客戶端證書
openssl ca -in /var/lib/mysql/ssl/client-csr.pem \
  -out /var/lib/mysql/ssl/client-cert.pem -days 365 \
  -batch -config /etc/pki/tls/openssl.cnf

1.4 權限與分發


# 設置權限 (關鍵：私鑰僅擁有者可讀)
chown -R mysql:mysql /var/lib/mysql/ssl/
chmod 600 /var/lib/mysql/ssl/*-key.pem
chmod 644 /var/lib/mysql/ssl/*-cert.pem /var/lib/mysql/ssl/*.pem

# 分發 CA 證書到 Slave
scp /etc/pki/CA/cacert.pem slave:/var/lib/mysql/ssl/
scp /var/lib/mysql/ssl/client-*.pem slave:/var/lib/mysql/ssl/

二、Master 配置 (my.cnf)


[mysqld]
# SSL 核心配置
ssl_ca=/var/lib/mysql/ssl/cacert.pem
ssl_cert=/var/lib/mysql/ssl/server-cert.pem
ssl_key=/var/lib/mysql/ssl/server-key.pem
ssl_cipher=TLSv1.2

# 復制專用賬號 (要求 SSL)
# 執行 SQL:
# GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* 
# TO 'repl_ssl'@'slave_ip' IDENTIFIED BY 'ReplPass123!' REQUIRE SSL;

三、Slave 配置 (my.cnf)


[mysqld]
server-id=2
relay-log=relay-bin
read-only=1

# SSL 客戶端配置
ssl_ca=/var/lib/mysql/ssl/cacert.pem
ssl_cert=/var/lib/mysql/ssl/client-cert.pem
ssl_key=/var/lib/mysql/ssl/client-key.pem
ssl_cipher=TLSv1.2

四、建立 SSL 復制鏈路


# Master 重啟驗證 SSL 生效
systemctl restart mysqld
mysql> SHOW VARIABLES LIKE '%ssl%';
# have_ssl=YES, ssl_ca/cert/key 路徑正確

# Slave 配置 Master 連接 (動態配置，不寫入 my.cnf 避免自動重連干擾排查)
mysql> CHANGE MASTER TO
    MASTER_HOST='master_ip',
    MASTER_USER='repl_ssl',
    MASTER_PASSWORD='ReplPass123!',
    MASTER_LOG_FILE='mysql-bin.000025',
    MASTER_LOG_POS=245,
    MASTER_SSL=1,
    MASTER_SSL_CA='/var/lib/mysql/ssl/cacert.pem',
    MASTER_SSL_CERT='/var/lib/mysql/ssl/client-cert.pem',
    MASTER_SSL_KEY='/var/lib/mysql/ssl/client-key.pem';

# 啟動復制
mysql> START SLAVE;

# 驗證
mysql> SHOW SLAVE STATUS\G
# Master_SSL_Allowed: Yes
# Master_SSL_CA_File: /var/lib/mysql/ssl/cacert.pem
# Slave_IO_Running: Yes, Slave_SQL_Running: Yes

五、驗證加密傳輸


# Master 抓包驗證 (應見 TLS 握手，無明文 SQL)
tcpdump -i eth0 -n port 3306 -w replication.pcap

# Slave 狀態確認
mysql> SHOW STATUS LIKE 'Ssl_cipher';
+---------------+--------------------+
| Variable_name | Value              |
+---------------+--------------------+
| Ssl_cipher    | ECDHE-RSA-AES256-GCM-SHA384 |
+---------------+--------------------+

六、生產環境加固建議

項目	建議配置
Slave 只讀	read_only=1, super_read_only=1 (5.7.8+)
事務安全	sync_binlog=1, innodb_flush_log_at_trx_commit=1, innodb_support_xa=ON
防自動重連	CHANGE MASTER TO 動態配置，不寫 my.cnf
元數據同步	sync_master_info=1, sync_relay_log_info=1
證書輪換	到期前 30 天自動化續簽分發

總結

基於 OpenSSL 的 MySQL SSL 復制實現了傳輸層加密、雙向身份認證、數據完整性校驗。核心流程：CA 構建 → 雙端證書申請簽發 → 雙端配置 ssl_ca/cert/key → 動態 CHANGE MASTER TO MASTER_SSL=1 → 驗證雙 Yes。生產環境務必配合事務安全參數、只讀限制、證書生命週期管理。