mysql主从复制中的failover与手动切换方案

MySQL主从切换时必须重设MASTER_LOG_FILE和MASTER_LOG_POS，仅改MASTER_HOST会导致复制错误；需停写、确认追平、重置auto_increment、清理relay log并避免误判Seconds_Behind_Master为NULL。

MySQL 主从切换时 CHANGE MASTER TO 的关键参数必须重设

主库宕机后，若要将原从库提升为主库并让其他从库指向它，CHANGE MASTER TO 不只是改 MASTER_HOST 就行。漏掉 MASTER_LOG_FILE 和 MASTER_LOG_POS 会导致从库拉取错误位置的 binlog，轻则数据跳变，重则复制中断报错 Could not find first log file name in binary log index file。

实操建议：

• 在原主库宕机前，用 SHOW MASTER STATUS 记下 File 和 Position；若已不可达，则在新主库上执行 SHOW SLAVE STATUS\G 查 Relay_Master_Log_File 和

  

  Exec_Master_Log_Pos（需确保 Seconds_Behind_Master = 0）
• 所有待切换的从库执行：

  CHANGE MASTER TO
    MASTER_HOST='new-master-ip',
    MASTER_PORT=3306,
    MASTER_USER='repl',
    MASTER_PASSWORD='xxx',
    MASTER_LOG_FILE='mysql-bin.000012',
    MASTER_LOG_POS=198765432;

• 切勿复用旧的 MASTER_AUTO_POSITION=1 —— GTID 模式下 failover 更复杂，非 GTID 环境必须显式指定日志文件与位置

手动切换前必须停写并确认从库已追平

很多故障源于“以为追平了”，实际 Seconds_Behind_Master 显示为 0 但 IO 线程仍在缓存 relay log，SQL 线程尚未执行完。直接切会导致新主库缺失最后一批事务。

验证方法：

• 在从库执行 STOP SLAVE; 后，再查 SHOW SLAVE STATUS\G，确认 Slave_IO_Running: No、Slave_SQL_Running: No，且 Exec_Master_Log_Pos 与 Relay_Master_Log_File 和原主库宕机前的 SHOW MASTER STATUS 完全一致
• 若原主库已无法访问，可用 SELECT MASTER_POS_WAIT('mysql-bin.000012', 198765432, 30) 在从库上阻塞等待追平（需提前开启 log_slave_updates）
• 切主前，在原主库上执行 FLUSH TABLES WITH READ LOCK; 并记录 SHOW MASTER STATUS，再 UNLOCK TABLES; —— 这是唯一能保证逻辑一致性的停写方式

failover 脚本里漏判 Seconds_Behind_Master = NULL 是高频坑

当从库 IO 线程异常断开（如网络闪断、主库关闭），Seconds_Behind_Master 会变成 NULL 而非数字。用 if [ $delay -eq 0 ] 类脚本判断是否可切，会直接报错退出或误判为延迟 0 秒。

正确做法：

• Shell 脚本中应先检查字段值是否为数字：

  delay=$(mysql -Nse "SHOW SLAVE STATUS\G" | grep "Seconds_Behind_Master:" | awk '{print \$2}')
  if [[ "$delay" =~ ^[0-9]+$ ]] && [ "$delay" -eq 0 ]; then
    echo "ready to promote"
  fi

• 监控告警也需区分 NULL（IO 异常）、0（正常追平）、负数（GTID 模式下可能的特殊情况）
• SHOW SLAVE STATUS 中 Slave_IO_Running 和 Slave_SQL_Running 必须同时为 Yes 才算健康，缺一不可

切换后务必重置 auto_increment 偏移以防写冲突

原主库恢复后若作为从库重新接入，若未调整 auto_increment_offset 和 auto_increment_increment，多主写入时极易出现主键冲突。即使当前是单主架构，也要为后续扩容留余地。

操作建议：

• 新主库上立即执行：

  SET GLOBAL auto_increment_increment=2;
  SET GLOBAL auto_increment_offset=2;

• 原主库重入集群前，将其 auto_increment_offset 设为 1，保持两台机器 offset 错开
• 该设置仅对新生成的自增 ID 生效，已有数据不受影响；但必须在任何写入发生前完成，否则第一条 INSERT 就可能撞上重复值

实际中最容易被忽略的是：切换后没清空旧主库的 relay-log 文件，导致它重连时尝试重放早已失效的 relay 日志，报错 Could not parse relay log event entry。每次 promote 后，新主库应运行 RESET SLAVE ALL; 彻底清理复制元数据。