@[toc]

Quick use of ShardingProxy

The function of ShardingProxy is to divide databases and tables, but it is an independently deployed server that provides unified database proxy service. Note that ShardingProxy currently supports only MySQL and PostgreSQL. Also, when the client connects to ShardingProxy, it is best to use MySQL’s JDBC client. Let’s deploy a ShardingProxy service.

1. ShardingProxy deployment

ShardingProxy provides a unified deployment distribution package on Windows and Linux. We can download the 4.1.1 version of ShardingProxy release package apache-ShardingSphere-4.1.1-sharding-proxy-bin.tar. gz from the official website of ShardingSphere and decompress it to the local directory. The supporting materials are provided

Be careful not to have Chinese paths

First, we need to manually copy the MySQL JDBC driver package mysql-connector-java-8.0.20.jar to the lib directory of ShardingProxy. By default, ShardingProxy ships with only the PostgreSQL JDBC driver package, not the MySQL JDBC driver package.

Then, we need to go to the conf directory, modify server.yaml, and open the authentication and props comments in the configuration file.

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

props:
  max.connections.size.per.query: 1
  acceptor.size: 16  # The default value is available processors count * 2.
  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
  allow.range.query.with.inline.sharding: false
Copy the code

Then, we modify config-sharding.yaml in the conf directory. This configuration file is the shardingProxy configuration for the database and table parts. The entire configuration is roughly the same as when we used ShardingJDBC. We added the following configuration at the bottom according to our database environment:

schemaName: sharding_db

dataSources:
  m1:
    url: jdbc:mysql://localhost:3306/userdb? serverTimezone=GMT%2B8&useSSL=false
    username: root
    password: root
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50

shardingRule:
  tables:
    course:
      actualDataNodes: m1.course_$->{1.. 2}
      tableStrategy:
        inline:
          shardingColumn: cid
          algorithmExpression: course_$->{cid%2+1}
      keyGenerator:
        type: SNOWFLAKE
        column: cid
Copy the code

This section is configured according to the rules in our earlier application01.properties file. As you can see, the overall configuration is pretty much the same.

And then, one little thing to notice is that when we go into the Lib directory of ShardingProxy, we’re going to have jar packages in there that are so long that some of the file suffixes are truncated, and we’re going to have to manually change their file suffixes.

Then we can start the ShardingProxy service. The startup script is in the bin directory. The Windows script is start.bat, and the Linux script is start.sh and stop.sh

At startup, we can run the start.bat script directly, and ShardingProxy uses port 3307 by default. In order not to conflict with the multiple MySQL service ports we set up before, we customized the startup port to 3316.

start.bat 3316
Copy the code

Why is stop.bat not available on Windows? Since start.bat occupies a command line window, closing the command line window will stop the ShardingProxy service.

After the startup is complete, you can see several lines of key log identification that the service started successfully.

[the main] [INFO] 10:46:53. 930 C.A.D.X a.X ATransactionalResource - resource - 1 - m1: Checkxaresource [INFO] 10:46:54.580 [main] Shardingsphere-metadata-loading 1 logic tables' metadata. [INFO] 10:46:56.717 [main] Shardingsphere-metadata-loading 8 tables' metadata. [INFO] 10:46:56.953 [NioEventLoopGroup-2-1] i.n.handler.logging.LoggingHandler - [id: 0 xc90e0eef] REGISTERED [INFO] 10:46:56. 958] [nioEventLoopGroup - 2-1 I.N.H andler. Logging. LoggingHandler - [id: 0xc90e0eef] BIND: 0.0.0.0/0.0.0.0:3316 [INFO] 10:46:56. 960] [nioEventLoopGroup - 2-1 I.N.H andler. Logging. LoggingHandler - [id: 0xc90e0eef, L:/0:0:0:0:0:0:0:0:3316] ACTIVECopy the code

2, ShardingProxy use

In this way, we can connect to ShardingProxy just like a standard MySQL service.

D:\dev-hook\mysql-8.0.20-winx64\bin>mysql.exe -p3316 -uroot -p Enter password: **** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 8.0.20 -Sharding-proxy 4.1.0 Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help; ' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; + -- -- -- -- -- -- -- -- -- -- -- -- -- + | Database | + -- -- -- -- -- -- -- -- -- -- -- -- -- + | sharding_db | + -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.03 SEC) mysql > use sharding_db Database changed mysql> show tables; +--------------------+ | Tables_in_coursedb | +--------------------+ | course | | t_dict | +--------------------+ 2 rows In set (0.16sec) mysql> select * from course; +--------------------+-------+---------+---------+ | cid | cname | user_id | cstatus | +--------------------+-------+---------+---------+ | 545730330389118976 | java | 1001 | 1 | | 545730330804355072 | java | 1001 | 1 | | 545730330842103808 | java | 1001 | 1 | | 545730330879852544 | java | 1001 | 1 | | 545730330917601280 | Java | 1001 | 1 | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 5 rows in the set (0.08 SEC)Copy the code

Before in the ShardingJDBC part of the completed several other types of database sub-table strategy and read and write separation strategy, please verify by yourself.

3. Service governance of ShardingProxy

As you can see from ShardingProxy’s server.yaml, ShardingProxy also supports a very large number of service governance functions. The orchestration attribute in the server.yaml configuration file demonstrates how to register ShardingProxy with Zookeeper.

orchestration:
  orchestration_ds:
    orchestrationType: registry_center,config_center,distributed_lock_manager
    instanceType: zookeeper
    serverLists: localhost:2181
    namespace: orchestration
    props:
      overwrite: false
      retryIntervalMilliseconds: 500
      timeToLiveSeconds: 60
      maxRetries: 3
      operationTimeoutMilliseconds: 500
Copy the code

In terms of service governance, ShardingSphere mainly has two parts:

One is data access and elastic scaling. The simple understanding is to quickly migrate the data of MySQL or other data sources into the sharding library of ShardingSphere. And it can quickly expand and subdivide the existing ShardingShere sharding library. This is supported by the product ShardingSphere-Scaling. However, this feature is currently in the Alpha testing phase of version 4.1.1.

ShardingSphere, on the other hand, enables the uploading of complex repository and table configurations to a unified registry for centralized management. Zookeeper and Etcd are currently supported registries. ShardingSphere also provides SPI extension interface for quick access to Nacos, Apollo and other registries. We have already seen an example of this part of the configuration in ShardingProxy’s server.yaml.

In addition, ShardingSphere provides a ShardingSphere-UI product to provide page support for these ecological functions. Shardingsphere-ui is a simple and useful Web administration console for the entire ShardingSphere. It is used to help users to use related functions of ShardingSphere more easily. It provides registry management, dynamic configuration management, database orchestration management and other functions.

The running package of the latest version 5.0.0-alpha of ShardingSphere-UI is also collected in the supporting materials. After decompression, execute the start.bat in it to run directly.

4. Other functions of Shardingproxy

Shadow library

This part of the function is mainly used for pressure measurement. By means of the key to the production environment configuration database table a shadow library that can be written to the production data to write all the shadow in the library, and shadow library is usually configured to follow production environment in the same library, so you can directly to conduct stress tests on the production environment, and will not affect production data.

There is an example of how to configure a shadow library in conf/config-shadow.yaml. Among them, the core is the shadowRule part below.

# # # shadowRule: column: shadow shadowMappings: # binding shadow_ds for the shadow of the ds library # ds: shadow_dsCopy the code

Data encryption

Another function of ShardingProxy, data encryption, is also demonstrated in conf/config-encrypt.yaml. AES symmetric encryption and MD5 encryption are integrated by default. You can also extend more encryption algorithms on your own through the SPI mechanism.

5. SPI extension of ShardingProxy

The previous section mentioned that ShardingSphere retains a large number of SPI extension interfaces, closed to the main process and open to SPI. This doesn’t make much sense in ShardingJDBC, but in ShardingProxy it can greatly improve the flexibility of the service.

In ShardingProxy, you only need to type the custom extension functions into JAR packages according to the SPI mechanism, and you can directly put the JAR packages into the lib directory, and then configure and use them.

For example, if you want to extend a new primary key generation strategy, you can simply develop your own primary key generation class

package com.roy.shardingDemo.spiextention;

import org.apache.shardingsphere.spi.keygen.ShardingKeyGenerator;

import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.Properties;
import java.util.concurrent.atomic.AtomicLong;

/ * * *@author: the loulan *@date: Created in 2020/12/17 *@description: * * /

public final class MykeyGenerator implements ShardingKeyGenerator {

    private AtomicLong atom = new AtomicLong(0);

    private Properties properties = new Properties();

    public synchronizedComparable<? > generateKey() {// Read a custom property
        String prefix = properties.getProperty("mykey-offset"."100");
        LocalDateTime ldt = LocalDateTime.now();
        String timestampS = DateTimeFormatter.ofPattern("HHmmssSSS").format(ldt);
        return Long.parseLong(""+prefix+timestampS+atom.incrementAndGet());
    }
    // Extend the type of algorithm
    public String getType(a) {
        return "MYKEY";
    }

    public Properties getProperties(a) {
        return this.properties;
    }

    public void setProperties(Properties properties) {
        this.properties = properties; }}Copy the code

Then add a meta-inf/services/org. Apache. Shardingsphere. Spi. The keygen. ShardingKeyGenerator file, and the implementation class in the file.com.roy.shardingDemo.spiextention.MykeyGeneratorPut the extension class and the SPI service file into a JAR package and place it directly in the Lib directory of ShardingProxy.

This can then be introduced in config-sharding. Yaml in a configuration similar to the following.

shardingRule:
  tables:
    course:
      actualDataNodes: m1.course_$->{1.. 2}
      tableStrategy:
        inline:
          shardingColumn: cid
          algorithmExpression: course_$->{cid%2+1}
      keyGenerator:
# type: SNOWFLAKE
        type: MYKEY # Custom primary key generator
        column: cid

Copy the code

Then we can start ShardingProxy and try our own custom primary key generator.

mysql> select * from course; +--------------------+-------+---------+---------+ | cid | cname | user_id | cstatus | +--------------------+-------+---------+---------+ | 222 | java2 | 1002 | 1 | | 545730330389118976 | java | 1001 | 1 | |  545730330804355072 | java | 1001 | 1 | | 545730330842103808 | java | 1001 | 1 | | 545730330879852544 | java | 1001 | 1 Java | | 545730330917601280 | | 1001 | 1 | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 6 rows in the set (0.01 SEC) mysql> insert into course(cname,user_id,cstatus) values ('java2',1002,'1'); Query OK, 1 row affected (0.11 SEC) mysql> insert into score (cname,user_id,cstatus) values ('java2',1003,'1'); Query OK, 1 row affected (0.01sec) mysql> select * from course; +--------------------+-------+---------+---------+ | cid | cname | user_id | cstatus | +--------------------+-------+---------+---------+ | 222 | java2 | 1002 | 1 | | 1001509178012 | java2 | 1003 | 1 | | 545730330389118976 | java | 1001 | 1 | | 545730330804355072 | java | 1001 | 1 | | 545730330842103808 | java | 1001 | 1 |  | 545730330879852544 | java | 1001 | 1 | | 545730330917601280 | java | 1001 | 1 | | 1001509119631 | java2 | 1002 | 1 | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 8 rows in the set (0.01 SEC)Copy the code

According to the result, the CID of the two records inserted is 1001509178012 and 1001509119631. In this way, we quickly complete a custom primary key generation policy.

As for SPI extension points of ShardingSphere, there are more comprehensive and detailed descriptions in the developer manual section of the supporting material “ShardingSphere_DOCS_CN. PDF”.

2. Summary of ShardingSphere

Now we have learned all products of ShardingSphere except sharding-sidecar. In essence, all these functions of Sharding + proxy only solve one problem, that is, the capacity of stand-alone database. Hardware resources are managed at the software level to facilitate the horizontal expansion of the database.

However, we should also be aware of the many problems he brings.

For example, the intrusion into the business. The SQL written by the business system will no longer be pure SQL that can be run on the server, and it is difficult to verify a large number of cross-dimensional JOIN, aggregation, subquery, sort and other functions in the business. This will inevitably weaken the functionality of the database.

In addition, after ShardingSphere management, databases become closely dependent, which will bring considerable difficulty to the expansion of the whole cluster.

In addition, ShardingSphere actually transforms the original working mode of business management SQL into that of business management logic SQL, and the mixed working mode of operation and maintenance management of actual SQL, coupled with the introduction of a lot of services, increases the maintenance workload and difficulty of the whole service operation and maintenance to a great extent.

Of course, it is believed that with the continuous upgrading and optimization of the subsequent versions of ShardingSphere, these problems will be improved to varying degrees.

Comparison with other related products