MySQL 主從複寫架構高可用實戰：雙向同步與 PHP 自動切換

架構目標

• 主庫（Master）故障時，應用自動切至從庫（Slave）持續服務。
• 主庫恢復後，雙向同步確保資料零差異。
• 無共享存儲、低成本實現高可用。

環境規劃

步驟 1：主庫啟用 Binlog 與建立複寫帳號

# my.cnf [mysqld]
server-id=1
log-bin=mysql-bin
binlog-do-db=app_db

# SQL
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.1.1.2' IDENTIFIED BY 'StrongPass!';
FLUSH PRIVILEGES;

步驟 2：主庫熱備份與同步座標

# 主庫鎖表備份
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;  -- 記錄 File & Position
# 解鎖
UNLOCK TABLES;

# 備份資料目錄或使用 mysqldump --master-data=2 --single-transaction

步驟 3：從庫還原與配置

# my.cnf [mysqld]
server-id=2
relay-log=relay-bin
read-only=1  # 防誤寫

# SQL
CHANGE MASTER TO
  MASTER_HOST='10.1.1.1',
  MASTER_USER='repl',
  MASTER_PASSWORD='StrongPass!',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=154;
START SLAVE;
SHOW SLAVE STATUS\G  -- 確認 Slave_IO_Running & Slave_SQL_Running = Yes

步驟 4：建立雙向複寫（B → A）

在 B 上同樣啟用 log-bin、建立帳號，在 A 上執行 CHANGE MASTER TO ... MASTER_HOST='10.1.1.2' 並 START SLAVE。

步驟 5：PHP 智能連線切換

function db_connect() {
    $cfg = [
        'primary'   => ['host'=>'10.1.1.1', 'port'=>3306],
        'secondary' => ['host'=>'10.1.1.2', 'port'=>3306],
        'user' => 'app_user', 'pass' => 'AppPass!', 'db' => 'app_db',
        'timeout' => 3  // 秒
    ];

    foreach (['primary','secondary'] as $role) {
        $h = $cfg[$role];
        if ($fp = @fsockopen($h['host'], $h['port'], $errno, $errstr, $cfg['timeout'])) {
            fclose($fp);
            $link = mysqli_connect($h['host'], $cfg['user'], $cfg['pass'], $cfg['db'], $h['port']);
            if ($link) return $link;
        }
    }
    throw new Exception('All DB nodes unreachable');
}

關鍵： 使用 fsockopen 先測 TCP 連通性，避免 mysqli_connect 無超時參數導致長時間阻塞。

運維注意事項

• 錯誤處理： SHOW SLAVE STATUS 發現 Last_SQL_Error 需手工修復後 START SLAVE。
• 自增衝突： 雙主建議設定 auto_increment_increment=2 + auto_increment_offset=1/2 避免 PK 衝突。
• 延遲監控： Seconds_Behind_Master > 30 秒告警。
• GTID 模式 (5.6+)： 簡化故障轉移，強烈建議啟用 gtid_mode=ON。

此架構成本低、原理透明，適合中小流量核心業務；大規模高可用建議採用 MHA、Orchestrator 或 InnoDB Cluster (Group Replication)。