关于数据库:ShardingSphere-4x-ShardingProxy-配置手册

33次阅读

共计 7663 个字符，预计需要花费 20 分钟才能阅读完成。

数据源与分片配置示例

Sharding-Proxy 反对多逻辑数据源，每个以 config- 前缀命名的 yaml 配置文件，即为一个逻辑数据源。以下是 config-xxx.yaml 的配置配置示例。

数据分片

dataSources:

schemaName: sharding_db

dataSources:
  ds0: 
    url: jdbc:postgresql://localhost:5432/ds0
    username: root
    password: 
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 65
  ds1:
    url: jdbc:postgresql://localhost:5432/ds1
    username: root
    password: 
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 65

shardingRule:
  tables:
    t_order:
      actualDataNodes: ds${0..1}.t_order${0..1}
      databaseStrategy:
        inline:
          shardingColumn: user_id
          algorithmExpression: ds${user_id % 2}
      tableStrategy: 
        inline:
          shardingColumn: order_id
          algorithmExpression: t_order${order_id % 2}
      keyGenerator:
        type: SNOWFLAKE
        column: order_id
    t_order_item:
      actualDataNodes: ds${0..1}.t_order_item${0..1}
      databaseStrategy:
        inline:
          shardingColumn: user_id
          algorithmExpression: ds${user_id % 2}
      tableStrategy:
        inline:
          shardingColumn: order_id
          algorithmExpression: t_order_item${order_id % 2}
      keyGenerator:
        type: SNOWFLAKE
        column: order_item_id
  bindingTables:
    - t_order,t_order_item
  defaultTableStrategy:
    none:

读写拆散

schemaName: master_slave_db

dataSources:
  ds_master:
    url: jdbc:postgresql://localhost:5432/ds_master
    username: root
    password: 
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 65
  ds_slave0:
    url: jdbc:postgresql://localhost:5432/ds_slave0
    username: root
    password: 
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 65
  ds_slave1:
    url: jdbc:postgresql://localhost:5432/ds_slave1
    username: root
    password: 
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 65

masterSlaveRule:
  name: ds_ms
  masterDataSourceName: ds_master
  slaveDataSourceNames: 
    - ds_slave0
    - ds_slave1

数据脱敏

schemaName: encrypt_db

dataSource:
  url: jdbc:mysql://127.0.0.1:3306/demo_ds?serverTimezone=UTC&useSSL=false
  username: root
  password:
  connectionTimeoutMilliseconds: 30000
  idleTimeoutMilliseconds: 60000
  maxLifetimeMilliseconds: 1800000
  maxPoolSize: 50

encryptRule:
  encryptors:
    encryptor_aes:
      type: aes
      props:
        aes.key.value: 123456abc
    encryptor_md5:
      type: md5
  tables:
    t_encrypt:
      columns:
        user_id:
          plainColumn: user_plain
          cipherColumn: user_cipher
          encryptor: encryptor_aes
        order_id:
          cipherColumn: order_cipher
          encryptor: encryptor_md5

数据分片 + 读写拆散

schemaName: sharding_master_slave_db

dataSources:
  ds0:
    url: jdbc:postgresql://localhost:5432/ds0
    username: root
    password: 
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 65
  ds0_slave0:
    url: jdbc:postgresql://localhost:5432/ds0_slave0
    username: root
    password: 
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 65
  ds0_slave1:
    url: jdbc:postgresql://localhost:5432/ds0_slave1
    username: root
    password: 
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 65
  ds1:
    url: jdbc:postgresql://localhost:5432/ds1
    username: root
    password: 
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 65
  ds1_slave0:
    url: jdbc:postgresql://localhost:5432/ds1_slave0
    username: root
    password: 
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 65
  ds1_slave1:
    url: jdbc:postgresql://localhost:5432/ds1_slave1
    username: root
    password: 
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 65

shardingRule:  
  tables:
    t_order: 
      actualDataNodes: ms_ds${0..1}.t_order${0..1}
      databaseStrategy:
        inline:
          shardingColumn: user_id
          algorithmExpression: ms_ds${user_id % 2}
      tableStrategy: 
        inline:
          shardingColumn: order_id
          algorithmExpression: t_order${order_id % 2}
      keyGenerator:
        type: SNOWFLAKE
        column: order_id
    t_order_item:
      actualDataNodes: ms_ds${0..1}.t_order_item${0..1}
      databaseStrategy:
        inline:
          shardingColumn: user_id
          algorithmExpression: ms_ds${user_id % 2}
      tableStrategy:
        inline:
          shardingColumn: order_id
          algorithmExpression: t_order_item${order_id % 2}
      keyGenerator:
        type: SNOWFLAKE
        column: order_item_id
  bindingTables:
    - t_order,t_order_item
  broadcastTables:
    - t_config
  
  defaultDataSourceName: ds0
  defaultTableStrategy:
    none:
  
  masterSlaveRules:
    ms_ds0:
      masterDataSourceName: ds0
      slaveDataSourceNames:
        - ds0_slave0
        - ds0_slave1
      loadBalanceAlgorithmType: ROUND_ROBIN
    ms_ds1:
      masterDataSourceName: ds1
      slaveDataSourceNames: 
        - ds1_slave0
        - ds1_slave1
      loadBalanceAlgorithmType: ROUND_ROBIN

数据分片 + 数据脱敏

dataSources:

schemaName: sharding_db

dataSources:
  ds0: 
    url: jdbc:postgresql://localhost:5432/ds0
    username: root
    password: 
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 65
  ds1:
    url: jdbc:postgresql://localhost:5432/ds1
    username: root
    password: 
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 65

shardingRule:
  tables:
    t_order: 
      actualDataNodes: ds${0..1}.t_order${0..1}
      databaseStrategy:
        inline:
          shardingColumn: user_id
          algorithmExpression: ds${user_id % 2}
      tableStrategy: 
        inline:
          shardingColumn: order_id
          algorithmExpression: t_order${order_id % 2}
      keyGenerator:
        type: SNOWFLAKE
        column: order_id
    t_order_item:
      actualDataNodes: ds${0..1}.t_order_item${0..1}
      databaseStrategy:
        inline:
          shardingColumn: user_id
          algorithmExpression: ds${user_id % 2}
      tableStrategy:
        inline:
          shardingColumn: order_id
          algorithmExpression: t_order_item${order_id % 2}
      keyGenerator:
        type: SNOWFLAKE
        column: order_item_id
  bindingTables:
    - t_order,t_order_item
  defaultTableStrategy:
    none:
    
  encryptRule:
    encryptors:
      encryptor_aes:
        type: aes
        props:
          aes.key.value: 123456abc
    tables:
      t_order:
        columns:
          order_id:
            plainColumn: order_plain
            cipherColumn: order_cipher
            encryptor: encryptor_aes

全局配置示例

Sharding-Proxy 应用 conf/server.yaml 配置注册核心、认证信息以及专用属性。

治理

治理模块目前反对配置核心和注册核心，具体配置为：

  • orchestrationType: config_center #配置配置核心
  • orchestrationType: registry_center #配置注册核心
  • orchestrationType: config_center,registry_center #同时配置配置核心和注册核心
# 省略数据分片和读写拆散配置

orchestration:
  orchestration_ds: 
    orchestrationType: config_center,registry_center
    instanceType: zookeeper
    serverLists: localhost:2181
    namespace: orchestration
    props:
      overwrite: true

认证信息

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

专用属性

props:
  executor.size: 16
  sql.show: false

数据源与分片配置项阐明

数据分片

schemaName: #逻辑数据源名称

dataSources: #数据源配置，可配置多个 data_source_name
  <data_source_name>: #与 Sharding-JDBC 配置不同，无需配置数据库连接池
    url: #数据库 url 连贯
    username: #数据库用户名
    password: #数据库明码
    connectionTimeoutMilliseconds: 30000 #连贯超时毫秒数
    idleTimeoutMilliseconds: 60000 #闲暇连贯回收超时毫秒数
    maxLifetimeMilliseconds: 1800000 #连贯最大存活工夫毫秒数
    maxPoolSize: 65 #最大连接数

shardingRule: #省略数据分片配置，与 Sharding-JDBC 配置统一

读写拆散

schemaName: #逻辑数据源名称

dataSources: #省略数据源配置，与数据分片统一

masterSlaveRule: #省略读写拆散配置，与 Sharding-JDBC 配置统一

数据脱敏

dataSource: #省略数据源配置

encryptRule:
  encryptors:
    <encryptor-name>:
      type: #加解密器类型，可自定义或抉择内置类型：MD5/AES 
      props: #属性配置, 留神：应用 AES 加密器，须要配置 AES 加密器的 KEY 属性：aes.key.value
        aes.key.value: 
  tables:
    <table-name>:
      columns:
        <logic-column-name>:
          plainColumn: #存储明文的字段
          cipherColumn: #存储密文的字段
          assistedQueryColumn: #辅助查问字段，针对 ShardingQueryAssistedEncryptor 类型的加解密器进行辅助查问
          encryptor: #加密器名字
props:
  query.with.cipher.column: true #是否应用密文列查问

全局配置项阐明

治理

与 Sharding-JDBC 配置统一。

Proxy 属性

# 省略与 Sharding-JDBC 统一的配置属性
props:
  acceptor.size: #用于设置接管客户端申请的工作线程个数，默认为 CPU 核数 *2
  proxy.transaction.type: #默认为 LOCAL 事务，容许 LOCAL，XA，BASE 三个值，XA 采纳 Atomikos 作为事务管理器，BASE 类型须要拷贝实现 ShardingTransactionManager 的接口的 jar 包至 lib 目录中
  proxy.opentracing.enabled: #是否开启链路追踪性能，默认为不开启。详情请参见[链路追踪](/cn/features/orchestration/apm/)
  check.table.metadata.enabled: #是否在启动时查看分表元数据一致性，默认值: false
  proxy.frontend.flush.threshold: # 对于单个大查问, 每多少个网络包返回一次

权限验证

用于执行登录 Sharding Proxy 的权限验证。配置用户名、明码、可拜访的数据库后，必须应用正确的用户名、明码才可登录 Proxy。

authentication:
  users:
    root: # 自定义用户名
      password: root # 自定义用户名
    sharding: # 自定义用户名
      password: sharding # 自定义用户名
      authorizedSchemas: sharding_db, masterslave_db # 该用户受权可拜访的数据库，多个用逗号分隔。缺省将领有 root 权限，可拜访全副数据库。

Yaml 语法阐明

!! 示意实例化该类

- 示意能够蕴含一个或多个

[] 示意数组，能够与减号互相替换应用

正文完
数据库
发表至： 数据库
2020-07-26
 0
关于数据库:腾讯云TDSQL-PostgreSQL版-最佳实践-｜优化-SQL-语句
关于数据库:工具-pgrecovery-设计原理与源码解读
关于数据库:OPPO京东云-loT-项目数据架构改造数据处理痛点这样破解
关于数据库:前沿分享｜阿里云数据库资深技术专家-姚奕玮AnalyticDB-MySQL离在线一体化技术揭秘