Spring Boot：配置 MySQL 集群

1. 自定义 DataSource

1.1. 配置 application.yml

application.yml

spring:
  datasource:
    master:
      jdbc-url: jdbc:mysql://master-db-host:3306/mydb?serverTimeZone=Asia/Shanghai&characterEncoding=utf8&allowMultiQueries=true
      username: root
      password: root
      driver-class-name: com.mysql.cj.jdbc.Driver
    slaves:
      - jdbc-url: jdbc:mysql://slave1-db-host:3306/mydb?serverTimeZone=Asia/Shanghai&characterEncoding=utf8&allowMultiQueries=true
        username: root
        password: root
        driver-class-name: com.mysql.cj.jdbc.Driver
      - jdbc-url: jdbc:mysql://slave2-db-host:3306/mydb?serverTimeZone=Asia/Shanghai&characterEncoding=utf8&allowMultiQueries=true
        username: root
        password: root
        driver-class-name: com.mysql.cj.jdbc.Driver

1.2. 配置数据源

在 DataSourceConfig 中注册主从数据源，并指定自定义的 MyRoutingDataSource 作为主数据源：

DataSourceConfig.java

@Configuration
@EnableConfigurationProperties(DataSourceProperties.class)
@RequiredArgsConstructor
public class DataSourceConfig {
    private final DataSourceProperties dataSourceProperties;

    @Bean
    public DataSource masterDataSource() {
        DataSourceProperties.DataSourceProperty master = dataSourceProperties.getMaster();
        return DataSourceBuilder.create()
                .url(master.getJdbcUrl())
                .username(master.getUsername())
                .password(master.getPassword())
                .driverClassName(master.getDriverClassName())
                .build();
    }

    @Bean
    public List<DataSource> slaveDataSources() {
        List<DataSource> dataSources = new ArrayList<>();
        for (DataSourceProperties.DataSourceProperty slave : dataSourceProperties.getSlaves()) {
            DataSource dataSource = DataSourceBuilder.create()
                    .url(slave.getJdbcUrl())
                    .username(slave.getUsername())
                    .password(slave.getPassword())
                    .driverClassName(slave.getDriverClassName())
                    .build();
            dataSources.add(dataSource);
        }
        return dataSources;
    }

    @Bean
    @Primary
    public DataSource dynamicDataSource(@Qualifier("masterDataSource") DataSource masterDataSource,
                                        @Qualifier("slaveDataSources") List<DataSource> slaveDataSources) {
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put("master", masterDataSource);
        int index = 1;
        for (DataSource slaveDataSource : slaveDataSources) {
            targetDataSources.put("slave" + index++, slaveDataSource);
        }

        MyRoutingDataSource routingDataSource = new MyRoutingDataSource();
        routingDataSource.setTargetDataSources(targetDataSources);
        routingDataSource.setDefaultTargetDataSource(masterDataSource);
        routingDataSource.setSlaveKeys(targetDataSources.keySet().stream().filter(k -> k.toString().startsWith("slave")).collect(Collectors.toList()));
        return routingDataSource;
    }
}

DataSourceProperties.java

@ConfigurationProperties(prefix = "spring.datasource")
@Data
@NoArgsConstructor
public class DataSourceProperties {
    private DataSourceProperty master;
    private List<DataSourceProperty> slaves;

    @Data
    @NoArgsConstructor
    public static class DataSourceProperty {
        private String jdbcUrl;
        private String username;
        private String password;
        private String driverClassName;
    }
}

1.3. 实现动态数据源切换

MyRoutingDataSource 继承 AbstractRoutingDataSource 实现动态数据源切换，并增加负载均衡逻辑。

MyRoutingDataSource.java

public class MyRoutingDataSource extends AbstractRoutingDataSource {
    private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();

    private final AtomicInteger counter = new AtomicInteger(0);

    @Setter
    private List<Object> slaveKeys;

    public static void setDataSource(String key) {
        contextHolder.set(key);
    }

    public static void clearDataSource() {
        contextHolder.remove();
    }

    @Override
    protected Object determineCurrentLookupKey() {
        return !TransactionSynchronizationManager.isActualTransactionActive() // 如果存在活动事务，则返回主库，确保事务内的一致性
                && "slave".equals(contextHolder.get()) && !CollectionUtils.isEmpty(slaveKeys)
                ? slaveKeys.get(counter.getAndUpdate(n -> n == Integer.MAX_VALUE ? 0 : n + 1) % slaveKeys.size())
                : "master";
    }
}

1.4. 使用 AOP 实现读写分离

创建 DataSourceAspect.java：

DataSourceAspect.java

@Aspect
@Component
public class DataSourceAspect {
    @Around("@annotation(com.example.annotation.Master)")
    public Object useMaster(ProceedingJoinPoint joinPoint) throws Throwable {
        try {
            MyRoutingDataSource.setDataSource("master");
            return joinPoint.proceed();
        } finally {
            MyRoutingDataSource.clearDataSource();
        }
    }

    @Around("@annotation(com.example.annotation.Slave)")
    public Object useSlave(ProceedingJoinPoint joinPoint) throws Throwable {
        try {
            MyRoutingDataSource.setDataSource("slave");
            return joinPoint.proceed();
        } finally {
            MyRoutingDataSource.clearDataSource();
        }
    }
}

Master.java

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

Slave.java

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

1.5. 在 Service 层使用

UserService.java

@Service
public class UserService {
    @Autowired
    private UserMapper userMapper;

    @Master
    public void saveUser(User user) {
        userMapper.insertUser(user);
    }

    @Slave
    public User getUserById(Long id) {
        return userMapper.selectUserById(id);
    }
}

1. 写入操作走主库：

   userService.saveUser(new User("Alice", 30));

2. 读取操作走从库，并且多个从库轮询分配：

   userService.getUserById(1L); // 第一次走 slave1
   userService.getUserById(2L); // 第二次走 slave2
   userService.getUserById(3L); // 第三次又走 slave1

2. 使用 ShardingSphere-JDBC

Note：基于 Spring Boot 2.7.6 + ShardingSphere 5.2.1 版本。

2.1. 引入依赖

pom.xml

<!-- SnakeYAML -->
<dependency>
    <groupId>org.yaml</groupId>
    <artifactId>snakeyaml</artifactId>
    <version>1.33</version>
</dependency>

<!-- ShardingSphere Starter -->
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
    <version>5.2.1</version>
</dependency>

Warning：这里显式指定了 SnakeYAML 的版本，原因是 ShardingSphere 5.2.1 与 Spring Boot 2.7.6 默认指定的 1.30 版本不兼容。

Note：ShardingSphere 的 Starter 组件在 5.2.1 版本后没有继续更新。如需引入更高版本的 ShardingSphere 请改用 shardingsphere-jdbc-core 依赖。或者搭建 ShardingSphere-Proxy 集群。

2.2. 配置 application.yml

application.yml

spring:
  shardingsphere:
    datasource:
      names: master,slave1,slave2
      master:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://master-db-host:3306/mydb?serverTimeZone=Asia/Shanghai&characterEncoding=utf8&allowMultiQueries=true
        username: root
        password: root
      slave1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://slave1-db-host:3306/mydb?serverTimeZone=Asia/Shanghai&characterEncoding=utf8&allowMultiQueries=true
        username: root
        password: root
      slave2:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://slave2-db-host:3306/mydb?serverTimeZone=Asia/Shanghai&characterEncoding=utf8&allowMultiQueries=true
        username: root
        password: root
    rules:
      readwrite-splitting:
        data-sources:
          rw-ds:
            static-strategy:
              write-data-source-name: master
              read-data-source-names: slave1,slave2
            load-balancer-name: round_robin
        load-balancers:
          round_robin:
            type: ROUND_ROBIN
    props:
      sql-show: false
      check-table-metadata-enabled: false

2.3. 使用 Hint 强制主库查询

HintManager.getInstance().setWriteRouteOnly();