侧边栏壁纸
博主头像
码森林博主等级

一起走进码森林,享受编程的乐趣,发现科技的魅力,创造智能的未来!

  • 累计撰写 145 篇文章
  • 累计创建 73 个标签
  • 累计收到 4 条评论

目 录CONTENT

文章目录

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

码森林
2022-11-15 / 0 评论 / 0 点赞 / 598 阅读 / 1,075 字 / 正在检测是否收录...
温馨提示:
本文最后更新于 2022-11-17,若内容或图片失效,请留言反馈。部分素材来自网络,若不小心影响到您的利益,请联系我们删除。

简介

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

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

使用 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

使用 navicat 连接 sharding-proxy

1、由于后端数据库是MySQL,需要将mysql-connector.jar挂载到/opt/sharding-proxy/ext-lib目录,注意版本需要一致,否则连接报错,,如果配置中存在无法连接到的数据源,也会连接报错

2、navicat 连接,可以看到逻辑表的数据。试了下修改真实表,可以同步更新到逻辑表,但无法直接修改逻辑表

使用 mysql 命令连接 sharding-proxy

使用 Sharding-UI 链接 Sharding-Proxy

相关配置文件

github

0

评论区