配置手册

数据源与分片配置示例

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

数据分片

dataSources:

  1. schemaName: sharding_db
  3. dataSources:
  4.   ds0: 
  5.     url: jdbc:postgresql://localhost:5432/ds0
  6.     username: root
  7.     password: 
  8.     connectionTimeoutMilliseconds: 30000
  9.     idleTimeoutMilliseconds: 60000
  10.     maxLifetimeMilliseconds: 1800000
  11.     maxPoolSize: 65
  12.   ds1:
  13.     url: jdbc:postgresql://localhost:5432/ds1
  14.     username: root
  15.     password: 
  16.     connectionTimeoutMilliseconds: 30000
  17.     idleTimeoutMilliseconds: 60000
  18.     maxLifetimeMilliseconds: 1800000
  19.     maxPoolSize: 65
  21. shardingRule:
  22.   tables:
  23.     t_order:
  24.       actualDataNodes: ds${0..1}.t_order${0..1}
  25.       databaseStrategy:
  26.         inline:
  27.           shardingColumn: user_id
  28.           algorithmExpression: ds${user_id % 2}
  29.       tableStrategy: 
  30.         inline:
  31.           shardingColumn: order_id
  32.           algorithmExpression: t_order${order_id % 2}
  33.       keyGenerator:
  34.         type: SNOWFLAKE
  35.         column: order_id
  36.     t_order_item:
  37.       actualDataNodes: ds${0..1}.t_order_item${0..1}
  38.       databaseStrategy:
  39.         inline:
  40.           shardingColumn: user_id
  41.           algorithmExpression: ds${user_id % 2}
  42.       tableStrategy:
  43.         inline:
  44.           shardingColumn: order_id
  45.           algorithmExpression: t_order_item${order_id % 2}
  46.       keyGenerator:
  47.         type: SNOWFLAKE
  48.         column: order_item_id
  49.   bindingTables:
  50.     - t_order,t_order_item
  51.   defaultTableStrategy:
  52.     none:

读写分离

  1. schemaName: master_slave_db
  3. dataSources:
  4.   ds_master:
  5.     url: jdbc:postgresql://localhost:5432/ds_master
  6.     username: root
  7.     password: 
  8.     connectionTimeoutMilliseconds: 30000
  9.     idleTimeoutMilliseconds: 60000
  10.     maxLifetimeMilliseconds: 1800000
  11.     maxPoolSize: 65
  12.   ds_slave0:
  13.     url: jdbc:postgresql://localhost:5432/ds_slave0
  14.     username: root
  15.     password: 
  16.     connectionTimeoutMilliseconds: 30000
  17.     idleTimeoutMilliseconds: 60000
  18.     maxLifetimeMilliseconds: 1800000
  19.     maxPoolSize: 65
  20.   ds_slave1:
  21.     url: jdbc:postgresql://localhost:5432/ds_slave1
  22.     username: root
  23.     password: 
  24.     connectionTimeoutMilliseconds: 30000
  25.     idleTimeoutMilliseconds: 60000
  26.     maxLifetimeMilliseconds: 1800000
  27.     maxPoolSize: 65
  29. masterSlaveRule:
  30.   name: ds_ms
  31.   masterDataSourceName: ds_master
  32.   slaveDataSourceNames: 
  33.     - ds_slave0
  34.     - ds_slave1

数据脱敏

  1. schemaName: encrypt_db
  3. dataSource:
  4.   url: jdbc:mysql://127.0.0.1:3306/demo_ds?serverTimezone=UTC&useSSL=false
  5.   username: root
  6.   password:
  7.   connectionTimeoutMilliseconds: 30000
  8.   idleTimeoutMilliseconds: 60000
  9.   maxLifetimeMilliseconds: 1800000
  10.   maxPoolSize: 50
  12. encryptRule:
  13.   encryptors:
  14.     encryptor_aes:
  15.       type: aes
  16.       props:
  17.         aes.key.value: 123456abc
  18.     encryptor_md5:
  19.       type: md5
  20.   tables:
  21.     t_encrypt:
  22.       columns:
  23.         user_id:
  24.           plainColumn: user_plain
  25.           cipherColumn: user_cipher
  26.           encryptor: encryptor_aes
  27.         order_id:
  28.           cipherColumn: order_cipher
  29.           encryptor: encryptor_md5

数据分片 + 读写分离

  1. schemaName: sharding_master_slave_db
  3. dataSources:
  4.   ds0:
  5.     url: jdbc:postgresql://localhost:5432/ds0
  6.     username: root
  7.     password: 
  8.     connectionTimeoutMilliseconds: 30000
  9.     idleTimeoutMilliseconds: 60000
  10.     maxLifetimeMilliseconds: 1800000
  11.     maxPoolSize: 65
  12.   ds0_slave0:
  13.     url: jdbc:postgresql://localhost:5432/ds0_slave0
  14.     username: root
  15.     password: 
  16.     connectionTimeoutMilliseconds: 30000
  17.     idleTimeoutMilliseconds: 60000
  18.     maxLifetimeMilliseconds: 1800000
  19.     maxPoolSize: 65
  20.   ds0_slave1:
  21.     url: jdbc:postgresql://localhost:5432/ds0_slave1
  22.     username: root
  23.     password: 
  24.     connectionTimeoutMilliseconds: 30000
  25.     idleTimeoutMilliseconds: 60000
  26.     maxLifetimeMilliseconds: 1800000
  27.     maxPoolSize: 65
  28.   ds1:
  29.     url: jdbc:postgresql://localhost:5432/ds1
  30.     username: root
  31.     password: 
  32.     connectionTimeoutMilliseconds: 30000
  33.     idleTimeoutMilliseconds: 60000
  34.     maxLifetimeMilliseconds: 1800000
  35.     maxPoolSize: 65
  36.   ds1_slave0:
  37.     url: jdbc:postgresql://localhost:5432/ds1_slave0
  38.     username: root
  39.     password: 
  40.     connectionTimeoutMilliseconds: 30000
  41.     idleTimeoutMilliseconds: 60000
  42.     maxLifetimeMilliseconds: 1800000
  43.     maxPoolSize: 65
  44.   ds1_slave1:
  45.     url: jdbc:postgresql://localhost:5432/ds1_slave1
  46.     username: root
  47.     password: 
  48.     connectionTimeoutMilliseconds: 30000
  49.     idleTimeoutMilliseconds: 60000
  50.     maxLifetimeMilliseconds: 1800000
  51.     maxPoolSize: 65
  53. shardingRule:  
  54.   tables:
  55.     t_order: 
  56.       actualDataNodes: ms_ds${0..1}.t_order${0..1}
  57.       databaseStrategy:
  58.         inline:
  59.           shardingColumn: user_id
  60.           algorithmExpression: ms_ds${user_id % 2}
  61.       tableStrategy: 
  62.         inline:
  63.           shardingColumn: order_id
  64.           algorithmExpression: t_order${order_id % 2}
  65.       keyGenerator:
  66.         type: SNOWFLAKE
  67.         column: order_id
  68.     t_order_item:
  69.       actualDataNodes: ms_ds${0..1}.t_order_item${0..1}
  70.       databaseStrategy:
  71.         inline:
  72.           shardingColumn: user_id
  73.           algorithmExpression: ms_ds${user_id % 2}
  74.       tableStrategy:
  75.         inline:
  76.           shardingColumn: order_id
  77.           algorithmExpression: t_order_item${order_id % 2}
  78.       keyGenerator:
  79.         type: SNOWFLAKE
  80.         column: order_item_id
  81.   bindingTables:
  82.     - t_order,t_order_item
  83.   broadcastTables:
  84.     - t_config
  86.   defaultDataSourceName: ds0
  87.   defaultTableStrategy:
  88.     none:
  90.   masterSlaveRules:
  91.     ms_ds0:
  92.       masterDataSourceName: ds0
  93.       slaveDataSourceNames:
  94.         - ds0_slave0
  95.         - ds0_slave1
  96.       loadBalanceAlgorithmType: ROUND_ROBIN
  97.     ms_ds1:
  98.       masterDataSourceName: ds1
  99.       slaveDataSourceNames: 
  100.         - ds1_slave0
  101.         - ds1_slave1
  102.       loadBalanceAlgorithmType: ROUND_ROBIN

数据分片 + 数据脱敏

dataSources:

  1. schemaName: sharding_db
  3. dataSources:
  4.   ds0: 
  5.     url: jdbc:postgresql://localhost:5432/ds0
  6.     username: root
  7.     password: 
  8.     connectionTimeoutMilliseconds: 30000
  9.     idleTimeoutMilliseconds: 60000
  10.     maxLifetimeMilliseconds: 1800000
  11.     maxPoolSize: 65
  12.   ds1:
  13.     url: jdbc:postgresql://localhost:5432/ds1
  14.     username: root
  15.     password: 
  16.     connectionTimeoutMilliseconds: 30000
  17.     idleTimeoutMilliseconds: 60000
  18.     maxLifetimeMilliseconds: 1800000
  19.     maxPoolSize: 65
  21. shardingRule:
  22.   tables:
  23.     t_order: 
  24.       actualDataNodes: ds${0..1}.t_order${0..1}
  25.       databaseStrategy:
  26.         inline:
  27.           shardingColumn: user_id
  28.           algorithmExpression: ds${user_id % 2}
  29.       tableStrategy: 
  30.         inline:
  31.           shardingColumn: order_id
  32.           algorithmExpression: t_order${order_id % 2}
  33.       keyGenerator:
  34.         type: SNOWFLAKE
  35.         column: order_id
  36.     t_order_item:
  37.       actualDataNodes: ds${0..1}.t_order_item${0..1}
  38.       databaseStrategy:
  39.         inline:
  40.           shardingColumn: user_id
  41.           algorithmExpression: ds${user_id % 2}
  42.       tableStrategy:
  43.         inline:
  44.           shardingColumn: order_id
  45.           algorithmExpression: t_order_item${order_id % 2}
  46.       keyGenerator:
  47.         type: SNOWFLAKE
  48.         column: order_item_id
  49.   bindingTables:
  50.     - t_order,t_order_item
  51.   defaultTableStrategy:
  52.     none:
  54.   encryptRule:
  55.     encryptors:
  56.       encryptor_aes:
  57.         type: aes
  58.         props:
  59.           aes.key.value: 123456abc
  60.     tables:
  61.       t_order:
  62.         columns:
  63.           order_id:
  64.             plainColumn: order_plain
  65.             cipherColumn: order_cipher
  66.             encryptor: encryptor_aes

全局配置示例

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

治理

  1. #省略数据分片和读写分离配置
  3. orchestration:
  4.   name: orchestration_ds
  5.   overwrite: true
  6.   registry:
  7.     type: zookeeper
  8.     namespace: orchestration
  9.     serverLists: localhost:2181

认证信息

  1. authentication:
  2.   users:
  3.     root:
  4.       password: root
  5.     sharding:
  6.       password: sharding 
  7.       authorizedSchemas: sharding_db

公用属性

  1. props:
  2.   executor.size: 16
  3.   sql.show: false

数据源与分片配置项说明

数据分片

  1. schemaName: #逻辑数据源名称
  3. dataSources: #数据源配置，可配置多个data_source_name
  4.   <data_source_name>: #与Sharding-JDBC配置不同，无需配置数据库连接池
  5.     url: #数据库url连接
  6.     username: #数据库用户名
  7.     password: #数据库密码
  8.     connectionTimeoutMilliseconds: 30000 #连接超时毫秒数
  9.     idleTimeoutMilliseconds: 60000 #空闲连接回收超时毫秒数
  10.     maxLifetimeMilliseconds: 1800000 #连接最大存活时间毫秒数
  11.     maxPoolSize: 65 #最大连接数
  13. shardingRule: #省略数据分片配置，与Sharding-JDBC配置一致

读写分离

  1. schemaName: #逻辑数据源名称
  3. dataSources: #省略数据源配置，与数据分片一致
  5. masterSlaveRule: #省略读写分离配置，与Sharding-JDBC配置一致

数据脱敏

  1. dataSource: #省略数据源配置
  3. encryptRule:
  4.   encryptors:
  5.     <encryptor-name>:
  6.       type: #加解密器类型，可自定义或选择内置类型：MD5/AES 
  7.       props: #属性配置, 注意：使用AES加密器，需要配置AES加密器的KEY属性：aes.key.value
  8.         aes.key.value: 
  9.   tables:
  10.     <table-name>:
  11.       columns:
  12.         <logic-column-name>:
  13.           plainColumn: #存储明文的字段
  14.           cipherColumn: #存储密文的字段
  15.           assistedQueryColumn: #辅助查询字段，针对ShardingQueryAssistedEncryptor类型的加解密器进行辅助查询
  16.           encryptor: #加密器名字
  17. props:
  18.   query.with.cipher.column: true #是否使用密文列查询

全局配置项说明

治理

与Sharding-JDBC配置一致。

Proxy属性

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

权限验证

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

  1. authentication:
  2.   users:
  3.     root: # 自定义用户名
  4.       password: root # 自定义用户名
  5.     sharding: # 自定义用户名
  6.       password: sharding # 自定义用户名
  7.       authorizedSchemas: sharding_db, masterslave_db # 该用户授权可访问的数据库，多个用逗号分隔。缺省将拥有root权限，可访问全部数据库。

Yaml语法说明

!! 表示实例化该类

- 表示可以包含一个或多个

[] 表示数组，可以与减号相互替换使用