MySQL 主從/雙主複制配置標準化流程與常見坑點避坑指南

MySQL 主從/雙主複制配置標準化流程與常見坑點避坑指南

標準化部署步驟、關鍵參數、驗證指標與故障排查清單，確保複制穩定可靠。

1. 架構規劃

• 單向主從：Master 寫、Slave 讀（報表/備份）。
• 雙主（互為主從）：雙寫，極易衝突，**生產不建議**，改用 MHA/Orchestrator 單主高可用。
• GTID 複制：MySQL 5.6+，自動定位位點，故障切換無需手動 CHANGE MASTER。

2. 配置檔模板

# Master (192.168.1.100)
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog_format = ROW
binlog-do-db = test
binlog-ignore-db = mysql
sync_binlog = 1
gtid_mode = ON
enforce_gtid_consistency = ON

# Slave (192.168.1.101)
[mysqld]
server-id = 2
relay_log = relay-bin
replicate-do-db = test
replicate-ignore-db = mysql
gtid_mode = ON
enforce_gtid_consistency = ON
read_only = 1          # 防止誤寫入
super_read_only = 1    # 5.7.8+ 僅 super 權限可寫

3. 標準部署步驟（GTID 模式）

# 1. 主庫建立複制帳號
CREATE USER 'repl'@'192.168.1.%' IDENTIFIED BY 'StrongPass@123';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.%';
FLUSH PRIVILEGES;

# 2. 主庫備份（熱備 xtrabackup 或邏輯備份）
# 此處示範 xtrabackup 流式傳輸
innobackupex --stream=xbstream --user=root --password=xxx ./ | ssh 192.168.1.101 "xbstream -x -C /var/lib/mysql"

# 3. 從庫啟動、設定 GTID 複制
systemctl start mysqld
mysql> CHANGE MASTER TO
  MASTER_HOST='192.168.1.100',
  MASTER_USER='repl',
  MASTER_PASSWORD='StrongPass@123',
  MASTER_AUTO_POSITION=1;  -- GTID 自動定位
START SLAVE;

# 4. 驗證
SHOW SLAVE STATUS\G
# Slave_IO_Running: Yes
# Slave_SQL_Running: Yes
# Retrieved_Gtid_Set / Executed_Gtid_Set 同步推進
# Seconds_Behind_Master: 0

4. 雙主額外配置（若必須使用）

# 兩庫均設定
auto_increment_increment = 2
auto_increment_offset = 1  # Master A
auto_increment_offset = 2  # Master B
# 防止雙寫衝突：業務層路由同一實體單寫、或用行級鎖協調

5. 常見故障速查表

6. 監控關鍵指標

• Seconds_Behind_Master < 5s（同城機房）。
• Slave_IO_Running / Slave_SQL_Running = Yes。
• Relay_Log_Space 不持續增長。
• Binlog 磁碟佔用、Relay Log 磁碟佔用。

總結

複制穩定性 = 標準化部署 + GTID 自動定位 + 多線程重放 + 唯讀從庫 + 完善監控告警 + 定期演練切換。拒絕雙寫，擁抱單主高可用架構。

1. 上一篇文章： MySQL InnoDB Memcached 插件深度解析：架構、配置、限制與實戰範例
2. 下一篇文章： SQL Server 遷移 MySQL 實戰：ODBC 連結伺服器導入大數據量方案

MySQL 綜合教學

MySQL LOAD DATA 實戰：高效導入文本文件到數據庫

LOAD DATA INFILE 是 MySQL 批量導入數

C# 連接 MySQL 解決中文亂碼完整指南

開發環境與問題現象 環境： Visual Studio 20

MySQL mysqldump 數據導出全攻略：常用場景、核心參數與跨實例遷移實戰

MySQL mysqldump 數據導出全攻略：常用場景、核

MyEclipse 連接 MySQL 圖文教程：Database Explorer 配置與專案導包實戰

環境準備 MyEclipse 2017 / 2019 /