ShardingSphere-JDBC5.0正式版读写分离配置示例

前言

2020年10月，第一次使用shardingsphere-jdbc5版本实现读写分离。只不过当时只是5.0.0-alpha版本，还不算是满血版。
2021年11月，发布了5.0.0正式版，带来相当多的新特性，比如我们很需要的“支持解析MySQL UNION/UNION ALL语句”。
更多特性参考release note：https://github.com/apache/shardingsphere/releases
目前生产环境已升级5.0.0，且稳定运行。
升级过程中发现部分API存在变动，所以重新写一篇5.0.0的配置示例。

依赖

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.27</version>
        </dependency>
        <dependency>
            <groupId>com.zaxxer</groupId>
            <artifactId>HikariCP</artifactId>
            <version>5.0.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc-core</artifactId>
            <version>5.0.0</version>
        </dependency>

配置

#MySQL数据源配置
mysql-ds:
  #主库
  master:
    jdbc-url: jdbc:mysql://localhost:3306/demo_db?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai
    username: web_app
    password: 123456
  #从库1
  replica-1:
    jdbc-url: jdbc:mysql://localhost:3306/demo_db?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai
    username: web_app
    password: 123456
  #从库2
  replica-2:
    jdbc-url: jdbc:mysql://localhost:3306/demo_db?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai
    username: web_app
    password: 123456

初始化

package cn.mrxionge.readwritesplittingdemo;

import com.zaxxer.hikari.HikariDataSource;
import org.apache.shardingsphere.driver.api.ShardingSphereDataSourceFactory;
import org.apache.shardingsphere.infra.config.algorithm.ShardingSphereAlgorithmConfiguration;
import org.apache.shardingsphere.readwritesplitting.api.ReadwriteSplittingRuleConfiguration;
import org.apache.shardingsphere.readwritesplitting.api.rule.ReadwriteSplittingDataSourceRuleConfiguration;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.*;

@Configuration
public class DSConfig {

    @Value("${mysql-ds.master.jdbc-url}")
    private String masterJdbcUrl;

    @Value("${mysql-ds.master.username}")
    private String masterUsername;

    @Value("${mysql-ds.master.password}")
    private String masterPassword;

    @Value("${mysql-ds.replica-1.jdbc-url}")
    private String replica1JdbcUrl;

    @Value("${mysql-ds.replica-1.username}")
    private String replica1Username;

    @Value("${mysql-ds.replica-1.password}")
    private String replica1Password;

    @Value("${mysql-ds.replica-2.jdbc-url}")
    private String replica2JdbcUrl;

    @Value("${mysql-ds.replica-2.username}")
    private String replica2Username;

    @Value("${mysql-ds.replica-2.password}")
    private String replica2Password;

    @Bean
    public DataSource dataSource() throws SQLException {
        //数据源Map
        Map<String, DataSource> dsMap = new HashMap<>();
        //配置主库
        HikariDataSource masterDs = new HikariDataSource();
        masterDs.setDriverClassName("com.mysql.cj.jdbc.Driver");
        masterDs.setJdbcUrl(masterJdbcUrl);
        masterDs.setUsername(masterUsername);
        masterDs.setPassword(masterPassword);
        masterDs.setMinimumIdle(8);
        masterDs.setMaximumPoolSize(16);
        masterDs.setConnectionInitSql("SET NAMES utf8mb4");
        dsMap.put("master_ds", masterDs);
        //配置从库1
        HikariDataSource replicaDs1 = new HikariDataSource();
        replicaDs1.setDriverClassName("com.mysql.cj.jdbc.Driver");
        replicaDs1.setJdbcUrl(replica1JdbcUrl);
        replicaDs1.setUsername(replica1Username);
        replicaDs1.setPassword(replica1Password);
        replicaDs1.setMinimumIdle(8);
        replicaDs1.setMaximumPoolSize(16);
        replicaDs1.setConnectionInitSql("SET NAMES utf8mb4");
        dsMap.put("replica_ds_1", replicaDs1);
        //配置从库2
        HikariDataSource replicaDs2 = new HikariDataSource();
        replicaDs2.setDriverClassName("com.mysql.cj.jdbc.Driver");
        replicaDs2.setJdbcUrl(replica2JdbcUrl);
        replicaDs2.setUsername(replica2Username);
        replicaDs2.setPassword(replica2Password);
        replicaDs2.setMinimumIdle(8);
        replicaDs2.setMaximumPoolSize(16);
        replicaDs2.setConnectionInitSql("SET NAMES utf8mb4");
        dsMap.put("replica_ds_2", replicaDs2);
        //主从数据源配置
        List<ReadwriteSplittingDataSourceRuleConfiguration> dsConfigurations = new ArrayList<>();
        dsConfigurations.add(new ReadwriteSplittingDataSourceRuleConfiguration("ds", null, "master_ds", List.of("replica_ds_1", "replica_ds_2"), "load_balancer"));
        //负载均衡算法配置
        Map<String, ShardingSphereAlgorithmConfiguration> loadBalancers = new HashMap<>();
        loadBalancers.put("load_balancer", new ShardingSphereAlgorithmConfiguration("ROUND_ROBIN", new Properties()));
        ReadwriteSplittingRuleConfiguration ruleConfiguration = new ReadwriteSplittingRuleConfiguration(dsConfigurations, loadBalancers);
        //创建DS
        return ShardingSphereDataSourceFactory.createDataSource(dsMap, List.of(ruleConfiguration), new Properties());
    }
}

强制主库

    public void runTest() {
        try (HintManager instance = HintManager.getInstance()) {
            instance.setWriteRouteOnly();
            // do something
            // ...
            // ...
        }
    }