您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 主從複制搭建實戰：從配置到故障切換的完整流程

編輯：MySQL 資料庫相關知識

架構總覽

主庫（Master）：寫入節點，啟用 log-bin 記錄二進位日誌
從庫（Slave）：讀取節點，通過 I/O 執行緒拉取 Binlog，SQL 執行緒重放
非同步複制：預設模式，主庫不等待從庫確認即提交（低延遲、可能丟數據）

步驟 1：主庫配置

# 1. 建立複制專用帳號（僅需 REPLICATION SLAVE 權限）
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.1.1.2' IDENTIFIED BY 'strong_pwd';
FLUSH PRIVILEGES;

# 2. 修改 my.cnf [mysqld] 區段
server-id = 1
log-bin = mysql-bin
# 可選：binlog_format = ROW  （推薦 ROW 格式，減少衝突）

# 3. 重啟服務，獲取快照
systemctl restart mysqld
# 導出全庫（含主鍵、觸發器、存儲過程）
mysqldump --all-databases --master-data=2 --single-transaction -r full_backup.sql

# 4. 記錄當前 Binlog 位置
SHOW MASTER STATUS;
-- 記下 File (如 mysql-bin.000003) 和 Position (如 7321)

步驟 2：從庫配置

# 1. 修改 my.cnf [mysqld]
server-id = 2
relay-log = relay-bin
read-only = 1          # 防止誤寫入
# 可選：replicate-do-db = business_db  （只同步特定庫）

# 2. 重啟服務，導入快照
systemctl restart mysqld
mysql < full_backup.sql

# 3. 配置主庫連線資訊（MySQL 5.6+ 建議用 CHANGE MASTER TO）
CHANGE MASTER TO
  MASTER_HOST = '10.1.1.1',
  MASTER_USER = 'repl',
  MASTER_PASSWORD = 'strong_pwd',
  MASTER_LOG_FILE = 'mysql-bin.000003',
  MASTER_LOG_POS = 7321;

# 4. 啟動複制
START SLAVE;

# 5. 驗證狀態
SHOW SLAVE STATUS\G
-- 關鍵指標：Slave_IO_Running=Yes, Slave_SQL_Running=Yes, Seconds_Behind_Master=0

步驟 3：雙向複制（Master-Master）實現高可用

在從庫上同樣啟用 log-bin、設定 server-id=2，並在主庫上執行 CHANGE MASTER TO 指向從庫，形成雙主。關鍵：設定 auto_increment_offset / auto_increment_increment 避免主鍵衝突。

故障切換與 PHP 應用層處理

// PHP 伪代碼：讀寫分離 + 自動故障轉移
function getDbConnection($write = false) {
    $master = ['host' => '10.1.1.1', 'user' => 'rw_user', ...];
    $slave  = ['host' => '10.1.1.2', 'user' => 'ro_user', ...];
    
    if ($write || !isSlaveHealthy()) {
        return new PDO($master);
    }
    return new PDO($slave);
}

function isSlaveHealthy() {
    $pdo = new PDO($slave);
    $stmt = $pdo->query("SHOW SLAVE STATUS");
    $row = $stmt->fetch();
    return $row['Slave_IO_Running'] === 'Yes' 
        && $row['Slave_SQL_Running'] === 'Yes'
        && $row['Seconds_Behind_Master'] < 10;
}