Springboot连接MySQL主从集群

一、maven依赖


com.alibaba
druid-spring-boot-starter

    <!-- Mysql驱动包 -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
    </dependency>

二、nacos配置中心配置
spring:
datasource:
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.cj.jdbc.Driver
druid:
# 主库数据源
master:
url: jdbc:mysql://master.IP:6311/db?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8&allowMultiQueries=true
username: user
password: pwd
connectTimeout: 60000
socketTimeout: 60000
# 从库数据源
slave:
# 从数据源开关/默认关闭
enabled: true
url: jdbc:mysql://slave:6312/db1?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8&allowMultiQueries=true
username: user
password: pwd

  connectTimeout: 60000
  socketTimeout: 60000
  # 初始连接数
  initialSize: 5
  # 最小连接池数量
  minIdle: 10
  # 最大连接池数量
  maxActive: 20
  # 配置获取连接等待超时的时间
  maxWait: 60000
  # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
  timeBetweenEvictionRunsMillis: 60000
  # 配置一个连接在池中最小生存的时间，单位是毫秒
  minEvictableIdleTimeMillis: 300000
  # 配置一个连接在池中最大生存的时间，单位是毫秒
  maxEvictableIdleTimeMillis: 900000
  # 配置检测连接是否有效
  validationQuery: SELECT 1 FROM DUAL
  testWhileIdle: true
  testOnBorrow: false
  testOnReturn: false
  webStatFilter:
    enabled: true
  statViewServlet:
    enabled: true
    # 设置白名单，不填则允许所有访问
    allow:
    url-pattern: /druid/*
    # 控制台管理用户名和密码
    login-username: ruoyi
    login-password: 123456
  filter:
    stat:
      enabled: true
      # 慢SQL记录
      log-slow-sql: true
      slow-sql-millis: 1000
      merge-sql: true
    wall:
      config:
        multi-statement-allow: true

三、DruidConfig.java配置
package com.sms.framework.config;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import com.alibaba.druid.spring.boot.autoconfigure.properties.DruidStatProperties;
import com.alibaba.druid.util.Utils;
import com.sms.common.enums.DataSourceType;
import com.sms.common.utils.spring.SpringUtils;
import com.sms.framework.config.properties.DruidProperties;
import com.sms.framework.datasource.DynamicDataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

import javax.servlet.*;
import javax.sql.DataSource;
import java.io.IOException;
import java.util.HashMap;
import java.util.Map;

/**

• druid 配置多数据源
  */
  @Configuration
  public class DruidConfig {
  private static Logger logger = LoggerFactory.getLogger(DruidConfig.class);

  @Bean
  @ConfigurationProperties("spring.datasource.druid.master")
  public DataSource masterDataSource(DruidProperties druidProperties) {
  DruidDataSource dataSource = DruidDataSourceBuilder.create().build();
  logger.info("masterDataSource加载bean");
  return druidProperties.dataSource(dataSource);
  }

  @Bean
  @ConfigurationProperties("spring.datasource.druid.slave")
  @ConditionalOnProperty(prefix = "spring.datasource.druid.slave", name = "enabled", havingValue = "true")
  public DataSource slaveDataSource(DruidProperties druidProperties) {
  DruidDataSource dataSource = DruidDataSourceBuilder.create().build();
  logger.info("slaveDataSource加载bean");
  return druidProperties.dataSource(dataSource);
  }

  @Bean(name = "dynamicDataSource")
  @Primary
  public DynamicDataSource dataSource(DataSource masterDataSource) {
  Map<Object, Object> targetDataSources = new HashMap<>();
  targetDataSources.put(DataSourceType.MASTER.name(), masterDataSource);
  setDataSource(targetDataSources, DataSourceType.SLAVE.name(), "slaveDataSource");
  return new DynamicDataSource(masterDataSource, targetDataSources);
  }

  /**

  • 设置数据源
  • @param targetDataSources 备选数据源集合
  • @param sourceName 数据源名称
  • @param beanName bean名称
    */
    public void setDataSource(Map<Object, Object> targetDataSources, String sourceName, String beanName) {
    try {
    DataSource dataSource = SpringUtils.getBean(beanName);
    targetDataSources.put(sourceName, dataSource);
    } catch (Exception e) {
    logger.warn(e.getMessage());
    }
    }

  /**

  • 去除监控页面底部的广告
    /
    @SuppressWarnings({"rawtypes", "unchecked"})
    @Bean
    @ConditionalOnProperty(name = "spring.datasource.druid.statViewServlet.enabled", havingValue = "true")
    public FilterRegistrationBean removeDruidFilterRegistrationBean(DruidStatProperties properties) {
    // 获取web监控页面的参数
    DruidStatProperties.StatViewServlet config = properties.getStatViewServlet();
    // 提取common.js的配置路径
    String pattern = config.getUrlPattern() != null ? config.getUrlPattern() : "/druid/";
    String commonJsPattern = pattern.replace("\*", "js/common.js");
    final String filePath = "support/http/resources/js/common.js";
    // 创建filter进行过滤
    Filter filter = new Filter() {
    @Override
    public void init(javax.servlet.FilterConfig filterConfig) throws ServletException {
    // Do nothing because of X and Y.
    }

     @Override
     public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain)
             throws IOException, ServletException {
         chain.doFilter(request, response);
         // 重置缓冲区，响应头不会被重置
         response.resetBuffer();
         // 获取common.js
         String text = Utils.readFromResource(filePath);
         // 正则替换banner, 除去底部的广告信息
         text = text.replace("<a.*?banner\"></a><br/>", "");
         text = text.replace("powered.*?shrek.wang</a>", "");
         response.getWriter().write(text);
     }
    
     @Override
     public void destroy() {
         // Do nothing because of X and Y.
     }
    

    };
    FilterRegistrationBean registrationBean = new FilterRegistrationBean<>();
    registrationBean.setFilter(filter);
    registrationBean.addUrlPatterns(commonJsPattern);
    return registrationBean;
    }
    }

五、启动微服务加载数据库连接