Version: 5.0.0 – beta

content

This paper focuses on the demonstration of shardingSphere-Proxy sub-database sub-table, read/write separation and data encryption

Start the

  1. downloadApache - shardingsphere - 5.0.0 - beta - shardingsphere - proxy - bin. Tar. Gz
  2. Modify the configuration, willexamples\shardingsphere-proxy-example\shardingsphere-proxy-boot-mybatis-example\src\main\resources\confThe following three configuration files:config-readwrite-splitting.yaml.config-sharding.yaml.server.yamlCopy the files to the conf folder and modify the database configuration. Modify the parameters in the proxy CONFconfig-encrypt.yamlThe database configuration of the file is used for data encryption and decryption testing
  3. Copy mysql-connector-java-8.0.26.jar to the lib directory
  4. Start start.bat, default port 3307 (add port after start.bat to start specified port) 
    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 ] The 2021-08-25 09:15:47. 928. [the main] O.A.S.P.I.I.A bstractBootstrapInitializer - the Database name is ` MySQL `, Version is ` 5.7.28 - log ` [INFO] 09:15:49. 2021-08-25, 210 [main] O.A.S.P.F rontend. ShardingSphereProxy - ShardingSphere-Proxy start success.Copy the code

Function demonstration

Shardingsphere-proxy in this paper is the source code startup, and the startup entrance is the bootstrap of the sub-module ShardingSphere-proxy-Bootstrap under ShardingSphere-Proxy

Reading and writing separation

The entrance

Shardingsphere – proxy – the boot – mybatis – ProxySpringBootStarterExample example project

configuration

  1. ShardingSphere – Proxy configuration

    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_1Copy the code

  2. Shardingsphere – proxy – the boot – mybatis – example configuration

    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
Copy the code

Results,

  1. Write SQL Shardingsphere-proxy logs

    [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 = option. 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]Copy the code

  2. Read SQL ShardingSphere-proxy logs

    [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;Copy the code

Depots table

The entrance

Shardingsphere – proxy – the boot – mybatis – ProxySpringBootStarterExample example project

configuration

  1. ShardingSphere – Proxy configuration

    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
Copy the code

  2. Shardingsphere – proxy – the boot – mybatis – example configuration

    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
Copy the code

Add &uselocalsessionState =true to resolve the problem of mass startup select @@session.transaction_read_only

Results,

  1. The even-numbered user_id falls into the DS_0 library and the odd-numbered user_id falls into the DS_1 library
  2. The even-numbered order_id falls into the T_ORDER_0 library, and the odd-numbered order_id falls into the t_ORDER_1 library

encryption

The entrance

Shardingsphere – proxy – the boot – mybatis – ProxySpringBootStarterExample example project

configuration

  1. ShardingSphere – Proxy configuration

    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
Copy the code

  2. Shardingsphere – proxy – the boot – mybatis – example configuration

    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
Copy the code

Results,

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

Comparing database results:

reference

  • shardingsphere 5.x document