Read write separation is to ensure the stability of database products , Many databases have dual hot standby function .

principle ： Let the main database （master） Transactional increase 、 Change 、 Delete operation , And from the database （slave） Handle select Query operation .

Be careful ：

• Sharding-JDBC adopt sql Sentence semantic analysis , Realize the separation of reading and writing , No data synchronization .
• Data synchronization requires the database to build a master-slave cluster architecture , To do master-slave synchronization between data .

The master-slave cluster architecture of the database is built by itself , The following from Sharding-JDBC Application layer to separate data reading and writing .

One 、 Read / write separation

1、application.properties The configuration file

stay application.properties This is done in the configuration file

#  Configure real data sources 
spring.shardingsphere.datasource.names=db1,db2
#  Configure the 1 Data sources 
spring.shardingsphere.datasource.db1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.db1.url=jdbc:mysql://localhost:3306/sharding_db1?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=GMT
spring.shardingsphere.datasource.db1.username=root
spring.shardingsphere.datasource.db1.password=123456
#  Configure the 2 Data sources 
spring.shardingsphere.datasource.db2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.db2.url=jdbc:mysql://localhost:3306/sharding_db2?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=GMT
spring.shardingsphere.datasource.db2.username=root
spring.shardingsphere.datasource.db2.password=123456

## Configure master-slave logical data sources , Read write separation rules , db1 Main library ,db2 Slave Library 
spring.shardingsphere.sharding.master-slave-rules.ds0.master-data-source-name=db1
spring.shardingsphere.sharding.master-slave-rules.ds0.slave-data-source-names[0]=db2
# Table fragmentation based on read-write separation 
spring.shardingsphere.sharding.tables.course.actual-data-nodes=ds0.course_$->{
   1..2}

#  Specifies the primary key generation policy for the table 
spring.shardingsphere.sharding.tables.course.key-generator.column=id
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.course.key-generator.props.worker.id=1
# Table strategy 
spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{id%2+1}

#  open shardingsphere Of sql Log output .
spring.shardingsphere.props.sql.show=true

#  An entity class corresponds to two tables , Cover 
spring.main.allow-bean-definition-overriding=true

2、 test

1） preservation

   @Test
    public void testSave() throws InterruptedException {
    
        for (int i = 0; i < 4; i++) {
    
            Course course = new Course();
            course.setGmtCreate(new Date());
            course.setName("java");
            course.setUserId(1001L + i);
            course.setStatus("1");

            TimeUnit.MILLISECONDS.sleep(50);
            int count = courseMapper.insert(course);
            System.out.println("id ->" + course.getId());
        }

    }

From the log , The databases for saving operations are all primary databases （db1）.

2） Inquire about

    @Test
    public void testIN() {
    
        QueryWrapper<Course> wrapper = new QueryWrapper<Course>();
        wrapper.in("id",1515577381737865217L, 1515492164109004802L, 1515576508269936641L);
        List<Course> courses = courseMapper.selectList(wrapper);
        System.out.println(courses.size());
        courses.forEach(course -> System.out.println(course));
    }

From the log , The database of query operation is from the database （db2）.

because id=1515577381737865217 Record in main library （db1） in . The data has not been synchronized to the slave Library （db2）, So we can't find it .

– Stay Hungry , stay foolish .