環境規劃
| 角色 | IP | Server-ID | 用途 |
|---|
| Master | 10.0.1.10 | 1 | 寫入主庫 |
| Slave | 10.0.2.20 | 2 | 讀取從庫、備援 |
Master 配置 (/etc/my.cnf)
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON
log-slave-updates = ON
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1
# 安全：僅內網介面
bind-address = 10.0.1.10
require_secure_transport = ON
建立複寫帳號 (Master 執行)
CREATE USER 'repl_user'@'10.0.2.20' IDENTIFIED BY 'ReplPass123!' REQUIRE SSL;
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'10.0.2.20';
FLUSH PRIVILEGES;
Slave 配置 (/etc/my.cnf)
[mysqld]
server-id = 2
relay-log = relay-bin
log-slave-updates = ON
read-only = ON
gtid_mode = ON
enforce_gtid_consistency = ON
master-info-repository = TABLE
relay-log-info-repository = TABLE
# 複寫連線參數 (亦可用 CHANGE MASTER TO 動態設定)
master-host = 10.0.1.10
master-port = 3306
master-user = repl_user
master-password = ReplPass123!
master-connect-retry = 60
master-retry-count = 86400
# 安全
bind-address = 10.0.2.20
require_secure_transport = ON
初始化同步 (兩種方式)
方式一：XtraBackup 熱備份 (推薦、不鎖表、大數據適用)
# Master: 備份
xtrabackup --backup --target-dir=/backup/full_$(date +%F) --user=backup --password='Pass' --slave-info
# Slave: 複製備份檔、還原
xtrabackup --prepare --target-dir=/backup/full_20240115
xtrabackup --copy-back --target-dir=/backup/full_20240115
chown -R mysql:mysql /var/lib/mysql
systemctl start mysql
# Slave: 啟動複寫 (GTID 模式自動定位)
CHANGE MASTER TO MASTER_HOST='10.0.1.10', MASTER_USER='repl_user', MASTER_PASSWORD='ReplPass123!', MASTER_AUTO_POSITION=1;
START SLAVE;
方式二：mysqldump 邏輯備份 (小數據量、跨版本遷移)
# Master: 匯出 (含 GTID 資訊)
mysqldump --single-transaction --master-data=2 --all-databases --routines --triggers --events | gzip > full_$(date +%F).sql.gz
# Slave: 匯入
gunzip < full_20240115.sql.gz | mysql -u root -p
# Slave: 啟動複寫
CHANGE MASTER TO MASTER_HOST='10.0.1.10', MASTER_USER='repl_user', MASTER_PASSWORD='ReplPass123!', MASTER_AUTO_POSITION=1;
START SLAVE;
驗證複寫狀態
# Slave 執行
SHOW SLAVE STATUS\G
# 關鍵指標
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
Master_Server_Id: 1
Master_UUID:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 1
常見故障與處理
| 現象 | 原因 | 處理 |
|---|
| Slave_IO_Running: Connecting | 網路不通、防火牆、認證失敗、SSL 憑證 | telnet master 3306、檢查 user 表 Host/Plugin、SSL CA |
| Slave_SQL_Running: No | SQL 執行錯誤 (主鍵衝突、欄位不存在、外鍵失敗) | SHOW SLAVE STATUS\G 看 Last_SQL_Error、修正資料/結構、START SLAVE |
| Seconds_Behind_Master 偏大 | 長交易、大批次更新、Slave 規格不足、單執行緒複寫瓶頸 | 啟用多執行緒複寫 (slave_parallel_workers=4)、優化大交易、升級 Slave 硬體 |
| GTID 不一致 | Master 重置、Slave 手動執行寫入、跳過事務 | 重新同步 (XtraBackup) 或 GTID 集合修正 (高風險) |
故障切換演練 (Master 宕機 → Slave 升級)
- 確認 Master 確實不可用： 多節點健康檢查、防止腦裂
- Slave 停止複寫、提升為獨立主庫：
STOP SLAVE;
RESET SLAVE ALL;
SET GLOBAL read_only = OFF;
-- 更新應用配置指向新 Master IP
- 建立新 Slave (原 Master 修復後或新節點)： 重新初始化同步
- 事後複盤： 根因分析、流程優化、文件更新
監控告警規則 (Prometheus)
- alert: MySQLReplicationLag
expr: mysql_slave_status_seconds_behind_master > 300
for: 5m
labels: { severity: warning }
annotations:
summary: "MySQL 複寫延遲超過 5 分鐘"
description: "Slave {{ \$labels.instance }} 延遲 {{ \$value }} 秒"
- alert: MySQLReplicationDown
expr: mysql_slave_status_slave_io_running == 0 OR mysql_slave_status_slave_sql_running == 0
for: 1m
labels: { severity: critical }
annotations:
summary: "MySQL 複寫中斷"
description: "Slave {{ \$labels.instance }} IO/SQL 執行緒停止"
結語
MySQL 5.0+ 主從複寫採用 **GTID 模式 + ROW 格式 + SSL 加密 + XtraBackup 熱備份** 為企業級標準配置。關鍵成功因素：**Server-ID 唯一、複寫帳號最小權限、GTID 自動定位、多執行緒複寫 (5.6+)、監控告警全覆蓋、故障切換定期演練**。複寫非備份，務必配合 XtraBackup 定期實體備份構建完整資料保護體系。
