MySQL 主從複製配置實戰：標準架構、雙主熱備、權限隔離與故障處理

MySQL Replication：從單向主從到雙主熱備的完整配置指南

複製架構核心：Master 寫 Binlog → Slave 讀 Relay Log → SQL 線程重放。版本原則：Master 版本 ≤ Slave 版本，Binlog 格式向前相容。

環境假設

• Master: rep1 (10.0.0.1), Slave: rep2 (10.0.0.2)
• MySQL 5.0.15, Basedir: /usr/local/mysql, Datadir: /usr/local/mysql/data
• 複製賬號: repl/replpass

一、Master 配置

[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-ignore-db = mysql          # 不同步權限庫
# binlog-do-db = business_db      # 僅同步指定庫 (二選一)

# 建立複製賬號
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'rep2' IDENTIFIED BY 'replpass';
-- 若需 LOAD DATA FROM MASTER，額外授權：
GRANT FILE, SELECT, REPLICATION SLAVE ON *.* TO 'repl'@'rep2' IDENTIFIED BY 'replpass';
FLUSH PRIVILEGES;

二、Master 數據基線備份（熱備不鎖表首選）

# 方法 A：mysqldump 單線程 (小庫適用)
mysqldump --single-transaction --master-data=2 --all-databases > base.sql

# 方法 B：物理複製 (大庫、極速)
# 1. 加全局讀鎖
FLUSH TABLES WITH READ LOCK;
# 2. 另開終端打包數據目錄
tar czf /tmp/data.tar.gz /usr/local/mysql/data
# 3. 記錄 Binlog 位置
SHOW MASTER STATUS;  -- 記下 File + Position
# 4. 解鎖
UNLOCK TABLES;
# 5. 傳輸解壓到 Slave，設置權限

三、Slave 配置

[mysqld]
server-id = 2
master-host = rep1
master-user = repl
master-password = replpass
master-port = 3306
master-connect-retry = 60
replicate-ignore-db = mysql
replicate-do-db = business_db
relay-log = relay-bin
log-slave-updates = 1          # 鏈式複製必開
read-only = 1                  # 防止誤寫入

四、啟動並驗證

-- Slave 導入基線數據後
CHANGE MASTER TO
  MASTER_HOST='rep1',
  MASTER_USER='repl',
  MASTER_PASSWORD='replpass',
  MASTER_LOG_FILE='mysql-bin.000001',  -- 對應 SHOW MASTER STATUS
  MASTER_LOG_POS=107;

START SLAVE;
SHOW SLAVE STATUS\G
-- 關鍵指標：
-- Slave_IO_Running: Yes
-- Slave_SQL_Running: Yes
-- Seconds_Behind_Master: 0

五、雙主熱備 (Master-Master) 擴展

在 Master 端追加 Slave 配置，在 Slave 端追加 Master 配置，server-id 必須不同。關鍵防衝突：

• auto_increment_offset / auto_increment_increment 錯開自增 ID。
• 業務層路由寫入單一節點，另一節點僅作熱備/讀取。
• 監控 Seconds_Behind_Master、Slave_*_Running、Binlog 延遲。

常見故障與處理

監控腳本片段

#!/bin/bash
STATUS=$(mysql -e "SHOW SLAVE STATUS\G" | grep -E "Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master")
if echo "$STATUS" | grep -q "No"; then
  echo "$STATUS" | mail -s "MySQL Replication Alert" dba@example.com
fi

1. 上一篇文章： CentOS 5.x 升級 PHP 5.2 + MySQL 5.1 實戰：yum 源配置、版本鎖定與依賴處理
2. 下一篇文章： Access 數據遷移至 MySQL 兩大實戰方案：文本導入與 mysqldump 對比

MySQL 綜合教學

MySQL 多表查詢核心：內連接、外連接、自然連接與笛卡爾積演化全解析

MySQL 多表查詢核心：從笛卡爾積到各類 JOIN 的演化

MySQL 命令行導入資料庫實戰：source 命令、編碼設置與大檔案優化

MySQL 命令行導入資料庫實戰：source 命令、編碼設

MySQL 中文亂碼終極解決：匯出 SQL/Excel 與 Java 連線編碼實戰

環境 Mac OS X 10.8.3、MySQL 5.6.1

MS SQL Server Management Studio Express (SSMSE) 安裝圖文教程

MS SQL Server Management Studi