A comprehensive solution to the problem of MySQL master-slave synchronization delay

Main libraryBinlog → Main libraryDumpThread → From the libraryIOThread → From the libraryRelay Log → From the librarySQLThread → From the library数据

-- View Delay from Library(Second)
SHOW SLAVE STATUS\G
-- focus on:
-- Seconds_Behind_Master 
-- Slave_SQL_Running_State

-- Check thread status
SHOW PROCESSLIST;

-- CheckBinlogLocation
SHOW MASTER STATUS;
SHOW SLAVE STATUS\G

# Main library installation heartbeatpt-heartbeat --user=monitor --password=xxx --host=master \
             --create-table --database=test --interval=1 --update

# Detect delay from the librarypt-heartbeat --user=monitor --password=xxx --host=slave \
             --database=test --monitor --master-server-id=1

#Configuration[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 implements latency-aware routingpublic class DelayAwareRoutingDataSource extends AbstractRoutingDataSource {

    private long maxAcceptableDelay = 1000; // 1 second    
    @Override
    protected Object determineCurrentLookupKey() {
        if(isWriteOperation()) {
            return "master";
        }
        
        // Get delay from library        long delay = getSlaveDelay();
        
        return delay <= maxAcceptableDelay ? "slave" : "master";
    }
    
    private long getSlaveDelay() {
        // Get real-time delay from monitoring system        return ();
    }
}

# Control Binlog generationsync_binlog = 1              # Each transaction is submitted to swipe the diskbinlog_group_commit_sync_delay = 0 
binlog_group_commit_sync_no_delay_count = 0

# Big transaction processingbinlog_cache_size = 4M
max_binlog_size = 512M
binlog_rows_query_log_events = ON  # Record the complete SQL

# Parallel replication configuration (MySQL 5.7+)slave_parallel_workers = 8      # 50-75% of CPU coresslave_parallel_type = LOGICAL_CLOCK
slave_preserve_commit_order = 1 # Ensure transaction order
# Network and IO optimizationslave_net_timeout = 60          # Network timeout (seconds)slave_compressed_protocol = 1   # Enable compressionslave_pending_jobs_size_max = 2G # Memory queue size
# Hardware relatedinnodb_flush_log_at_trx_commit = 2  # From the library can be relaxedsync_relay_log = 10000           # Regularly brush the disk

-- Main library installation plug-in
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

-- Configuration parameters
SET GLOBAL rpl_semi_sync_master_enabled = 1;
SET GLOBAL rpl_semi_sync_master_timeout = 10000; # 10 seconds timeout
-- Configuration from library
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_slave_enabled = 1;

#Configuration[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. Update the cache immediately after updating the main library        updateCache();
        
        // 2. Start the delay task check slave library        (() -> {
            if(checkSlaveSync()) {
                refreshCacheFromSlave();
            }
        }, 1, );
    }
    
    private boolean checkSlaveSync() {
        // Check whether the master and slave positions are consistent        return ();
    }
}

# Check the status of the thread from the librarySHOW PROCESSLIST;

# View the currently executed SQLSELECT * FROM performance_schema.events_statements_current 
WHERE thread_id = (SELECT THREAD_ID FROM performance_schema.threads 
                  WHERE PROCESSLIST_ID = <SQLThreadID>);

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

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

Does delay affect business？
├─ yes → yesno有紧急修复方案？
│   ├─ yes → Implement repair(If you skip transaction)
│   └─ no → Trigger failover
└─ no → Monitoring and Observation + Record events

# Use sysbench to generate loadsysbench --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

# Monitor delay changeswatch -n 1 "mysql -e 'SHOW SLAVE STATUS\G' | grep Seconds_Behind"

Master-slave copy → Semi-synchronous copy → MGR → Distributed database(likeTiDB)