MySQL 主从复制全解：底层原理、复制模式差异、主从延迟排查与优化实战

[mysqld]
server-id=1
log-bin=mysql-bin
binlog_format=ROW
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_expire_logs_seconds=604800

[mysqld]
server-id=2
relay_log=relay-bin
read_only=ON
super_read_only=ON
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=OFF

CHANGE MASTER TO
MASTER_HOST='192.168.1.100',
MASTER_PORT=3306,
MASTER_USER='repl',
MASTER_PASSWORD='Repl@123456',
MASTER_AUTO_POSITION=1;
START SLAVE;

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
SET GLOBAL rpl_semi_sync_master_enabled = 1;
SET GLOBAL rpl_semi_sync_master_wait_for_slave_count = 1;
SET GLOBAL rpl_semi_sync_master_wait_point = 'AFTER_SYNC';
SET GLOBAL rpl_semi_sync_master_timeout = 1000;

INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_slave_enabled = 1;
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;

SHOW SLAVE STATUS\G

-- 主库执行，查看当前最新GTID
SELECT @@global.gtid_executed;
-- 从库执行，查看已重放的GTID
SELECT @@global.gtid_executed;

SELECT * FROM performance_schema.replication_applier_status_by_worker;

SELECT table_schema,table_name,engine FROM information_schema.tables 
WHERE table_schema NOT IN ('sys','mysql','information_schema','performance_schema') 
AND (engine='InnoDB' AND table_name NOT IN (SELECT table_name FROM information_schema.key_column_usage WHERE constraint_name='PRIMARY'));

-- 主库查看当前运行的长事务
SELECT trx_id,trx_started,trx_rows_modified,user,host,db FROM information_schema.innodb_trx 
WHERE TIMESTAMPDIFF(SECOND,trx_started,NOW()) > 10;
-- 从库查看正在重放的事务
SELECT * FROM performance_schema.events_statements_current WHERE thread_id IN (SELECT thread_id FROM performance_schema.replication_applier_status_by_worker);

-- 查看从库并行复制配置
SHOW VARIABLES LIKE 'slave_parallel%';
SHOW VARIABLES LIKE 'binlog_transaction_dependency_tracking';

[mysqld]
slave_parallel_type=LOGICAL_CLOCK
slave_parallel_workers=8
binlog_transaction_dependency_tracking=WRITESET
transaction_write_set_extraction=XXHASH64
slave_preserve_commit_order=ON

配置说明：
- `slave_parallel_workers`：并行复制worker线程数，建议设置为从库CPU核心数的50%-70%，最高不超过32
- `binlog_transaction_dependency_tracking=WRITESET`：开启基于行修改的并行复制，只要事务修改的行无冲突，即可并行重放，无需同一批提交
- `slave_preserve_commit_order=ON`：保障从库事务提交顺序与主库一致，避免主从数据不一致

-- 优化前：大事务，一次性删除百万行数据，导致严重主从延迟
DELETE FROM order_info WHERE create_time < '2024-01-01';

-- 优化后：拆分小事务，每次删除1000行，循环执行
WHILE ROW_COUNT() > 0 DO
DELETE FROM order_info WHERE create_time < '2024-01-01' LIMIT 1000;
SELECT SLEEP(0.1);
END WHILE;

[mysqld]
read_only=ON
super_read_only=ON
innodb_flush_log_at_trx_commit=2
sync_binlog=1000
innodb_flush_method=O_DIRECT
log_slave_updates=OFF
slow_query_log=OFF

配置说明：
- `super_read_only=ON`：禁止超级用户在从库执行写操作，彻底避免人为写入导致的数据不一致
- `innodb_flush_log_at_trx_commit=2`：从库无需和主库一样的双1配置，降低刷盘IO开销，提升重放性能
- `log_slave_updates=OFF`：关闭从库的binlog写入（级联复制除外），减少IO开销

CREATE TABLE user_no_pk (
nameVARCHAR(50) NOTNULL,
age INTNOTNULL,
create_time DATETIME NOTNULLDEFAULTCURRENT_TIMESTAMP
) ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;

-- 插入100万行测试数据
INSERTINTO user_no_pk (name,age) 
WITHRECURSIVE t AS (
SELECT1AS n UNIONALLSELECT n+1FROM t WHERE n < 1000000
)
SELECTCONCAT('user',n),n%100FROM t;

UPDATE user_no_pk SET age=20 WHERE name='user999999';

主库执行耗时0.01秒，但是从库重放该语句耗时超过10秒，直接导致主从延迟飙升。核心原因是ROW格式下，从库无主键，只能全表扫描100万行数据，逐行匹配字段定位要更新的行。

ALTER TABLE user_no_pk ADD COLUMN id BIGINT PRIMARY KEY AUTO_INCREMENT FIRST;

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>3.2.5</version>
        <relativePath/>
    </parent>
    <groupId>com.jam.demo</groupId>
    <artifactId>mysql-rw-split-demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>mysql-rw-split-demo</name>
    <properties>
        <java.version>17</java.version>
        <mybatis-plus.version>3.5.7</mybatis-plus.version>
        <guava.version>33.1.0-jre</guava.version>
        <fastjson2.version>2.0.49</fastjson2.version>
        <springdoc.version>2.5.0</springdoc.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>${mybatis-plus.version}</version>
        </dependency>
        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springdoc</groupId>
            <artifactId>springdoc-openapi-starter-webmvc-ui</artifactId>
            <version>${springdoc.version}</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.32</version>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>com.google.guava</groupId>
            <artifactId>guava</artifactId>
            <version>${guava.version}</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba.fastjson2</groupId>
            <artifactId>fastjson2</artifactId>
            <version>${fastjson2.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

package com.jam.demo.enums;

import lombok.Getter;

@Getter
publicenum DataSourceType {
    MASTER("master", "主库-写操作"),
    SLAVE("slave", "从库-读操作");

    privatefinal String code;
    privatefinal String desc;

    DataSourceType(String code, String desc) {
        this.code = code;
        this.desc = desc;
    }
}

package com.jam.demo.config;

import com.jam.demo.enums.DataSourceType;
import org.springframework.util.ObjectUtils;

publicclass DynamicDataSourceContextHolder {

    privatestaticfinal ThreadLocal<DataSourceType> CONTEXT_HOLDER = new ThreadLocal<>();

    private DynamicDataSourceContextHolder() {
    }

    /**
     * 设置当前数据源类型
     * @param dataSourceType 数据源类型
     */
    public static void setDataSourceType(DataSourceType dataSourceType) {
        if (ObjectUtils.isEmpty(dataSourceType)) {
            thrownew IllegalArgumentException("数据源类型不能为空");
        }
        CONTEXT_HOLDER.set(dataSourceType);
    }

    /**
     * 获取当前数据源类型
     * @return 数据源类型
     */
    public static DataSourceType getDataSourceType() {
        return ObjectUtils.isEmpty(CONTEXT_HOLDER.get()) ? DataSourceType.MASTER : CONTEXT_HOLDER.get();
    }

    /**
     * 清除数据源类型
     */
    public static void clearDataSourceType() {
        CONTEXT_HOLDER.remove();
    }
}

package com.jam.demo.config;

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

public class DynamicRoutingDataSource extends AbstractRoutingDataSource {

    @Override
    protected Object determineCurrentLookupKey() {
        return DynamicDataSourceContextHolder.getDataSourceType().getCode();
    }
}

package com.jam.demo.config;

import com.alibaba.druid.pool.DruidDataSource;
import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.core.config.GlobalConfig;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import com.jam.demo.enums.DataSourceType;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.support.TransactionTemplate;

import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

@Configuration
publicclass DataSourceConfig {

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.master")
    public DataSource masterDataSource() {
        returnnew DruidDataSource();
    }

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.slave")
    public DataSource slaveDataSource() {
        returnnew DruidDataSource();
    }

    @Bean
    @Primary
    public DataSource dynamicDataSource() {
        Map<Object, Object> targetDataSources = new HashMap<>(2);
        targetDataSources.put(DataSourceType.MASTER.getCode(), masterDataSource());
        targetDataSources.put(DataSourceType.SLAVE.getCode(), slaveDataSource());

        DynamicRoutingDataSource dynamicDataSource = new DynamicRoutingDataSource();
        dynamicDataSource.setTargetDataSources(targetDataSources);
        dynamicDataSource.setDefaultTargetDataSource(masterDataSource());
        return dynamicDataSource;
    }

    @Bean
    public MybatisSqlSessionFactoryBean sqlSessionFactory() throws Exception {
        MybatisSqlSessionFactoryBean sessionFactory = new MybatisSqlSessionFactoryBean();
        sessionFactory.setDataSource(dynamicDataSource());
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));

        MybatisConfiguration configuration = new MybatisConfiguration();
        configuration.setMapUnderscoreToCamelCase(true);
        configuration.setCacheEnabled(false);
        sessionFactory.setConfiguration(configuration);

        GlobalConfig globalConfig = new GlobalConfig();
        globalConfig.setBanner(false);
        sessionFactory.setGlobalConfig(globalConfig);
        return sessionFactory;
    }

    @Bean
    public PlatformTransactionManager transactionManager() {
        returnnew DataSourceTransactionManager(dynamicDataSource());
    }

    @Bean
    public TransactionTemplate transactionTemplate(PlatformTransactionManager transactionManager) {
        returnnew TransactionTemplate(transactionManager);
    }
}

package com.jam.demo.annotation;

import com.jam.demo.enums.DataSourceType;
import java.lang.annotation.*;

@Target({ElementType.METHOD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface DataSourceSwitch {

    DataSourceType value() default DataSourceType.MASTER;
}

package com.jam.demo.aspect;

import com.jam.demo.annotation.DataSourceSwitch;
import com.jam.demo.config.DynamicDataSourceContextHolder;
import lombok.extern.slf4j.Slf4j;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Pointcut;
import org.aspectj.lang.reflect.MethodSignature;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;

import java.lang.reflect.Method;

@Slf4j
@Aspect
@Component
@Order(1)
publicclass DataSourceSwitchAspect {

    @Pointcut("@annotation(com.jam.demo.annotation.DataSourceSwitch)")
    public void dataSourceSwitchPointCut() {
    }

    @Around("dataSourceSwitchPointCut()")
    public Object around(ProceedingJoinPoint joinPoint) throws Throwable {
        MethodSignature signature = (MethodSignature) joinPoint.getSignature();
        Method method = signature.getMethod();
        DataSourceSwitch annotation = method.getAnnotation(DataSourceSwitch.class);

        if (annotation != null) {
            DynamicDataSourceContextHolder.setDataSourceType(annotation.value());
        }

        try {
            return joinPoint.proceed();
        } finally {
            DynamicDataSourceContextHolder.clearDataSourceType();
        }
    }
}

package com.jam.demo.entity;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import io.swagger.v3.oas.annotations.media.Schema;
import lombok.Data;

import java.time.LocalDateTime;

@Data
@TableName("user_info")
@Schema(description = "用户信息实体")
publicclass UserInfo {

    @TableId(type = IdType.AUTO)
    @Schema(description = "用户ID", example = "1")
    private Long id;

    @Schema(description = "用户名", example = "testUser")
    private String username;

    @Schema(description = "年龄", example = "20")
    private Integer age;

    @Schema(description = "创建时间")
    private LocalDateTime createTime;
}

package com.jam.demo.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.jam.demo.entity.UserInfo;
import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface UserInfoMapper extends BaseMapper<UserInfo> {
}

package com.jam.demo.service;

import com.baomidou.mybatisplus.extension.service.IService;
import com.jam.demo.entity.UserInfo;

public interface UserInfoService extends IService<UserInfo> {

    boolean createUser(UserInfo userInfo);

    UserInfo getUserById(Long id);
}

package com.jam.demo.service.impl;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.jam.demo.annotation.DataSourceSwitch;
import com.jam.demo.entity.UserInfo;
import com.jam.demo.enums.DataSourceType;
import com.jam.demo.mapper.UserInfoMapper;
import com.jam.demo.service.UserInfoService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;
import org.springframework.transaction.support.TransactionTemplate;
import org.springframework.util.ObjectUtils;

/**
 * 用户信息服务实现类
 * @author ken
 */
@Slf4j
@Service
publicclass UserInfoServiceImpl extends ServiceImpl<UserInfoMapper, UserInfo> implements UserInfoService {

    privatefinal TransactionTemplate transactionTemplate;

    public UserInfoServiceImpl(TransactionTemplate transactionTemplate) {
        this.transactionTemplate = transactionTemplate;
    }

    @Override
    @DataSourceSwitch(DataSourceType.MASTER)
    public boolean createUser(UserInfo userInfo) {
        if (ObjectUtils.isEmpty(userInfo)) {
            thrownew IllegalArgumentException("用户信息不能为空");
        }
        Boolean result = transactionTemplate.execute(status -> {
            try {
                returnthis.save(userInfo);
            } catch (Exception e) {
                status.setRollbackOnly();
                log.error("创建用户失败", e);
                returnfalse;
            }
        });
        return Boolean.TRUE.equals(result);
    }

    @Override
    @DataSourceSwitch(DataSourceType.SLAVE)
    public UserInfo getUserById(Long id) {
        if (ObjectUtils.isEmpty(id) || id <= 0) {
            thrownew IllegalArgumentException("用户ID不合法");
        }
        returnthis.getById(id);
    }
}

package com.jam.demo.controller;

import com.jam.demo.entity.UserInfo;
import com.jam.demo.service.UserInfoService;
import io.swagger.v3.oas.annotations.Operation;
import io.swagger.v3.oas.annotations.Parameter;
import io.swagger.v3.oas.annotations.tags.Tag;
import lombok.extern.slf4j.Slf4j;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;

@Slf4j
@RestController
@RequestMapping("/user")
@Tag(name = "用户管理", description = "用户信息增删改查接口")
publicclass UserInfoController {

    privatefinal UserInfoService userInfoService;

    public UserInfoController(UserInfoService userInfoService) {
        this.userInfoService = userInfoService;
    }

    @PostMapping
    @Operation(summary = "创建用户", description = "新增用户信息，路由至主库执行")
    public ResponseEntity<Boolean> createUser(@RequestBody UserInfo userInfo) {
        return ResponseEntity.ok(userInfoService.createUser(userInfo));
    }

    @GetMapping("/{id}")
    @Operation(summary = "查询用户", description = "根据用户ID查询信息，路由至从库执行")
    public ResponseEntity<UserInfo> getUserById(
            @Parameter(description = "用户ID", required = true) @PathVariable Long id) {
        return ResponseEntity.ok(userInfoService.getUserById(id));
    }
}

spring:
  application:
    name:mysql-rw-split-demo
datasource:
    master:
      driver-class-name:com.mysql.cj.jdbc.Driver
      url:jdbc:mysql://192.168.1.100:3306/demo_db?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
      username:root
      password:Root@123456
      initial-size:5
      max-active:20
      min-idle:5
      max-wait:60000
    slave:
      driver-class-name:com.mysql.cj.jdbc.Driver
      url:jdbc:mysql://192.168.1.101:3306/demo_db?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
      username:root
      password:Root@123456
      initial-size:5
      max-active:20
      min-idle:5
      max-wait:60000

springdoc:
swagger-ui:
    path:/swagger-ui.html
    enabled:true
api-docs:
    enabled:true

mybatis-plus:
mapper-locations:classpath:mapper/*.xml
type-aliases-package:com.jam.demo.entity
configuration:
    map-underscore-to-camel-case:true
    log-impl:org.apache.ibatis.logging.stdout.StdOutImpl