前言
ShardingSphere-JDBC近几次更新后,相对应的API也发生了一些变化。
从5.0.0升级5.1.0之后,数据源配置也有一点点不同,所以在这里记录一下5.1.0版本的读写分离配置。
下面的配置,还是以Spring框架体系为例,且用同一个数据库模拟多库。
依赖
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.28</version>
</dependency>
<dependency>
<groupId>com.zaxxer</groupId>
<artifactId>HikariCP</artifactId>
<version>5.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core</artifactId>
<version>5.1.0</version>
</dependency>
配置
#mysql
mysql-ds:
#主库
master:
jdbc-url: jdbc:mysql://127.0.0.1:3306/web_benchmark?characterEncoding=utf8&rewriteBatchedStatements=true&useSSL=false&serverTimezone=Asia/Shanghai
username: root
password: 123456
#读库1
replica-1:
jdbc-url: jdbc:mysql://127.0.0.1:3306/web_benchmark?characterEncoding=utf8&rewriteBatchedStatements=true&useSSL=false&serverTimezone=Asia/Shanghai
username: root
password: 123456
#读库2
replica-2:
jdbc-url: jdbc:mysql://127.0.0.1:3306/web_benchmark?characterEncoding=utf8&rewriteBatchedStatements=true&useSSL=false&serverTimezone=Asia/Shanghai
username: root
password: 123456
初始化
package cn.mrxionge.webbenchmark.config;
import com.zaxxer.hikari.HikariDataSource;
import lombok.extern.slf4j.Slf4j;
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.*;
/**
* MySQL读写分离数据源
*/
@Slf4j
@Configuration
public class MySQLDataSourceConfig {
@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.setMinimumIdle(16);
masterDs.setMaximumPoolSize(64);
masterDs.setConnectionInitSql("SET NAMES utf8mb4");
masterDs.setUsername(masterUsername);
masterDs.setPassword(masterPassword);
dsMap.put("master_ds", masterDs);
//配置读库1
HikariDataSource replicaDs1 = new HikariDataSource();
replicaDs1.setDriverClassName("com.mysql.cj.jdbc.Driver");
replicaDs1.setJdbcUrl(replica1JdbcUrl);
replicaDs1.setMinimumIdle(32);
replicaDs1.setMaximumPoolSize(128);
replicaDs1.setConnectionInitSql("SET NAMES utf8mb4");
replicaDs1.setUsername(replica1Username);
replicaDs1.setPassword(replica1Password);
dsMap.put("replica_ds_1", replicaDs1);
//配置读库2
HikariDataSource replicaDs2 = new HikariDataSource();
replicaDs2.setDriverClassName("com.mysql.cj.jdbc.Driver");
replicaDs2.setJdbcUrl(replica2JdbcUrl);
replicaDs2.setMinimumIdle(32);
replicaDs2.setMaximumPoolSize(128);
replicaDs2.setConnectionInitSql("SET NAMES utf8mb4");
replicaDs2.setUsername(replica2Username);
replicaDs2.setPassword(replica2Password);
dsMap.put("replica_ds_2", replicaDs2);
//配置读写数据源名称
Properties dsProperties = new Properties();
dsProperties.put("write-data-source-name", "master_ds");
dsProperties.put("read-data-source-names", "replica_ds_1,replica_ds_2");
//主从数据源配置
List<ReadwriteSplittingDataSourceRuleConfiguration> dsConfigurations = new ArrayList<>();
dsConfigurations.add(new ReadwriteSplittingDataSourceRuleConfiguration("ds", "static", dsProperties, "load_balancer"));
//负载均衡算法配置
Map<String, ShardingSphereAlgorithmConfiguration> loadBalanceMap = new HashMap<>();
loadBalanceMap.put("load_balancer", new ShardingSphereAlgorithmConfiguration("ROUND_ROBIN", new Properties()));
ReadwriteSplittingRuleConfiguration ruleConfiguration = new ReadwriteSplittingRuleConfiguration(dsConfigurations, loadBalanceMap);
//创建DS
log.info("创建 ShardingSphere 读写分离数据源");
return ShardingSphereDataSourceFactory.createDataSource(dsMap, List.of(ruleConfiguration), new Properties());
}
}
强制主库
try (HintManager instance = HintManager.getInstance()) {
instance.setWriteRouteOnly();
// do something
// ...
// ...
}