主庫(kù)Binlog → 主庫(kù)Dump線程 → 從庫(kù)IO線程 → 從庫(kù)Relay Log → 從庫(kù)SQL線程 → 從庫(kù)數(shù)據(jù)

-- 查看從庫(kù)延遲(秒)
SHOW SLAVE STATUS\G
-- 關(guān)注:
-- Seconds_Behind_Master 
-- Slave_SQL_Running_State

-- 查看線程狀態(tài)
SHOW PROCESSLIST;

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

# 主庫(kù)安裝心跳
pt-heartbeat --user=monitor --password=xxx --host=master \
             --create-table --database=test --interval=1 --update

# 從庫(kù)檢測(cè)延遲
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 實(shí)現(xiàn)延遲感知路由
public class DelayAwareRoutingDataSource extends AbstractRoutingDataSource {

    private long maxAcceptableDelay = 1000; // 1秒
    
    @Override
    protected Object determineCurrentLookupKey() {
        if(isWriteOperation()) {
            return "master";
        }
        
        // 獲取從庫(kù)延遲
        long delay = getSlaveDelay();
        
        return delay <= maxAcceptableDelay ? "slave" : "master";
    }
    
    private long getSlaveDelay() {
        // 從監(jiān)控系統(tǒng)獲取實(shí)時(shí)延遲
        return MonitoringService.getSlaveDelay();
    }
}

# 控制Binlog生成
sync_binlog = 1              # 每次事務(wù)提交刷盤
binlog_group_commit_sync_delay = 0 
binlog_group_commit_sync_no_delay_count = 0

# 大事務(wù)處理
binlog_cache_size = 4M
max_binlog_size = 512M
binlog_rows_query_log_events = ON  # 記錄完整SQL

# 并行復(fù)制配置(MySQL 5.7+)
slave_parallel_workers = 8      # CPU核心數(shù)的50-75%
slave_parallel_type = LOGICAL_CLOCK
slave_preserve_commit_order = 1 # 保證事務(wù)順序

# 網(wǎng)絡(luò)與IO優(yōu)化
slave_net_timeout = 60          # 網(wǎng)絡(luò)超時(shí)(秒)
slave_compressed_protocol = 1   # 啟用壓縮
slave_pending_jobs_size_max = 2G # 內(nèi)存隊(duì)列大小

# 硬件相關(guān)
innodb_flush_log_at_trx_commit = 2  # 從庫(kù)可放寬
sync_relay_log = 10000           # 定期刷盤

-- 主庫(kù)安裝插件
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

-- 配置參數(shù)
SET GLOBAL rpl_semi_sync_master_enabled = 1;
SET GLOBAL rpl_semi_sync_master_timeout = 10000; # 10秒超時(shí)

-- 從庫(kù)配置
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"
loose-group_replication_bootstrap_group = off

public class CacheAspect {
    
    @AfterReturning("@annotation(cacheUpdate)")
    public void afterUpdate(JoinPoint jp) {
        // 1. 更新主庫(kù)后立即更新緩存
        updateCache();
        
        // 2. 啟動(dòng)延遲任務(wù)檢查從庫(kù)
        scheduledExecutor.schedule(() -> {
            if(checkSlaveSync()) {
                refreshCacheFromSlave();
            }
        }, 1, TimeUnit.SECONDS);
    }
    
    private boolean checkSlaveSync() {
        // 檢查主從位置是否一致
        return replicationService.isSynced();
    }
}

# 查看從庫(kù)線程狀態(tài)
SHOW PROCESSLIST;

# 查看當(dāng)前執(zhí)行的SQL
SELECT * FROM performance_schema.events_statements_current 
WHERE thread_id = (SELECT THREAD_ID FROM performance_schema.threads 
                  WHERE PROCESSLIST_ID = <SQL線程ID>);

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

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

出現(xiàn)延遲是否影響業(yè)務(wù)？
├─ 是 → 是否有緊急修復(fù)方案？
│   ├─ 是 → 實(shí)施修復(fù)(如跳過(guò)事務(wù))
│   └─ 否 → 觸發(fā)故障轉(zhuǎn)移
└─ 否 → 監(jiān)控觀察 + 記錄事件

# 使用sysbench生成負(fù)載
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

# 監(jiān)控延遲變化
watch -n 1 "mysql -e 'SHOW SLAVE STATUS\G' | grep Seconds_Behind"

主從復(fù)制 → 半同步復(fù)制 → MGR → 分布式數(shù)據(jù)庫(kù)(如TiDB)