14.10.5 复制与故障切换异常处理

复制与故障切换异常处理#

原理草图（读写分离与故障切换）#

文章图片

典型故障现象#

  • 主从复制延迟或中断，读流量误打到延迟从库
  • 自动切换未生效或频繁切换（抖动）
  • 读写分离路由指向错误主机
  • MySQL组成员状态异常：ONLINE/SHUNNED/OFFLINE 频繁变化

排查与处理步骤（含命令示例）#

  1. 确认后端主从健康（MySQL侧）
    sql -- 在从库执行 SHOW SLAVE STATUS\G -- 重点关注字段： -- Seconds_Behind_Master, Last_IO_Error, Last_SQL_Error

  2. 核对 ProxySQL Hostgroup 配置
    sql -- 进入 ProxySQL 管理端口 6032 SELECT hostgroup_id, hostname, port, status, weight, comment FROM mysql_servers ORDER BY hostgroup_id, hostname;

  3. 检查复制组与监控规则
    sql SELECT * FROM mysql_replication_hostgroups; SELECT * FROM global_variables WHERE variable_name IN ( 'mysql-monitor_replication_lag', 'mysql-monitor_read_only_interval', 'mysql-monitor_connect_interval' );

  4. 检查 monitor 用户权限
    sql -- 在 MySQL 主库执行 SHOW GRANTS FOR 'monitor'@'%'; -- 需要包含 REPLICATION CLIENT, PROCESS 等权限

诊断命令与预期效果#

-- 1) 查看后端状态（应看到主库 ONLINE，从库 ONLINE 或 SHUNNED）
SELECT hostgroup_id, hostname, status, weight, comment
FROM mysql_servers;

-- 2) 查看最近监控日志（确认复制延迟与只读探测）
SELECT hostname, port, time_start, time_end, success_time_us, error
FROM mysql_server_replication_log
ORDER BY time_start DESC
LIMIT 20;

-- 3) 查看当前路由与读写分离匹配情况
SELECT rule_id, active, match_pattern, destination_hostgroup
FROM mysql_query_rules
ORDER BY rule_id;

典型故障与处理示例#

1）复制延迟导致从库被剔除#

现象：从库状态被 SHUNNED，读流量不足
处理：调整复制延迟阈值并重新加载运行时

-- 调整阈值（单位：秒）
UPDATE global_variables
SET variable_value='30'
WHERE variable_name='mysql-monitor_replication_lag';

LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

2）主库误被标记为只读#

现象：主库被路由为从库或不接收写
处理：确认 read_only 并修正状态

-- MySQL 主库上确认 read_only
SHOW VARIABLES LIKE 'read_only';

-- 若误开启，关闭 read_only（谨慎操作）
SET GLOBAL read_only=OFF;
SET GLOBAL super_read_only=OFF;

3）自动切换未生效#

现象：主库宕机，ProxySQL未将写路由切换
处理：核对复制组配置与监控用户

-- 查看复制组配置
SELECT * FROM mysql_replication_hostgroups;

-- 修复后重新加载
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

常见根因与处理建议#

  • 复制延迟过大
  • 提升从库性能或减轻写压力
  • 设置合理 mysql-monitor_replication_lag
  • 主库误被标记为只读
  • 检查 read_only 状态与自动切换脚本
  • 频繁切换
  • 放宽监控阈值与检测频率
  • 增加 failover 冷却时间
  • 监控用户权限不足
  • 确保 monitor 用户具备 REPLICATION CLIENT、PROCESS 等权限

应急处理（可执行示例）#

-- 1) 临时强制主库上线（谨慎）
UPDATE mysql_servers
SET status='ONLINE'
WHERE hostgroup_id=10 AND hostname='10.0.0.11';

LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

-- 2) 临时暂停监控（防止频繁切换）
UPDATE global_variables
SET variable_value='20000'
WHERE variable_name='mysql-monitor_connect_interval';

LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

安装与环境验证（最小可用检查）#

# 进入 ProxySQL 管理端口
mysql -u admin -padmin -h 127.0.0.1 -P 6032

# 验证 ProxySQL 版本与运行状态
SELECT @@version;
SELECT * FROM stats_mysql_global;

练习与自测#

  1. 模拟复制延迟：在从库上执行长事务，观察 mysql_server_replication_log 中延迟变化。
  2. 模拟主库只读：短暂开启 read_only=ON，观察 ProxySQL 路由变化与恢复策略。
  3. 验证切换阈值：修改 mysql-monitor_replication_lag 并比较从库被剔除时间。