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

MySQL 主從複制架構完整實作指南：從基礎配置到雙向同步與故障轉移

編輯：MySQL 資料庫相關知識

前言：為什麼需要 MySQL 複制？

MySQL 內建的複制功能建立在兩台或多台伺服器之間，透過主從關係實現資料同步。一台作為主伺服器負責寫入，其他作為從伺服器負責讀取與備份。本文將詳細記錄如何配置雙向主從複制、處理故障切換，以及改善 PHP 連接程式的容錯機制。測試環境基於 MySQL 3.23.23，官方建議生產環境使用最新穩定版並確保主從版本一致。

第一步：配置主伺服器

假設兩台伺服器：

主機 A：IP 10.1.1.1
備機 B：IP 10.1.1.2

1. 建立複制專用帳號

從伺服器需以特定帳號連線主伺服器讀取二進制日誌，因此在主機建立帳號並僅授予 FILE 權限：

GRANT FILE ON *.* TO 'replicate'@'10.1.1.2' IDENTIFIED BY 'passWord';
FLUSH PRIVILEGES;

2. 啟用二進制日誌並建立資料快照

編輯 my.cnf 的 [mysqld] 區段加入 log-bin，重啟 MySQL 後主機會產生 主機名-bin.序號 格式的二進制日誌檔。

接著關閉 MySQL，將主機所有資料庫目錄完整複製（注意：MyISAM 資料檔 .MYD/.MYI/.frm 依賴檔案系統，異構環境請改用 mysqldump），複製完成後重新啟動 MySQL。此時你擁有了建立快照那一刻的資料狀態，以及後續所有變更的二進制日誌。

第二步：配置從伺服器

停止備機 MySQL 服務。
將主機複製的資料目錄放入備機 data 目錄，修正擁有者為 mysql:mysql，檔案權限 660，目錄權限 770。
啟動備機驗證資料完整性（僅執行 SELECT 測試），確認無誤後再次關閉服務。
編輯備機 my.cnf 的 [mysqld] 區段加入：

master-host=10.1.1.1
master-user=replicate
master-password=passWord

啟動備機後，它會自動連線主機、讀取 master.info 記錄的位置、拉取二進制日誌並重放變更。可用 SHOW SLAVE STATUS 監控同步狀態；若發生錯誤，從執行緒會停止並記錄於 .err 日誌，修正後可用 SLAVE START 重新啟動。

第三步：建立雙向主從關係（互為主備）

單向複制只能在主機故障時切換讀取流量，主機恢復後無法自動同步回寫入的資料。解決方法是建立 B→A 的複制：

在備機 B 的 my.cnf 加入 log-bin 並重啟。
在 B 建立給 A 使用的複制帳號：GRANT FILE ON *.* TO 'replicate'@'10.1.1.1' IDENTIFIED BY 'passWord';
在主機 A 的 my.cnf 加入指向 B 的 master-host/user/password 設定並重啟。

完成後，任一伺服器的寫入都會複寫到另一台。但需注意：從伺服器合併日誌的速度非即時，不建議用此架構做寫入負載平衡（可能導致自增主鍵衝突）。

第四步：改良資料庫連接程式——實現自動故障轉移

基礎版：簡單嘗試備機

function db_connect() {
    $username = 'replUser';
    $password = 'passWord';
    $primary  = '10.1.1.1';
    $backup   = '10.1.1.2';

    if (!($link = @mysql_connect($primary, $username, $password))) {
        $link = @mysql_connect($backup, $username, $password);
    }
    return $link;
}

缺點：若主機整台掛掉（非 MySQL 服務），PHP 的 mysql_connect 會無限期等待（無逾時參數），導致腳本卡死。

進階版：使用 fsockopen 實現連線逾時偵測

function db_connect_plus() {
    $username = 'username';
    $password = 'passWord';
    $primary  = '10.1.1.1';
    $backup   = '10.1.1.2';
    $timeout  = 15; // 秒

    // 偵測主機 3306 埠是否可達
    if ($fp = @fsockopen($primary, 3306, $errno, $errstr, $timeout)) {
        fclose($fp);
        return mysql_connect($primary, $username, $password);
    }
    // 主機不可達，嘗試備機
    if ($fp = @fsockopen($backup, 3306, $errno, $errstr, $timeout)) {
        fclose($fp);
        return mysql_connect($backup, $username, $password);
    }
    return false; // 雙雙失敗
}

此函式先以 TCP 連線測試埠可用性，若主機 MySQL 掛掉但主機存活，連線會立即失敗並秒切備機；若主機整台離線，fsockopen 會在 15 秒後逾時，避免腳本長時間阻塞。若自訂埠號請同步修改。

結論與實務建議

完整快照是關鍵：漏複製任何資料表會導致從執行緒報錯停止。建立快照前務必關閉二進制日誌，否則從端重放時可能因主鍵重複報錯。
監控同步延遲：定期檢查 Seconds_Behind_Master 確保從庫未落後過多。
讀寫分離優於雙寫：生產環境建議「單寫多讀」：主庫處理所有 INSERT/UPDATE/DELETE，多個從庫分擔 SELECT，避免雙向複制的衝突風險。
升級無停機：複制架構允許輪流升級從庫、切換角色、再升級原主庫，實現零停機維護。

MySQL 複制配置相對簡單，卻能為系統提供額外的高可用性保障。更多進階參數請參閱官方線上手冊第 11 章。