深入解析MySQL中继日志：核心机制与配置

[mysqld]
relay_log = /var/lib/mysql/relay-bin
relay_log_index = /var/lib/mysql/relay-bin.index
relay_log_info_file = relay-log.info
relay_log_purge = ON
max_relay_log_size = 1G
sync_relay_log = 1
relay_log_recovery = ON

-- 临时禁用自动清理
SET GLOBAL relay_log_purge = 0;

-- 查看当前中继日志状态
SHOW SLAVE STATUS\G

-- 清理指定日志
PURGE RELAY LOGS BEFORE '2023-08-20 00:00:00';

-- 设置中继日志文件最大为2GB
SET GLOBAL max_relay_log_size = 2G;

-- 开启中继日志崩溃恢复
SET GLOBAL relay_log_recovery = ON;

-- 查看当前配置
SHOW VARIABLES LIKE '%relay_log%';

# 查看日志内容
mysqlbinlog /var/lib/mysql/relay-bin.000001

# 实时监控日志事件
SHOW RELAYLOG EVENTS IN 'relay-bin.000001' FROM 123 LIMIT 10;

-- 查看复制状态
SHOW SLAVE STATUS\G

-- 查看当前中继日志
SHOW RELAYLOG EVENTS;

-- 监控延迟
SELECT 
  UNIX_TIMESTAMP() - UNIX_TIMESTAMP(Last_SQL_Exec_Time) AS delay_seconds 
FROM performance_schema.replication_applier_status_by_worker;

-- 手动清理所有已应用日志
PURGE RELAY LOGS TO 'relay-bin.000015';

-- 设置自动保留策略
SET GLOBAL relay_log_purge = 1;
SET GLOBAL expire_logs_days = 7;

# 调整IO参数（适合SSD）
sync_relay_log = 1000
relay_log_space_limit = 10G

# 提升并行复制
slave_parallel_workers = 8
slave_parallel_type = LOGICAL_CLOCK

STOP SLAVE;
CHANGE MASTER TO RELAY_LOG_FILE='relay-bin.000001', RELAY_LOG_POS=123;
START SLAVE;

# 临时切换日志路径
SET GLOBAL relay_log = '/new_path/relay-bin';

-- 为每个通道维护独立的中继日志
CHANGE MASTER TO MASTER_HOST='master2' FOR CHANNEL 'channel2';

-- 查看 relay log 配置
SHOW VARIABLES LIKE '%relay_log%';

-- 查看 binlog 配置
SHOW VARIABLES LIKE '%log_bin%';

-- 关键监控项
SELECT 
  Relay_Log_Space AS log_space,
  Slave_SQL_Running_State AS sql_state,
  Seconds_Behind_Master AS replication_delay
FROM performance_schema.replication_connection_status;