MySQL 主從複制、讀寫分離、備份恢復企業級實戰全紀錄

一、主從複制部署 (GTID 模式推薦)

1. 規劃與前置

• 兩節點同版本 (5.6+/5.7/8.0)，`server-id` 唯一 (主 < 從)
• 主庫開啟 `log-bin`、`gtid_mode=ON`、`enforce_gtid_consistency=ON`
• 建立專用複制帳號：`GRANT REPLICATION SLAVE ON *.* TO 'rep'@'192.168.200.%' IDENTIFIED BY 'nick';`

2. 主庫鎖定、備份、記錄位點

FLUSH TABLES WITH READ LOCK;  -- 5.1: FLUSH TABLES WITH READ LOCK
SHOW MASTER STATUS;           -- 記錄 File + Position (或 GTID: Executed_Gtid_Set)
mysqldump -uroot -p -A --single-transaction --master-data=2 | gzip > /backup/full_$(date +%F).sql.gz
UNLOCK TABLES;

3. 從庫還原、配置、啟動

gunzip < /backup/full_20240115.sql.gz | mysql -uroot -p -S /data/3307/mysql.sock

CHANGE MASTER TO 
    MASTER_HOST='192.168.200.98', MASTER_PORT=3306,
    MASTER_USER='rep', MASTER_PASSWORD='nick',
    MASTER_AUTO_POSITION=1;  -- GTID 模式無需 File/Position
START SLAVE;
SHOW SLAVE STATUS\G  -- 確認 IO/SQL Running=Yes, Seconds_Behind_Master=0

4. 讀寫分離實現

• 從庫 `read-only=ON` (需帳號無 SUPER 權限)
• 中間件路由：ProxySQL / MyCat / ShardingSphere / 自研 Router
• 寫操作 → Master，讀操作 → Slave (可加權重、延遲檢測剔除)

二、故障處理與高可用

常見同步錯誤跳過

-- my.cnf
slave-skip-errors = 1032,1062,1007  -- 找不到行/主鍵衝突/庫已存在
skip-name-resolve  -- 避免 DNS 解析延遲

謹慎使用，優先修正根因 (主鍵衝突通常源於雙寫)。

主庫宕機：從庫提升手冊

1. 所有從庫 `STOP SLAVE IO_THREAD;` 等待 Relay Log 全部重放
2. 比對 `master.info` 選擇 Position 最大者為新主
3. 新主：`STOP SLAVE; RESET MASTER;` 刪除 `master.info`、`relay-log.info`
4. 啟用 `log-bin`、`log-slave-updates`、註解 `read-only`、`replicate-*`
5. 其他從庫重新 `CHANGE MASTER TO` 指向新主

雙主 (Active-Active) 關鍵配置

-- 兩庫均設：
auto_increment_increment = 2
auto_increment_offset = 1  -- 主1
auto_increment_offset = 2  -- 主2
log-slave-updates = 1

應用層按 ID 奇偶路由寫入，避免衝突。

三、備份與恢復體系

mysqldump 關鍵參數

生產備份腳本範例

# 每日 02:00 完整備份 (InnoDB)
mysqldump -u$USER -p$PASS -S $SOCK -F --single-transaction -A -B | gzip > /backup/full_$(date +%F).sql.gz

# 每小時增量：複製新 Binlog
mysqladmin -u$USER -p$PASS -S $SOCK flush-logs
cp /data/3306/mysql-bin.0000XX /backup/binlog/

增量恢復實戰 (誤刪資料庫)

1. 封鎖寫入 (防火牆/鎖表)
2. 解壓完整備份：`mysql < full_20240115.sql`
3. 提取誤操作後至誤操作前的 Binlog：`mysqlbinlog -d db_name mysql-bin.0000XX > inc.sql`，**刪除誤操作語句 (DROP DATABASE)**
4. 重放增量：`mysql db_name < inc.sql`
5. 驗證資料完整性

四、監控與運維清單

• 關鍵指標：`Seconds_Behind_Master`、Binlog 大小/數量、磁碟使用率、錯誤計數
• 定期 `SHOW SLAVE STATUS\G`、`SHOW MASTER STATUS` 巡檢
• 季度故障切換演練、年度全量恢復演練
• 升級 MySQL 版本時同步複制格式相容性測試

MySQL 主從 + 讀寫分離 + 分級備份構成經典高可用基石，配合 GTID、ProxySQL、Binlog 增量恢復，可滿足絕多數中型互聯網業務 RPO<15min、RTO<30min 需求。