MySQL SSL 主從同步部署完整指南：跨資料中心異構複寫實戰

業務場景

• 主站與 Mobile 版 PHP 版本不一致，需部署不同伺服器
• 新舊伺服器位於**不同資料中心**，無內網直連
• 架構決策：主庫寫入 + SSL 加密同步到從庫，從庫唯讀；寫操作直連主庫 SSL
• 訪問量不大，可容忍同步延遲

一、SSL 憑證產生與配置 (雙向驗證)

1. 主庫產生 CA、Server、Client 憑證

mkdir -p /etc/mysql/certs && cd /etc/mysql/certs

# 1. 產生 CA
openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 3650 -key ca-key.pem -subj "/CN=MySQL_CA" > ca-cert.pem

# 2. 產生 Server 憑證
openssl req -newkey rsa:2048 -days 3650 -nodes -keyout server-key.pem -subj "/CN=mysql-master" > server-req.pem
openssl x509 -req -in server-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

# 3. 產生 Client 憑證 (給從庫用)
openssl req -newkey rsa:2048 -days 3650 -nodes -keyout client-key.pem -subj "/CN=mysql-slave" > client-req.pem
openssl x509 -req -in client-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 02 -out client-cert.pem

# 4. 驗證
openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem

2. 主庫 my.cnf 配置 SSL

[mysqld]
ssl
ssl-ca=/etc/mysql/certs/ca-cert.pem
ssl-cert=/etc/mysql/certs/server-cert.pem
ssl-key=/etc/mysql/certs/server-key.pem

# 強制要求 SSL 連線 (可選，視相容性)
# require_secure_transport=ON

重啟 MySQL：`systemctl restart mysqld`

3. 驗證 SSL 啟用

mysql> SHOW VARIABLES LIKE '%ssl%';
+---------------+----------------------------------+
| Variable_name | Value                            |
+---------------+----------------------------------+
| have_openssl  | YES                              |
| have_ssl      | YES                              |
| ssl_ca        | /etc/mysql/certs/ca-cert.pem     |
| ssl_cert      | /etc/mysql/certs/server-cert.pem |
| ssl_key       | /etc/mysql/certs/server-key.pem  |
+---------------+----------------------------------+

4. 從庫部署 Client 憑證

將主庫產生的三個檔案複製到從庫對應目錄：

• ca-cert.pem
• client-cert.pem
• client-key.pem

從庫 my.cnf：

[mysqld]
ssl
ssl-ca=/etc/mysql/certs/ca-cert.pem
ssl-cert=/etc/mysql/certs/client-cert.pem
ssl-key=/etc/mysql/certs/client-key.pem

重啟從庫 MySQL。

二、建立複寫帳號 (強制 SSL)

-- 主庫執行
CREATE USER 'repl_user'@'slave_ip' IDENTIFIED BY 'StrongPassword123!';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'slave_ip' REQUIRE SSL;
FLUSH PRIVILEGES;

三、主庫啟用 Binlog 配置

[mysqld]
server-id = 1                    -- 唯一，叢集內不可重複
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 7             -- 保留 7 天
max_binlog_size = 100M
binlog_format = ROW              -- 推薦 ROW 格式，資料一致性最佳
binlog_do_db = your_database     -- 僅同步指定庫 (可選)
# binlog_ignore_db = mysql,test,information_schema,performance_schema

重啟主庫。

四、初始資料同步 (不鎖表熱備份)

# 主庫執行：--master-data=2 記錄 binlog 位置於檔案開頭 (CHANGE MASTER TO 註解)
mysqldump -u root -p --single-transaction --master-data=2 --databases your_database > /tmp/master_snapshot.sql

# 複製到從庫
scp /tmp/master_snapshot.sql slave_user@slave_ip:/tmp/

五、從庫匯入資料並啟動複寫

1. 建立資料庫並匯入

# 從庫執行
mysql -u root -p -e "CREATE DATABASE your_database;"
mysql -u root -p your_database < /tmp/master_snapshot.sql

2. 取得 Binlog 位置

head -n 50 /tmp/master_snapshot.sql | grep "CHANGE MASTER TO"
# 輸出範例：
# CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000072', MASTER_LOG_POS=898280714;

3. 配置並啟動 Slave

mysql> CHANGE MASTER TO
    MASTER_HOST='master_ip',
    MASTER_USER='repl_user',
    MASTER_PASSWORD='StrongPassword123!',
    MASTER_LOG_FILE='mysql-bin.000072',
    MASTER_LOG_POS=898280714,
    MASTER_SSL=1,
    MASTER_SSL_CA='/etc/mysql/certs/ca-cert.pem',
    MASTER_SSL_CERT='/etc/mysql/certs/client-cert.pem',
    MASTER_SSL_KEY='/etc/mysql/certs/client-key.pem';

mysql> START SLAVE;

4. 驗證複寫狀態

mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: master_ip
                  Master_User: repl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000072
          Read_Master_Log_Pos: 898280714
               Relay_Log_File: slave-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000072
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Seconds_Behind_Master: 0
           Master_SSL_Allowed: Yes
      Master_SSL_CA_File: /etc/mysql/certs/ca-cert.pem
      Master_SSL_Cert: /etc/mysql/certs/client-cert.pem
      Master_SSL_Key: /etc/mysql/certs/client-key.pem

關鍵指標：**Slave_IO_Running: Yes、Slave_SQL_Running: Yes、Seconds_Behind_Master: 0** 表示同步正常。

六、常見問題與排查

七、進階優化與監控

1. 多執行緒複寫 (MySQL 5.7+)：

   STOP SLAVE;
   SET GLOBAL slave_parallel_workers = 4;
   SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';
   START SLAVE;
   

2. GTID 模式 (MySQL 5.6+)：簡化故障轉移、自動定位位置

   [mysqld]
   gtid_mode = ON
   enforce_gtid_consistency = ON
   log_slave_updates = 1
   

3. 監控指標：
   • Seconds_Behind_Master > 300 秒告警
   • Slave_IO_Running / Slave_SQL_Running != Yes 立即告警
   • Relay_Log_Space 過大 (如 > 10GB) 檢查 SQL 執行緒堵塞
4. 備份策略**：從庫執行 `mysqldump --single-transaction --master-data=2` 不影響主庫效能

八、安全加固建議

• 憑證有效期設 10 年 (3650 天)，建立到期提醒機制
• 私鑰檔案權限 `chmod 600 *.pem`，擁有者 mysql:mysql
• 防火牆僅開放主庫 3306 給從庫 IP、管理 IP
• 複寫帳號**僅授予 REPLICATION SLAVE**，最小權限原則
• 啟用 `require_secure_transport=ON` 強制所有連線使用 SSL (MySQL 5.7.8+)

完成上述部署，即可實現跨資料中心、加密傳輸、高可用的 MySQL 主從架構，滿足異構應用讀寫分離與災備需求。