Sharding-Proxy分库分表

最新推荐文章于 2024-04-27 09:13:51 发布

认怂保平安

最新推荐文章于 2024-04-27 09:13:51 发布

阅读量916

点赞数

分类专栏：数据库文章标签： java mysql

本文链接：https://blog.csdn.net/zhanglei7655/article/details/107938664

版权

数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

Sharding-JDBC可参考https://www.cnblogs.com/qdhxhz/p/11688371.html

Proxy参考https://blog.csdn.net/zhuyu19911016520/article/details/90051340

1、proxy下载地址

https://archive.apache.org/dist/shardingsphere/4.1.0/

Linux下：

wget https://mirrors.tuna.tsinghua.edu.cn/apache/shardingsphere/4.1.0/apache-shardingsphere-4.1.0-sharding-proxy-bin.tar.gz

2、解压

tar xvf apache-shardingsphere-4.1.0-sharding-proxy-bin.tar.gz

注意：有些jar包名称过长，导致解压失败，运行时会缺包报错，如：

Starting the Sharding-Proxy ...
Exception in thread "main" Cannot create property=orchestration for JavaBean=org.apache.shardingsphere.shardingproxy.config.yaml.YamlProxyServerConfiguration@1517365b
 in 'reader', line 24, column 1:
    orchestration:
    ^
Type org.apache.shardingsphere.orchestration.center.yaml.config.YamlCenterRepositoryConfiguration not present
 in 'reader', line 25, column 3:
      orchestration_ds:

3、把mysql jar包复制到lib下

wget https://cdn.mysql.com//Downloads/Connector-J/mysql-connector-java-5.1.47.tar.gz

4、修改sharding proxy基本配置server.yaml

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

props:
  max.connections.size.per.query: 1
  acceptor.size: 16  # The default value is available processors count * 2.
  executor.size: 16  # Infinite by default.
  proxy.frontend.flush.threshold: 128  # The default value is 128.
    # LOCAL: Proxy will run with LOCAL transaction.
    # XA: Proxy will run with XA transaction.
    # BASE: Proxy will run with B.A.S.E transaction.
  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/demo_ds_0?serverTimezone=UTC&useSSL=false
    username: root
    password: root
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
  ds_1:
    url: jdbc:mysql://127.0.0.1:3306/demo_ds_1?serverTimezone=UTC&useSSL=false
    username: root
    password: root
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50

shardingRule:
  tables:
    t_order:
      actualDataNodes: ds_${0..1}.t_order_${0..1}
      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}
      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
  defaultDatabaseStrategy:
    inline:
      shardingColumn: user_id
      algorithmExpression: ds_${user_id % 2}
  defaultTableStrategy:
    none:

6、连接逻辑数据库sharding