MySQL主从同步延迟问题的全面解决方案

主库Binlog → 主库Dump线程 → 从库IO线程 → 从库Relay Log → 从库SQL线程 → 从库数据

-- 查看从库延迟(秒)
SHOW SLAVE STATUS\G
-- 关注:
-- Seconds_Behind_Master 
-- Slave_SQL_Running_State

-- 查看线程状态
SHOW PROCESSLIST;

-- 查看Binlog位置
SHOW MASTER STATUS;
SHOW SLAVE STATUS\G

# 主库安装心跳
pt-heartbeat --user=monitor --password=xxx --host=master \
             --create-table --database=test --interval=1 --update

# 从库检测延迟
pt-heartbeat --user=monitor --password=xxx --host=slave \
             --database=test --monitor --master-server-id=1

# my.cnf配置
[mysqld]
server-id = 2
log_bin = mysql-bin
binlog_format = ROW
binlog_row_image = FULL
gtid_mode = ON
enforce_gtid_consistency = ON
log_slave_updates = ON

// Spring Boot + HikariCP 实现延迟感知路由
public class DelayAwareRoutingDataSource extends AbstractRoutingDataSource {

    private long maxAcceptableDelay = 1000; // 1秒
    
    @Override
    protected Object determineCurrentLookupKey() {
        if(isWriteOperation()) {
            return "master";
        }
        
        // 获取从库延迟
        long delay = getSlaveDelay();
        
        return delay <= maxAcceptableDelay ? "slave" : "master";
    }
    
    private long getSlaveDelay() {
        // 从监控系统获取实时延迟
        return MonitoringService.getSlaveDelay();
    }
}

# 控制Binlog生成
sync_binlog = 1              # 每次事务提交刷盘
binlog_group_commit_sync_delay = 0 
binlog_group_commit_sync_no_delay_count = 0

# 大事务处理
binlog_cache_size = 4M
max_binlog_size = 512M
binlog_rows_query_log_events = ON  # 记录完整SQL

# 并行复制配置(MySQL 5.7+)
slave_parallel_workers = 8      # CPU核心数的50-75%
slave_parallel_type = LOGICAL_CLOCK
slave_preserve_commit_order = 1 # 保证事务顺序

# 网络与IO优化
slave_net_timeout = 60          # 网络超时(秒)
slave_compressed_protocol = 1   # 启用压缩
slave_pending_jobs_size_max = 2G # 内存队列大小

# 硬件相关
innodb_flush_log_at_trx_commit = 2  # 从库可放宽
sync_relay_log = 10000           # 定期刷盘

-- 主库安装插件
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

-- 配置参数
SET GLOBAL rpl_semi_sync_master_enabled = 1;
SET GLOBAL rpl_semi_sync_master_timeout = 10000; # 10秒超时

-- 从库配置
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_slave_enabled = 1;

# my.cnf配置
[mysqld]
plugin_load_add = 'group_replication.so'
transaction_write_set_extraction = XXHASH64
loose-group_replication_group_name = "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot = off
loose-group_replication_local_address = "node1:33061"
loose-group_replication_group_seeds = "node1:33061,node2:33061,node3:33061"
loojavascriptse-group_replication_bootstrap_group = off

public class CacheASPect {
    
    @AfterReturning("@annotation(cacheUpdate)")
    public http://www.chinasem.cnvoid afterUpdate(JoinPoint jp) {
        // 1. 更新主库后立即更新缓存
        updateCache();
        
        // 2. 启动延迟任务检查从库
        scheduledExecutor.schedule(() -> {
            if(checkSlaveSync()) {
                refreshCacheFromSlave();
            }
        }, 1, TimeUnit.SECONDS);
    }
    
    private boolean checkSlaveSync() {
        // 检查主从位置是否一致
        return replicationService.isSynced();
    }
}

# 查看从库线程状态
SHOW PROCESSLIST;

# 查看当前执行的SQL
SELECT * FROM performance_schema.events_statements_current 
WHERE thread_id = (SELECT THREAD_ID FROM performance_schema.threads 
                  WHERE PROCESSLIST_ID = <China编程SQL线程ID>);

STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

mysqldump --master-data=2 --single-transaction -uroot -p dbname > dbname.sql

出现延迟是否影响业务？
├─ 是 → 是否有紧急修复方案？
│   ├─ 是 → 实施修复(如跳过事务)
│   └─ 否 → 触发故障转移
└─ 否 → 监控观察 + 记录事件

# 使用sysbench生成负载
sysbench --db-driver=mysql --mysql-host=master \
         --mysql-user=test --mysql-password=test \
         /usr/share/sysbench/oltp_write_only.Lua \
         --tables=10 --table-size=1000000 prepare

# 监控延迟变化
watch -n 1 "mysql -e 'SHOW SLAVE STATUS\G' | grandroidep Seconds_Behind"

主从复制 → 半同步复制 → MGR → 分布式数据库(如TiDB)