Apache ShardingSphere 研读（3）：Shardingsphere-Proxy 演示

如果可以改变
980 阅读3分钟

版本：5.0.0-beta

内容

本文重点关注 ShardingSphere-Proxy 的分库分表、读写分离和数据加密的演示

启动

  1. 下载 apache-shardingsphere-5.0.0-beta-shardingsphere-proxy-bin.tar.gz
  2. 修改配置，将examples\shardingsphere-proxy-example\shardingsphere-proxy-boot-mybatis-example\src\main\resources\conf 下面的三个配置文件：config-readwrite-splitting.yaml，config-sharding.yaml，server.yaml 复制到解压后的文件夹下的conf里面，修改数据库的配置。另外修改proxy端conf里面的config-encrypt.yaml 文件的数据库配置用于数据加解密测试
  3. 将 mysql-connector-java-8.0.26.jar 复制到lib目录下
  4. 启动 start.bat, 默认3307 端口（在start.bat后面加端口可启动指定端口） 
    Starting the ShardingSphere-Proxy ...
Thanks for using Atomikos! Evaluate http://www.atomikos.com/Main/ExtremeTransactions for advanced features and professional support
or register at http://www.atomikos.com/Main/RegisterYourDownload to disable this message and receive FREE tips & advice
[INFO ] 2021-08-25 09:15:47.928 [main] o.a.s.p.i.i.AbstractBootstrapInitializer - Database name is `MySQL`, version is `5.7.28-log`
[INFO ] 2021-08-25 09:15:49.210 [main] o.a.s.p.frontend.ShardingSphereProxy - ShardingSphere-Proxy start success.

功能演示

本文的 ShardingSphere-Proxy 为源码启动，启动入口为 shardingsphere-proxy 下的子模块 shardingsphere-proxy-bootstrap 的 Bootstrap

读写分离

入口

shardingsphere-proxy-boot-mybatis-example 项目的 ProxySpringBootStarterExample

配置

  1. ShardingSphere-Proxy 配置

    schemaName: readwrite_splitting_db

dataSources:
  write_ds:
    url: jdbc:mysql://127.0.0.1:3306/demo_write_ds?serverTimezone=UTC&useSSL=false
    username: root
    password: 123456
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1
  read_ds_0:
    url: jdbc:mysql://127.0.0.1:3306/demo_read_ds_0?serverTimezone=UTC&useSSL=false
    username: root
    password: 123456
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1
  read_ds_1:
    url: jdbc:mysql://127.0.0.1:3306/demo_read_ds_1?serverTimezone=UTC&useSSL=false
    username: root
    password: 123456
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1

rules:
- !READWRITE_SPLITTING
  dataSources:
    pr_ds:
      writeDataSourceName: write_ds
      readDataSourceNames:
        - read_ds_0
        - read_ds_1

  2. shardingsphere-proxy-boot-mybatis-example 配置

    mybatis.config-location=classpath:META-INF/mybatis-config.xml

spring.datasource.type=com.zaxxer.hikari.HikariDataSource
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3307/readwrite_splitting_db?useServerPrepStmts=true&cachePrepStmts=true&useLocalSessionState=true
spring.datasource.username=root
spring.datasource.password=root

演示结果

  1. 写 SQL 的 ShardingSphere-Proxy 日志

    [INFO ] 2021-09-07 15:50:38.674 [Connection-23-ThreadExecutor] ShardingSphere-SQL - Logic SQL: INSERT INTO t_order_item (order_id, user_id, status) VALUES (?, ?, ?);
[INFO ] 2021-09-07 15:50:38.674 [Connection-23-ThreadExecutor] ShardingSphere-SQL - SQLStatement: MySQLInsertStatement(setAssignment=Optional.empty, onDuplicateKeyColumns=Optional.empty)
[INFO ] 2021-09-07 15:50:38.674 [Connection-23-ThreadExecutor] ShardingSphere-SQL - Actual SQL: write_ds ::: INSERT INTO t_order_item (order_id, user_id, status) VALUES (?, ?, ?); ::: [9, 9, INSERT_TEST]

  2. 读 SQL 的 ShardingSphere-Proxy 日志

    [INFO ] 2021-09-07 15:50:55.493 [Connection-23-ThreadExecutor] ShardingSphere-SQL - Logic SQL: SELECT * from t_order_item
[INFO ] 2021-09-07 15:50:55.493 [Connection-23-ThreadExecutor] ShardingSphere-SQL - SQLStatement: MySQLSelectStatement(limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
[INFO ] 2021-09-07 15:50:55.493 [Connection-23-ThreadExecutor] ShardingSphere-SQL - Actual SQL: read_ds_0 ::: SELECT * from t_order_item
[INFO ] 2021-09-07 15:50:55.452 [Connection-23-ThreadExecutor] ShardingSphere-SQL - Logic SQL: SELECT * FROM t_order;
[INFO ] 2021-09-07 15:50:55.452 [Connection-23-ThreadExecutor] ShardingSphere-SQL - SQLStatement: MySQLSelectStatement(limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
[INFO ] 2021-09-07 15:50:55.452 [Connection-23-ThreadExecutor] ShardingSphere-SQL - Actual SQL: read_ds_1 ::: SELECT * FROM t_order;

分库分表

入口

shardingsphere-proxy-boot-mybatis-example 项目的 ProxySpringBootStarterExample

配置

  1. ShardingSphere-Proxy 配置

    schemaName: sharding_db

dataSources:
  ds_0:
    url: jdbc:mysql://127.0.0.1:3306/demo_ds_0?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/demo_ds_1?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

  2. shardingsphere-proxy-boot-mybatis-example 配置

    mybatis.config-location=classpath:META-INF/mybatis-config.xml

spring.datasource.type=com.zaxxer.hikari.HikariDataSource
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3307/sharding_db?useServerPrepStmts=true&cachePrepStmts=true&useLocalSessionState=true
spring.datasource.username=root
spring.datasource.password=root

增加 &useLocalSessionState=true 解决启动后大量出现 select @@session.transaction_read_only

演示结果

  1. 偶数的 user_id 落在 ds_0 库，奇数的 user_id 落在 ds_1 库
  2. 偶数的 order_id 落在 t_order_0 库，奇数的 order_id 落在 t_order_1 库

加密

入口

shardingsphere-proxy-boot-mybatis-example 项目的 ProxySpringBootStarterExample

配置

  1. ShardingSphere-Proxy 配置

    schemaName: encrypt_db

dataSources:
  ds_0:
    url: jdbc:mysql://127.0.0.1:3306/demo_ds_0?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/demo_ds_1?serverTimezone=UTC&useSSL=false
    username: root
    password: 123456
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1

rules:
  - !ENCRYPT
    encryptors:
      aes_encryptor:
        type: AES
        props:
          aes-key-value: 123456abc
      md5_encryptor:
        type: MD5
    tables:
      t_user:
        columns:
          user_name:
            cipherColumn: user_name
            encryptorName: aes_encryptor
          pwd:
            cipherColumn: pwd
            encryptorName: md5_encryptor

  2. shardingsphere-proxy-boot-mybatis-example 配置

    mybatis.config-location=classpath:META-INF/mybatis-config.xml

spring.datasource.type=com.zaxxer.hikari.HikariDataSource
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3307/encrypt_db?useServerPrepStmts=true&cachePrepStmts=true&useLocalSessionState=true
spring.datasource.username=root
spring.datasource.password=root

演示结果

---------------------------- Print User Data -----------------------
user_id: 1, user_name: test_mybatis_1, pwd: d8b04170dddc3fe760c403a2deb0414e
......

对比数据库的结果：

image.png

参考

avatar
文章
阅读
粉丝