ShardingSphere4.1.1 | Sharding-Proxy部署及配置

简介

Sharding-Proxy是ShardingSphere的第二个产品。它定位为透明化的数据库代理端，提供封装了数据库二进制协议的服务端版本，用于完成对异构语言的支持。目前先提供MySQL/PostgreSQL版本，它可以使用任何兼容MySQL/PostgreSQL协议的访问客户端(如：MySQL Command Client, MySQL Workbench, Navicat等)操作数据，对DBA更加友好。

向应用程序完全透明，可直接当做MySQL/PostgreSQL使用。
适用于任何兼容MySQL/PostgreSQL协议的的客户端。

使用 Docker 运行 sharding-proxy:4.1.1

1、拉取镜像

https://hub.docker.com/r/apache/sharding-proxy/tags?page=1&ordering=last_updated

docker pull apache/sharding-proxy:4.1.1

2、创建配置本地挂载目录 /opt/shardingsphere/conf

3、创建并运行 sharding-proxy 容器

docker run --name sharding-proxy-13308 -d -v /opt/shardingsphere/conf:/opt/sharding-proxy/conf -v /opt/shardingsphere/ext-lib:/opt/sharding-proxy/ext-lib -e PORT=3308 -p13308:3308 apache/sharding-proxy:4.1.1

容器名称为 sharding-proxy-13308
配置本地挂载目录 /opt/shardingsphere/conf
配置容器内目录必须为 /opt/sharding-proxy/conf
/opt/sharding-proxy/ext-lib 扩展目录，可以引入一些jar
Docker容器端口为3308，宿主机端口为13308

4、在本地挂载目录创建配置 server.yaml，该配置用于配置ShardingSphere注册中心、配置中心、授权、属性等

vi /opt/shardingsphere/conf/server.yaml

编辑内容如下：

orchestration:
  orchestration_ds:
    orchestrationType: registry_center,config_center
    instanceType: zookeeper
    serverLists: localhost:2181
    namespace: orchestration
    props:
      overwrite: true
      retryIntervalMilliseconds: 500
      timeToLiveSeconds: 60
      maxRetries: 3
      operationTimeoutMilliseconds: 500

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

props:
  max.connections.size.per.query: 1
  acceptor.size: 16  #默认值为可用处理器计数*2
  executor.size: 16 # 默认为无限
  proxy.frontend.flush.threshold: 128  # 默认值为128
  # LOCAL: 代理将与本地事务一起运行
  # XA: 代理将与XA事务一起运行
  # BASE: 代理将与BASE事务一起运行
  proxy.transaction.type: LOCAL
  proxy.opentracing.enabled: false
  proxy.hint.enabled: false
  query.with.cipher.column: true
  sql.show: false
  allow.range.query.with.inline.sharding: false

5、数据分片配置 config-sharding.yaml

schemaName: sharding_db

dataSources:
 ds_0:
   url: jdbc:mysql://127.0.0.1:3306/ds0?serverTimezone=UTC&useSSL=false
   username: root
   password: 123456
   connectionTimeoutMilliseconds: 30000
   idleTimeoutMilliseconds: 60000
   maxLifetimeMilliseconds: 1800000
   maxPoolSize: 50
   minPoolSize: 1
 ds_1:
   url: jdbc:mysql://127.0.0.1:3306/ds1?serverTimezone=UTC&useSSL=false
   username: root
   password: 123456
   connectionTimeoutMilliseconds: 30000
   idleTimeoutMilliseconds: 60000
   maxLifetimeMilliseconds: 1800000
   maxPoolSize: 50
   minPoolSize: 1

rules:
- !SHARDING
 tables:
   t_order:
     actualDataNodes: ds_${0..1}.t_order_${0..1}
     tableStrategy:
       standard:
         shardingColumn: order_id
         shardingAlgorithmName: t_order_inline
     keyGenerateStrategy:
       column: order_id
       keyGeneratorName: snowflake
   t_order_item:
     actualDataNodes: ds_${0..1}.t_order_item_${0..1}
     tableStrategy:
       standard:
         shardingColumn: order_id
         shardingAlgorithmName: t_order_item_inline
     keyGenerateStrategy:
       column: order_item_id
       keyGeneratorName: snowflake
 bindingTables:
   - t_order,t_order_item
 defaultDatabaseStrategy:
   standard:
     shardingColumn: user_id
     shardingAlgorithmName: database_inline
 defaultTableStrategy:
   none:
 
 shardingAlgorithms:
   database_inline:
     type: INLINE
     props:
       algorithm-expression: ds_${user_id % 2}
   t_order_inline:
     type: INLINE
     props:
       algorithm-expression: t_order_${order_id % 2}
   t_order_item_inline:
     type: INLINE
     props:
       algorithm-expression: t_order_item_${order_id % 2}
 
 keyGenerators:
   snowflake:
     type: SNOWFLAKE
     props:
       worker-id: 123

6、读写分离配置 config-master_slave.yaml

schemaName: master_slave_db

dataSources:
 master:
   url: jdbc:mysql://127.0.0.1:3306/ds2?serverTimezone=UTC&useSSL=false
   username: root
   password: 123456
   connectionTimeoutMilliseconds: 30000
   idleTimeoutMilliseconds: 60000
   maxLifetimeMilliseconds: 1800000
   maxPoolSize: 50
 slave:
   url: jdbc:mysql://127.0.0.1:3307/ds2?serverTimezone=UTC&useSSL=false
   username: root
   password: 123456
   connectionTimeoutMilliseconds: 30000
   idleTimeoutMilliseconds: 60000
   maxLifetimeMilliseconds: 1800000
   maxPoolSize: 50

masterSlaveRule:
 name: ms_ds
 masterDataSourceName: master
 slaveDataSourceNames: 
   - slave

7、数据脱敏配置config-encrypt.yaml

schemaName: encrypt_db

dataSource:
 url: jdbc:mysql://127.0.0.1:3306/ds1?serverTimezone=UTC&useSSL=false
 username: root
 password: 123456
 connectionTimeoutMilliseconds: 30000
 idleTimeoutMilliseconds: 60000
 maxLifetimeMilliseconds: 1800000
 maxPoolSize: 50
encryptRule:
 encryptors:
   encryptor_aes:
     type: aes
     props:
       aes.key.value: 123456
   # encryptor_md5:
   #   type: md5
 tables:
   t_encrypt:
     columns:
       password:
         plainColumn: password
         cipherColumn: password_encryptor
         encryptor: encryptor_aes

8、影子库配置 config-shadow.yaml

schemaName: sharding_db

dataSources:
 ds:
   url: jdbc:mysql://127.0.0.1:3306/ds0?serverTimezone=UTC&useSSL=false
   username: root
   password: 123456
   connectionTimeoutMilliseconds: 30000
   idleTimeoutMilliseconds: 60000
   maxLifetimeMilliseconds: 1800000
   maxPoolSize: 50
 shadow_ds:
   url: jdbc:mysql://127.0.0.1:3306/ds1?serverTimezone=UTC&useSSL=false
   username: root
   password: 123456
   connectionTimeoutMilliseconds: 30000
   idleTimeoutMilliseconds: 60000
   maxLifetimeMilliseconds: 1800000
   maxPoolSize: 50

shadowRule:
 column: shadow
 shadowMappings:
   ds: shadow_ds