Shulou(Shulou.com)06/02 Report--

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!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.