Skip to main content

SpringBoot + Sharding-JDBC + Read-Write Splitting

1. Setting Up Docker + MySQL Master-Slave Replication​

This article uses Docker installed on Windows, with MySQL version 5.6.

1.1 Create MySQL Configuration Files for Master and Slave​

image.png

1.1.1 Master MySQL​

D:\DockerWorkspace\mysql\3307\con\mysql.cnf

[mysqld]
user=mysql
log-bin=mysql-bin
server-id=1
character-set-server=utf8mb4
default_authentication_plugin=mysql_native_password
table_definition_cache=400
[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4

1.1.2 Slave MySQL​

D:\DockerWorkspace\mysql\3308\conf\mysql.cnf

[mysqld]
user=mysql
server-id=2
character-set-server=utf8mb4
default_authentication_plugin=mysql_native_password
table_definition_cache=400
[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4

1.2 Create Docker Network for Container Communication​

Create a new Bridge network to enable communication between containers. Reference: Docker Network - Container Communication

docker network create mysql

1.3 Install MySQL with Docker​

Configuration details:

--name: Container alias -p: Port mapping -e: Container environment variables -v: Mount host files (MySQL config here) --network: Use Bridge network. Reference: Docker network --network-alias: Network alias -d: Run container in background

1.2.1 Master MySQL​

docker run --name mysql-3307 -p 3307:3306 -e MYSQL_ROOT_PASSWORD=123456 -v D:\DockerWorkspace\mysql\3307\conf\mysql.cnf:/etc/mysql/conf.d/mysql.cnf --network mysql --network-alias mysql-master -d mysql:5.6

1.2.2 Slave MySQL​

docker run --name mysql-3308 -p 3308:3306 -e MYSQL_ROOT_PASSWORD=123456 -v D:\DockerWorkspace\mysql\3308\conf\mysql.cnf:/etc/mysql/conf.d/mysql.cnf --network mysql --network-alias mysql-slave  -d mysql:5.6

Both MySQL services are now running. Check with:

docker ps -a

On Windows, you can see running containers in the panel: image.png

1.4 Configure MySQL Containers​

1.4.1 Master MySQL​

docker exec -it e8c6 /bin/bash

Connect to MySQL database (adjust password as needed):

mysql -h127.0.0.1 -uroot -p123456

Grant replication privileges to slave:

GRANT REPLICATION SLAVE ON *.* TO 'root'@'%';

Flush privileges:

flush privileges;

Check master status:

show master status;

image.png

Don't close this window. Note the File column name and Position number - needed for slave sync.

1.4.2 Slave MySQL​

Open a new terminal and enter the container:

docker exec -it a8aa /bin/bash

Connect to MySQL:

mysql -h127.0.0.1 -uroot -p123456

Configure sync:

  • master_host: Master MySQL name or IP
  • master_user: User to connect to master
  • master_password: Connection password
  • master_log_file: File value from show master status on master
  • master_log_pos: Position to start sync (0 = from beginning, or use Position value from master)

Execute:

change master to master_host='mysql-master',master_user='root',master_password='123456',master_log_file='mysql-bin.000004',master_log_pos=502,master_port=3306;

Start slave (enable sync):

start slave;

Check slave status:

show slave status\G;

When both show YES, configuration is successful:

image.png

Now all operations on master will sync to slave.

2. Setting Up Sharding-JDBC Read-Write Splitting​

Official documentation: SHARDING-JDBC

2.1 Create SpringBoot Project and Add Dependencies​

image.png

pom.xml dependencies:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.1.3</version>
</dependency>

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.1.16</version>
</dependency>

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.0.0-RC1</version>
</dependency>

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
</dependency>

2.2 Configure Sharding-JDBC​

application.properties:

server.port=8080

# Datasource names, comma-separated for multiple
spring.shardingsphere.datasource.names=master,slave

# Master datasource
spring.shardingsphere.datasource.master.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.master.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master.url=jdbc:mysql://localhost:3307/temp1?characterEncoding=utf-8
spring.shardingsphere.datasource.master.username=root
spring.shardingsphere.datasource.master.password=123456

# Slave datasource
spring.shardingsphere.datasource.slave.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.slave.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave.url=jdbc:mysql://localhost:3308/temp1?characterEncoding=utf-8
spring.shardingsphere.datasource.slave.username=root
spring.shardingsphere.datasource.slave.password=123456

# Read-write splitting config
# Load balance algorithm: ROUND_ROBIN or RANDOM
spring.shardingsphere.masterslave.load-balance-algorithm-type=round_robin
spring.shardingsphere.masterslave.name=dataSource
spring.shardingsphere.masterslave.master-data-source-name=master
spring.shardingsphere.masterslave.slave-data-source-names=slave

# Show SQL, default: false
spring.shardingsphere.props.sql.show=true
# MyBatis config
mybatis.type-aliases-package=run.runnable.readwritesplit.entity

That's all you need! Sharding automatically routes inserts to master and queries to slave.

Source code: https://github.com/MingGH/read-write-split