apache-shardingsphere配置

shardingsphere-jdbc

### 1. SpringMonitoringConfig!!!记得打开
### 2. pom sharding-jdbc-spring-boot-starter 记得删除屏蔽

spring.application.name=demo

swagger.enable=true

management.endpoints.jmx.exposure.include=*
management.endpoints.web.exposure.include=*
management.endpoint.health.show-details=always


# spring cloud access&secret config
# 可以访问如下地址查看: https://usercenter.console.aliyun.com/#/manage/ak
alibaba.cloud.access-key=****
alibaba.cloud.secret-key=****

mybatis.mapper-locations=classpath:mapper/*Mapper.xml
#sharding.jdbc.ds0.mapper-locations=classpath*:org/demo/test/persistence/xmlmapper/ds0/*.xml
#sharding.jdbc.ds1.mapper-locations=classpath*:org/demo/test/persistence/xmlmapper/ds1/*.xml
mybatis.type-aliases-package=com.example.demo.dao

# 应用服务 WEB 访问端口
server.port=8080
# Actuator Web 访问端口
management.server.port=8081

# Redis
boot4j.redis.enabled=true
spring.redis.database=1
spring.redis.host=192.168.1.69
spring.redis.password=ycredis
spring.redis.port=6379
spring.redis.pool.max-active=3000
spring.redis.pool.max-wait=3000
spring.redis.pool.max-idle=1000
spring.redis.pool.min-idle=0
spring.redis.timeout=1000

### tomcat access日志 ###
server.tomcat.accesslog.enabled=true
server.tomcat.accesslog.directory=E:/log
server.tomcat.accesslog.rotate=true
server.tomcat.accesslog.pattern='%t %a %A %m %U%q %s %D %I %B'
server.tomcat.accesslog.buffered=false
server.tomcat.accesslog.prefix=access_log

### 数据源 ###
#spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
#spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver
#spring.datasource.url=jdbc:mysql://192.168.1.69:3307/sharding_db?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8
#spring.datasource.username=root
#spring.datasource.password=root

### 连接池配置 ###
#spring.datasource.druid.initial-size=5
#spring.datasource.druid.max-active=50
#spring.datasource.druid.min-idle=5
#spring.datasource.druid.max-wait=60000
##spring.datasource.druid.pool-prepared-statements=
##spring.datasource.druid.max-pool-prepared-statement-per-connection-size=
##spring.datasource.druid.max-open-prepared-statements= #和上面的等价
##spring.datasource.druid.validation-query=
##spring.datasource.druid.validation-query-timeout=
##spring.datasource.druid.test-on-borrow=
##spring.datasource.druid.test-on-return=
##spring.datasource.druid.test-while-idle=
#spring.datasource.druid.time-between-eviction-runs-millis=60000
#spring.datasource.druid.min-evictable-idle-time-millis=30000
##spring.datasource.druid.max-evictable-idle-time-millis=
#spring.datasource.druid.filters=stat,wall

### 监控配置 ###
###WebStatFilter
#spring.datasource.druid.web-stat-filter.enabled=true
#spring.datasource.druid.web-stat-filter.url-pattern=/*
#spring.datasource.druid.web-stat-filter.exclusions=/druid/*,*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico
#spring.datasource.druid.web-stat-filter.session-stat-enable=true
#spring.datasource.druid.web-stat-filter.session-stat-max-count=10
#spring.datasource.druid.web-stat-filter.principal-session-name=session_name
#spring.datasource.druid.web-stat-filter.principal-cookie-name=cookie_name
#spring.datasource.druid.web-stat-filter.profile-enable=
####StatViewServlet
#spring.datasource.druid.stat-view-servlet.enabled=true
## 配置DruidStatViewServlet
#spring.datasource.druid.stat-view-servlet.url-pattern=/my/d/*
##  禁用HTML页面上的“Reset All”功能
#spring.datasource.druid.stat-view-servlet.reset-enable=true
#spring.datasource.druid.stat-view-servlet.login-username=admin
#spring.datasource.druid.stat-view-servlet.login-password=123456
##IP白名单(没有配置或者为空，则允许所有访问)
#spring.datasource.druid.stat-view-servlet.allow=
##IP黑名单 (存在共同时，deny优先于allow)
#spring.datasource.druid.stat-view-servlet.deny=


### apache-sharding-config ###
spring.main.allow-bean-definition-overriding=true
# 数据源
sharding.jdbc.datasource.names=ds0,ds1
sharding.jdbc.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSource
sharding.jdbc.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
sharding.jdbc.datasource.ds0.url=jdbc:mysql://192.168.1.56:3306/unicom?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8
sharding.jdbc.datasource.ds0.username=root
sharding.jdbc.datasource.ds0.password=YC_mysql@2020
sharding.jdbc.datasource.ds0.initial-size=5
sharding.jdbc.datasource.ds0.max-active=50
sharding.jdbc.datasource.ds0.min-idle=5
sharding.jdbc.datasource.ds0.max-wait=60000
sharding.jdbc.datasource.ds0.time-between-eviction-runs-millis=60000
sharding.jdbc.datasource.ds0.min-evictable-idle-time-millis=30000
sharding.jdbc.datasource.ds0.filters=stat,wall

sharding.jdbc.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource
sharding.jdbc.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
sharding.jdbc.datasource.ds1.url=jdbc:mysql://192.168.1.56:3306/unicom_dev?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8
sharding.jdbc.datasource.ds1.username=root
sharding.jdbc.datasource.ds1.password=YC_mysql@2020
sharding.jdbc.datasource.ds1.initial-size=5
sharding.jdbc.datasource.ds1.max-active=50
sharding.jdbc.datasource.ds1.min-idle=5
sharding.jdbc.datasource.ds1.max-wait=60000
sharding.jdbc.datasource.ds1.time-between-eviction-runs-millis=60000
sharding.jdbc.datasource.ds1.min-evictable-idle-time-millis=30000
sharding.jdbc.datasource.ds1.filters=stat,wall


sharding.jdbc.config.props.sql.show=true

### 配置读写分离 ps:mysql需要自行配置主从同步
#sharding.jdbc.config.masterslave.name=ms
#sharding.jdbc.config.masterslave.master-data-source-name=ds0
#sharding.jdbc.config.masterslave.slave-data-source-names=ds1

### 分库配置
#sharding.jdbc.config.sharding.tables.users.actual-data-nodes=
#sharding.jdbc.config.sharding.tables.users.actual-data-nodes=ds$->{0..1}
#sharding.jdbc.config.sharding.tables.users.database-strategy.inline.sharding-column=id
#sharding.jdbc.config.sharding.tables.users.database-strategy.inline.algorithm-expression=ds$->{id % 2}
#分表
sharding.jdbc.config.sharding.tables.users.actual-data-nodes=ds0.users$->{0..1}
sharding.jdbc.config.sharding.tables.users.table-strategy.inline.sharding-column=id
sharding.jdbc.config.sharding.tables.users.table-strategy.inline.algorithm-expression=users$->{id % 2}
#分库
#sharding.jdbc.config.sharding.tables.users.database-strategy.inline.sharding-column=id
#sharding.jdbc.config.sharding.tables.users.database-strategy.inline.algorithm-expression=ds$->{id % 2}

shardingsphere-porxy
配置文件：
1.server.yaml

authentication:
  users:
    root:
      password: root
    sharding:
      password: sharding 
      authorizedSchemas: sharding_db,master_slave_db

props:
  max.connections.size.per.query: 1
  acceptor.size: 16  # The default value is available processors count * 2.
  executor.size: 16  # Infinite by default.
  proxy.frontend.flush.threshold: 128  # The default value is 128.
    # LOCAL: Proxy will run with LOCAL transaction.
    # XA: Proxy will run with XA transaction.
    # BASE: Proxy will run with B.A.S.E transaction.
  proxy.transaction.type: LOCAL
  proxy.opentracing.enabled: false
  proxy.hint.enabled: false
  query.with.cipher.column: true
  sql.show: true
  allow.range.query.with.inline.sharding: false

2.config-sharding.yaml

schemaName: sharding_db

dataSources:
  ds0:
    url: jdbc:mysql://192.168.1.56:3306/unicom?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8
    username: root
    password: YC_mysql@2020
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
#  ds1:
#    url: jdbc:mysql://192.168.1.56:3306/unicom_dev?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8
#    username: root
#    password: YC_mysql@2020
#    connectionTimeoutMilliseconds: 30000
#    idleTimeoutMilliseconds: 60000
#    maxLifetimeMilliseconds: 1800000
#    maxPoolSize: 50

shardingRule:
  tables:
   # users: #逻辑表名,在一个库里分表:ds0.t_user_0,ds0.t_user_1
   #   actualDataNodes: ds0.users$->{0..1}
   #   tableStrategy: #表分片策略
   #     inline: #行表达式分片策略
   #       shardingColumn: id #分片的字段
   #       algorithmExpression: users$->{id % 2} #分片的算法
    unicom_result:
      actualDataNodes: ds0.unicom_result$->{0..9}
      tableStrategy: #表分片策略
        inline: #行表达式分片策略
          shardingColumn: phone #分片的字段
          algorithmExpression: unicom_result$->{phone.substring(phone.length()-1, phone.length())} #分片的算法
    unicom_winning_record:
      actualDataNodes: ds0.unicom_winning_record$->{0..9}
      tableStrategy: #表分片策略
        inline: #行表达式分片策略
          shardingColumn: nick_name #分片的字段
          algorithmExpression: unicom_winning_record$->{nick_name.substring(nick_name.length()-1, nick_name.length())} #分片的算法
#    t_dept: #分库,两个库里建系统的表:ds0.t_dept,ds1.t_dept
#      actualDataNodes: ds$->{0..1}.t_dept
#      databaseStrategy: #数据库分片策略
#        inline: #行表达式分片策略
#          shardingColumn: dept_id #分片的字段
#          algorithmExpression: ds$->{dept_id % 2} #分片的算法