Sharding JDBC realizes read-write separation + sub database and sub table. It's written very well!

1. Overview

2. MySQL master-slave replication

1) Create the directory required by the master server

mkdir -p /usr/local/mysqlData/master/cnf mkdir -p /usr/local/mysqlData/master/data

2) Define master server profile

vim /usr/local/mysqlData/master/cnf/mysql.cnf [mysqld] ## Set up server_id, be unique server-id=1 ## Enable binlog log-bin=mysql-bin ## binlog cache binlog_cache_size=1M ## binlog format (mixed, statement, row, default format is statement) binlog_format=mixed

3) Create and start mysql main service

docker run -itd -p 3306:3306 --name master -v /usr/local/mysqlData/master/cnf:/etc/mysql/conf.d -v /usr/local/mysqlData/master/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7

4) Add a user reader that replicates master data for use from the server

[root@aliyun /]# docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 6af1df686fff mysql:5.7 "docker-entrypoint..." 5 seconds ago Up 4 seconds 0.0.0.0:3306->3306/tcp, 33060/tcp master [root@aliyun /]# docker exec -it master /bin/bash root@41d795785db1:/# mysql -u root -p123456 mysql> GRANT REPLICATION SLAVE ON *.* to 'reader'@'%' identified by 'reader'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec)

5) Create the required directory from the server and edit the configuration file

mkdir /usr/local/mysqlData/slave/cnf -p mkdir /usr/local/mysqlData/slave/cnf -p vim /usr/local/mysqlData/slave/cnf/mysql.cnf [mysqld] ## Set up server_id, be unique server-id=2 ## Enable binlog for use when Slave is the Master of other Slave log-bin=mysql-slave-bin ## relay_log configure relay log relay_log=edu-mysql-relay-bin ## If you need to synchronize functions or stored procedures log_bin_trust_function_creators=true ## binlog cache binlog_cache_size=1M ## binlog format (mixed, statement, row, default format is statement) binlog_format=mixed ## Skip all errors encountered in master-slave replication or specified types of errors to avoid the interruption of slave side replication ## For example, the 1062 error refers to the duplication of some primary keys, and the 1032 error is due to the inconsistency between the primary and secondary database data slave_skip_errors=1062

6) Create and run mysql slave server

docker run -itd -p 3307:3306 --name slaver -v /usr/local/mysqlData/slave/cnf:/etc/mysql/conf.d -v /usr/local/mysqlData/slave/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7

7) Configure the information to connect to the master server on the slave server

First, view the master on the master server_ log_ file,master_log_pos two parameters, and then switch to the slave server to set the connection information of the master server

Execute on main service:

root@6af1df686fff:/# mysql -u root -p123456 mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000003 | 591 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)

docker views the ip address of the primary server container

[root@aliyun /]# docker inspect --format='{{.NetworkSettings.IPAddress}}' master 172.17.0.2

From the server:

[root@aliyun /]# docker exec -it slaver /bin/bash root@fe8b6fc2f1ca:/# mysql -u root -p123456 mysql> change master to master_host='172.17.0.2',master_user='reader',master_password='reader',master_log_file='mysql-bin.000003',master_log_pos=591;

8) Start the I/O thread and SQL thread from the server

mysql> start slave; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.17.0.2 Master_User: reader Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 591 Relay_Log_File: edu-mysql-relay-bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes

Slave_IO_Running: Yes，Slave_SQL_Running: Yes indicates successful startup

1) redo log

InnoDB first puts the redo log into the redo log buffer, and then refreshes it to the redo log file at a certain frequency

The redo log buffer will be refreshed to the redo log file in the following three cases:

• The Master Thread flushes the redo log buffer to the redo log file every second
• When each transaction is committed, the redo log buffer will be refreshed to the redo log file
• When the remaining space of the redo log buffer pool is less than 1 / 2, the redo log buffer will be flushed to the redo log file

The full name of the WAL technology often used in MySQL is Write Ahead Log, that is, when a transaction is committed, write the redo log first and then modify the page. That is, when a record needs to be updated, InnoDB will first write the record to the redo log and update the page of Buffer Pool. At this time, the update operation is completed

Buffer Pool is the cache of physical pages. Any modification to InnoDB will be performed on the page of Buffer Pool first, and then such pages will be marked as dirty pages and placed on a special Flush List. Later, these pages will be written to the disk periodically by a special dirty brushing thread

InnoDB's redo log is a fixed size. For example, it can be configured as a group of four files, each of which is 1GB in size. It is used circularly. It is written from the beginning, and then back to the beginning after writing to the end. It is written in sequence, which saves the IO consumption of random writing to the disk

Write Pos is the position of the current record. It moves back while writing. After writing to the end of file No. 3, it returns to the beginning of file No. 0. Check Point is the current position to be erased, which is also moved back and cycled. Before erasing the record, the record should be updated to the data file

The empty part between Write Pos and Check Point can be used to record new operations. If Write Pos catches up with Check Point, no new updates can be performed at this time. You need to stop and erase some records to push Check Point forward

When the database goes down, the database does not need to redo all logs, because the pages before Check Point have been flushed back to disk. Only the redo log after Check Point needs to be recovered, which shortens the recovery time

When the buffer pool is not enough, the least recently used page will overflow according to the LRU algorithm. If this page is a dirty page, you need to forcibly execute Check Point to refresh the dirty page back to disk

2) binlog (archive log)

MySQL as a whole has two parts: one is the Server layer, which mainly does things at the functional level of MySQL; Another is the engine layer, which is responsible for specific matters related to storage. redo log is a unique log of InnoDB engine, and the Server layer also has its own log, called binlog

binlog records all operations that change the MySQL database, excluding such operations as SELECT and SHOW. It is mainly used for master-slave replication of the database and incremental recovery of data

When using mysqldump for backup, only the data for a period of time is fully backed up. However, if the database server fails suddenly after backup, the binlog log will be used at this time

There are three binlog formats: state, ROW and MIXED

• STATEMENT mode: the original SQL STATEMENT is recorded in binlog. The advantage is that there is no need to record the data changes of each row, which reduces the amount of binlog logs, saves IO and improves performance. The disadvantage is that in some cases, the data in the master slave will be inconsistent
• ROW mode: do not record the context information of each SQL STATEMENT, just record which data has been modified and what it has been modified. This solves the inconsistency of data in master slave in the state mode. The disadvantage is that a large number of logs will be generated, especially when the alter table is used, the logs will soar
• MIXED mode: the above two modes are used in combination. For general replication, binlog is saved in the state mode. For operations that cannot be replicated in the state mode, binlog is saved in the ROW mode. MySQL will select the log saving method according to the executed SQL statements

3) The difference between redo log and binlog log

• redo log is unique to InnoDB engine; binlog is implemented in the Server layer of MySQL and can be used by all engines
• redo log is a physical log that records the changes made to a certain data; binlog is a logic log that records the original logic of the statement. For example, add 1 to the c field of the line ID=2
• redo log is written circularly, and the space is fixed and will be used up; Binlog can be written additionally. After the binlog file is written to a certain size, it will switch to the next one and will not overwrite the previous log

4) Two stage submission

create table T(ID int primary key, c int); update T set c=c+1 where ID=2;

The internal process of the executor and InnoDB engine when executing this update statement:

• The actuator first finds the engine and takes the line ID=2. ID is the primary key, and the engine finds this row directly by tree search. If the data of the line ID=2 is already in memory, it will be directly returned to the actuator; Otherwise, you need to read memory from disk before returning
• The actuator obtains the row data given by the engine, adds 1 to the value, obtains a new row of data, and then calls the engine interface to write the new row of data
• The engine updates this line of new data into memory and records the update operation in the redo log. At this time, the redo log is in the prepare state. The executor is then informed that execution is complete and the transaction can be committed at any time
• The actuator generates the binlog of this operation and writes the binlog to disk
• The executor calls the commit transaction interface of the engine. The engine changes the redo log just written to the commit status and the update is completed

The execution flow chart of the update statement is as follows. In the figure, the light box indicates that it is executed inside InnoDB, and the dark box indicates that it is executed in the actuator

The redo log writing is divided into two steps: prepare and commit, which are two-phase commit

A long connection is maintained between slave Library B and master library a. There is a thread inside main library a, which is dedicated to serving the long connection from library B. The complete process of a transaction log synchronization is as follows:

• On slave database B, set the IP, port, user name and password of master database A through the change master command, and where to start requesting binlog. This location contains the file name and log offset
• Execute the start slave command on slave library B. at this time, the slave library will start two threads, I/O thread and SQL thread in the figure. The I/O thread is responsible for establishing a connection with the main library
• After verifying the user name and password, main database A starts to read binlog locally according to the location transmitted from database B and send it to database B
• After getting the binlog from library B, it is written to the local file, which is called the relay log
• The SQL thread reads the relay log, parses the commands in the log, and executes them

Due to the introduction of multi-threaded replication scheme, SQL thread has evolved into multiple threads

Master-slave replication is not synchronous in full real time, but asynchronous in real time. There is an execution delay between the master and slave services. If the pressure on the master server is great, it may lead to a large delay between the master and slave servers

3. Sharding JDBC implementation of read-write separation

I won't introduce the basics of Spring Boot. I recommend this practical tutorial:
https://www.javastack.cn/cate...

1) Create a new Springboot project and introduce related dependencies

<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.4</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> <version>1.1.21</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.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> </dependencies>

2) , application.properties configuration file

spring.main.allow-bean-definition-overriding=true #Show sql spring.shardingsphere.props.sql.show=true #Configure data sources spring.shardingsphere.datasource.names=ds1,ds2,ds3 #master-ds1 database connection information spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.ds1.url=jdbc:mysql://47.101.58.187:3306/sharding-jdbc-db?useUnicode=true&useSSL=false&serverTimezone=Asia/Shanghai spring.shardingsphere.datasource.ds1.username=root spring.shardingsphere.datasource.ds1.password=123456 spring.shardingsphere.datasource.ds1.maxPoolSize=100 spring.shardingsphere.datasource.ds1.minPoolSize=5 #slave-ds2 database connection information spring.shardingsphere.datasource.ds2.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.ds2.driver-class-name=com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.ds2.url=jdbc:mysql://47.101.58.187:3307/sharding-jdbc-db?useUnicode=true&useSSL=false&serverTimezone=Asia/Shanghai spring.shardingsphere.datasource.ds2.username=root spring.shardingsphere.datasource.ds2.password=123456 spring.shardingsphere.datasource.ds2.maxPoolSize=100 spring.shardingsphere.datasource.ds2.minPoolSize=5 #slave-ds3 database connection information spring.shardingsphere.datasource.ds3.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.ds3.driver-class-name=com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.ds3.url=jdbc:mysql://47.101.58.187:3307/sharding-jdbc-db?useUnicode=true&useSSL=false&serverTimezone=Asia/Shanghai spring.shardingsphere.datasource.ds3.username=root spring.shardingsphere.datasource.ds3.password=123456 spring.shardingsphere.datasource.ds.maxPoolSize=100 spring.shardingsphere.datasource.ds3.minPoolSize=5 #Configure the default data source ds1 default data source, which is mainly used for writing spring.shardingsphere.sharding.default-data-source-name=ds1 #Configure master-slave name spring.shardingsphere.masterslave.name=ms #Set the master database to be responsible for writing data spring.shardingsphere.masterslave.master-data-source-name=ds1 #Configuring slave slave nodes spring.shardingsphere.masterslave.slave-data-source-names=ds2,ds3 #Configure the load balancing strategy of the slave node and adopt the polling mechanism spring.shardingsphere.masterslave.load-balance-algorithm-type=round_robin #Integrate the configuration of mybatis mybatis.type-aliases-package=com.ppdai.shardingjdbc.entity

3) , create t_user table

CREATE TABLE `t_user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `nickname` varchar(100) DEFAULT NULL, `password` varchar(100) DEFAULT NULL, `sex` int(11) DEFAULT NULL, `birthday` varchar(50) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

4) , define Controller, Mapper, Entity

@Data public class User { private Integer id; private String nickname; private String password; private Integer sex; private String birthday; } @RestController @RequestMapping("/api/user") public class UserController { @Autowired private UserMapper userMapper; @PostMapping("/save") public String addUser() { User user = new User(); user.setNickname("zhangsan" + new Random().nextInt()); user.setPassword("123456"); user.setSex(1); user.setBirthday("1997-12-03"); userMapper.addUser(user); return "success"; } @GetMapping("/findUsers") public List<User> findUsers() { return userMapper.findUsers(); } } public interface UserMapper { @Insert("insert into t_user(nickname,password,sex,birthday) values(#,#,#,#)") void addUser(User user); @Select("select * from t_user") List<User> findUsers(); }

5) . verification

Three data sources in the startup log were initialized successfully:

call http://localhost:8080/api/user/save Go all the way to the ds1 master node

call http://localhost:8080/api/user/findUsers Go all the way to the ds2 and ds3 nodes, and enter by polling

4. MySQL sub database and table principle

Horizontal split: split the data of the same table into different libraries and different tables. It can be split according to time, region or a business key dimension, or through hash, and finally access specific data through routing. The structure of each table after splitting is consistent

Vertical splitting: splitting a table with many fields into multiple tables or libraries. The structure of each library table is different, and each library table contains some fields. Generally speaking, it can be split according to business dimensions. For example, the order table can be split into order, order support, order address, order goods, order extension and other tables; You can also split the data according to the degree of cold and hot. 20% of the hot fields are split into one table and 80% of the cold fields are split into another table

Generally, there is a process to split a database. At first, it is a single table, and then it is slowly split into multiple tables. Let's look at how to smoothly transition from MySQL single table to MySQL sub database and sub table architecture

• MySQL+Canal is used for incremental data synchronization, and the database and table middleware is used to route the data to the corresponding new table
• Using the database and table middleware, the full amount of data is imported into the corresponding new table
• Through the comparison of single table data and sub database and sub table data, the mismatched data is updated to the new table
• After the data is stable, switch the single table configuration to the sub database and sub table configuration

5. Sharding JDBC implements sub database and sub table

User data is split into two tables according to order id%2: t_order0 and t_order1. Their logical table name is: t_order

Same table for multiple data sources:

#Multiple data sources $- > . Logical table name $- > same table spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds$->.t_order$->

Different tables from multiple data sources:

#Multiple data sources $- > . Logical table name $- > different tables spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds0.t_order$->,ds1.t_order$->

Single warehouse sub table:

#Configuration mode of single data source spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds0.t_order$->

Specify all manually:

spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds0.t_order0,ds1.t_order0,ds0.t_order1,ds1.t_order1

spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds$->.t_order$-> #Data source fragmentation strategy spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.sharding-column=user_id #Data source slicing algorithm spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.algorithm-expression=ds$-> #Table partition strategy spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id #Table slicing algorithm spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order$->

The above configuration is through user_id%2 to determine the specific data source through order_id%2 to determine the specific table

insert into t_order(user_id,order_id) values(2,3)，user_id%2 = 0 using data source ds0, order_id%2 = 1 using t_order1, the final operation of the insert statement is the T of the data source ds0_ Order1 table.

Sharding JDBC can configure the distributed primary key generation strategy. By default, snowflake algorithm is used to generate 64bit long integer data, and UUID mode is also supported

#Column name of primary key spring.shardingsphere.sharding.tables.t_order.key-generator.column=id #Primary key generation strategy spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE

Requirements:

The 1000w user data is divided into databases and tables, and the data of the user table is divided into tables and databases. Odd numbers are stored in t according to age_ User1, even t_user0. At the same time, the odd number is stored in ds1 and the even number is ds0

Table structure:

CREATE TABLE `t_user0` ( `id` bigint(20) DEFAULT NULL, `nickname` varchar(200) DEFAULT NULL, `password` varchar(200) DEFAULT NULL, `age` int(11) DEFAULT NULL, `sex` int(11) DEFAULT NULL, `birthday` varchar(100) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE `t_user1` ( `id` bigint(20) DEFAULT NULL, `nickname` varchar(200) DEFAULT NULL, `password` varchar(200) DEFAULT NULL, `age` int(11) DEFAULT NULL, `sex` int(11) DEFAULT NULL, `birthday` varchar(100) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Both databases contain t_user0 and t_user1 two tables

application.properties:

spring.main.allow-bean-definition-overriding=true #Show sql spring.shardingsphere.props.sql.show=true #Configure data sources spring.shardingsphere.datasource.names=ds0,ds1 #ds0 database connection information spring.shardingsphere.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.ds0.url=jdbc:mysql://47.101.58.187:3306/t_user_db0?useUnicode=true&useSSL=false&serverTimezone=Asia/Shanghai spring.shardingsphere.datasource.ds0.username=root spring.shardingsphere.datasource.ds0.password=123456 spring.shardingsphere.datasource.ds0.maxPoolSize=100 spring.shardingsphere.datasource.ds0.minPoolSize=5 #ds1 database connection information spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.ds1.url=jdbc:mysql://47.101.58.187:3306/t_user_db1?useUnicode=true&useSSL=false&serverTimezone=Asia/Shanghai spring.shardingsphere.datasource.ds1.username=root spring.shardingsphere.datasource.ds1.password=123456 spring.shardingsphere.datasource.ds1.maxPoolSize=100 spring.shardingsphere.datasource.ds1.minPoolSize=5 #Integrate the configuration of mybatis mybatis.type-aliases-package=com.ppdai.shardingjdbc.entity spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=ds$->.t_user$-> #Data source fragmentation strategy spring.shardingsphere.sharding.tables.t_user.database-strategy.inline.sharding-column=sex #Data source slicing algorithm spring.shardingsphere.sharding.tables.t_user.database-strategy.inline.algorithm-expression=ds$-> #Table partition strategy spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.sharding-column=age #Table slicing algorithm spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.algorithm-expression=t_user$-> #Column name of primary key spring.shardingsphere.sharding.tables.t_user.key-generator.column=id spring.shardingsphere.sharding.tables.t_user.key-generator.type=SNOWFLAKE

I won't introduce the basics of Spring Boot. I recommend this practical tutorial:
https://www.javastack.cn/cate...

Test class:

@SpringBootTest class ShardingJdbcApplicationTests { @Autowired private UserMapper userMapper; /** * sex:Odd number * age:Odd number * ds1.t_user1 */ @Test public void test01() { User user = new User(); user.setNickname("zhangsan" + new Random().nextInt()); user.setPassword("123456"); user.setAge(17); user.setSex(1); user.setBirthday("1997-12-03"); userMapper.addUser(user); } /** * sex:Odd number * age:even numbers * ds1.t_user0 */ @Test public void test02() { User user = new User(); user.setNickname("zhangsan" + new Random().nextInt()); user.setPassword("123456"); user.setAge(18); user.setSex(1); user.setBirthday("1997-12-03"); userMapper.addUser(user); } /** * sex:even numbers * age:Odd number * ds0.t_user1 */ @Test public void test03() { User user = new User(); user.setNickname("zhangsan" + new Random().nextInt()); user.setPassword("123456"); user.setAge(17); user.setSex(2); user.setBirthday("1997-12-03"); userMapper.addUser(user); } /** * sex:even numbers * age:even numbers * ds0.t_user0 */ @Test public void test04() { User user = new User(); user.setNickname("zhangsan" + new Random().nextInt()); user.setPassword("123456"); user.setAge(18); user.setSex(2); user.setBirthday("1997-12-03"); userMapper.addUser(user); } }

Reference documents:\
https://shardingsphere.apache... \
https://www.bilibili.com/vide...

Original link: https://blog.csdn.net/qq_4037... \
Copyright notice: This is the original article of CSDN blogger "sloppy wandering swordsman", which follows the CC 4.0 BY-SA copyright agreement. Please attach the original source link and this notice for reprint.

Recent hot article recommendations:

1.1000 + Java interview questions and answers (2021 latest version)

2.Stop playing if/ else on the full screen. Try the strategy mode. It's really fragrant!!

3.what the fuck! What is the new syntax of xx ≠ null in Java?

4.Spring Boot 2.5 heavy release, dark mode is too explosive!

5.Java development manual (Songshan version) is the latest release. Download it quickly!

Feel good, don't forget to like + forward!

Add new comment

0 comments