This is the first day of my participation in the August Challenge. For details, see:August is more challenging
preface
Writing this blog is purely for fun, if there is something wrong, please take a look. At present, the background of the two signature middleware in the field of library and table is Sharding JDBC and MyCAT, today we will build respectively, see how to play. This article may not be a good fit if you are looking for an interview. If you have recently changed the backend architecture of your project to a database table, this article will help you quickly put together a framework for a database table. Play begins.
Database Preparation
The database I prepared is mysql, version is 5.7.35, too old version does not support master/slave replication, it is recommended that you download the 5.6 version or above. If you are installing a newer version, this error may occur during the installation
Because the new version of the password verification is more strict, you only need to go back to the previous step to re-enter the password to check, let ckeck not pass for a while and then enter the passed password, back to this step should be able to pass.
The primary and secondary databases are synchronized
After installing the database, we need to set up a master/slave synchronization mechanism, because sharding JDBC can only route to the master and slave database, and can not realize the master/slave data synchronization mechanism, the master/slave synchronization mechanism still depends on mysql’s own mechanism. To start.
1 Add a mysql instance
Since my master and slave are both on the same machine, I copied the mysql file as shown below
Prot = 3307 basedir = "C: / Program Files/MySQL/MySQL Server 5.7 s1 /" datadir = C: / ProgramData/MySQL/MySQL Server 5.7 s1 / DataCopy the codeTo install the slave library as a Windows server, run the following command
2 Add the following content to the INI file of the primary and secondary libraries
First, the main library
Mysql-id =1 server id=1 binlog_do_db=ds0 binlog_do_db=ds1Copy the codeSecond, from the library
% replicate_wild_do_table=ds0.% replicate_wild_do_table=ds1.%Copy the codeThen restart the master and slave libraries
Iii. Primary/secondary replication authorization account
Grant replication slave on. To myslave@localhost identified by 'myslave'; Then FLUSH PRIVILEGES; Then look at the following file name and locus show master status;Copy the codeAccording to the following
Configure the secondary database to synchronize data from the primary database
# stop slave; Change master to master_host='localhost', master_user='myslave', master_password='myslave', master_log_file='mysql-bin.000006', master_log_pos=15626542; # start slave; # show slave status;Copy the codeAccording to the following
When both are yes, the master/slave synchronization is successful. If your slave is a replicated master, Slave_IO_Running should be No, just change the uUID in auto-.cnf under the slave’s Data to be different from that of the master, as follows
sharding-jdbc
After the database master synchronization was completed, sharding- JDBC was started.
Springboot integrates Sharding – JDBC
1. Create a SpringBoot project and add it to the SHARding-JDBC JAR package. The detailed POM file is as follows
<? The XML version = "1.0" encoding = "utf-8"? > < project XMLNS = "http://maven.apache.org/POM/4.0.0" XMLNS: xsi = "http://www.w3.org/2001/XMLSchema-instance" Xsi: schemaLocation = "http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd" > < modelVersion > 4.0.0 < / modelVersion > < the parent > < groupId > org. Springframework. Boot < / groupId > The < artifactId > spring - the boot - starter - parent < / artifactId > < version > 2.5.2 < / version > < relativePath / > <! -- lookup parent from repository --> </parent> <groupId>com.example</groupId> <artifactId>demo</artifactId> <version>0.0.1 -snapshot </version> <name>liuxc-jdbc</name> <description>Demo project for Spring Boot</description> </ Java. Version > </ Java. Version > </ Java. <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> < the groupId > org. Mybatis. Spring. The boot < / groupId > < artifactId > mybatis - spring - the boot - starter < / artifactId > < version > 2.2.0 < / version > </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <dependency> < groupId > org, apache httpcomponents < / groupId > < artifactId > httpclient < / artifactId > < version > 4.5.5 < / version > < / dependency > < the dependency > < groupId > Commons - codec < / groupId > < artifactId > Commons - codec < / artifactId > < version > 1.11 < / version > </dependency> <dependency> <groupId>com.google.code.gson</groupId> <artifactId>gson</artifactId> </artifactId> fastjson</artifactId> </artifactId> </artifactId> The < version > 1.2.28 < / version > < / dependency > <! -- https://mvnrepository.com/artifact/org.apache.shardingsphere/sharding-jdbc-core --> <dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId>sharding-jdbc-spring-boot-starter</artifactId> </groupId> </artifactId> druid</artifactId> </artifactId> < span style = "box-sizing: border-box; color: RGB (50, 50, 50); font-size: 13px! Important; word-break: break-word! Important;" <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> </plugins> </build> </project>Copy the codeThe structure of the project is roughly
In order to use Sharding – JDBC, one of the main tasks is to configure the sharding rules. Before configuring the sharding rules, I create some library tables. As shown in the figure, I create two libraries DS0 and DS1 respectively in the master and slave database, with the tables t_user1 and T_user2 in each library
Then configure the sharding- JDBC sharding rule in application.properties
server.port=8080 spring.application.name=sharding-springboot mybatis.mapper-locations=classpath:mapper/*.xml spring.shardingsphere.datasource.names= m1,m2,s1,s2 spring.shardingsphere.datasource.m1.type= com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.m1.driver-class-name= com.mysql.jdbc.Driver spring.shardingsphere.datasource.m1.url= jdbc:mysql://localhost:3306/ds0 spring.shardingsphere.datasource.m1.username= root spring.shardingsphere.datasource.m1.password= root spring.shardingsphere.datasource.s1.type= com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.s1.driver-class-name= com.mysql.jdbc.Driver spring.shardingsphere.datasource.s1.url= jdbc:mysql://localhost:3307/ds0 spring.shardingsphere.datasource.s1.username= root spring.shardingsphere.datasource.s1.password= root spring.shardingsphere.datasource.m2.type= com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.m2.driver-class-name= com.mysql.jdbc.Driver spring.shardingsphere.datasource.m2.url= jdbc:mysql://localhost:3306/ds1 spring.shardingsphere.datasource.m2.username= root spring.shardingsphere.datasource.m2.password= root spring.shardingsphere.datasource.s2.type= com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.s2.driver-class-name= com.mysql.jdbc.Driver spring.shardingsphere.datasource.s2.url= jdbc:mysql://localhost:3307/ds1 spring.shardingsphere.datasource.s2.username= Root spring. Shardingsphere. The datasource. S2. Password = # root main library from the library spring.shardingsphere.sharding.master-slave-rules.ds1.master-data-source-name=m1 spring.shardingsphere.sharding.master-slave-rules.ds1.slave-data-source-names=s1 spring.shardingsphere.sharding.master-slave-rules.ds2.master-data-source-name=m2 Spring. Shardingsphere. Sharding. Master - slave - rules. Ds2. The slave - data - the source - names = s2 # shard key library configuration and subdivision algorithm spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=user_id Spring. Shardingsphere. Sharding. Default - database - strategy. The inline. Algorithm - expression = ds $- > {user_id % 2 + 1} # table configuration shard key and subdivision algorithm spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=ds$->{1.. 2}.t_user$->{1} spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.sharding-column=user_id spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.algorithm-expression=t_user$->{1} spring.shardingsphere.sharding.tables.t_user.key-generator.column=user_id spring.shardingsphere.sharding.tables.t_user.key-generator.type=SNOWFLAKE spring.shardingsphere.props.sql.show=true logging.level.root=info logging.level.org.springframework.web=infoCopy the codeM1 and M2 correspond to ds0 and DS1 of the primary library. S1 and S2 pairs should be from ds0 and DS1 of the library. This section describes the configuration of master and slave and the configuration of database and table
# Use master-data-source-name and slave-data-source-names to identify master and slave libraries spring.shardingsphere.sharding.master-slave-rules.ds1.master-data-source-name=m1 spring.shardingsphere.sharding.master-slave-rules.ds1.slave-data-source-names=s1 spring.shardingsphere.sharding.master-slave-rules.ds2.master-data-source-name=m2 Spring. Shardingsphere. Sharding. Master - slave - rules. Ds2. The slave - data - the source - names = s2 # shard key library configuration and subdivision algorithm spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=user_id spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds$->{user_id % 2+1}Copy the codeDs $->{user_id % 2+1} ds$->{user_id % 2+1} ds$->{user_id % 2+1} ds$->{user_id % 2+1
T_user1; t_user2; t_user$->{1.. 2} can spring. Shardingsphere. Sharding. Name t_user. Actual data - nodes = ds $- > {1.. 2}. T_user $- > {1} # specifies the shard key table and subdivision algorithm. The spring shardingsphere. Sharding. Name t_user. Table - strategy. The inline. Sharding - column = user_id Spring. Shardingsphere. Sharding. Tables. T_user. Tablestrategy. Inline. The algorithm - expression = t_user $- > {1} # specified table's primary key generation strategy for snow algorithm spring.shardingsphere.sharding.tables.t_user.key-generator.column=user_id spring.shardingsphere.sharding.tables.t_user.key-generator.type=SNOWFLAKECopy the codeOk configuration introduction is finished, let’s write an insert method, then start the project, see the effect, insert method is as follows
It’s successful. Let’s check the database
It is found that the user_id with even numbers is in T_user1 table of DS0, and the user_id with odd numbers is in T_user1 table of DS1. It is also found in the log that all data is inserted into the primary database
Then let’s write a query method to see if it is found from the slave database, and steal an ID query from the master database
@RequestMapping("/getUser")
@ResponseBody
public User getUser() {
User user = userMapper.get(633690716052127744l);
return user;
}
Copy the codeThe log is queried from the slave library
mycat
1 download mycat
Before downloading MyCat, ensure that jdK1.7 or later is installed. Then download MyCat from the official website and decompress it
Look at the three files under conf
1.server.xml
After downloading, there should be a logical library named TESTDB and two users, root and user, as shown in the figure above
2.schema.xml
<? The XML version = "1.0"? > <! DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100"> <! -- auto sharding by id (long) rule="auto-sharding-long" --> <table name="t_user1" primaryKey="user_id" dataNode="dn1,dn2" rule="crc32slot"/> <table name="t_user2" primaryKey="user_id" dataNode="dn1,dn2" rule="crc32slot1"/> </schema> <dataNode name="dn1" dataHost="localhost1" database="ds0" /> <dataNode name="dn2" dataHost="localhost1" database="ds1" /> <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <! -- can have multi write hosts --> <writeHost host="hostM1" url="localhost:3306" user="root" password="root"> <! -- can have multi read hosts --> <readHost host="hostS2" url="localhost:3307" user="root" password="root" /> </writeHost> </dataHost> </mycat:schema>Copy the codeThe top section defines table names, primary keys, distribution nodes, and sharding algorithms, and the bottom section defines read-write routing. This is easy to understand and I won’t go over here.
This file is used to define sharding rules. The crc32slot rule is defined in this file. If a table uses this method, it will not be used
Start the mycat
Run as an administrator in the bin directory
mycat.bat install
Copy the codeViewing the Service list
And then you log in to Navicat with the user name and the password that is configured in server.xml
To see what happens, add a new piece of data to mycat
Finally, let’s use JMeter to press these two methods
sharding-jdbc
100 thread count loop 10 times, insert 10 data at a time, total insert 10000 data, let’s try
There are 5008 in DS1
Throughput is around 116
mycat
conclusion
Ha ha ha ha ha ha ha ha ha ha ha ha ha ha. From the test data myCat than Sharding – JDBC or almost, of course, this and the configuration of the server has a great relationship, you test the words may not be the same ah, I just in accordance with my test data, in fact, write this article is mainly to play, so you don’t take it too seriously. The next plan is to write one more article every week. I haven’t decided what to write next. See you next week