How to integrate SpringBoot with sharding-jdbc to realize the Separation of Database and Table and the Separation of Reading and Writing

This article mainly explains "SpringBoot how to integrate sharding-jdbc to achieve sub-library sub-table and read-write separation", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn "how to integrate SpringBoot sharding-jdbc to achieve sub-library sub-table and read-write separation" bar!

I. Preface

This paper will integrate sharding-jdbc based on the following environment to realize the separation of library and table and read and write.

Springboot2.4.0

Mybatis-plus3.4.3.1

Mysql5.7 master and slave

II. Preparation of database tables

Warm Tip: when this sql is executed, if there is a corresponding library and table before, it will be automatically deleted and then created!

DROP DATABASE IF EXISTS ds0;CREATE DATABASE ds0;USE ds0;SET NAMES utf8mb4;SET FOREIGN_KEY_CHECKS = 0Mutual-Table structure for tweak user0Mew-DROP TABLE IF EXISTS `t _ user0` CREATE TABLE `t _ user0` (`user_ id` bigint (20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'primary key ID', `username` varchar (20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT' username', `password` varchar (50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'password' `sex` tinyint (4) NULL DEFAULT NULL COMMENT 'gender', `remark` varchar (100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'remarks', PRIMARY KEY (`user_ id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = 'user' ROW_FORMAT = Dynamic -Table structure for tweak user1muri-DROP TABLE IF EXISTS `tuser1` CREATE TABLE `t _ user1` (`username` bigint (20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'primary key ID', `username` varchar (20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT' username', `password` varchar (50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'password' `sex` tinyint (4) NULL DEFAULT NULL COMMENT 'gender', `remark` varchar (100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'remarks', PRIMARY KEY (`user_ id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = 'user' ROW_FORMAT = Dynamic SET FOREIGN_KEY_CHECKS = 1Mutual-= DROP DATABASE IF EXISTS ds1;CREATE DATABASE ds1;USE ds1;SET NAMES utf8mb4;SET FOREIGN_KEY_CHECKS = 0brim-Table structure for tweak user0muri-DROP TABLE IF EXISTS `t _ user0` CREATE TABLE `t _ user0` (`user_ id` bigint (20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'primary key ID', `username` varchar (20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT' username', `password` varchar (50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'password' `sex` tinyint (4) NULL DEFAULT NULL COMMENT 'gender', `remark` varchar (100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'remarks', PRIMARY KEY (`user_ id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = 'user' ROW_FORMAT = Dynamic -Table structure for tweak user1muri-DROP TABLE IF EXISTS `tuser1` CREATE TABLE `t _ user1` (`username` bigint (20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'primary key ID', `username` varchar (20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT' username', `password` varchar (50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'password' `sex` tinyint (4) NULL DEFAULT NULL COMMENT 'gender', `remark` varchar (100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'remarks', PRIMARY KEY (`user_ id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = 'user' ROW_FORMAT = Dynamic SET FOREIGN_KEY_CHECKS = 1

III. Integration

1. Introduce dependency into pom

Org.apache.shardingsphere sharding-jdbc-spring-boot-starter 4.1.1

2. Application.yml configuration

Spring: # sharding-jdbc configuration shardingsphere: # whether to enable SQL display props: sql: show: true # = = ↓ data source configuration ↓ = = datasource: names: ds-master-0,ds-slave-0-1 Ds-slave-1-2 # = = ↓ configuration the first master / slave library ↓ = # Master library 1 ds-master-0: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.jdbc.Driver jdbc-url: jdbc:mysql://127.0.0.1:3306/ds0?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF8&zeroDateTimeBehavior=convertToNull&useSSL=false # MySQL is in a higher version If you need to indicate whether to solve the SSL connection, add & useSSL=false username: root password: root # master library 1-slave library 1 ds-slave-0-1: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.jdbc.Driver jdbc-url: jdbc:mysql://127.0.0.1:3307/ds0?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF8&zeroDateTimeBehavior=convertToNull&useSSL=false # MySQL in If the higher version needs to indicate whether to solve the SSL connection, add & useSSL=false username: root password: root # Master Library 1-Slave Library 2 ds-slave-0-2: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.jdbc.Driver jdbc-url: jdbc:mysql://127.0.0.1:3307/ds0?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF8&zeroDateTimeBehavior=convertToNull&useSSL=false # MySQL needs to indicate whether to solve the SSL connection in the higher version, then add & useSSL=false username: root password: root # = = ↓ configuration second master / slave library ↓ = # master library 2 ds-master-1: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.jdbc.Driver jdbc-url: jdbc: Mysql://127.0.0.1:3306/ds1?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF8&zeroDateTimeBehavior=convertToNull&useSSL=false # MySQL needs to indicate whether to solve the SSL connection in the higher version, then add & useSSL=false username: root password: root # Master Library 2-Slave Library 1 ds-slave-1-1: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.jdbc.Driver jdbc-url : jdbc:mysql://127.0.0.1:3307/ds1?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF8&zeroDateTimeBehavior=convertToNull&useSSL=false # MySQL needs to indicate whether to solve SSL connection in the higher version, then add & useSSL=false username: root password: root # Master Library 2-Slave Library 2 ds-slave-1-2: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.jdbc.Driver Jdbc-url: jdbc:mysql://127.0.0.1:3307/ds1?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF8&zeroDateTimeBehavior=convertToNull&useSSL=false # MySQL needs to indicate whether to solve the SSL connection in the higher version, then add & useSSL=false username: root password: root sharding: # = ↓ sub-library sub-table configuration ↓ = = # sub-library policy = > split to different according to user_id mode Default-database-strategy: inline: sharding-column: user_id algorithm-expression: ds-master-$- > {user_id% 2} # split table policy tables: t_user: actual-data-nodes: ds-master-$- > {0.1} .t _ user$- > {0.1} key-generator: Column: user_id # primary key ID type: SNOWFLAKE # generation policy # add data split table policy table-strategy: inline: # add data split table field (insert data into that table ex:sex based on the field) sharding-column: sex # shard Algorithm expression = > split into different tables based on the gender of the user algorithm-expression: tweak username-> {sex% 2} # = = ↓ read-write separation configuration ↓ = = master-slave-rules: ds-master-0: # Master library masterDataSourceName: ds-master-0 # slave library SlaveDataSourceNames:-ds-slave-0-1-ds-slave-0-2 # load balancing algorithm for querying data from database there are two algorithms: round_robin (polling) and random (random) # algorithm interface org.apache.shardingsphere.spi.masterslave.MasterSlaveLoadBalanceAlgorithm # implementation class RandomMasterSlaveLoadBalanceAlgorithm and RoundRobinMasterSlaveLoadBalanceAlgorithm loadBalanceAlgorithmType: ROUND_ROBIN Ds-master-1: masterDataSourceName: ds-master-1 slaveDataSourceNames:-ds-slave-1-1-ds-slave-1-2 loadBalanceAlgorithmType: ROUND_ROBIN

3. Data source health configuration after the introduction of sharding-jdbc

Solve the startup error problem: ConnectionCallback; isValid; nested exception is java.sql.SQLFeatureNotSupportedException: isValid

Reason: springboot2.4 data source health check

@ Configurationpublic class DataSourceHealthConfig extends DataSourceHealthContributorAutoConfiguration {@ Value ("${spring.datasource.dbcp2.validation-query:select 1}") private String defaultQuery; public DataSourceHealthConfig (Map dataSources, ObjectProvider metadataProviders) {super (dataSources, metadataProviders);} @ Override protected AbstractHealthIndicator createIndicator (DataSource source) {DataSourceHealthIndicator indicator = (DataSourceHealthIndicator) super.createIndicator (source); if (! StringUtils.hasText (indicator.getQuery () {indicator.setQuery (defaultQuery) } return indicator;}}

When this configuration is complete, then you can write your own CRUD for testing. The configuration is relatively simple and belongs to the introduction.

4. Docker-compose deployment mysql Master / Slave # Environment prepares git clone https://gitee.com/zhengqingya/docker-compose.gitcd docker-compose/Liunx# to run docker-compose- f docker-compose-mysql-master-slave.yml-p mysql-master-slave up-d # = ↓ configuration Master Library ↓ = # enter Master Library docker exec-it mysql_master / bin/bash# login mysqlmysql-uroot-proot# create user slave Password 123456CREATE USER 'slave'@'%' IDENTIFIED BY' 123456' # Grant slave user `SLAVE` permission and `REPLICATION GRANT REPLICATION SLAVE permission, which is used to synchronize data GRANT REPLICATION SLAVE between `master ``slaves. If REPLICATION CLIENT ON *. * TO 'slave'@'%';# grants all permissions, execute the command: GRANT ALL PRIVILEGES ON *. * TO' slave'@'%';# to make the operation effective FLUSH PRIVILEGES;# View status show master status # Note: the values of File and Position fields will be used in slave. Do not manipulate master before the slave operation is completed, otherwise it will cause a state change. That is, the values of File and Position fields change!! # +-+ # | File | Position | Binlog_Do_DB | Binlog_ Ignore_DB | Executed_Gtid_Set | # +-+ # | mysql-bin.000003 | 769 | | | | # +-+ # 1 row in set (0.00 sec) # = ↓ | Configure entry from library ↓ = # login to mysqlmysql-uroot-prootchange master to master_host='www.zhengqingya.com' from library docker exec-it mysql_slave / bin/bash# Master_port=3306, master_user='slave', master_password='123456', master_log_file='mysql-bin.000003', master_log_pos= 769, master_connect_retry=30 # start the master-slave synchronization process [stop command: stop slave;] start slave;# to check the master-slave synchronization status show slave status\ G# Slave_IO_Running and Slave_SQL_Running are Yes, it means that the master-slave synchronization has been configured! # if Slave_IO_Running is Connecting,SlaveSQLRunning and Yes, then there is a problem with the configuration. At this time, you need to check which step in the configuration has a problem. You can make an error according to the Last_IO_Error field information or Google … # * * 1. Row * # Slave_IO_State: Waiting for master to send event# Master_Host: www.zhengqingya.com# Master_User: slave# Master_Port: 3306# Connect_Retry: 30# Master_Log_File: mysql-bin.000003# Read_Master_Log_Pos: 769# Relay_Log_File: c598d8402b43-relay-bin.000002# Relay_Log_Pos: 320# Relay_Master_Log_File: mysql-bin.000003# Slave_IO_Running: Yes# Slave_SQL_Running: Yes# Replicate_Do_DB: so far I believe that the "SpringBoot how to integrate sharding-jdbc to achieve sub-library sub-table and read-write separation" have a deeper understanding, might as well to the actual operation of it! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!