ShardingSphere-Proxy 安装配置

ShardingSphere

发布日期: 2023-04-02

文章字数: 695

ShardingSphere-Proxy

简介

ShardingSphere-Proxy 的定位为透明化的数据库代理，理论上支持任何使用 MySQL、PostgreSQL、openGauss 协议的客户端操作数据，对异构语言、运维场景更友好。

应用场景

ShardingSphere-Proxy 对系统库/表（如 information_schema、pg_catalog）支持有限，通过部分图形化数据库客户端连接 Proxy 时，可能客户端或 Proxy 会有错误提示。可以使用命令行客户端（mysql、psql、gsql 等）连接 Proxy 验证功能。

使用限制

目前仅支持 JAVA 语言

前提条件

使用 Docker 启动 ShardingSphere-Proxy 无须额外依赖。使用二进制分发包启动 Proxy，需要环境具备 Java JRE 8 或更高版本。

ShardingSphere-Proxy MySql使用安装配置

使用docker 前提条件：

安装Docker
安装MySql

服务器：

192.168.1.100 192.168.1.200 mysql实例，测试可使用单个实例多个库即可
192.168.1.300 安装ShardingSphere-Proxy

Docker 安装ShardingSphere-Proxy

## 临时启动容器
docker run -d --name shardingsphere-proxy --entrypoint=bash apache/shardingsphere-proxy

## 复制配置
docker cp  shardingsphere-proxy:/opt/shardingsphere-proxy/conf /home/shardingsphere-proxy/conf
docker cp  shardingsphere-proxy:/opt/shardingsphere-proxy/ext-lib  /home/shardingsphere-proxy
## 删除临时容器
docker rm  shardingsphere-proxy
## 修改配置
vi server.yaml 

vi config-sharding.yaml

## 下载mysql驱动 放入ext-lib
wget https://repo1.maven.org/maven2/com/mysql/mysql-connector-j/8.0.32/mysql-connector-j-8.0.32.jar

## 启动服务
docker run -d  --name shardingsphere-proxy   -v /home/shardingsphere-proxy/conf:/opt/shardingsphere-proxy/conf     -v /home/shardingsphere-proxy/ext-lib:/opt/shardingsphere-proxy/ext-lib    -v /etc/localtime:/etc/localtime:ro  -e PORT=3308 -p13308:3308 apache/shardingsphere-proxy

修改配置文件

server.yaml:


mode:
  type: Standalone
  repository:
    type: JDBC


rules:
  - !AUTHORITY
    users:
      - root@%:root
      - mysql@%:sy@dmin12
    provider:
      type: ALL_PRIVILEGES_PERMITTED
  - !TRANSACTION
    defaultType: LOCAL

config-sharding.yaml


databaseName: cbb

dataSources:
  ds_0:
    url: jdbc:mysql://192.168.1.100:3306/demo1?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
    username: mysql
    password: 123456
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 150
    minPoolSize: 1
  ds_1:
    url: jdbc:mysql://192.168.1.200:3306/demo2?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
    username: mysql
    password: 123456
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 150
    minPoolSize: 1


rules:
  - !SINGLE
    defaultDataSource: ds_0 ## 不分表分分库的默认数据源
  - !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
    broadcastTables:
      - t_address
    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

Navicat 客户端或者应用连接

host: 192.168.1.300
port: 13308
username: mysql
password: sy@dmin12

执行SQL

CREATE TABLE IF NOT EXISTS t_order (order_id BIGINT AUTO_INCREMENT, user_id INT NOT NULL, address_id BIGINT NOT NULL, status VARCHAR(50), PRIMARY KEY (order_id));
CREATE TABLE IF NOT EXISTS t_order_item (order_item_id BIGINT AUTO_INCREMENT, order_id BIGINT, user_id INT NOT NULL, status VARCHAR(50) , PRIMARY KEY (order_item_id));
CREATE TABLE IF NOT EXISTS t_address (address_id BIGINT NOT NULL, address_name VARCHAR(100) NOT NULL, PRIMARY KEY (address_id));