spring boot shardingsphere mybatis-plus druid mysql 搭建mysql数据库读写分离架构

spring boot shardingsphere mybatis-plus druid mysql 搭建mysql数据库读写分离架构

##关于window mysql主从搭建简单教程

传送门 window mysql5.7 搭建主从同步环境-CSDN博客

##父pom.xml

<?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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.2.2.RELEASE</version>
    </parent>
    <groupId>com.yym</groupId>
    <artifactId>yympro</artifactId>
    <version>1.0</version>
    <packaging>pom</packaging>


</project>

##模块pom.xml

<?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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <parent>
    <groupId>com.yym</groupId>
    <artifactId>yympro</artifactId>
    <version>1.0</version>
  </parent>
  <groupId>com.yym</groupId>
  <artifactId>service</artifactId>
  <version>1.0</version>
  <packaging>jar</packaging>

  <name>service</name>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <maven.compiler.source>11</maven.compiler.source>
    <maven.compiler.target>11</maven.compiler.target>
    <alibaba-druid>1.1.22</alibaba-druid>
    <dynamic-datasource-spring-boot-starter.version>3.1.1</dynamic-datasource-spring-boot-starter.version>
    <mybatis-plus-boot-starter>3.3.2</mybatis-plus-boot-starter>
    <shardingsphere>4.1.1</shardingsphere>
  </properties>

  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter</artifactId>
    </dependency>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>druid</artifactId>
      <version>${alibaba-druid}</version>
    </dependency>
    <dependency>
      <groupId>com.baomidou</groupId>
      <artifactId>mybatis-plus-boot-starter</artifactId>
      <version>${mybatis-plus-boot-starter}</version>
    </dependency>
    <!--<dependency>
      <groupId>com.baomidou</groupId>
      <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
      <version>${dynamic-datasource-spring-boot-starter.version}</version>
    </dependency>-->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
    </dependency>
    <dependency>
      <groupId>org.apache.shardingsphere</groupId>
      <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
      <version>${shardingsphere}</version>
    </dependency>
  </dependencies>

  <build>
    <pluginManagement><!-- lock down plugins versions to avoid using Maven defaults (may be moved to parent pom) -->
      <plugins>
        <!-- clean lifecycle, see https://maven.apache.org/ref/current/maven-core/lifecycles.html#clean_Lifecycle -->
        <plugin>
          <artifactId>maven-clean-plugin</artifactId>
          <version>3.1.0</version>
        </plugin>
        <!-- default lifecycle, jar packaging: see https://maven.apache.org/ref/current/maven-core/default-bindings.html#Plugin_bindings_for_jar_packaging -->
        <plugin>
          <artifactId>maven-resources-plugin</artifactId>
          <version>3.0.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-compiler-plugin</artifactId>
          <version>3.8.0</version>
        </plugin>
        <plugin>
          <artifactId>maven-surefire-plugin</artifactId>
          <version>2.22.1</version>
        </plugin>
        <plugin>
          <artifactId>maven-jar-plugin</artifactId>
          <version>3.0.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-install-plugin</artifactId>
          <version>2.5.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-deploy-plugin</artifactId>
          <version>2.8.2</version>
        </plugin>
        <!-- site lifecycle, see https://maven.apache.org/ref/current/maven-core/lifecycles.html#site_Lifecycle -->
        <plugin>
          <artifactId>maven-site-plugin</artifactId>
          <version>3.7.1</version>
        </plugin>
        <plugin>
          <artifactId>maven-project-info-reports-plugin</artifactId>
          <version>3.0.0</version>
        </plugin>
        <plugin>
          <groupId>org.springframework.boot</groupId>
          <artifactId>spring-boot-maven-plugin</artifactId>
        </plugin>
      </plugins>
    </pluginManagement>
  </build>
</project>

##yml配置

server:
  port: 8866
  servlet:
    context-path: /yym
  tomcat:
    max-threads: 300
    connection-timeout: 57000
    max-connections: 500
  connection-timeout: 57000
spring:
  datasource:
    dynamic:
      primary: master
      strict: false # 严格模式
      datasource:
        master:
          type: com.alibaba.druid.pool.DruidDataSource
          driver-class-name: com.mysql.cj.jdbc.Driver
          url: jdbc:mysql://192.168.3.156:3306/yymdb?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
          username: yym
          password: 123456
          druid:
            initialSize: 5
            minIdle: 5
            maxActive: 20
            maxWait: 60000
            timeBetweenEvictionRunsMillis: 60000
            minEvictableIdleTimeMillis: 300000
            validationQuery: SELECT 1 FROM DUAL
            testWhileIdle: true
            testOnBorrow: false
            testOnReturn: false
            webStatFilter:
              enabled: true
            statViewServlet:
              enabled: true
              url-pattern: /druid/*
              # 控制台管理用户名和密码
              login-username: admin
              login-password: admin
            filter:
              stat:
                enabled: true
                # 慢SQL记录
                log-slow-sql: true
                slow-sql-millis: 1000
                merge-sql: true
              wall:
                config:
                  multi-statement-allow: true
  shardingsphere:
    datasource:
      names: master,slave0
      master:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.3.188:3308/yymdb?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
        username: yym
        password: 123456
        druid:
          initialSize: 5
          minIdle: 5
          maxActive: 20
          maxWait: 60000
          timeBetweenEvictionRunsMillis: 60000
          minEvictableIdleTimeMillis: 300000
          validationQuery: SELECT 1 FROM DUAL
          testWhileIdle: true
          testOnBorrow: false
          testOnReturn: false
          webStatFilter:
            enabled: true
          statViewServlet:
            enabled: true
            url-pattern: /druid/*
            # 控制台管理用户名和密码
            login-username: admin
            login-password: admin
          filter:
            stat:
              enabled: true
              # 慢SQL记录
              log-slow-sql: true
              slow-sql-millis: 1000
              merge-sql: true
            wall:
              config:
                multi-statement-allow: true
      slave0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.3.188:3309/yymdb?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
        username: yym
        password: 123456
        druid:
          initialSize: 5
          minIdle: 5
          maxActive: 20
          maxWait: 60000
          timeBetweenEvictionRunsMillis: 60000
          minEvictableIdleTimeMillis: 300000
          validationQuery: SELECT 1 FROM DUAL
          testWhileIdle: true
          testOnBorrow: false
          testOnReturn: false
          webStatFilter:
            enabled: true
          statViewServlet:
            enabled: true
            url-pattern: /druid/*
            # 控制台管理用户名和密码
            login-username: admin
            login-password: admin
          filter:
            stat:
              enabled: true
              # 慢SQL记录
              log-slow-sql: true
              slow-sql-millis: 1000
              merge-sql: true
            wall:
              config:
                multi-statement-allow: true
    masterslave:
      load-balance-algorithm-type: ROUND_ROBIN
      name: ms
      master-data-source-name: master
      slave-data-source-names: slave0
mybatis-plus:
  mapper-locations: classpath*:mapper/**/*Mapper.xml
  global-config:
    db-config:
      id-type: auto
      table-underline: true
logging:
  config: classpath:config/logback-spring.xml

##mapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.newland.mbop.mapper.PortalInfoDefMapper">

</mapper>

##TestMapper

package com.yym.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.yym.entity.Test;
import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface TestMapper extends BaseMapper<Test> {
}

##TestService

package com.yym.service;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.yym.entity.Test;
import com.yym.mapper.TestMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class TestService {

    @Autowired
    private TestMapper testMapper;

    public Test query(Test test) {
        return testMapper.selectOne(new QueryWrapper<Test>().eq("name", test.getName()));
    }

    public int add(Test test) {
        return testMapper.insert(test);
    }

}

##TestController

package com.yym.controller;

import com.yym.entity.Test;
import com.yym.service.TestService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.concurrent.TimeUnit;

@RestController
@RequestMapping("/test/v1/")
public class TestController {

    @Autowired
    private TestService testService;

    Logger logger = LoggerFactory.getLogger(TestController.class);

    public TestController() {
        logger.info("TestController");
    }

    @PostMapping("tomcatTimeOut")
    public String tomcatTimeOut() {
        try {
            TimeUnit.SECONDS.sleep(120);
        }catch (Exception e) {
            e.printStackTrace();
        }
        return "sc";
    }

    @GetMapping("query")
    public String query() {
        Test test = new Test();
        test.setName("yym");
        Test testDB = testService.query(test);
        return testDB.getAddress();
    }

    @GetMapping("add")
    public String add() {
        Test test = new Test();
        test.setName("斑鸠");
        test.setAddress("枯藤老树昏鸠");
        int count = testService.add(test);
        return count>0?"success":"fail";
    }

}

##浏览器访问

##数据库