SpringBoot MySql读写分离-多数据源集成

阳光穿透心脏的1/2处 2023-02-16 15:30 206阅读 0赞

###### 1.业务场景 ######

客户给了3台MySQL数据库服务器，一主二从，程序需要集成读写分离，充分提升数据处理能力  
数据层使用了MyBatis-Plus，它有一个多数据源的中间件 dynamic-datasource-spring-boot-starter  
参考：[MyBatis-Plus][]

###### 2.MySql主从 ######

一主二从，搭建方式参考：[docker搭建mysql读写分离][docker_mysql]

###### 3.SpringBoot快速集成 ######

*  1.新建SpringBoot项目，核心依赖

<dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>
    
    <dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>mybatis-plus-boot-starter</artifactId>
        <version>3.1.1</version>
    </dependency>
    
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid-spring-boot-starter</artifactId>
        <version>1.1.20</version>
    </dependency>
    
    <dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
        <version>3.0.0</version>
    </dependency>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
    
            
            <plugin>
                <groupId>org.mybatis.generator</groupId>
                <artifactId>mybatis-generator-maven-plugin</artifactId>
                <version>1.3.5</version>
                <configuration>
                    <configurationFile>${ basedir}/src/main/resources/generator/generatorConfig.xml</configurationFile>
                    <overwrite>true</overwrite>
                    <verbose>true</verbose>
                </configuration>
            </plugin>
        </plugins>
    </build>

*  2.创建数据库表
 *  3.使用 mybatis-generator-maven-plugin 插件连接数据，生成对应的实体类，Mapper接口和Mapper.xml文件，可参考：[Mybatis持久层框架][Mybatis]
 *  4.在application.yml中添加配置，配置好主库，从库连接地址

server:
      port: 8008
    mybatis-plus:
      mapper-locations: classpath:mapping/*.xml type-aliases-package: com.zypcy.dynamicdatabase.entity spring: datasource: dynamic: primary: master #设置默认的数据源或者数据源组,默认值即为master strict: false #设置严格模式,默认false不启动. 启动后在未匹配到指定数据源时候回抛出异常,不启动会使用默认数据源. datasource: master: url: jdbc:mysql://${egov-china.datasource.master.ip}:${egov-china.datasource.master.port}/${egov-china.datasource.master.databaseName}?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&allowMultiQueries=true username: root password: 123456 driver-class-name: com.mysql.jdbc.Driver druid: initial-size: 5 max-active: 25 min-idle: 5 max-wait: 120000 min-evictable-idle-time-millis: 30000 max-evictable-idle-time-millis: 30000 time-between-eviction-runs-millis: 0 validation-query: select 1 validation-query-timeout: -1 test-on-borrow: false test-on-return: false test-while-idle: true pool-prepared-statements: true max-open-prepared-statements: 100 filters: stat share-prepared-statements: true slave_1: url: jdbc:mysql://${egov-china.datasource.slave_1.ip}:${egov-china.datasource.slave_1.port}/${egov-china.datasource.slave_1.databaseName}?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&allowMultiQueries=true username: root password: 123456 driver-class-name: com.mysql.jdbc.Driver druid: initial-size: 5 max-active: 25 min-idle: 5 max-wait: 120000 min-evictable-idle-time-millis: 30000 max-evictable-idle-time-millis: 30000 time-between-eviction-runs-millis: 0 validation-query: select 1 validation-query-timeout: -1 test-on-borrow: false test-on-return: false test-while-idle: true pool-prepared-statements: true max-open-prepared-statements: 100 filters: stat share-prepared-statements: true slave_2: url: jdbc:mysql://${egov-china.datasource.slave_2.ip}:${egov-china.datasource.slave_2.port}/${egov-china.datasource.slave_2.databaseName}?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&allowMultiQueries=true username: root password: 123456 driver-class-name: com.mysql.jdbc.Driver druid: initial-size: 5 max-active: 25 min-idle: 5 max-wait: 120000 min-evictable-idle-time-millis: 30000 max-evictable-idle-time-millis: 30000 time-between-eviction-runs-millis: 0 validation-query: select 1 validation-query-timeout: -1 test-on-borrow: false test-on-return: false test-while-idle: true pool-prepared-statements: true max-open-prepared-statements: 100 filters: stat share-prepared-statements: true # 自定义数据连接配置 egov-china: # mysql 地址配置 datasource: master: ip: 47.106.67.247 port: 3336 databaseName: party slave_1: ip: 120.79.181.1 port: 3336 databaseName: party slave_2: ip: 120.79.181.1 port: 3337 databaseName: party

@MapperScan("com.zypcy.dynamicdatabase.repository")
    @SpringBootApplication(exclude = DruidDataSourceAutoConfigure.class)
    public class DynamicDatabaseApplication { 
        public static void main(String[] args) { 
            SpringApplication.run(DynamicDatabaseApplication.class, args);
        }
    }

*  5.在 Mapper接口文件中，指定查询方法使用从库 ，使用 @DS 注解，这是 Dynamic-Datasource提供的，slave代表轮询slave\_1 和 slave\_2 ，两个从库，如果只有一个从库，可以 @DS(“slave\_1”)，这样insert update delete走主库，select走从库，里面的方法名是默认生成的  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXl1MTk5MTEwMTY1MjA_size_16_color_FFFFFF_t_70]
 *  6.创建好控制器、服务，进行新增和查询
 *  7.启动项目，可以看的master，slave\_1，slave2分别 初始化好了  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXl1MTk5MTEwMTY1MjA_size_16_color_FFFFFF_t_70_pic_center]  
    调用新增接口，插入多条数据，然后手动在两个从库中，修改 zhuyu ，分别为 zhuyu2 , zhuyu3  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXl1MTk5MTEwMTY1MjA_size_16_color_FFFFFF_t_70_pic_center 1]  
    多次调用查询接口，可以看到@DS注解起作用了，分别查询两个从库的数据  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXl1MTk5MTEwMTY1MjA_size_16_color_FFFFFF_t_70_pic_center 2]
 *  8.[源码下载][Link 1]

此方式可以简单的实现多数据源读写分离控制，但是对代码侵入性高，需要在查询的方法上手动控制，而 利用 [Sharding-JDBC][docker_mysql] 实现读写分离对代码侵入性低，会自动识别select或写 sql，来自动读写

[MyBatis-Plus]: https://mp.baomidou.com/guide/dynamic-datasource.html
[docker_mysql]: https://zhuyu.blog.csdn.net/article/details/89645221
[Mybatis]: https://zhuyu.blog.csdn.net/article/details/81190948
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXl1MTk5MTEwMTY1MjA_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200610143537685.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXl1MTk5MTEwMTY1MjA=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXl1MTk5MTEwMTY1MjA_size_16_color_FFFFFF_t_70_pic_center]: https://img-blog.csdnimg.cn/20200610143917350.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXl1MTk5MTEwMTY1MjA=,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXl1MTk5MTEwMTY1MjA_size_16_color_FFFFFF_t_70_pic_center 1]: https://img-blog.csdnimg.cn/20200610143943285.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXl1MTk5MTEwMTY1MjA=,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXl1MTk5MTEwMTY1MjA_size_16_color_FFFFFF_t_70_pic_center 2]: https://img-blog.csdnimg.cn/20200610143953802.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXl1MTk5MTEwMTY1MjA=,size_16,color_FFFFFF,t_70#pic_center
[Link 1]: https://gitee.com/zhuyu1991/spring-cloud/tree/master/dynamic-database