MySQL之读写分离-sharding-jdbc

版本及环境

• springboot:2.3.1.RELEASE
• shardingsphere:4.0.0-RC1
• 配置好主从复制的2-3台mysql节点

maven依赖

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.0.1</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

        <!--druid-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.6</version>
        </dependency>

        <!--mysql读写分离 sharding-jdbc4.x版本-->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>${shardingsphere-version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-core-common</artifactId>
            <version>${shardingsphere-version}</version>
        </dependency>

配置文件

db:
  db: ds
  db0: ds0
  db1: ds1

spring:
  main:
    allow-bean-definition-overriding: true
  shardingsphere:
    datasource:
      names: ${db.db},${db.db0},${db.db1}
      # 配置主库
      ds:
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.77.20:3316/test?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT%2B8
        username: root
        password: 123456
        #最大连接数
        maxPoolSize: 10
        minPoolSize: 5
      ds0: # 配置第一个从库
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.77.20:3317/test?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT%2B8
        username: root
        password: 123456
        maxPoolSize: 5
        minPoolSize: 2
      ds1: # 配置第二个从库
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.77.20:3317/test?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT%2B8
        username: root
        password: 123456
        maxPoolSize: 5
        minPoolSize: 2
    # 配置默认数据源，主要用于写，注意一定要配读写分离，否则会把所有节点都当作slave，导致master压力太大
    sharding:
      default-data-source-name: ${db.db}
    # 配置读写分离，配置之前一定要确认数据库做了主从复制
    masterslave:
      # 名称，任意
      name: ms
      load-balance-algorithm-type: round_robin # 配置从库选择策略，提供轮询与随机，这里选择用轮询//random 随机 //round_robin 轮询
      master-data-source-name: ${db.db}
      slave-data-source-names: ${db.db0},${db.db1}
    props:
      sql: # 开启SQL显示，默认值: false，注意：仅配置读写分离时不会打印日志！！！
        show: true