MySQL 主從切換（Failover）操作手冊

適用場景

主庫故障需提升從庫為新主庫，或進行計畫性主從角色互換。操作前請確認雙向複寫狀態正常、資料一致。

操作步驟

第一階段：準備與狀態確認

-- 1. 確認主庫讀寫狀態（主庫應為 read-only=0，從庫 read-only=1）
SHOW VARIABLES LIKE 'read_only';

-- 2. 查看主從狀態
-- 主庫
SHOW MASTER STATUS\G
-- 從庫
SHOW SLAVE STATUS\G

第二階段：停止從庫複寫並提升為新主庫

-- 在從庫執行
STOP SLAVE IO_THREAD;
-- 等待 Relay Log 全部應用完成，確認 Has read all relay log
SHOW PROCESSLIST;

STOP SLAVE;
RESET MASTER;      -- 重置二進制日誌，成為新主庫起點
RESET SLAVE;       -- 清除複寫元資料

-- 建立複寫帳號供原主庫連接
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'10.45.172.40' IDENTIFIED BY 'repl_pass';
FLUSH PRIVILEGES;

-- 確認新主庫 Binlog 位置
SHOW MASTER STATUS\G

常見錯誤處理： 若執行 RESET MASTER 報 ERROR 1186: Binlog closed，請在 /etc/my.cnf 的 [mysqld] 區段加入 log-bin=mysql-bin 並重啟 MySQL 服務。

第三階段：將原主庫降級為從庫

-- 在原主庫執行
STOP SLAVE;           -- 若原本也是從庫
RESET MASTER;
RESET SLAVE;

-- 設定指向新主庫
CHANGE MASTER TO
  MASTER_HOST='10.45.172.37',
  MASTER_USER='repl_user',
  MASTER_PASSWORD='repl_pass',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.000001',  -- 取自新主庫 SHOW MASTER STATUS
  MASTER_LOG_POS=98,
  MASTER_CONNECT_RETRY=60;

START SLAVE;

第四階段：驗證雙向複寫

-- 新主庫
SHOW MASTER STATUS\G

-- 新從庫（原主庫）
SHOW SLAVE STATUS\G
-- 確認 Slave_IO_Running: Yes, Slave_SQL_Running: Yes, Seconds_Behind_Master: 0

關鍵參數說明

注意事項

1. 操作前務必備份關鍵資料。
2. 確保防火牆允許 3306 端口雙向通訊。
3. 生產環境建議使用 MHA、Orchestrator 等自動化故障轉移工具。
4. 切換後更新應用程式資料庫連線配置（VIP 或 DNS 切換更平滑）。