架構規劃
- Master：寫入節點，啟用 Binlog，
server-id=1
- Slave：讀取/備份節點，
server-id=2，啟用 Relay Log
- 網路互通、防火牆開放 3306、時間同步 (NTP)
1. Master 配置 (my.cnf)
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW -- 推薦 ROW 格式，避免非確定性 SQL 問題
binlog-do-db = business_db -- 僅同步指定庫 (可選)
# binlog-ignore-db = test -- 排除庫 (可選)
# InnoDB 穩健性 (生產必開)
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
# GTID 模式 (5.6+ 推薦)
gtid_mode = ON
enforce_gtid_consistency = ON
2. 建立複寫帳號
CREATE USER 'repl_user'@'%' IDENTIFIED BY 'StrongReplPass!23';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';
FLUSH PRIVILEGES;
3. 取得 Master 同步座標
FLUSH TABLES WITH READ LOCK; -- 短暫鎖表確保一致性
SHOW MASTER STATUS;
-- 記下 File (如 mysql-bin.000001) 和 Position (如 154)
UNLOCK TABLES;
4. Slave 配置 (my.cnf)
[mysqld]
server-id = 2
relay-log = slave-relay-bin
relay-log-index = slave-relay-bin.index
read_only = 1 -- 防止誤寫入
# GTID 模式同步開啟
gtid_mode = ON
enforce_gtid_consistency = ON
5. 初始化 Slave 資料 (兩種方式)
方式 A：mysqldump + 導入 (小/中型庫)
# Master
mysqldump -uroot -p --all-databases --master-data=2 --single-transaction --routines --triggers --events > full.sql
# Slave
mysql -uroot -p < full.sql
方式 B：Percona XtraBackup 實體備份 (大庫、不鎖表)
# Master
xtrabackup --backup --target-dir=/backup/full --user=root --password
# 傳輸到 Slave 後
xtrabackup --prepare --target-dir=/backup/full
rsync -av /backup/full/ /var/lib/mysql/
chown -R mysql:mysql /var/lib/mysql
6. 啟動複寫 (GTID 模式 5.6+)
CHANGE MASTER TO
MASTER_HOST='master_ip',
MASTER_PORT=3306,
MASTER_USER='repl_user',
MASTER_PASSWORD='StrongReplPass!23',
MASTER_AUTO_POSITION=1; -- GTID 自動定位，無需 File/Position
START SLAVE;
7. 驗證狀態
SHOW SLAVE STATUS\G
-- 關鍵指標
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
Retrieved_Gtid_Set: xxxx
Executed_Gtid_Set: xxxx
常見故障排查表
| 現象 | 可能原因 | 解決 |
|---|
| IO_Running: Connecting | 網路不通/防火牆/帳號密碼錯 | telnet master 3306、檢查 GRANT、FLUSH PRIVILEGES |
| SQL_Running: No (Error 1062) | 主鍵衝突 (雙寫/手動改 Slave) | 跳過錯誤 SET GLOBAL sql_slave_skip_counter=1; 或重建 Slave |
| Seconds_Behind_Master 持續增長 | Slave 處理慢 / 大事務 / 無索引 | 優化慢查詢、slave_parallel_workers (5.7+)、升級硬體 |
| Relay log 損壞 | 磁碟故障 / kill -9 | STOP SLAVE; CHANGE MASTER TO MASTER_AUTO_POSITION=1; START SLAVE; (GTID 重新同步) |
高可用延伸：MHA / Orchestrator / Group Replication
- MHA：Master 高可用自動故障轉移 (Perl 腳本)。
- Orchestrator：視覺化拓撲管理、自動故障轉移、遷移。
- Group Replication (MySQL 5.7.17+)：原生多主同步、Paxos 一致性、自動主選。
