配置手册

数据源与分片配置示例

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

数据分片

dataSources:

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

读写分离

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

数据脱敏

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

数据分片 + 读写分离

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

数据分片 + 数据脱敏

dataSources:

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

全局配置示例

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

治理

  1. #省略数据分片和读写分离配置
  2. orchestration:
  3. name: orchestration_ds
  4. overwrite: true
  5. registry:
  6. type: zookeeper
  7. namespace: orchestration
  8. 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: #逻辑数据源名称
  2. dataSources: #数据源配置,可配置多个data_source_name
  3. <data_source_name>: #与Sharding-JDBC配置不同,无需配置数据库连接池
  4. url: #数据库url连接
  5. username: #数据库用户名
  6. password: #数据库密码
  7. connectionTimeoutMilliseconds: 30000 #连接超时毫秒数
  8. idleTimeoutMilliseconds: 60000 #空闲连接回收超时毫秒数
  9. maxLifetimeMilliseconds: 1800000 #连接最大存活时间毫秒数
  10. maxPoolSize: 65 #最大连接数
  11. shardingRule: #省略数据分片配置,与Sharding-JDBC配置一致

读写分离

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

数据脱敏

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

!! 表示实例化该类

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

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