MySQL 主從複製集群搭建實戰：GTID、故障排查、災難恢復全記錄

MySQL 主從複製集群搭建實戰：GTID、故障排查、災難恢復全記錄

基於 CentOS 6.5 + Percona Server 5.6.20，記錄雙節點主從搭建全過程，含防火牆阻斷、權限缺失、只讀配置失效等真實坑點及解決，附災難恢復 SOP。

架構與版本

• 主庫：192.168.18.66 (server-id=10)
• 從庫：192.168.18.67 (server-id=20)
• MySQL：5.6.20 (GTID 模式)
• 復制庫：reptest

主庫配置 /etc/my.cnf

[mysqld]
server-id=10
log-bin=mysql-bin
binlog_format=ROW
binlog-ignore-db=mysql,information_schema,performance_schema
replicate-do-db=reptest
gtid-mode=on
enforce-gtid-consistency=on
log-slave-updates=1
sync-master-info=1

從庫配置 /etc/my.cnf

[mysqld]
server-id=20
relay_log=mysql-relay-bin
read_only=1
# 以下舊式配置在 5.6+ GTID 模式下不生效，由 CHANGE MASTER TO 指定
# master-host=192.168.18.66
# master-user=repl
# master-password=123

關鍵步驟：建立複製賬號、快照、同步

1. 主庫創建複製用戶：

   GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY '123456';
   FLUSH PRIVILEGES;

2. 主庫鎖表、記錄 binlog 位點、導出數據：

   FLUSH TABLES WITH READ LOCK;
   SHOW MASTER STATUS\G;  -- 記錄 File、Position
   mysqldump -uroot -p reptest --triggers --routines --events > /home/zxw/master_reptest.sql
   UNLOCK TABLES;

3. 從庫建庫、導入快照：

   CREATE DATABASE reptest;
   mysql -uroot -p reptest < /home/zxw/master_reptest.sql

4. 從庫配置同步起點、啟動複製：

   CHANGE MASTER TO
         MASTER_HOST='192.168.18.66',
         MASTER_PORT=3306,
         MASTER_USER='repl',
         MASTER_PASSWORD='123456',
         MASTER_LOG_FILE='mysql-bin.000002',
         MASTER_LOG_POS=401,
         MASTER_AUTO_POSITION=1;  -- GTID 模式核心參數
   START SLAVE;

5. 驗證：

   SHOW SLAVE STATUS\G;
   -- 關鍵指標：
   Slave_IO_Running: Yes
   Slave_SQL_Running: Yes
   Seconds_Behind_Master: 0
   Auto_Position: 1
   Retrieved_Gtid_Set: ...
   Executed_Gtid_Set: ...

真實坑點排查記錄

坑 1：Slave_IO_Running: Connecting — 防火牆阻斷 3306

Last_IO_Error: error connecting to master 'repl@192.168.18.66:3306' - retry-time: 60 retries: 1
Last_IO_Errno: 2003

• 現象：從庫 mysql -h 192.168.18.66 -u repl -p 連不上，主庫 iptables -L 無 3306 規則。
• 解決：主庫 /etc/sysconfig/iptables 添加 -A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT，service iptables restart。
• 驗證：iptables -L 出現 ACCEPT tcp -- anywhere anywhere state NEW tcp dpt:mysql，從庫 START SLAVE 即正常。

坑 2：權限不足 — 複製用戶缺 REPLICATION SLAVE

• 現象：即便防火牆通了，仍報 Access denied for user 'repl'@'...'。
• 根因：Fabric/手動創建用戶時未授 REPLICATION SLAVE。
• 解決：主庫 GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY '123456';。

坑 3：read_only=1 不生效 — 從庫仍可寫入

• 現象：配置 read_only=1 重啟後，直接連從庫 INSERT 成功。
• 原因：read_only **不限制 SUPER 權限用戶**（root 默認有 SUPER）。
• 解決：業務賬號**不要給 SUPER 權限**；或用 super_read_only=1 (5.7.8+) 連 SUPER 也限制。

災難恢復 SOP

場景 A：主從數據不一致（同步斷裂、跳過錯誤導致）

1. 從庫 STOP SLAVE;
2. 重新執行「快照導出 → 導入 → CHANGE MASTER TO → START SLAVE」全流程（省略配置文件修改、用戶創建）。

場景 B：主庫掛了，從庫提升為新主

1. 從庫 STOP SLAVE;
2. 從庫創建新複製用戶：GRANT REPLICATION SLAVE ON *.* TO 'repl'@'從庫IP' IDENTIFIED BY '123456';
3. 原主庫恢復後，按場景 A 流程接入為新從庫（server-id 需唯一）。

監控關鍵指標

總結

**GTID + MASTER_AUTO_POSITION=1** 是 5.6+ 主從搭建的標準姿勢，告別手動算 binlog 位點。真實環境三大殺手：**防火牆、權限缺失、read_only 對 SUPER 不生效**。建立「部署清單、驗證清單、監控儀表板、災難演練腳本」四件套，才能讓主從集群在生產環境穩定跑滿 SLA。