详解：数据库中间件sharding-jdbc之读写分离

本节开启sharding-jdbc的从读写分离入手，讲述sharding-jdbc如何完成读写分离。

（一）配置mysql数据库读写分离

配置mysql端主从的数据自动同步，mycat 不负责任何数据同步问题。

启动主从配置

mycat-mysql01为master，02为slave
mycat-mysql01 配置my.cnf
log-bin=mysql-bin //[必须]启用二进制日志
server-id=101 //[必须]服务器唯一ID，默认是1，一般取IP最后一段

vi /etc/my.cnfservice mysqld restart

mycat-mysql02 配置my.cnf

vi /etc/my.cnfservice mysqld restart;

在主服务器101上

一般不使用root，专题的上一节我设置了，liming这个用户使用这个用户来。

mysqld -uroot -pshow master status;

记住：mysql-bin.000023 845

在从服务器102上连接主服务

mysql -uroot -p
stop slave; 
change master to master_host='192.168.3.101',master_user='liming',master_password='1qaz@WSX',master_log_file='mysql-bin.000023',master_log_pos=845;  
start slave; 
show slave status;

查看mysql主从效果

#在主库添加sql
mysql> create database hi2_db;
mysql> use hi2_db;
mysql> create table hi_tb(id int(3),name char(10));
mysql> insert into hi_tb values(001,'bobu');
mysql> show databases;

已经完成了 mysql的读写同步。

（二） Sharding-JDBC读写分离

不使用Spring

引入Maven依赖

<dependency> 
<groupId>org.apache.shardingsphere</groupId> 
<artifactId>sharding-jdbc-core</artifactId> 
<version>${sharding-sphere.version}</version> 
</dependency>

基于Java编码的规则配置

// 配置真实数据源 
Map<String, DataSource> dataSourceMap = new HashMap<>(); 
// 配置主库 
BasicDataSource masterDataSource = new BasicDataSource(); 
masterDataSource.setDriverClassName("com.mysql.jdbc.Driver"); 
masterDataSource.setUrl("jdbc:mysql://localhost:3306/ds_master"); 
masterDataSource.setUsername("root"); masterDataSource.setPassword(""); 
dataSourceMap.put("ds_master", masterDataSource); 
// 配置第一个从库 
BasicDataSource slaveDataSource1 = new BasicDataSource(); 
slaveDataSource1.setDriverClassName("com.mysql.jdbc.Driver"); 
slaveDataSource1.setUrl("jdbc:mysql://localhost:3306/ds_slave0"); 
slaveDataSource1.setUsername("root"); slaveDataSource1.setPassword(""); 
dataSourceMap.put("ds_slave0", slaveDataSource1); 
// 配置第二个从库 
BasicDataSource slaveDataSource2 = new BasicDataSource(); 
slaveDataSource2.setDriverClassName("com.mysql.jdbc.Driver"); 
slaveDataSource2.setUrl("jdbc:mysql://localhost:3306/ds_slave1");
slaveDataSource2.setUsername("root"); slaveDataSource2.setPassword(""); 
dataSourceMap.put("ds_slave1", slaveDataSource2); 
// 配置读写分离规则 
MasterSlaveRuleConfiguration masterSlaveRuleConfig = new MasterSlaveRuleConfiguration("ds_master_slave", "ds_master", Arrays.asList("ds_slave0", "ds_slave1")); 
// 获取数据源对象 
DataSource dataSource = MasterSlaveDataSourceFactory.createDataSource(createDataSourceMap(), masterSlaveRuleConfig, new HashMap<String, Object>(), new Properties());

基于Yaml的规则配置，与以上配置等价

dataSources: 
 ds_master: !!org.apache.commons.dbcp.BasicDataSource 
  driverClassName: com.mysql.jdbc.Driver 
  url: jdbc:mysql://localhost:3306/ds_master 
  username: root 
  password: 
 ds_slave0: !!org.apache.commons.dbcp.BasicDataSource
  driverClassName: com.mysql.jdbc.Driver 
  url: jdbc:mysql://localhost:3306/ds_slave0 
  username: root 
  password: 
 ds_slave1: !!org.apache.commons.dbcp.BasicDataSource 
  driverClassName: com.mysql.jdbc.Driver
  url: jdbc:mysql://localhost:3306/ds_slave1 
  username: root 
  password: 
masterSlaveRule: 
 name: ds_ms 
 masterDataSourceName: ds_master
 slaveDataSourceNames: [ds_slave0, ds_slave1]
props:
 sql.show: true
configMap: 
 key1: value1
# java配置
DataSource dataSource = MasterSlaveDataSourceFactory.createDataSource(yamlFile);

使用原生JDBC

通过MasterSlaveDataSourceFactory工厂和规则配置对象获取MasterSlaveDataSource，MasterSlaveDataSource实现自JDBC的标准接口DataSource。然后可通过DataSource选择使用原生JDBC开发，或者使用JPA, MyBatis等ORM工具。

DataSource dataSource = MasterSlaveDataSourceFactory.createDataSource(yamlFile); 
String sql = "SELECT i.* FROM t_order o JOIN t_order_item i ON o.order_id=i.order_id WHERE o.user_id=? AND o.order_id=?"; 
try ( 
Connection conn = dataSource.getConnection(); 
PreparedStatement preparedStatement = conn.prepareStatement(sql)) 
{ 
    preparedStatement.setInt(1, 10); 
    preparedStatement.setInt(2, 1001); 
    try (ResultSet rs = preparedStatement.executeQuery()) 
    {
       while(rs.next()) 
        { 
            System.out.println(rs.getInt(1)); 
            System.out.println(rs.getInt(2)); 
        } 
    } 
}

使用Spring

为了方便演示，搭建了springboot的web应用，pom.xml文件配置了哪些

<?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 http://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>2.1.3.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.study.liming</groupId>
    <artifactId>sharding-jdbc-study</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>sharding-jdbc-study</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <!--springBoot 的 web应用-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!--mybatis-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.0.0</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
        </dependency>
        <!--mysql的jar-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!--引入的shardingjdbc当下最新的版本-->
        <dependency>
            <groupId>io.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>3.1.0</version>
        </dependency>
        <!--引入了阿里的数据库连接池-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.14</version>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

演示的最终目的就是为了演示shardingjdbc，上面的引入后就可以配置对应的数据源了，需要了解下数据源具体可以配置哪些信息，具体能配置的信息，上面的参数说明写的很清楚，连接地址，数据库用户和明码，超时控制，连接空闲时间，最大的连接数，最小的连接数，都有详细的说明，一起看看配置的情况。
默认的数据源ds0，显示sql

service 配置

Order 配置

OrderDao 配置

OrderController 配置

启动类

新建立表，在3.101建立个t_order

CREATE TABLE t_order ( order_id BIGINT PRIMARY KEY, order_time DATETIME, customer_id BIGINT);

yml 配置

sharding:
  jdbc:
    datasource:
      names: ds0,ds1
      ds0: 
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class: com.mysql.jdbc.Driver
        url: jdbc:mysql://192.168.3.101:3306/hi2_db?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC
        username: liming
        password: 1qaz@WSX
        maxPoolSize: 50
        minPoolSize: 1
      ds1: 
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class: com.mysql.jdbc.Driver
        url: jdbc:mysql://192.168.3.102:3306/hi2_db?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC
        username: liming
        password: 1qaz@WSX
        maxPoolSize: 50
        minPoolSize: 1
    config:
      sharding:
        default-data-source-name: ds0
      masterslave:
        name: ms
        master-data-source-name: ds0
        slave-data-source-names: ds1
      props:
        sql.show: true
mybatis:
  configuration:
    map-underscore-to-camel-case: true

添加记录，http://localhost:8080/order/add?orderId=1001&customerId=2001
添加到ds0

查询试试，http://localhost:8080/order/get?orderId=1001

PS：sharding-jdbc做读写分离其实并不是复杂，增加一个配置就可以了。如果配置不简单谁还要呢，下节说些复杂的，开始说说分库分表。

文章来源：https://baijiahao.baidu.com/s?id=1735581363311705698&wfr=spider&for=pc