1. What is ShardingSphere

ShardingSphere is an open source distributed database middleware solution that provides database sharding, database replication, and distributed transaction support for various database management systems. It aims to help achieve horizontal scalability and data distribution in large and complex database systems.

Sharding: ShardingSphere provides database sharding, which involves splitting a large database into smaller, more manageable pieces called shards. Each shard can be stored on a separate database server. Sharding is especially useful for handling large volumes of data and high levels of concurrent access.

Data Replication: ShardingSphere supports various data replication methods, including master-slave and replica sets. This helps in achieving high availability, load balancing, and fault tolerance.

Distributed Transactions: It offers distributed transaction support, allowing you to manage transactions that span multiple databases or data sources.

SQL Routing: ShardingSphere can route SQL queries to the appropriate database shard based on predefined sharding rules.

Data Merging: It can merge data from multiple shards into a single result set, making it transparent to the application.

Read/Write Splitting: ShardingSphere supports read/write splitting, enabling you to direct read and write operations to different database instances, improving performance and reducing the load on the primary database.

Dynamic Data Source Management: It allows you to dynamically add or remove data sources without application restart, making it easy to scale your database infrastructure.

Compatible with Multiple Database Engines: ShardingSphere is compatible with various database engines, including MySQL, PostgreSQL, SQL Server, and more.

High Performance: ShardingSphere is designed for high performance, making it suitable for demanding applications and workloads.

Open Source and Community-Driven: ShardingSphere is open-source and has an active community that contributes to its development and maintenance.

2. How to install?

1
2
3
4
5
6
7
8
cd /to/your/path
# Download shardingsphere-proxy 5.3.0
wget "https://dlcdn.apache.org/shardingsphere/5.3.0/apache-shardingsphere-5.3.0-shardingsphere-proxy-bin.tar.gz"
tar -zxf apache-shardingsphere-5.3.0-shardingsphere-proxy-bin.tar.gz
cd /home/work/tool/app/apache-shardingsphere-5.3.0-shardingsphere-proxy-bin
cd /home/work/tool/app/apache-shardingsphere-5.3.0-shardingsphere-proxy-bin/lib
# Download mysql connection package
wget "https://repo1.maven.org/maven2/mysql/mysql-connector-java/5.1.47/mysql-connector-java-5.1.47.jar"

3. How to configure?

1
2
3
4
5
6
7
8
9
10
11
12
13
# server.yaml
# Account permission configuration
authority:
  users:
    - user: root@%
      password: ******
    - user: sharding
      password: ******
  privilege:
    type: ALL_PERMITTED
props:
  sql-show: true # output sql
  sql-simple: true # output simple sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
# config-sharding.yaml
# your database name
databaseName: db_name
 
dataSources:
 ds_0:
   url: jdbc:mysql://127.0.0.1:3306/db_name?serverTimezone=UTC&useSSL=false
   username:
   password:
   connectionTimeoutMilliseconds: 30000
   idleTimeoutMilliseconds: 60000
   maxLifetimeMilliseconds: 1800000
   maxPoolSize: 50
   minPoolSize: 1
 ds_1:
   url: jdbc:mysql://127.0.0.2:3306/db_name?serverTimezone=UTC&useSSL=false
   username:
   password:
   connectionTimeoutMilliseconds: 30000
   idleTimeoutMilliseconds: 60000
   maxLifetimeMilliseconds: 1800000
   maxPoolSize: 50
   minPoolSize: 1
 ds_2:
   url: jdbc:mysql://127.0.0.3:3306/db_name?serverTimezone=UTC&useSSL=false
   username:
   password:
   connectionTimeoutMilliseconds: 30000
   idleTimeoutMilliseconds: 60000
   maxLifetimeMilliseconds: 1800000
   maxPoolSize: 50
   minPoolSize: 1
 ds_3:
   url: jdbc:mysql://127.0.0.4:3306/db_name?serverTimezone=UTC&useSSL=false
   username:
   password:
   connectionTimeoutMilliseconds: 30000
   idleTimeoutMilliseconds: 60000
   maxLifetimeMilliseconds: 1800000
   maxPoolSize: 50
   minPoolSize: 1

rules:
- !SHARDING
  tables:
    # tb_test Mapping-table
    tb_test:
      # Separate into four databases, each database has 8 tables
      actualDataNodes: ds_0.tb_test_${[0,4,8,12,16,20,24,28]},ds_1.tb_test_${[1,5,9,13,17,21,25,29]},ds_2.tb_test_${[2,6,10,14,18,22,26,30]},ds_3.tb_test_${[3,7,11,15,19,23,27,31]}
      tableStrategy:
       standard:
         # Fields that separate tables
         shardingColumn: user_id
         # The function that separate tables
         shardingAlgorithmName: tb_test_inline
      # The primary key generation method uses the snowflake algorithm.
      keyGenerateStrategy:
        column: id
        keyGeneratorName: snowflake
  # default function that separate database
  defaultDatabaseStrategy:
    standard:
      shardingColumn: user_id
      shardingAlgorithmName: database_inline
  defaultTableStrategy:
    none:
  shardingAlgorithms:
    database_inline:
      type: INLINE
      props:
        algorithm-expression: ds_${user_id % 4}
    tb_test_inline:
      type: INLINE
      props:
        algorithm-expression: tb_test_${user_id % 32}
  keyGenerators:
    snowflake:
      type: SNOWFLAKE

4. How to start?

1
2
3
4
5
6
7
8
cd /to/your/path/apache-shardingsphere-5.3.0-shardingsphere-proxy-bin
# Start
sh start.sh
# Stop
sh stop.sh
 
# Log output file
 /home/work/tool/app/apache-shardingsphere-5.3.0-shardingsphere-proxy-bin/logs/stdout.log