Introduction to the

In the previous article, ShardingSphere JDBC version of the sub-library sub-table read-write separation data encryption and other functions, today through ShardingSphere example code, to study the proxy version of these corresponding functions.

Shardingsphere-proxy’s advantage lies in its support for heterogeneous languages, as well as providing an operational portal for DBAs. Shardingsphere-jdbc supports only Java code. Shardingsphere-proxy supports any version of any language

download

Download the tar packages

ShardingSphere official offers tar download www.apache.org/dyn/closer…. Download it and unzip it

If you want to use mysql, copy the appropriate driver package to the project lib directory

(download address: repo1.maven.org/maven2/mysq…).

Configure database and table rules

Shardingsphere-proxy provides only the CONFIGURATION mode based on YAML.

Start by configuring serverL.yML

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

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
  proxy-opentracing-enabled: false
  proxy-hint-enabled: false
  query-with-cipher-column: true
  sql-show: false
  check-table-metadata-enabled: false


Copy the code

Then configure config-sharding.yml

SchemaName: sharding_db dataSources: ds_0: url: JDBC: mysql: / / 127.0.0.1:3306 / demo_ds_0? serverTimezone=UTC&useSSL=false username: root password: connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 50 minPoolSize: 1 maintenanceIntervalMilliseconds: 30000 ds_1: url: JDBC: mysql: / / 127.0.0.1:3306 / demo_ds_1? serverTimezone=UTC&useSSL=false username: root password: connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 50 minPoolSize: 1 maintenanceIntervalMilliseconds: 30000 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 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 props: worker-id: 123Copy the code

Start the result

Check the startup log in stdout.log:

Sub-database sub-table test table

Use in sharding_db

CREATE TABLE `t_order` (  
  `order_id` bigint(20) NOT NULL AUTO_INCREMENT,  
  `user_id` int(11) NOT NULL,  
  `status` varchar(50) COLLATE utf8_bin DEFAULT NULL,  
 PRIMARY KEY (`order_id`) ) 
 ENGINE=InnoDB AUTO_INCREMENT=279205305122816001 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
Copy the code

The tables T_ORDER_0 and T_ORDER_0 are created in ds_1 and DS_0 respectively and then the INSERT statement is executed

INSERT INTO `t_order` (`order_id`, `user_id`, `status`) VALUES (11 ,  0 ,  '2');
Copy the code

The proxy will shard the good data directly

Reading and writing separation

Config-replica-query.xml is first configured

SchemaName: replica_query_db dataSources: primary_ds: url: JDBC: mysql: / / 127.0.0.1:3306 / demo_ds_master? serverTimezone=UTC&useSSL=false username: root password: root connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 50 minPoolSize: 1 maintenanceIntervalMilliseconds: 30000 replica_ds_0: url: JDBC: mysql: / / 127.0.0.1:3306 / demo_ds_slave_0? serverTimezone=UTC&useSSL=false username: root password: root connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 50 minPoolSize: 1 maintenanceIntervalMilliseconds: 30000 replica_ds_1: url: JDBC: mysql: / / 127.0.0.1:3306 / demo_ds_slave_1? serverTimezone=UTC&useSSL=false username: root password: root connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 50 minPoolSize: 1 maintenanceIntervalMilliseconds: 30000 rules: - ! REPLICA_QUERY dataSources: pr_ds: name: pr_ds primaryDataSourceName: primary_ds replicaDataSourceNames: - replica_ds_0 - replica_ds_1Copy the code

Create demo_ds_master, demo_ds_slave_0, demo_ds_slave_1 libraries in mysql. Change the library in server.yaml to replica_query_db

  users:
    root:
      password: root
    test:
      password: test
      authorizedSchemas: replica_query_db
Copy the code

Test connections are available:

mysql> show databases;

+------------------+

| Database         |

+------------------+

| replica_query_db |

+------------------+

Copy the code

Create tables and test data using SQL

CREATE TABLE `t_order` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Copy the code

INSERT INTO `t_order` (`id`, `name`) VALUES (1 ,  'jam' );
Copy the code

There is an important point here: Read/write separation of Sharding Proxy does not automatically implement master/slave replication. You need to configure master/slave replication for the database itself.

So we manually added two copies of data from the library to test read/write separation:

mysql> SELECT * FROM `order`; +------+--------+ | id | name | +------+--------+ | 1 | slave0 | +------+--------+ mysql> SELECT * FROM `order`; + -- -- -- -- -- - + -- -- -- -- -- -- -- -- + | | id name | + -- -- -- -- -- - + -- -- -- -- -- -- -- -- + | 1 | slave1 | + -- -- -- -- -- - + -- -- -- -- -- -- -- -- + 1 row in the set (0.02 SEC)Copy the code

The query is evenly found in the secondary database, but not in the primary database. Read/write separation is successful

The problem

The library 3307 connected using Navicat reported an exception

10002 - 2Unknown exception: [Can not route tables for `[ENGINES]`, please make sure the tables are in same schema.]
Copy the code

Successful connection with command:

Mysql -u sharding -psharding -h 127.0.0.1 -p 3307Copy the code

mysql> show databases; + -- -- -- -- -- -- -- -- -- -- -- -- -- + | Database | + -- -- -- -- -- -- -- -- -- -- -- -- -- + | sharding_db | + -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.02 SEC)Copy the code

This exception already exists in git issue github.com/apache/shar… As I understand it, Navicat will run a bunch of SQL to query the DB schema when connecting. However, part of SharingSphere-Proxy is not fully supported. It is expected that the later version will gradually fix this problem.