最新java springboot druid shardingsphere 与shardingsphere-proxy读写分离_5.4 shardingsphere proxy 配置

最新java springboot druid shardingsphere 读写分离

• 公司又有新的项目，然后项目需求要求有数据库读写分离，这个需求倒也没什么问题，就是采用什么方式来解决
• 网上调研了一堆方案，最后选择比较大众的解决方案用shardingsphere，但是呢这个shardingsphere也有两种，一种是java接入shardingsphere的jar包，然后进行配置来进行读写分离，还有一种是shardingsphere-proxy，这种则不需要引入依赖，直接使用代理十分的方便，官网地址
• 这次我两种方案都用了，最后选择了引入jar包的方式，两种方案的部署方式我都会列出来，还有就是我原本项目是引入了druid的连接池来处理，这部分的区别我也会列出来
• 网上其实shardingsphere与shardingsphere-proxy的安装文档也不少，但是很多都是很老的版本，于是我只能自行研究最新的shardingsphere5.4.1

使用shardingsphere-proxy5.4.1来进行读写分离

我使用的docker来进行部署，比较方便

• 首先配置好三个目录

[root@localhost ~]# cd /home/docker/shardingsphere-proxy
[root@localhost shardingsphere-proxy]# ls
conf  ext-lib  logs

1
2
3
4

• 在ext-lib文件夹下放入MySQl驱动mysql-connector-java-8.0.27.jar，用于数据库连接，然后在conf文件夹中新建server.yaml，然后输入

authority:
  users:
    - user: root@%
      password: ~!@#xm
    - user: sharding
      password: sharding
  privilege:
    type: ALL_PERMITTED
props:
  sql-show: true
1
2
3
4
5
6
7
8
9
10

• 然后在conf文件夹中新建config-readwrite-splitting.yaml配置文件，主备的数据库请提前自行配置好

#schemaName用来指定->逻辑表名
schemaName: mysql_demo

dataSources:
  write_ds:
    url: jdbc:mysql://127.0.0.1:3306/mysql_demo?useAffectedRows=true&useUnicode=true&zeroDateTimeBehavior=convertToNull&useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=UTF8
    username: root
    password: ~!@#xm
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1
  read_ds_0:
    url: jdbc:mysql://127.0.0.1:3307/mysql_demo?useAffectedRows=true&useUnicode=true&zeroDateTimeBehavior=convertToNull&useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=UTF8
    username: root
    password: ~!@#xm
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1
rules:
- !READWRITE_SPLITTING
  dataSources:
    readwrite_ds:
      writeDataSourceName: write_ds
      readDataSourceNames:
        - read_ds_0
      loadBalancerName: random
  loadBalancers:
    random:
      type: RANDOM
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

• 使用docker启动最新版本apache/shardingsphere-proxy:5.4.1

docker run -d -e PORT=3308 -v /mydata/shardingproxy/conf:/opt/shardingsphere-proxy/conf -v /mydata/shardingproxy/ext-lib:/opt/shardingsphere-proxy/ext-lib -v /mydata/shardingproxy/logs:/opt/shardingsphere-proxy/logs  --name shardingproxy apache/shardingsphere-proxy:5.4.1
1

• 至此安装完毕，使用navicat连接完成，即可进行读备写主操作，使用的时候按照server.yaml中配的账号密码来连接即可

引入shardingsphere.jar5.4.1来进行读写分离

• 环境得java8以上，然后maven新增配置

        <!--shardingsphere读写分离-->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc-core</artifactId>
            <version>5.4.1</version>
        </dependency>
        <dependency>
            <groupId>org.yaml</groupId>
            <artifactId>snakeyaml</artifactId>
            <version>1.33</version>
        </dependency>
1
2
3
4
5
6
7
8
9
10
11

• 配置文件application.properties新增配置，并将之前的数据库配置与druid的配置都去掉

#spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
#spring.datasource.url=jdbc:mysql://127.0.0.1:3306/mysql_demo?useAffectedRows=true&useUnicode=true&zeroDateTimeBehavior=convertToNull&useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=UTF8
#spring.datasource.username=root
#spring.datasource.password=~!@#xm
spring.datasource.driver-class-name=org.apache.shardingsphere.driver.ShardingSphereDriver
spring.datasource.url=jdbc:shardingsphere:classpath:config.yaml
1
2
3
4
5
6

• 然后在同目录新建config.yaml

mode:
  type: Standalone
  repository:
    type: JDBC

dataSources:
  ds_0:
    dataSourceClassName: com.alibaba.druid.pool.DruidDataSource
    driverClassName: com.mysql.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/mysql_demo?useAffectedRows=true&useUnicode=true&zeroDateTimeBehavior=convertToNull&useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=UTF8
    username: root
    password: ~!@#xm
    druid:
      keep-alive: true
      max-active: 50
      max-wait: 60000
      min-evictable-idle-time-millis: 600000
      min-idle: 10
      test-on-borrow: false
      test-on-return: false
      test-while-idle: true
      time-between-eviction-runs-millis: 60000
      validation-query: select 1
  ds_1:
    dataSourceClassName: com.alibaba.druid.pool.DruidDataSource
    driverClassName: com.mysql.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3307/mysql_demo?useAffectedRows=true&useUnicode=true&zeroDateTimeBehavior=convertToNull&useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=UTF8
    username: root
    password: ~!@#xm
    druid:
      keep-alive: true
      max-active: 50
      max-wait: 60000
      min-evictable-idle-time-millis: 600000
      min-idle: 10
      test-on-borrow: false
      test-on-return: false
      test-while-idle: true
      time-between-eviction-runs-millis: 60000
      validation-query: select 1
rules:
- !READWRITE_SPLITTING
  dataSources:
    readwrite_ds:
      writeDataSourceName: ds_0
      readDataSourceNames:
        - ds_1
      loadBalancerName: round_robin
  loadBalancers:
    round_robin:
      type: ROUND_ROBIN
- !SINGLE
  tables:
    - "*.*" # 加载全部单表




props:
  sql-show: true

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63

• 这样就接入的shardingsphere.jar的读写分离，用的页数最新的5.4.1版本