An overview,

Because of the large amount of data, the performance problem of relational database is caused.

1. What is sub-database sub-table?

With the rapid development of services, the amount of data increases sharply, and the performance of the database becomes worse and worse. There are two solutions:

Plan a

Increase hardware, improve memory, CPU, etc., but the cost is too high, if the main problem is mysql, increase hardware, improve performance is very limited

Scheme 2

The data according to certain rules for dividing the different database, make a single database data quantity of small to relieve the single database performance issues, so as to enhance the purpose of the database performance, such as the electric commercial database, broken down into user base, products, orders and so on, the library will be large table into several small table, through the way to solve the problem of database performance.

== Split database and table is to solve the problem of database performance degradation caused by large amount of data. The original independent database is split into several databases, and the large data table is split into several small tables, so that the amount of data in a single database and data table becomes less, and the purpose of improving database performance is achieved. = =

2. Separate database and table

Vertical sub-table vertical sub-database horizontal sub-table horizontal sub-tableCopy the code

2.1 Vertical sub-table

Now, there is a table called commodity information, as follows:

The field name	The field type
id	bigint
Belongs to the store	bigint
Name of commodity	varchar
specifications	varchar
place of origin	varchar
Commodity prices	decimal
Commodity images	varchar
Commodity description	text

For users, generally only to check the goods list, only interested in a certain commodity, can click into to check the goods description, commodity description field visit frequency is low, and that field is the text takes up the space is large, access to a single data IO time is long, the other fields visit frequency is high, so it will be broken down into the following two tables:

Commodity information sheet

The field name	The field type
id	bigint
Belongs to the store	bigint
Name of commodity	varchar
specifications	varchar
place of origin	varchar
Commodity prices	decimal
Commodity images	varchar

Commodity description sheet

The field name	The field type
id	bigint
Product id	bigint
Commodity description	text

Advantage:

1. Avoids I/O contention and reduces the possibility of table lock. Users who view product information and users who view product description are not affected by each other. 2. Give full play to the operation efficiency of popular data, and the high frequency of operation of commodity information will not be dragged down by the low efficiency of commodity descriptionCopy the code

Split principle:

2. Separate large fields such as text and BLOb into attached tables 3. Frequently combined query columns are placed in a tableCopy the code

2.2 Vertical branch library

Vertical branch refers to the classification of tables according to business == =, ++ distribution to different databases, each library can be placed on different servers ++, its core concept is dedicated library dedicated

advantage

2. Hierarchical management, maintenance, monitoring, and expansion of data of different services 3. In high concurrency scenarios, vertical database separation improves I/O and database connections to a certain extent, and reduces the bottleneck of hardware resources on a single machineCopy the code

Different databases can be deployed on different servers. In this way, multiple servers share the burden. However, the problem of large data volume in a single table is still not solved

2.3 Level table

-- -- -- -- -- -- -- -- -- -- > commodity information Table 1 commodity information Table (product id % 2 + 1) -- -- -- -- -- -- -- -- -- -- > commodity information in Table 2Copy the code

Commodity information table according to the rules of (product id % 2 + 1), broken down into two tables, the fields, and so on are all consistent, when deposited in the commodity information, first of all determine whether id for even, if even just placed, commodity information table 1 】 【 if for odd number, are placed into the commodity information table 2 】, so as to solve the problem of the large amount of single table data, == A small table contains only part of the data, which reduces the amount of data in a single table and improves the retrieval ability. = =

The rules

Item ID % 2 + 1Copy the code

Note: Split table data does not affect the table structure

advantage

Optimize performance problems caused by a large amount of data in a single table. 2. Avoid I/O contention and reduce the possibility of table lockCopy the code

2.4 Horizontal repository

Take the commodity information table as an example (commodity database) :

The field name	The field type
id	bigint
Belongs to the store	bigint
Name of commodity	varchar
specifications	varchar
place of origin	varchar
Commodity prices	decimal
Commodity images	varchar
Commodity description	text

Break up

-- -- -- -- -- -- -- -- -- -- -- -- > commodities library 1 library (store ID % 2 + 1) -- -- -- -- -- -- -- -- -- -- -- -- > commodity base 2Copy the code

If the store ID is odd, it will be allocated to [Storehouse 1], and if it is even, it will be allocated to [Storehouse 2].

Horizontal database is to split the data of the same table into different databases according to certain rules, and each database can be placed on different servers.

== Vertical partition is to split different tables into different databases. It splits data rows without affecting table structure ==

advantage

1. Solve the bottleneck of single database big data and high concurrency 2. Improve system stability and availabilityCopy the code

Third, sharing – JDBC

3.1 What is Sharding-JDBC?

Sharding-jdbc, Sharding-Proxy, and Sharding-Sidecar (planned) is an open source ecosystem of distributed database middleware solutions composed of three independent products. They all provide standardized data sharding, distributed transaction and database governance functions, which can be applied to diverse application scenarios such as Java isomorphism, heterogeneous languages, cloud native and so on.Copy the code

• Works with any JDBC-based ORM framework, such as JPA, Hibernate, Mybatis, Spring JDBC Template or directly using JDBC.
• Support any third party database connection pool, such as DBCP, C3P0, BoneCP, Druid, HikariCP, etc.
• Support for any database that implements the JDBC specification. Currently supports MySQL, Oracle, SQLServer, PostgreSQL and any database that complies with the SQL92 standard.

3.2 features

Data fragmentation

1. Sub-library & sub-table
2. Reading and writing separation
3. Customize sharding policies
4. Decentralized distributed primary keys

Distributed transaction

1. Standardized transaction interface
2. XA strong consistent transactions
3. Flexible transaction

Database governance

1. Configuration dynamic
2. Orchestration & Governance
3. Data desensitization
4. Visual link tracking
5. Elastic expansion (under planning)

3.3 speaking

3.3.1 Level table

Create table couser_1,couser_2

create table couser_1(
	cid bigint(20) primary key,
	cname varchar(50) not null,
	user_id bigint(20) not null,
	cstatus varchar(10) not null
)


create table couser_2(
	cid bigint(20) primary key,
	cname varchar(50) not null,
	user_id bigint(20) not null,
	cstatus varchar(10) not null
)
Copy the code

(2) Build springboot project

Rely on

<dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency>  <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> The < version > 1.1.20 < / version > < / dependency > <! --sharding-jdbc--> <dependency> <groupId>org.apache.shardingsphere</groupId> < artifactId > sharding - JDBC - spring - the boot - starter < / artifactId > < version > 4.0.0 - RC1 < / version > < / dependency > < the dependency > < the groupId > com. Baomidou < / groupId > < artifactId > mybatis - plus - the boot - starter < / artifactId > < version > 3.0.5 < / version > </dependency>Copy the code

Configuration file (Sharding Policy)

According to the official website specification configuration:

= = shardingsphere.apache.org/document/le…

# # # # # # # # # # # # # # # # sharding - JDBC shard strategy configuration # # # # # # # # # # # # # # # # # # # with an entity class not corresponding to the two tables, although two tables are consistent, To solve the abnormal spring. But you need to set up the main. Allow - bean - definition - the overriding = true # data take alias spring. Shardingsphere. The datasource. Names = m1 # configuration data sources, spring. Shardingsphere. The datasource. The ds0, datasource data source behind an alias, Need and spring. Shardingsphere. The datasource. Names the same configuration spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.m1.url=jdbc:mysql://localhost:3306/sharding-jdbc?serverTimezone=GMT%2B8 spring.shardingsphere.datasource.m1.username=root spring.shardingsphere.datasource.m1.password=yang19960127 Couser (couser); Because the database is split into two tables (couser_1,couser_2), the couser field in tables. Couser is custom spring.shardingsphere.sharding.tables.couser.actual-data-nodes=m1.couser_$->{1.. 2} # sharding basis, According to cid shard spring. Shardingsphere. Sharding. Name couser. Table - strategy. The inline. Sharding - column = cid # according to cid % 2 + 1 to calculate the need to manipulate data table, The even number is couser_1, Odd for couser_2 spring. Shardingsphere. Sharding. Name couser. Table - strategy. The inline. Algorithm - expression = couser_ $- > {cid % 2+1} # primary key generation strategy Using the algorithm of snowflakes, the generated for cid spring. Shardingsphere. Sharding. Name couser. Key - the generator. The column = cid spring.shardingsphere.sharding.tables.couser.key-generator.type=SNOWFLAKECopy the code

The test code

@Autowired private CouserMapper couserMapper; @test void contextLoads() {/** * Since the primary key generation policy is already configured in the configuration file, there is no need to set the primary key value */ Couser = new Couser(); Couser. SetCname (Java development ""); couser.setCstatus("1"); couser.setUserId(20L); couserMapper.insert(couser); } @Test void selectCouserTest(){ List<Couser> cousers = couserMapper.selectList(null); System. The out. Println (" length: "+ cousers. The size ()); }Copy the code

Q&A

1.Cause: javax.xml.bind.JAXBException
Copy the code

Solution:

The JAXB API is a Java EE API, so this Jar package is no longer included in Java SE 9.0. The concept of modules was introduced in Java 9. By default, Java SE will no longer contain Java EE JARS, which were bundled together in Java 6/7/8Copy the code

Increase dependence:

<dependency> <groupId>javax.xml.bind</groupId> <artifactId> JAXB-API </artifactId> <version>2.3.0</version> </dependency> < the dependency > < groupId > com. Sun. XML. Bind < / groupId > < artifactId > jaxb - impl < / artifactId > < version > 2.3.0 < / version > </dependency> <dependency> <groupId>com.sun.xml.bind</groupId> <artifactId>jaxb-core</artifactId> < version > 2.3.0 < / version > < / dependency > < the dependency > < groupId > javax.mail. Activation < / groupId > < artifactId > activation < / artifactId > < version > 1.1.1 < / version > < / dependency >Copy the code

2.Consider renaming one of the beans or enabling overriding by setting spring.main.allow-bean-definition-overriding=true
Copy the code

Solution:

Since one entity class cannot correspond to two tables, although couser_1 and couser_2 structures and fields are the same, the following needs to be configured in the configuration file: spring.main.allow-bean-inferion-overriding =trueCopy the code

3.3.2 Level classification

(1) Create a database

• Create two databases: edu_db_1 and edu_db_2
• Each database has two tables, COUser_1 and COUser_2

        --------> couser_1(table)
edu_db_1
        --------> couser_2(table)


        --------> couser_1(table)
edu_db_2
        --------> couser_2(table)
Copy the code

demand

Database rule: (1) add even user_id to edu_db_1 (2) add odd user_id to edu_db_2 table rule: (1) Add even CID to couser_1 (2) Add odd CID to couser_2Copy the code

SQL for creating databases and tables

==edu_db_1:==

USE edu_db_1;

DROP TABLE IF EXISTS `couser_1`;
CREATE TABLE `couser_1` (
  `cid` bigint(20) NOT NULL,
  `cname` varchar(50) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `cstatus` varchar(10) NOT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `couser_2`;
CREATE TABLE `couser_2` (
  `cid` bigint(20) NOT NULL,
  `cname` varchar(50) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `cstatus` varchar(10) NOT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Copy the code

==edu_db_2:==

USE edu_db_2;

DROP TABLE IF EXISTS `couser_1`;
CREATE TABLE `couser_1` (
  `cid` bigint(20) NOT NULL,
  `cname` varchar(50) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `cstatus` varchar(10) NOT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `couser_2`;
CREATE TABLE `couser_2` (
  `cid` bigint(20) NOT NULL,
  `cname` varchar(50) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `cstatus` varchar(10) NOT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Copy the code

(2) Configuration file

# Since one entity class cannot correspond to two tables, even though both tables are identical, To solve the abnormal spring. But you need to set up the main. Allow - bean - definition - the overriding = true # # # # # # # # # # # # # # # # # # # # # # # # # # data source configuration # # # # # # # # # # # # # # # # # # # # # # # # # # # take alias data source, the level of depots need to create two data sources alias m1, m2 spring. Shardingsphere. The datasource. Names = m1, m2 # configuration data sources, due to the level of depots, Behind the need to create two data sources, including the datasource name must and spring shardingsphere. The datasource. Names = m1, m2 is the same configuration spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.m1.url=jdbc:mysql://localhost:3306/edu_db_1?serverTimezone=GMT%2B8 spring.shardingsphere.datasource.m1.username=root spring.shardingsphere.datasource.m1.password=yang19960127 spring.shardingsphere.datasource.m2.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.m2.driver-class-name=com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.m2.url=jdbc:mysql://localhost:3306/edu_db_2?serverTimezone=GMT%2B8 spring.shardingsphere.datasource.m2.username=root spring.shardingsphere.datasource.m2.password=yang19960127 # # # # # # # # # # # # # # # # # # # # # # # # # # data table shard strategy # # # # # # # # # # # # # # # # # # # # # # # # # # # for horizontally depots, so there are two data sources m1, m2, each with two tables in a database, so needs to be configured to m $- > {1.. 2}.couser_$->{1.. 2} # edu for custom name spring. Shardingsphere. Sharding. Name couser. Actual data - nodes = m $- > {1.. 2}.couser_$->{1.. 2} # sharding basis, According to cid shard spring. Shardingsphere. Sharding. Name couser. Table - strategy. The inline. Sharding - column = cid # according to cid % 2 + 1 to calculate the need to manipulate data table, The even number is couser_1, Odd for couser_2 spring. Shardingsphere. Sharding. Name couser. Table - strategy. The inline. Algorithm - expression = couser_ $- > {cid % 2+1} # primary key generation strategy Using the algorithm of snowflakes, the generated for cid spring. Shardingsphere. Sharding. Name couser. Key - the generator. The column = cid Spring. Shardingsphere. Sharding. Tables. Couser. Key - generator. Type = SNOWFLAKE # # # # # # # # # # # # # # # # # # # # # # # # # # database subdivision strategy # # # # # # # # # # # # # # # # # # # # # # # # # # # the default database - strategy said all the table to do so #spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=user_id #spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=m$->{user_id % 2+1} If user_id is even, edu_db_1 will be sharded, and if edu_db_1 is odd, Edu_db_2 # edu for custom name spring. Shardingsphere. Sharding. Name couser. Database - strategy. The inline. Sharding - column = user_id spring.shardingsphere.sharding.tables.couser.database-strategy.inline.algorithm-expression=m$->{user_id % 2+1} Print # open SQL statements spring. Shardingsphere. Props. SQL. The show = trueCopy the code

3.3.3 Vertical branch library

Special library special table special library special

user_db

    t_user (table)
Copy the code

course_db

    t_course (table)
Copy the code

##### (1) Configuration file

# # # # # # # # # # # # # # # # # # # # # # # # # # data source configuration # # # # # # # # # # # # # # # # # # # # # # # # # # # take alias data sources, Level depots need to create two data sources alias m0, m1 spring. Shardingsphere. The datasource. Names = m0, m1 # configuration data sources, due to the level of depots, Behind the need to create two data sources, including the datasource name must and spring shardingsphere. The datasource. Names = m1, m2 is the same configuration spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.m1.url=jdbc:mysql://localhost:3306/course_db?serverTimezone=GMT%2B8 spring.shardingsphere.datasource.m1.username=root spring.shardingsphere.datasource.m1.password=yang19960127 spring.shardingsphere.datasource.m0.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.m0.driver-class-name=com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.m0.url=jdbc:mysql://localhost:3306/user_db?serverTimezone=GMT%2B8 spring.shardingsphere.datasource.m0.username=root spring.shardingsphere.datasource.m0.password=yang19960127 # # # # # # # # # # # # # # # # # # # # # # # # # # data table shard strategy # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # configuration user_db t_user in database Specialized library # # # # # # # # # # # # # # # # # # # binding t_user table using data source spring. Shardingsphere. Sharding. Name the user. The actual data - nodes = m0. # t_user primary key generation strategy, Using the algorithm of snowflakes, the generated for cid spring. Shardingsphere. Sharding. Name the user. The key to the generator. The column = user_id spring.shardingsphere.sharding.tables.user.key-generator.type=SNOWFLAKE # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # the inside of the configuration database course_db t_course specialized library # # # # # # # # # # # # # # # binding t_course table using the data source Spring. Shardingsphere. Sharding. Tables. Course. Actual data - nodes = m1. # t_course primary key generation strategy, Using the algorithm of snowflakes, the generated for cid spring. Shardingsphere. Sharding. Tables. The course. The key to the generator. The column = cid Spring. Shardingsphere. Sharding. Tables. The course. The key - generator. Type = # SNOWFLAKE open print SQL statements spring.shardingsphere.props.sql.show=trueCopy the code

##### (2) Test code

@Autowired private CourseMapper courseMapper; @Autowired private UserMapper userMapper; @Test void contextLoads() { User user = new User(); user.setUsername("yangzinan"); user.setUstatus("success"); userMapper.insert(user); } @Test void courseTest(){ Course course = new Course(); Java development course. SetCname (" "); course.setCstatus("success"); course.setUserId(1313123205275234305L); courseMapper.insert(course); }Copy the code

Q&A

Cause: java.lang.IllegalStateException: Missing the data source name: 'null'
Copy the code

Solution:

Because usermapper.insert () does not know which table it is looking for, it needs to specify the TableName in the entity using @tablename

@Data
@TableName("t_user")
public class User {
    @TableId
    private Long userId;
    private String username;
    private String ustatus;
}
Copy the code

We do common table

For example, the uSTATUS field in t_user table, we agree that 1 represents normal, 0 represents locked, and we put the state in another table, which is called public table, as shown below:

t_user

user_id               10001
ustatus                 1
Copy the code

t_common

Id 10001 ustatus 1 name Normal account ID 10001 ustatus 0 name Locked accountCopy the code

T_common (ustatus=1) {ustatus=1} {ustatus=1} {ustatus=1} {ustatus=1}

(1) Create a common table with the same structure in multiple databases

Create t_common tables in user_DB and t_common in course_DB, but the T_common tables need to be the same

create table t_common(
	did bigint(20) primary key,
	dstatus varchar(20) not null,
	dvalue varchar(20) not null
)
Copy the code

(2) Test the code

Create a common table entity

@Data
@ToString
@TableName("t_common")
public class Common {
    private Long did;
    private String dstatus;
    private String dvalue;
}
Copy the code

Create mapper

@Mapper
public interface CommonMapper extends BaseMapper<Common> {
}
Copy the code

Test

== key ⚠️== : as long as the new, delete operation will be on all the same T_common table operation, all table new fields, delete fields, query, the same data will only have a, will not appear repeated

@Autowired private CommonMapper commonMapper; / / @test void addCommon() {Common Common = new Common(); common.setDstatus("1"); Common.setdvalue (" Account normal "); commonMapper.insert(common); } @test void delCommon(){commonMapper.deleteById(commonMapper.deletebyId);} @test void delCommon(){commonmapper.deletebyId (commonmapper.deletebyId); } @Test void findCommon(){ List<Common> commons = commonMapper.selectList(null); System.out.println(commons); }Copy the code

3.3.5 Read/write Separation

Master Host Slave SlaveCopy the code

Generally speaking, the master performs write operations, the slave performs read operations, and the master slave performs data replication to ensure data consistency. To improve database stability, many databases have dual-system hot backup.

A master-slave replication

When the primary server has a write (INSERT /update/dalete) statement, the secondary server automatically goes back

Master-slave synchronization between the Mysql server is based on the binary log mechanism, the main server using binary log to record changes in the database, through read and execute the log file from the server to maintain the servers and the main data is consistent, have the following functions: 1, the primary database appear problem, you can switch to from the database. 2, can be database level read and write separation. 3, you can do daily backup from the database.Copy the code

Reading and writing separation

Insert/UPDATE/DELETE statements operate on one server and select on the other

Sharding-jdbc read/write separation routes read and write operations to master slave respectively according to SQL semantic analysis. It provides transparent read/write separation, allowing users to use master/slave database clusters as much as possible, but specific read/write separation, The master slave of mysql needs to be configured, sharding-JDBC cannot achieve read-write separation, only after the implementation of mysql itself, the shading -JDBC is used for coordination between master slaves, first of all, the master slave replication of mysql needs to be implemented.

---

Mysql primary/secondary replication ==

(1) Install two mysql services with the following names:

mysql-master
mysql-slave
Copy the code

You can decompress the mysql.zip file into two files, and use the following names to customize them. The current two files are the same, except that the port numbers in the my.ini file are 3307 and 3308

(2) Start the service

    mysql-master>bin>mysqld.exe
    
    mysql-slave>bin>mysqld.exe
Copy the code

(3) Configure mysql-master

Step 1: my. Ini

Server_id =20 # enable log-bin=mysql-binlogCopy the code

Step 2: The master opens an account that can be synchronized with the slave

(1) First, you can view the account owned by the master

use mysql; select user,password,host from user; +------+-------------------------------------------+-----------+ | user | password | host | +------+-------------------------------------------+-----------+ | root | *2B4E16AB0D3E65D267D15EDE48E0FD368EADD875 | Localhost | | root | | 127.0.0.1 | | root | | : : 1 | | | | localhost | +------+-------------------------------------------+-----------+Copy the code

(2) create user (master) :

create user 'repl'@'localhost' identified by 'yang19960127'; +------+-------------------------------------------+-----------+ | user | password | host | +------+-------------------------------------------+-----------+ | root | *2B4E16AB0D3E65D267D15EDE48E0FD368EADD875 | Localhost | | root | | 127.0.0.1 | | root | | : : 1 | | | | localhost | | repl b4e16ab0d3e65d267d15ede48e0fd368eadd875 | * 2 | localhost | + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + explanation: Yang19960127 create user 'username'@'host' identified by 'password' 1. If mysql is not on the same server, set host to the IP address of slave, for example, 172.0.0.2 (slave), set host to 172.0.0.% % means wildcard, The slave IP address starting with 172.0.0 can be accessed.Copy the code

(3) Finally, set the permission of the repl user, set the permission of the binlog user (copy permission) :

Grant replication slave on *.* to 'repl'@'localhost' identified by 'yang19960127'; Flush PRIVILEGES; Grant replication slave on *.* to, where *.* represents what table in what database, e.g. Gofly.user (user table in gofly database) *.*Copy the code

Step 3: Set the slave

(1) the configuration my. Ini (slave) :

[mysqld]

port=3307

server_id =21
Copy the code

(2) to master

1. Run the show master status command to check log_file and position of the master. +---------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------------+----------+--------------+------------------+-------------------+ | mysql-binlog.000002 | 120 | | | | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 2, set on the command slave master information: Stop slave stop slave; change master to master_user='repl',master_password='yang19960127',master_port=3308,master_host='localhost',master_log_file='mysql-binlog .000002',master_log_pos=120; 1. Master_user master_password: (2) master_port: indicates the mysql port number of the master. (The default port number is 3306. You can set other port numbers in master my.ini.) (3) master_host: indicates the address of the master (4) master_log_file master_log_pos=120: show master status; # start slave start slave; show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: localhost Master_User: repl Master_Port: 3308 Connect_Retry: 60 Master_Log_File: mysql-binlog.000002 Read_Master_Log_Pos: 476 Relay_Log_File: DESKTOP-R5FUEVJ-relay-bin.000002 Relay_Log_Pos: 286 Relay_Master_Log_File: mysql-binlog.000002 Slave_IO_Running: Slave_IO_Running: Yes Slave_SQL_Running: YesCopy the code

== Sharding-JDBC read/write separation ==

TODO: Waiting to be replenished

Copy the code