MySQL 多主循環同步實戰：Rotate Master 存儲過程 + Event 調度 + 權重控制 + 監控

架構原理

單 Slave 輪詢多 Master，通過存儲過程記錄每個 Master 的 MASTER_LOG_FILE、MASTER_LOG_POS，定時 STOP SLAVE → CHANGE MASTER → START SLAVE 切換上游。適用於**多源匯總、異構同步、無 GTID 環境**。

1. 元數據表：rotate_master

CREATE TABLE `mysql`.`rotate_master` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `master_host` VARCHAR(255) NOT NULL COMMENT 'Master IP',
  `master_port` INT UNSIGNED NOT NULL DEFAULT 3306,
  `master_log_file` VARCHAR(255) DEFAULT NULL COMMENT '上次停止時 binlog 文件',
  `master_log_pos` INT UNSIGNED DEFAULT NULL COMMENT '上次停止時 binlog 位點',
  `is_slave_running` VARCHAR(10) DEFAULT NULL COMMENT '上次狀態 Yes/No',
  `in_use` TINYINT(1) DEFAULT 0 COMMENT '當前同步標記',
  `weight` INT UNSIGNED NOT NULL DEFAULT 1 COMMENT '權重 = 連續執行時間片數',
  `repeated_times` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '已執行時間片數',
  `last_execute_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='多主輪詢配置';

2. 初始化配置

INSERT INTO `mysql`.`rotate_master` 
(`master_host`, `master_port`, `master_log_file`, `master_log_pos`, `in_use`, `weight`)
VALUES
('192.168.0.1', 3306, 'mysql-bin.000542', 4, 1, 1),   -- Master 1，權重 1
('192.168.0.2', 3306, 'mysql-bin.000702', 64762429, 0, 2), -- Master 2，權重 2
('192.168.0.3', 3306, 'mysql-bin.000646', 22157422, 0, 5); -- Master 3，權重 5

3. 手動啟動首個 Master 同步

CHANGE MASTER TO
  MASTER_HOST='192.168.0.1',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.000542',
  MASTER_LOG_POS=4,
  MASTER_USER='repl',
  MASTER_PASSWORD='repl';
START SLAVE;

4. 核心存儲過程：rotate_master

DELIMITER $$
CREATE PROCEDURE `mysql`.`rotate_master`()
BEGIN
  DECLARE v_info TEXT;
  DECLARE v_master_file VARCHAR(255);
  DECLARE v_master_pos INT UNSIGNED;
  DECLARE v_master_host VARCHAR(255);
  DECLARE v_master_port INT UNSIGNED;
  DECLARE v_is_running VARCHAR(10);
  DECLARE v_id INT UNSIGNED;
  DECLARE v_weight INT UNSIGNED;
  DECLARE v_repeated INT UNSIGNED;

  -- 1. 獲取當前 Slave 運行狀態
  SELECT VARIABLE_VALUE INTO v_is_running
  FROM information_schema.GLOBAL_STATUS
  WHERE VARIABLE_NAME = 'Slave_running';

  -- 2. 停止 Slave，讀取 relay-log.info 獲取最新 Master 位點
  STOP SLAVE;
  SELECT LOAD_FILE(@@relay_log_info_file) INTO v_info;
  SELECT 
    SUBSTRING_INDEX(SUBSTRING_INDEX(v_info, '\n', 3), '\n', -1),
    SUBSTRING_INDEX(SUBSTRING_INDEX(v_info, '\n', 4), '\n', -1)
  INTO v_master_file, v_master_pos;

  -- 3. 更新當前 in_use=1 的記錄（保存位點、狀態）
  UPDATE `mysql`.`rotate_master`
  SET `master_log_file` = v_master_file,
      `master_log_pos` = v_master_pos,
      `id` = LAST_INSERT_ID(id),
      `is_slave_running` = v_is_running
  WHERE `in_use` = 1;

  -- 4. 判斷是否達到權重上限，需切換下一個 Master
  SELECT `weight`, `repeated_times` INTO v_weight, v_repeated
  FROM `mysql`.`rotate_master` WHERE `in_use` = 1;

  IF v_weight <= v_repeated THEN
    -- 4.1 選取下一個 Master（按 id 循環）
    SELECT `id`, `master_host`, `master_port`, `master_log_file`, `master_log_pos`
    INTO v_id, v_master_host, v_master_port, v_master_file, v_master_pos
    FROM `mysql`.`rotate_master`
    ORDER BY `id` <= LAST_INSERT_ID(), `id` LIMIT 1;

    -- 4.2 動態構建 CHANGE MASTER
    SET @sql = CONCAT(
      'CHANGE MASTER TO ',
      'MASTER_HOST=', QUOTE(v_master_host), ', ',
      'MASTER_PORT=', v_master_port, ', ',
      'MASTER_LOG_FILE=', QUOTE(v_master_file), ', ',
      'MASTER_LOG_POS=', v_master_pos, ', ',
      'MASTER_USER="repl", MASTER_PASSWORD="repl"'
    );
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- 4.3 更新標記
    UPDATE `mysql`.`rotate_master` SET `in_use` = 0, `repeated_times` = 0 WHERE `in_use` = 1;
    UPDATE `mysql`.`rotate_master` SET `in_use` = 1, `repeated_times` = 1 WHERE `id` = v_id;
  ELSE
    -- 4.4 未達權重，累加計數
    UPDATE `mysql`.`rotate_master` SET `repeated_times` = `repeated_times` + 1 WHERE `in_use` = 1;
  END IF;

  -- 5. 重新啟動 Slave
  START SLAVE;
END$$
DELIMITER ;

5. Event 排程器：每 2 分鐘執行一次

SET GLOBAL event_scheduler = ON;

CREATE EVENT `mysql`.`rotate_master_event`
ON SCHEDULE EVERY 120 SECOND
STARTS CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
ON COMPLETION NOT PRESERVE ENABLE
DO CALL `mysql`.`rotate_master`();

運行效果

• 時間片 = 2 分鐘
• Master 1：執行 1 × 2 = 2 分鐘 → 切換
• Master 2：執行 2 × 2 = 4 分鐘 → 切換
• Master 3：執行 5 × 2 = 10 分鐘 → 切換
• 循環往複，實現**加權輪詢**

監控與運維

-- 查看當前同步 Master
SELECT * FROM mysql.rotate_master WHERE in_use = 1;

-- 查看歷史切換記錄
SELECT * FROM mysql.rotate_master ORDER BY last_execute_time DESC;

-- 手動下線某 Master（設權重 0）
UPDATE mysql.rotate_master SET weight = 0 WHERE id = 2;

-- 手動上線新 Master
INSERT INTO mysql.rotate_master (master_host, master_port, master_log_file, master_log_pos, weight)
VALUES ('192.168.0.4', 3306, 'mysql-bin.000001', 4, 3);

優缺點與避坑

生產建議

1. 時間片建議 ≥ 5 分鐘，減少切換開銷。
2. Master 統一開啟 gtid_mode=ON、sync_binlog=1、binlog_format=ROW。
3. 監控告警：Slave_IO_Running/Slave_SQL_Running、Seconds_Behind_Master、rotate_master.last_execute_time 停滯。
4. 重要業務建議升級至 **MySQL 8.0 + Group Replication** 或 **MariaDB Galera Cluster** 實現真正多主同步。