Spring Boot demo series (10): ShardingSphereJDBC read-write separation + MySQL master-slave replication

1 Overview

This article describes how to use MyBatisPlus + ShardingSphereJDBC for read-write separation, and use MySQL for master-slave replication.

Specific steps include:

• MySQL Master-slave replication environment preparation ( Docker )
• build ShardingShpereJDBC + MyBatisPlus + Druid environment
• test

2 Environment

• OpenJDK 17.0.3
• Spring Boot 2.7.0
• MyBatis Plus 3.5.1
• MyBatis Plus Generator 3.5.2
• Druid 1.2.10
• ShardingSphereJDBC 5.1.1
• MySQL 8.0.29 ( Docker )

3 Some basic theories

3.1 Read-write separation

Separation of reading and writing, as the name implies, is the separation of reading and writing. More specifically, it is:

• Write operations are performed on the primary database
• Read operations are in progress from the database

The fundamental purpose of using read-write separation is to improve concurrent performance. If read-write is implemented on the same machine MySQL , I believe it will not be as good as one MySQL write, and the other two MySQL Reading such a configuration has high performance. On the other hand, in many cases, the requests for read operations are much higher than those for write operations, which makes it necessary to separate read and write operations.

3.2 Master-slave replication

Master-slave replication, as the name implies, is to copy the data of the master library to the slave library, because after the read and write separation, the write operations are performed in the master library, but the read operations are performed in the slave library, that is, the data on the master library If it cannot be copied to the slave library, the slave library will not read the data in the master library. Strictly speaking, read-write separation does not require master-slave replication, but only needs to be written and read from the master database. However, if there is no master-slave replication, read-write separation will lose its meaning. Therefore, read-write separation is usually used in conjunction with master-slave replication.

Because this example uses MySQL , here is the principle of MySQL master-slave replication, as shown in the following figure:

The workflow is as follows:

• After the main library modifies the data, write the modification log to binlog
• The slave library I/O thread reads the master library binlog and copies it to the slave library local binlog
• The local binlog from the library is read by the SQL thread, executes its contents and synchronizes to the slave library

3.3 Introduction to Database Middleware

Database middleware can simplify the operation of read-write separation and sub-database and sub-table, and hide the underlying implementation details. It can operate multiple databases and multiple tables just like operating a single database and a single table. There are two main design schemes:

• Server-side proxy: A proxy service needs to be deployed independently. The proxy service manages multiple database instances. In the application, a connection is established with the proxy server through a data source, and the proxy operates the underlying database and returns the corresponding results. The advantage is that it supports multiple languages and is transparent to the business. The disadvantage is that the implementation is complex and difficult to implement. At the same time, the agent needs to ensure its own high availability.
• Client proxy: A layer of encapsulation is carried out on the connection pool or database driver, internal connections are established with different databases, and necessary operations are performed on SQL , such as read-write separation and selection of master library or slave library, How to aggregate the results after sub-library and sub-table select . The advantage is simple implementation and natural decentralization. The disadvantage is that there are fewer supported languages and version upgrades are difficult.

Some common database middleware are as follows:

• Cobar : Alibaba's open source relational database distributed service middleware, which has been stopped.
• DRDS : Born out of Cobar , full name 分布式关系型数据库服务
• MyCat : Open source database middleware, currently updated MyCat2 version
• Atlas : Qihoo 360 company Web a data middle-tier project based on MySQL protocol developed and maintained by the infrastructure team of the platform department. A version of NoSQL bc9ab118770f36135e92d811820f7d0e--- called Pika
• tddl : Distributed database service independently developed by Alibaba
• Sharding-JDBC : a child of ShardingShpere 89d9c5a012f02e2464fb3225e2e21202---, a lightweight Java framework

4 MySQL Master-slave replication environment preparation

After reading some basic theories, you can start. In this section, prepare the MySQL master-slave replication environment, which is based on Docker + MySQL official documents.

4.1 Main library operation

4.1.1 Pull the image and create a container to run

 docker pull mysql
docker run -itd -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 --name master mysql
docker exec -it master /bin/bash

Update the mirror source in the main library, install vim and net-tools :

 cd /etc/apt
echo deb http://mirrors.aliyun.com/debian/ buster main non-free contrib deb-src http://mirrors.aliyun.com/debian/ buster main non-free contrib deb http://mirrors.aliyun.com/debian-security buster/updates main deb-src http://mirrors.aliyun.com/debian-security buster/updates main deb http://mirrors.aliyun.com/debian/ buster-updates main non-free contrib deb-src http://mirrors.aliyun.com/debian/ buster-updates main non-free contrib deb http://mirrors.aliyun.com/debian/ buster-backports main non-free contrib deb-src http://mirrors.aliyun.com/debian/ buster-backports main non-free contrib > sources.list
apt update && apt upgrade
apt install vim net-tools

4.1.2 Modify the configuration file

 vim /etc/mysql/my.cnf

Add the following two lines of data:

 [mysqld]
server-id=1                # 全局唯一，取值[1,2^32-1]，默认为1
binlog-do-db=test          # 表示需要复制的是哪个库

Reboot after modification is complete.

4.1.3 Prepare the data source

 CREATE DATABASE test;
USE test;
CREATE TABLE user(
    id BIGINT PRIMARY KEY,
    name VARCHAR(30) NOT NULL,
);

4.1.4 Create a user for replication operations (optional but recommended)

Note that you need to add mysql_native_password to create a user, otherwise the slave library will always be connected:

 CREATE USER 'repl'@'172.17.0.3' IDENTIFIED WITH mysql_native_password BY '123456';
GRANT REPLICATION slave ON *.* TO 'repl'@'172.17.0.3';

Please modify the specific address according to the address of the slave library. You can look at the slave library configuration section later.

4.1.5 Data Backup (Optional)

If there is data in the original master library, then this part of the data needs to be manually synchronized to the slave library:

 FLUSH TABLES WITH READ LOCK;

Open another terminal of the main library and use mysqldump to export:

 mysqldump -u root -p --all-databases --master-data > dbdump.db

After the export is complete, release the read lock:

 UNLOCK TABLES;

4.1.6 View the main library status

 SHOW MASTER STATUS;

You need to record File and Position , which will be used later in the configuration of the library.

4.2 Operation from the library

4.2.1 Pull the image and create a container to run

 docker pull mysql
docker run -itd -p 3307:3306 -e MYSQL_ROOT_PASSWORD=123456 --name slave mysql
docker exec -it slave /bin/bash

Once inside the container, update the sources like the main repository and then install vim and net-tools :

 cd /etc/apt
echo deb http://mirrors.aliyun.com/debian/ buster main non-free contrib deb-src http://mirrors.aliyun.com/debian/ buster main non-free contrib deb http://mirrors.aliyun.com/debian-security buster/updates main deb-src http://mirrors.aliyun.com/debian-security buster/updates main deb http://mirrors.aliyun.com/debian/ buster-updates main non-free contrib deb-src http://mirrors.aliyun.com/debian/ buster-updates main non-free contrib deb http://mirrors.aliyun.com/debian/ buster-backports main non-free contrib deb-src http://mirrors.aliyun.com/debian/ buster-backports main non-free contrib > sources.list
apt update && apt upgrade
apt install vim net-tools

4.2.2 Modify the configuration file

 vim /etc/mysql/my.cnf

Add the following two lines:

 [mysqld]
server-id=2            # 全局唯一，不能与主库相同
replicate-do-db=test   # 与主库相同，表示对该库进行复制

Reboot after modification is complete.

4.2.3 View ip address

View the ip address of the slave library for users who set synchronization for the master library:

 ifconfig

output:

 inet 172.17.0.3  netmask 255.255.0.0  broadcast 172.17.255.255

Then the user used for replication in the main library can be repl@172.17.0.3 .

4.2.4 Import data (optional)

If the master library has data, it can be imported to the slave library first:

 mysqldump -u root -p --all-databases < dbdump.db

4.2.5 Prepare the data source

 CREATE DATABASE test;
USE test;
CREATE TABLE user(
    id BIGINT PRIMARY KEY,
    name VARCHAR(30) NOT NULL,
);

4.2.6 Set the main library

You can use the change master to / change replication source to ( 8.0.23+ ) command:

 CHANGE REPLICATION SOURCE TO
source_host='172.17.0.2',                   # 可以使用ifconfig查看主库ip
source_user='repl',                         # 之前主库创建的用户
source_password='123456',                   # 密码
source_log_file='binlog.000003',            # 之前在主库上使用show master status查看的日志文件
source_log_pos=594;                         # 同样使用show master status查看

4.2.7 Open the slave library

 START SLAVE;
SHOW SLAVE STATUS\G

The new version ( 8.0.22+ ) can use:

 START REPLICA;
SHOW REPLICA STATUS\G

Requires IO and SQL thread display Yes to be successful:

4.3 Testing

The main library chooses to insert a piece of data:

 INSERT INTO user VALUES(1,"name",3);

Then from the library select arrives:

5 Build Spring Boot Environment

5.1 Create a new project and introduce dependencies

Create a new Spring Boot project and introduce the following dependencies:

 implementation 'com.alibaba:druid:1.2.10'
implementation 'com.baomidou:mybatis-plus-boot-starter:3.5.1'
implementation 'org.freemarker:freemarker:2.3.31'
implementation 'com.baomidou:mybatis-plus-generator:3.5.2'
implementation 'org.apache.shardingsphere:shardingsphere-jdbc-core-spring-boot-starter:5.1.1'

Maven Version:

 <dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.5.1</version>
</dependency>
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-generator</artifactId>
    <version>3.5.2</version>
</dependency>
<dependency>
    <groupId>org.freemarker</groupId>
    <artifactId>freemarker</artifactId>
    <version>2.3.31</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.2.10</version>
</dependency>
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>5.1.1</version>
</dependency>

5.2 Using generators

 import com.baomidou.mybatisplus.generator.FastAutoGenerator;
import com.baomidou.mybatisplus.generator.engine.FreemarkerTemplateEngine;

public class Generator {
    public static void main(String[] args) {
        FastAutoGenerator.create("jdbc:mysql://localhost:3306/test", "root", "123456")
        .globalConfig(builder ->
            builder.author("author").outputDir(System.getProperty("user.dir") + "/src/main/java").build())
        .packageConfig(builder -> 
            builder.parent("com.example.demo").moduleName("user").build())
        .strategyConfig(builder -> 
            builder.addInclude("user").entityBuilder().enableLombok().disableSerialVersionUID().build())
        .templateEngine(new FreemarkerTemplateEngine())
        .execute();
    }
}

Directly run main method to generate code. Please change the configuration according to your personal needs. For more detailed configuration, please refer to another article of the author.

5.3 Configuration files

 spring:
  shardingsphere:
    mode:
      type: Memory                                     # 内存模式，元数据保存在当前进程中
    datasource:
      names: master,slave                              # 数据源名称，这里有两个
      master:                                          # 跟上面的数据源对应
        type: com.alibaba.druid.pool.DruidDataSource   # 连接池
        url: jdbc:mysql://127.0.0.1:3306/test          # 连接url
        username: root
        password: 123456
      slave:                                           # 跟上面的数据源对应
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://127.0.0.1:3306/test
        username: root
        password: 123456
    rules:
      readwrite-splitting:                             # 读写分离规则
        data-sources:                                  # 数据源配置
          random:                                      # 这个名字随便起
            type: Static                               # 静态类型
            load-balancer-name: round_robin            # 负载均衡算法名字
            props:
              write-data-source-name: master           # 写数据源
              read-data-source-names: slave            # 读数据源
        load-balancers:                                # 负载均衡配置
          round_robin:                                 # 跟上面负载均衡算法的名字对应
            type: ROUND_ROBIN                          # 负载均衡算法
    props:
      sql-show: true                                   # 打印SQL

Because the content of the configuration file is relatively large, the following is a separate description.

5.3.1 Mode

spring.shardingsphere.mode.type , there are three modes:

• Memory : memory mode, initial configuration or execution SQL and other operations take effect in the current process
• Standalone : Stand-alone mode, metadata information such as data sources and rules can be persisted, but these metadata will not be synchronized in the cluster
• Cluster : Cluster mode, which provides multiple Apache ShardingSphere metadata sharing among instances and the ability to coordinate state in distributed scenarios, as well as horizontal expansion and high availability

The memory mode is used here. If you want to persist information such as metadata, please use the stand-alone mode. The stand-alone mode needs to configure the following properties:

• spring.shardingsphere.mode.type=Standalone : Set stand-alone mode
• spring.shardingsphere.mode.repository.type= : The type of persistent warehouse, the applicable type in stand-alone mode is File (the type can be viewed in the official document )
• spring.shardingsphere.mode.repository.props.path= : Metadata storage path, default .shardingsphere
• spring.shardingsphere.mode.overwrite= : whether to overwrite

In cluster mode, the following properties need to be configured:

• spring.shardingsphere.mode.type=Cluster : Set the cluster mode
• spring.shardingsphere.mode.repository.type= : persistent repository type, cluster mode support ZooKeeper and Etcd persistence
• spring.shardingsphere.mode.repository.props.namespace= : registry namespace
• spring.shardingsphere.mode.repository.props.server-lists= : list of registry servers
• spring.shardingsphere.mode.overwrite= : whether to overwrite
• spring.shardingsphere.mode.repository.props.<key>= ：注册中心的属性配置， ZooKeeper ， retryIntervalMilliseconds （重试间隔毫秒）、 maxRetries （客户端连接maximum retries), timeToLiveSeconds (temporary data to live in seconds), operationTimeoutMilliseconds (client operation timeout in milliseconds), digest (login password) Etcd , can be configured timeToLiveSeconds (temporary data survival seconds), connectionTimeout (connection timeout seconds)

5.3.2 Data source configuration

spring.shardingsphere.datasource.names , followed by the name of the data source, separated by , , for example, there are two data sources here:

• master
• slave

Then each data source can be configured:

• type : database connection pool type, here is Druid
• username : Username
• password : password
• jdbc-url ：连接url ，注意， Druid连接池， url不是jdbc-url , the reason can refer to the official issue

5.3.3 Read-write separation rule configuration

spring.shardingsphere.rules.readwrite-splitting , you need to configure the data source and load balancing type:

• spring.shardingsphere.rules.readwrite-splitting.data-sources
• spring.shardingsphere.rules.readwrite-splitting.load-balancers

5.3.3.1 Data source configuration

The data source configuration first needs to add a data source name, you can choose one, for example, here is random , and then you need to configure three properties:

• spring.shardingsphere.rules.readwrite-splitting.data-sources.random.type ：读写分离的类型，可选Static与Dynamic ，这里选择Static ，如果选择Dynamic , which is the dynamic data source, please use it with dynamic-datasource-spring-boot-starter
• spring.shardingsphere.rules.readwrite-splitting.data-sources.random.props.write-data-source-name : write data source
• spring.shardingsphere.rules.readwrite-splitting.data-sources.random.props.read-data-source-name : read data source
• spring.shardingsphere.rules.readwrite-splitting.data-sources.random.load-balancer-name : The name of the load balancing algorithm, here is round_robin

5.3.3.2 Load Balance Configuration

The load balancing configuration needs to correspond to the above spring.shardingsphere.rules.readwrite-splitting.data-sources.random.load-balancer-name attribute. For example, here is round_robin , then the configuration needs to be spring.shardingsphere.rules.readwrite-splitting.load-balancers.round_robin . Then the next step is to configure the specific load balancing algorithm.

There are three built-in load balancing algorithms:

• Polling algorithm: ROUND_ROBIN , configure type=ROUND_ROBIN , that is, spring.shardingsphere.rules.readwrite-splitting.load-balancers.round_robin.type=ROUND_ROBIN
• Random access algorithm: RANDOM , configuration type=RANDOM
• Weight access algorithm: WEIGHT , configure type=WEIGHT , and also need to configure props , configure the weight of each read node in it, you can refer to the official website document

5.3.4 Property Configuration

For attributes, only one spring.shardingsphere.props.sql-show=true is configured here, that is, printing SQL , other supported attributes are:

• spring.shardingsphere.props.sql-simple : Whether to print simple style SQL , the default is false
• spring.shardingsphere.props.kernel-exector-size : Set the size of the task processing thread pool, the default is infinite
• spring.shardingsphere.props.max-connections-size-per-query : The maximum number of database connections that can be used for each query, the default is 1
• spring.shardingsphere.props.check-table-metadata-enabled : Whether to check the consistency of fragment metadata at startup, the default is false
• spring.shardingsphere.props.check-duplicate-table-enabled : Whether to check the duplicate table at startup, the default is false
• spring.shardingsphere.props.sql-federation-enabled : Whether to enable federated query, the default is false

5.4 Preparation Controller

 @RestController
@RequestMapping("/user")
@RequiredArgsConstructor(onConstructor = @__(@Autowired))
public class UserController {
    private final UserServiceImpl userService;

    @GetMapping("/select")
    public User select() {
        return userService.getById(1);
    }

    @GetMapping("/insert")
    public boolean insert() {
        return userService.saveOrUpdate(User.builder().id(3L).name("name3").build());
    }
}

6 test

Access http://localhost:8080/user/insert , you can see that the write operation is performed in the main library:

Visit http://localhost:8080/user/select , you can see that the read operation is performed from the library:

In this way, the read-write separation is completed.

7 Reference source code

Java version:

• Github
• Code cloud
• GitCode

Kotlin version:

• Github
• Code cloud
• GitCode

8 References

• MySQL Official Documentation
• Tencent Cloud-Interview Question: Have you done MySQL read-write separation? How to realize the separation of read and write in MySQL?
• Know - Detailed Explanation of Database Middleware (Boutique Long Article)
• ShardingSphere Official Documentation
• ShardingSphere official demo