ShardingSphere

Apache ShardingSphere is an ecosystem of open source distributed database middleware solutions. It is composed of JDBC, Proxy and Sidecar (under planning), which are independent but can be deployed and used together. They all provide standardized data sharding, distributed transactions, and database governance functions, and can be applied to diverse application scenarios such as Java isomorphism, heterogeneous languages, and cloud native.

Apache ShardingSphere is positioned as relational database middleware, aiming to fully and reasonably utilize the computing and storage capabilities of relational databases in distributed scenarios, rather than realizing a new relational database. It captures the essence of things by focusing on the immutable. Relational databases still occupy a huge market today, and are the cornerstone of each company’s core business, and will be difficult to shake in the future. At present, we are more focused on incremental, rather than subversive, on the original foundation.

Apache ShardingSphere 5.x is dedicated to pluggable architecture, and the functional components of the project can be flexibly expanded in a pluggable manner. At present, functions such as data sharding, read/write separation, multiple data copies, data encryption, shadow library pressure measurement, as well as SQL and protocol support such as MySQL, PostgreSQL, SQLServer and Oracle are woven into the project through plug-ins. Developers can customize their own unique systems like building blocks. Apache ShardingSphere currently offers dozens of SPIs as extension points to the system, and the list is still growing.

Sharding-JDBC

Positioned as a lightweight Java framework that provides additional services in Java’s JDBC layer. It uses the client directly connected to the database, in the form of JAR package to provide services, without additional deployment and dependence, can be understood as an enhanced VERSION of THE JDBC driver, fully compatible with JDBC and various ORM frameworks.

Works with any JDBC-based ORM framework, such as JPA, Hibernate, Mybatis, Spring JDBC Template or directly using JDBC.
Support any third party database connection pool, such as DBCP, C3P0, BoneCP, Druid, HikariCP, etc.
Support for any database that implements the JDBC specification. Currently supports MySQL, Oracle, SQLServer, PostgreSQL and any database that complies with the SQL92 standard.

Sharding-JDBC implementation level table

The convention rules: Add data to course_0 if the course ID is even, and add data to course_1 if it is odd

Pom depends on

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.3.2 rainfall distribution on 10-12</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.1.22</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.0.0 - RC1</version>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <optional>true</optional>
</dependency>
Copy the code

Create databases and database tables as horizontal sub-tables

CREATE TABLE `course_0` (
  `cid` bigint NOT NULL,
  `cname` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `user_id` bigint NOT NULL,
  `status` varchar(10) NOT NULL.PRIMARY KEY (`cid`)
) ENGINE=InnoDB;

CREATE TABLE `course_1` (
  `cid` bigint NOT NULL,
  `cname` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `user_id` bigint NOT NULL,
  `status` varchar(10) NOT NULL.PRIMARY KEY (`cid`)
) ENGINE=InnoDB;
Copy the code

Configure the application. The properties

# shardingJDBC sharding policy
Create a name for the data source
spring.shardingsphere.datasource.names=m1
# One entity class for two tables, override
spring.main.allow-bean-definition-overriding=true
Configure the first data source details, including connection pool, driver, address, username and password
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=JDBC: mysql: / / 192.168.2.128:3306 / sharding_jdbc? serverTimezone=GMT%2B8&characterEncoding=UTF-8
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=root
M1. course_0 m1.course_1
spring.shardingsphere.sharding.tables.course.actual-data-nodes=m1.course_${0.. 1}
Select primary key CID from course table;
spring.shardingsphere.sharding.tables.course.key-generator.column=cid
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE
Add even cid values to the course_1 table, or add odd cid values to the course_2 table
spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=cid
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_${ cid %2 }
# open SQL output log
spring.shardingsphere.props.sql.show=true
Copy the code

Write the code

Entity class

@Data
@Accessors(chain = true)
public class Course {

    private Long cid;
    private String cname;
    private Long userId;
    private String status;
}
Copy the code

The mapper class

public interface CourseMapper extends BaseMapper<Course> {}Copy the code

Start the class add @ MapperScan (” com. CGP. Shardingjdbc. Mapper “)

Writing test classes

@Resource
private CourseMapper courseMapper;

@Test
void addCourse(a) {
    for (int i = 1; i <= 10; i++) {
        Course course = new Course();
        course.setCname("Java").setUserId(100L + i).setStatus("Normal"+ i); courseMapper.insert(course); }}@Test
void findCourse(a) {
    Course course = courseMapper.selectOne(new QueryWrapper<Course>().eq("cid".476417513358360577L));
    System.out.println(course);
}
// Delete...
Copy the code

Sharding-JDBC implementation of horizontal branch library

Specifies that the table sharding policy convention CID values even are added to the course_0 table, or if the CID is odd, to the course_1 table

Specify the database sharding policy convention where user_id is an even number and m1 is an odd number

Modify the properties file according to the above code

# shardingJDBC sharding policy
Create a name for the data source
Configure two data sources
spring.shardingsphere.datasource.names=m0,m1
# One entity class for two tables, override
spring.main.allow-bean-definition-overriding=true
Configure the first data source details, including connection pool, driver, address, username and password
spring.shardingsphere.datasource.m0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m0.url=JDBC: mysql: / / 192.168.2.128:3306 / sharding_jdbc_1? serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m0.username=root
spring.shardingsphere.datasource.m0.password=root
Configure the second data source details, including connection pool, driver, address, username and password
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=JDBC: mysql: / / 192.168.2.128:3306 / sharding_jdbc_2? serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=root
Select * from database where tables are distributed
# m1 m2 course_0 course_1
spring.shardingsphere.sharding.tables.course.actual-data-nodes=m$->{0.. 1}.course_$->{0.. 1}
Select * from cid where primary key = 'SNOWFLAKE'
spring.shardingsphere.sharding.tables.course.key-generator.column=cid
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE
Add even cid values to the course_0 table, or add them to the course_1 table if the CID is odd
spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=cid
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{cid % 2}
Add m0 if user_id is even, add m1 if odd
#spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=user_id
#spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=m$->{user_id % 2}
spring.shardingsphere.sharding.tables.course.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.course.database-strategy.inline.algorithm-expression=m$->{user_id % 2}
# open SQL output log
spring.shardingsphere.props.sql.show=true
Copy the code

The same as above

Writing test classes

@Test
void addCourseDb(a) {
    for (int i = 1; i <= 10; i++) {
        Course course = new Course();
        course.setCname("JavaDB").setUserId(100L).setStatus("Normal");
        courseMapper.insert(course);
    }
    for (int i = 1; i <= 10; i++) {
        Course course = new Course();
        course.setCname("JavaDB").setUserId(101L).setStatus("Normal"); courseMapper.insert(course); }}// Check...
Copy the code

After running, it is found that there are 5 data in 4 tables in 2 libraries

Sharding-JDBC implementation of vertical library

Modify the properties file to add the vertical branch configuration

# shardingJDBC sharding policy
Create a name for the data source
# Vertical branch database, new M2 data source
spring.shardingsphere.datasource.names=m0,m1,m2
# One entity class for two tables, override
spring.main.allow-bean-definition-overriding=true
# = = = = = = = = = = = = = = = = = = = = = = = = vertical depots configuration start = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
Configure the third data source specific content, vertical branch database
spring.shardingsphere.datasource.m2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m2.url=JDBC: mysql: / / 192.168.2.128:3306 / sharding_jdbc_user? serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m2.username=root
spring.shardingsphere.datasource.m2.password=root
Select * from t_user; select * from t_user
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=m2.t_user
Select * from t_user where user_id = 'SNOWFLAKE'
spring.shardingsphere.sharding.tables.t_user.key-generator.column=user_id
spring.shardingsphere.sharding.tables.t_user.key-generator.type=SNOWFLAKE
# specify vertical shard policy
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.algorithm-expression=t_user
# = = = = = = = = = = = = = = = = = = = = = = = = end of vertical depots configuration = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
Configure the first data source details, including connection pool, driver, address, username and password
spring.shardingsphere.datasource.m0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m0.url=JDBC: mysql: / / 192.168.2.128:3306 / sharding_jdbc_1? serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m0.username=root
spring.shardingsphere.datasource.m0.password=root
Configure the second data source details, including connection pool, driver, address, username and password
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=JDBC: mysql: / / 192.168.2.128:3306 / sharding_jdbc_2? serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=root
Select * from database where tables are distributed
# m1 m2 course_0 course_1
spring.shardingsphere.sharding.tables.course.actual-data-nodes=m$->{0.. 1}.course_$->{0.. 1}
Select * from cid where primary key = 'SNOWFLAKE'
spring.shardingsphere.sharding.tables.course.key-generator.column=cid
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE
Add even cid values to the course_0 table, or add them to the course_1 table if the CID is odd
spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=cid
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{cid % 2}
Add m0 if user_id is even, add m1 if odd
#spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=user_id
#spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=m$->{user_id % 2}
spring.shardingsphere.sharding.tables.course.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.course.database-strategy.inline.algorithm-expression=m$->{user_id % 2}
# open SQL output log
spring.shardingsphere.props.sql.show=true
Copy the code

Build table statements

CREATE TABLE `t_user` (
  `user_id` bigint NOT NULL,
  `username` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `age` int NOT NULL.PRIMARY KEY (`user_id`)
) ENGINE=InnoDB;
Copy the code

Entity class and Mapper class

@Data
@TableName("t_user")
@Accessors(chain = true)
public class User {
    private Long userId;
    private String username;
    private Integer age;
}
Copy the code

public interface UserMapper extends BaseMapper<User> {}Copy the code

The test class

@Resource
private UserMapper userMapper;

@Test
void addUser(a) {
    User user = new User();
    user.setUsername("Manaphy").setAge(18);
    userMapper.insert(user);
}
// Check...
Copy the code

Sharding-JDBC operates on common tables

role

(1) the table that stores fixed data, the table data rarely changes, and the query is often associated

(2) Create a common table with the same structure in each database

Modify the properties file to add common table configuration

# shardingJDBC sharding policy
Create a name for the data source
# Vertical branch database, new M2 data source
spring.shardingsphere.datasource.names=m0,m1,m2
# One entity class for two tables, override
spring.main.allow-bean-definition-overriding=true
# = = = = = = = = = = = = = = = = = = = = = = = = configure common table start = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
Configure the public table
spring.shardingsphere.sharding.broadcast-tables=t_dict
# specify public table inside primary key dict_id generate strategy SNOWFLAKE
spring.shardingsphere.sharding.tables.t_dict.key-generator.column=dict_id
spring.shardingsphere.sharding.tables.t_dict.key-generator.type=SNOWFLAKE
# = = = = = = = = = = = = = = = = = = = = = = = = configure common table end = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
# = = = = = = = = = = = = = = = = = = = = = = = = vertical depots configuration start = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
Configure the third data source specific content, vertical branch database
spring.shardingsphere.datasource.m2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m2.url=JDBC: mysql: / / 192.168.2.128:3306 / sharding_jdbc_user? serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m2.username=root
spring.shardingsphere.datasource.m2.password=root
Select * from t_user; select * from t_user
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=m2.t_user
Select * from t_user where user_id = 'SNOWFLAKE'
spring.shardingsphere.sharding.tables.t_user.key-generator.column=user_id
spring.shardingsphere.sharding.tables.t_user.key-generator.type=SNOWFLAKE
# specify vertical shard policy
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.algorithm-expression=t_user
# = = = = = = = = = = = = = = = = = = = = = = = = end of vertical depots configuration = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
Configure the first data source details, including connection pool, driver, address, username and password
spring.shardingsphere.datasource.m0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m0.url=JDBC: mysql: / / 192.168.2.128:3306 / sharding_jdbc_1? serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m0.username=root
spring.shardingsphere.datasource.m0.password=root
Configure the second data source details, including connection pool, driver, address, username and password
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=JDBC: mysql: / / 192.168.2.128:3306 / sharding_jdbc_2? serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=root
Select * from database where tables are distributed
# m1 m2 course_0 course_1
spring.shardingsphere.sharding.tables.course.actual-data-nodes=m$->{0.. 1}.course_$->{0.. 1}
Select * from cid where primary key = 'SNOWFLAKE'
spring.shardingsphere.sharding.tables.course.key-generator.column=cid
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE
Add even cid values to the course_0 table, or add them to the course_1 table if the CID is odd
spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=cid
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{cid % 2}
Add m0 if user_id is even, add m1 if odd
#spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=user_id
#spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=m$->{user_id % 2}
spring.shardingsphere.sharding.tables.course.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.course.database-strategy.inline.algorithm-expression=m$->{user_id % 2}
# open SQL output log
spring.shardingsphere.props.sql.show=true
Copy the code

T_dict table is added to sharding_jdbc_user sharding_jdbc_1 and sharding_jdbc_2 tables

CREATE TABLE `t_dict` (
  `dict_id` bigint NOT NULL,
  `dict_name` varchar(100) NOT NULL,
  `dict_value` varchar(100) NOT NULL.PRIMARY KEY (`dict_id`)
) ENGINE=InnoDB;
Copy the code

Entity class and Mapper class

@Data
@TableName("t_dict")
@Accessors(chain = true)
public class Dict {
    private Long dictId;
    private String dictName;
    private String dictValue;
}
Copy the code

public interface DictMapper extends BaseMapper<Dict> {}Copy the code

The test class

@Resource
private DictMapper dictMapper;

@Test
void addDict(a) {
    Dict dict = new Dict();
    dict.setDictName("Test").setDictValue("Enable");
    dictMapper.insert(dict);
}
// Check...
Copy the code

The same data is found in the T_dict table in all three libraries

Sharding-JDBC implements read and write separation

Docker-based primary/secondary mysql replication see blog.csdn.net/weixin_4563…

Configure the application. The properties

# shardingJDBC sharding policy
Create a name for the data source
spring.shardingsphere.datasource.names=master,slave
# One entity class for two tables, override
spring.main.allow-bean-definition-overriding=true
Configure the master data source details, including connection pool, driver, address, username and password
spring.shardingsphere.datasource.master.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.master.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.master.url=JDBC: mysql: / / 192.168.2.128:3307 / sharding_jdbc? serverTimezone=GMT%2B8
spring.shardingsphere.datasource.master.username=root
spring.shardingsphere.datasource.master.password=123456
Configure the details from the data source, including connection pool, driver, address, username and password
spring.shardingsphere.datasource.slave.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.slave.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.slave.url=JDBC: mysql: / / 192.168.2.128:3308 / sharding_jdbc? serverTimezone=GMT%2B8
spring.shardingsphere.datasource.slave.username=root
spring.shardingsphere.datasource.slave.password=123456
# t_user split table policy, fixed allocation to DS0 sharDING_JDBC real table
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=ds0.course
Ds0 is sharDING_JDBC
spring.shardingsphere.sharding.master-slave-rules.ds0.master-data-source-name=master
spring.shardingsphere.sharding.master-slave-rules.ds0.slave-data-source-names=slave
Select primary key CID from course table;
spring.shardingsphere.sharding.tables.course.key-generator.column=cid
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE
# open SQL output log
spring.shardingsphere.props.sql.show=true
Copy the code

Create a new course table in the main database

Construct the same level sub-tables of table sentences, entity classes and Mapper classes

Writing test classes

@Test
void addCourseMaster(a) {
    Course course = new Course();
    course.setCname("Java").setUserId(100L).setStatus("Normal");
    courseMapper.insert(course);
}

@Test
void findCourseSlave(a) {
    Course course = courseMapper.selectOne(new QueryWrapper<Course>().eq("cid".476494916214587393L));
    System.out.println(course);
}

@Test
void modifyCourse(a) {
    QueryWrapper<Course> wrapper = new QueryWrapper<>();
    wrapper.eq("cid".476494916214587393L);
    Course course = courseMapper.selectOne(wrapper);
    course.setCname("Sharding");
    courseMapper.update(course, wrapper);
}

@Test
void deleteCourse(a) {
    QueryWrapper<Course> wrapper = new QueryWrapper<>();
    wrapper.eq("cid".476494916214587393L);
    courseMapper.delete(wrapper);
}
Copy the code

Sharding-Proxy

Positioned as a transparent database agent, providing server-side versions that encapsulate database binary protocols to complete support for heterogeneous languages. Currently available in MySQL and PostgreSQL versions, it can use any MySQL/PostgreSQL compliant access client (e.g. MySQL Command Client, MySQL Workbench, Navicat, etc.) manipulate data and are more DBA-friendly.

Fully transparent to applications and can be used directly as MySQL/PostgreSQL.
This works with any client that is compatible with MySQL/PostgreSQL.

Download and Installation

Download the software from the official website
After downloading the compressed file, decompress, start the bin directory to start the file can be
Check the lib folder if the extension is notThe version number. The jarI’m going to rename it to fill inThe version number. The jar

Sharding-proxy configuration (sub-table)

Go to the conf directory, modify the file server.yaml, and open two content comments

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
  query.with.cipher.column: true
  sql.show: false
Copy the code

Go to the conf directory and open config-sharding.yaml

You can see this sentence

# If you want to connect to MySQL, you should manually copy MySQL driver to lib directory.

Copy the mysql driver JAR package to the lib directory
Configure the database and table rules

schemaName: sharding_db

dataSources:
  ds_0:
    url: JDBC: mysql: / / 192.168.2.128:3306 / sharding_proxy? serverTimezone=UTC&useSSL=false
    username: root
    password: root
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50

shardingRule:
  tables:
    t_order:
      actualDataNodes: ds_0.t_order_${0.. 1}
      tableStrategy:
        inline:
          shardingColumn: order_id
          algorithmExpression: t_order_${order_id % 2}
      keyGenerator:
        type: SNOWFLAKE
        column: order_id
  bindingTables:
    - t_order
  defaultDatabaseStrategy:
    inline:
      shardingColumn: user_id
      algorithmExpression: ds_0
  defaultTableStrategy:
    none:
Copy the code

Create a databasesharding_proxyThe database
Start the Sharding-proxy servicebin/start.bat(Windows environment) orbin/start.sh(Linux environment)

[INFO ] 22:23:16.578 [nioEventLoopGroup-2-1] i.n.handler.logging.LoggingHandler - [id: 0x7e19000c, L:/0:0:0:0:0:0:0:0:3307] READ: [id: 0x096f3d30, L:/0:0:0:0:0:0:0:1:3307 - R:/0:0:0:0:0:0:0:1:52814]
[INFO ] 22:23:16.580 [nioEventLoopGroup-2-1] i.n.handler.logging.LoggingHandler - [id: 0x7e19000c, L:/0:0:0:0:0:0:0:0:3307] READ COMPLETE
Copy the code

If the preceding information is displayed in the last two lines, the startup is successful

Since tools like Navicat do not support Sharding_Proxy, you can only connect using the command line window

mysql -uroot -P3307 -uroot -proot
   
   mysql> show databases;
   +-------------+
   | Database    |
   +-------------+
   | sharding_db |
   +-------------+
   1 row in set (0.01 sec)
   mysql> use sharding_db;
   Database changed
   mysql> create table if not exists ds_0.t_order(order_id BIGINT NOT NULL,user_id INT NOT NULL,status VARCHAR(50),PRIMARY KEY (order_id)); Create table t_order Query OK,0 rows affected (0.28 sec)
   mysql> show tables;
   +--------------------------+
   | Tables_in_sharding_proxy |
   +--------------------------+
   | t_order                  |
   +--------------------------+
   1 row in set (0.03 sec)
   mysql> insert into t_order(order_id,user_id,status)values(11.1,'init'); SQL > insert data into table;1 row affected (0.10 sec)
   mysql> select * from t_order;
   +----------+---------+--------+
   | order_id | user_id | status |
   +----------+---------+--------+
   |       11 |       1 | init   |
   +----------+---------+--------+
   1 row in set (0.06 sec)
Copy the code

Go back to the actual database at 192.168.2.128:3306 and see that the table has been created and data has been added

Sharding-proxy configuration (Branch library)

Open the config-sharding.yaml file and modify the content

schemaName: sharding_db

dataSources:
  ds_0:
    url: JDBC: mysql: / / 192.168.2.128:3306 / sharding_jdbc_1? serverTimezone=UTC&useSSL=false
    username: root
    password: root
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
  ds_1:
    url: JDBC: mysql: / / 192.168.2.128:3306 / sharding_jdbc_2? serverTimezone=UTC&useSSL=false
    username: root
    password: root
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50

shardingRule:
  tables:
    t_order:
      actualDataNodes: ds_${0.. 1}.t_order_${0.. 1}
      tableStrategy:
        inline:
          shardingColumn: order_id
          algorithmExpression: t_order_${order_id % 2}
      keyGenerator:
        type: SNOWFLAKE
        column: order_id
      keyGenerator:
        type: SNOWFLAKE
        column: order_item_id
  bindingTables:
    - t_order,t_order_item
  defaultDatabaseStrategy:
    inline:
      shardingColumn: user_id
      algorithmExpression: ds_${user_id % 2}
  defaultTableStrategy:
    none:
Copy the code

Start the Sharding-proxy service
Use the command line window to connect

mysql -P3307 -uroot -proot

mysql> show databases;
+-------------+
| Database    |
+-------------+
| sharding_db |
+-------------+
1 row in set (0.01 sec)

mysql> use sharding_db;
Database changed
mysql> show tables;
Empty set (0.10 sec)

mysql> create table if not exists ds_0.t_order(order_id BIGINT NOT NULL,user_id INT NOT NULL,status VARCHAR(50),PRIMARY KEY (order_id)); Create table t_order Query OK,0 rows affected (0.32 sec)

mysql> show tables;
+---------------------------+
| Tables_in_sharding_jdbc_1 |
+---------------------------+
| t_order                   |
+---------------------------+
1 row in set (0.03 sec)

mysql> insert into t_order(order_id,user_id,status)values(11.1,'init'); SQL > insert data into table;1 row affected (0.11 sec)
Copy the code

Go back to the actual database at 192.168.2.128:3306 and see that the table has been created and data has been added

Sharding-proxy configuration (read/write separation)

Simplify operations => Create sharding_master sharding_slave_0 sharing_slave_1 sharding_master sharding_slave_0 Sharing_slave_1 databases in 192.168.2.128:3306.
Open the config-master_slave.yaml file and modify the content

schemaName: master_slave_db

dataSources:
  master_ds:
    url: JDBC: mysql: / / 192.168.174.129:3307 / sharding_master? serverTimezone=UTC&useSSL=false
    username: root
    password: 123456
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
  slave_ds_0:
    url: JDBC: mysql: / / 192.168.174.129:3307 / sharding_slave_0? serverTimezone=UTC&useSSL=false
    username: root
    password: 123456
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
  slave_ds_1:
    url: JDBC: mysql: / / 192.168.174.129:3307 / sharding_slave_1? serverTimezone=UTC&useSSL=false
    username: root
    password: 123456
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50

masterSlaveRule:
  name: ms_ds
  masterDataSourceName: master_ds
  slaveDataSourceNames:
    - slave_ds_0
    - slave_ds_1
Copy the code

Start the Sharding-proxy service
Use the command line window to connect

mysql -P3307 -uroot -proot

mysql> show databases;
+-----------------+
| Database        |
+-----------------+
| master_slave_db |
| sharding_db     |
+-----------------+
2 rows in set (0.38 sec)
mysql> use master_slave_db;
Database changed
mysql> create table if not exists sharding_master.t_order(order_id BIGINT NOT NULL,user_id INT NOT NULL,status VARCHAR(50),PRIMARY KEY (order_id));
Query OK, 0 rows affected (0.08 sec)

mysql> use master_slave_db;
Database changed
mysql> create table if not exists sharding_slave_0.t_order(order_id BIGINT NOT NULL,user_id INT NOT NULL,status VARCHAR(50),PRIMARY KEY (order_id));
Query OK, 0 rows affected (0.03 sec)

mysql> create table if not exists sharding_slave_1.t_order(order_id BIGINT NOT NULL,user_id INT NOT NULL,status VARCHAR(50),PRIMARY KEY (order_id));
Query OK, 0 rows affected (0.02Mysql > alter table mysql> alter table mysql> alter table mysql> alter table mysql> alter table mysql +---------------------------+ | Tables_in_sharding_master | +---------------------------+ | t_order | +---------------------------+1 row in set (0.01 sec)

mysql> insert into t_order(order_id,user_id,status)values(11.1,'init');
Query OK, 1 row affected (0.04 sec)
mysql> select * from t_order;
Empty set (0.05Mysql > insert into sharding_slave_0.t_order(order_id,user_id,status)values(12.1,'init');
Query OK, 1 row affected (0.03 sec)

mysql> insert into sharding_slave_1.t_order(order_id,user_id,status)values(13.1,'init');
Query OK, 1 row affected (0.01Mysql > select * from t_order; mysql> select * from t_order; +----------+---------+--------+ | order_id | user_id | status | +----------+---------+--------+ |12 |       1 | init   |
+----------+---------+--------+
1 row in set (0.01 sec)
mysql> select * from t_order;
+----------+---------+--------+
| order_id | user_id | status |
+----------+---------+--------+
|       13 |       1 | init   |
+----------+---------+--------+
1 row in set (0.01 sec)

mysql> select * from t_order;
+----------+---------+--------+
| order_id | user_id | status |
+----------+---------+--------+
|       12 |       1 | init   |
+----------+---------+--------+
1 row in set (0.00 sec)

mysql> select * from t_order;
+----------+---------+--------+
| order_id | user_id | status |
+----------+---------+--------+
|       13 |       1 | init   |
+----------+---------+--------+
1 row in set (0.00SEC) # Multiple queries will find random queries from the tableCopy the code

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

Introduction to Sharding Sphere

ShardingSphere

Sharding-JDBC

Sharding-JDBC implementation level table

Sharding-JDBC implementation of horizontal branch library

Sharding-JDBC implementation of vertical library

Sharding-JDBC operates on common tables

Sharding-JDBC implements read and write separation

Sharding-Proxy

Download and Installation

Sharding-proxy configuration (sub-table)

Sharding-proxy configuration (Branch library)

Sharding-proxy configuration (read/write separation)

Introduction to Sharding Sphere

ShardingSphere

Sharding-JDBC

Sharding-JDBC implementation level table

Sharding-JDBC implementation of horizontal branch library

Sharding-JDBC implementation of vertical library

Sharding-JDBC operates on common tables

Sharding-JDBC implements read and write separation

Sharding-Proxy

Download and Installation

Sharding-proxy configuration (sub-table)

Sharding-proxy configuration (Branch library)

Sharding-proxy configuration (read/write separation)

Related Posts

Spring Cloud Gateway registers with Consul, a service center, to broker all services from the service center

Go’s threading model

Bytedance’s practice on Go Network library