Initial experience with ShardingSphere Proxy

Introduction to the

In the previous article, we experienced ShardingSphere JDBC data sharding, read/write separation, and data encryption. In this article, we will explore ShardingSphere Proxy related functions

The sample run

ShardingSphere Proxy is relatively unfamiliar, first of all, there must be a wave of official documents to understand:

ShardingSphere overview

Data sharding, read/write separation, and data encryption are described and configured in the same way as ShardingSphere JDBC

If ShardingSphere JDBC is to be accessed, as you can see from the previous article, it is intrusive and requires changes to existing business code

ShardingSphere Proxy feels more like a code that only needs to change the connection configuration of the database and is non-invasive. But it also increases the complexity of the whole system, both have their advantages and disadvantages

ShardingSphere Proxy startup related

Find the boot first: shardingsphere-proxy/shardingsphere-proxy-bootstrap/src/main/java/org/apache/shardingsphere/proxy/Bootstrap.java

Yaml server.yaml server.yaml server.yaml server.yaml shardingsphere-proxy/shardingsphere-proxy-bootstrap/src/main/resources/conf/server.yaml

Release all the following comments:

The ShardingSphere connection is configured as user name and password. The following configuration file has two: root and sharding

Sql-show = true, see log for troubleshooting

rules:
  - ! AUTHORITY
    users:
      - root@%:root
      - sharding@:sharding
    provider:
      type: NATIVE

props:
  max-connections-size-per-query: 1
  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
  xa-transaction-manager-type: Atomikos
  proxy-opentracing-enabled: false
  proxy-hint-enabled: false
  sql-show: true
  check-table-metadata-enabled: false
  lock-wait-timeout-milliseconds: 50000 # The maximum time to wait for a lock
  show-process-list-enabled: false
    # Proxy backend query fetch size. A larger value may increase the memory usage of ShardingSphere Proxy.
    # The default value is -1, which means set the minimum value for different JDBC drivers.
  proxy-backend-query-fetch-size: - 1
  check-duplicate-table-enabled: fals
Copy the code

Relevant configuration file located in: shardingsphere – proxy/shardingsphere – proxy – the bootstrap/SRC/main/resources/conf

The following are the configuration files that are enabled for each example. Do not start the configuration file and follow the steps below

Start a database with Docker:

docker run --name mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=root -d mysql:latest
Copy the code

Note: Single tests are used locally, so when one feature is tested, the configuration files for other features are commented out

Note: When connecting to ShardingSphere Proxy, the database name is the logical name of Proxy: schemaName. Change the database name accordingly

Data fragmentation

1. Initialize the database

Use the following statement to create the relevant database

CREATE SCHEMA IF NOT EXISTS demo_ds_0;
CREATE SCHEMA IF NOT EXISTS demo_ds_1;
Copy the code

2. ShardingSphere Proxy configuration

Let go of configuration: shardingsphere – proxy/shardingsphere – proxy – the bootstrap/SRC/main/resources/conf/config – sharding. Yaml

Simply change the password. The configuration is as follows:

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
    minPoolSize: 1
  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
    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

After the configuration is complete, restart the system

3. Run the example

Modify the configuration: examples/shardingsphere-proxy-example/shardingsphere-proxy-boot-mybatis-example/src/main/resources/application.propertie s

To match the database name and password, the configuration is roughly as follows:

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
spring.datasource.username=root
spring.datasource.password=root
Copy the code

Once started, we see the relevant ShardingSphere Proxy log:

Logic SQL: select @@session.transaction_read_only SQLStatement: MySQLSelectStatement(limit=Optional.empty, lock=Optional.empty, window=Optional.empty) Actual SQL: ds_0 ::: select @@session.transaction_read_only Logic SQL: INSERT INTO t_order_item (order_id, user_id, status) VALUES (? ,? ,?) ; SQLStatement: MySQLInsertStatement(setAssignment=Optional.empty, onDuplicateKeyColumns=Optional.empty) Actual SQL: ds_0 ::: INSERT INTO t_order_item_0 (order_id, user_id, status, order_item_id) VALUES (? ,? ,? ,?) ; ::: [637039855574429696, 10, INSERT_TEST, 637039855616372737] Logic SQL: select @@session.transaction_read_only SQLStatement: MySQLSelectStatement(limit=Optional.empty, lock=Optional.empty, window=Optional.empty) Actual SQL: ds_0 ::: select @@session.transaction_read_only Logic SQL: SELECT * FROM t_order; SQLStatement: MySQLSelectStatement(limit=Optional.empty, lock=Optional.empty, window=Optional.empty) Actual SQL: ds_0 ::: SELECT * FROM t_order_0 ORDER BY order_id ASC ; Actual SQL: ds_0 ::: SELECT * FROM t_order_1 ORDER BY order_id ASC ; Actual SQL: ds_1 ::: SELECT * FROM t_order_0 ORDER BY order_id ASC ; Actual SQL: ds_1 ::: SELECT * FROM t_order_1 ORDER BY order_id ASC ;Copy the code

As you can see from above, sharding works

Next, run the following example, remembering the configuration of the ShardingSphere Proxy environment

Reading and writing separation

1. Initialize the database

Use the following statement to create the relevant database

CREATE SCHEMA IF NOT EXISTS demo_write_ds;
CREATE SCHEMA IF NOT EXISTS demo_read_ds_0;
CREATE SCHEMA IF NOT EXISTS demo_read_ds_1;

CREATE TABLE IF NOT EXISTS demo_read_ds_0.t_order (order_id BIGINT NOT NULL AUTO_INCREMENT, user_id INT NOT NULL, status VARCHAR(50), PRIMARY KEY (order_id));
CREATE TABLE IF NOT EXISTS demo_read_ds_1.t_order (order_id BIGINT NOT NULL AUTO_INCREMENT, user_id INT NOT NULL, status VARCHAR(50), PRIMARY KEY (order_id));
CREATE TABLE IF NOT EXISTS demo_read_ds_0.t_order_item (order_item_id BIGINT NOT NULL AUTO_INCREMENT, order_id BIGINT NOT NULL, user_id INT NOT NULL, status VARCHAR(50), PRIMARY KEY (order_item_id));
CREATE TABLE IF NOT EXISTS demo_read_ds_1.t_order_item (order_item_id BIGINT NOT NULL AUTO_INCREMENT, order_id BIGINT NOT NULL, user_id INT NOT NULL, status VARCHAR(50), PRIMARY KEY (order_item_id));
Copy the code

2. ShardingSphere Proxy configuration

We let go of configuration: shardingsphere – proxy/shardingsphere – proxy – the bootstrap/SRC/main/resources/conf/config – readwrite – splitting. Yaml

Release the configuration and change the password as follows:

Remember that our database is readwrite_splitting_db

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

After the configuration is complete, restart the system

3. Run the example

Use the official example: examples/shardingsphere-proxy-example/shardingsphere-proxy-boot-mybatis-example/src/main/java/org/apache/shardingsphere/ example/proxy/spring/boot/mybatis/ProxySpringBootStarterExample.java

To modify the configuration: examples/shardingsphere-proxy-example/shardingsphere-proxy-boot-mybatis-example/src/main/resources/application.propertie s

Change the database name to readwrite_splitting_db as follows:

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
spring.datasource.username=root
spring.datasource.password=root
Copy the code

Then we start, we see the log, the log is ShardingSphere Proxy:

SQLStatement: MySQLInsertStatement(setAssignment=Optional.empty, onDuplicateKeyColumns=Optional.empty) Actual SQL: write_ds ::: INSERT INTO t_order_item (order_id, user_id, status) VALUES (? ,? ,?) ; ::: [20, 10, INSERT_TEST] Logic SQL: select @@session.transaction_read_only SQLStatement: MySQLSelectStatement(limit=Optional.empty, lock=Optional.empty, window=Optional.empty) Actual SQL: read_ds_0 ::: select @@session.transaction_read_only Logic SQL: SELECT * FROM t_order; SQLStatement: MySQLSelectStatement(limit=Optional.empty, lock=Optional.empty, window=Optional.empty) Actual SQL: read_ds_1 ::: SELECT * FROM t_orderCopy the code

From the above log, we can see that the write is successful, and the query is also removed from the library, indicating that our read/write separation is effective

But it also proves that we failed yesterday with read-write separation, which we’ll have to look at later

Error at startup: Column index out of range.

It feels like a sample problem, but I don’t care about it for now.

This test is not finished, when trying the next one, remember to restore the configuration

Data encryption

1. Initialize the database

Execute the following statement to initialize the database:

CREATE SCHEMA IF NOT EXISTS demo_ds_0;
CREATE SCHEMA IF NOT EXISTS demo_ds_1;
Copy the code

2. ShardingSphere Proxy configuration

Let go of encryption configuration: shardingsphere – proxy/shardingsphere – proxy – the bootstrap/SRC/main/resources/conf/config – encrypt. Yaml

The database name is declared: encrypt_DB

Encrypt the following two fields: user_id/order_id

The configuration is as follows:

schemaName: encrypt_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
    minPoolSize: 1
  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
    minPoolSize: 1

rules:
- ! ENCRYPT
  encryptors:
    aes_encryptor:
      type: AES
      props:
        aes-key-value: 123456abc
    md5_encryptor:
      type: MD5
  tables:
    t_encrypt:
      columns:
        user_id:
          plainColumn: user_plain
          cipherColumn: user_cipher
          encryptorName: aes_encryptor
        order_id:
          cipherColumn: order_cipher
          encryptorName: md5_encrypto
Copy the code

After configuration modification, restart (remember to comment out the configuration of other configuration files)

3. Run the example

Modify the configuration: examples/shardingsphere-proxy-example/shardingsphere-proxy-boot-mybatis-example/src/main/resources/application.propertie s

Change the database password and database name: encrypt_db

The general configuration is:

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
spring.datasource.username=root
spring.datasource.password=root
Copy the code

Run the startup and see from the log that we are actually running and the log looks fine

conclusion

ShardingSphere Proxy is used in this file, and data sharding, data encryption and read/write separation are tried in separate operation

In the example, it seems that the three are separate, different configuration files, different logical database names

Can the three be combined or combined in pairs? According to the documentation, I think so. Let’s explore that later

Initial experience with ShardingSphere Proxy

Introduction to the

The sample run

ShardingSphere Proxy startup related

Data fragmentation

1. Initialize the database

2. ShardingSphere Proxy configuration

3. Run the example

Reading and writing separation

1. Initialize the database

2. ShardingSphere Proxy configuration

3. Run the example

Data encryption

1. Initialize the database

2. ShardingSphere Proxy configuration

3. Run the example

conclusion

Related Posts

How PHP programmers can easily implement a service Governance Architecture (PART 3)

Introduction to Python crawlers | Python Theme Month

With Springboot + RabbitMQ message confirmation, I feel like I’m in a hole