MySQL 读写分离之自定义读写分离

自定义读写分离

　　基于 druid 连接池自定义配置做一主多从的读写分离配置，做读写分离的前提是数据库集群已做好主从同步配置。这里默认已经完成主从同步的配置，读写分离环境为：
　　JDK：1.8+
　　ORM：JPA
　　DB：MySQL 5.7+

添加 maven 依赖

　　这里主要添加 MySQL 驱动依赖和 durid 连接池用于配置多数据源。

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.2.8</version>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-configuration-processor</artifactId>
    <optional>true</optional>
</dependency>
<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-lang3</artifactId>
    <version>3.12.0</version>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <optional>true</optional>
</dependency>

 

导入yml配置

server:
  port: 8080

spring:
  application:
    name: db-sys-binglog
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    druid:
      master:
        url: jdbc:mysql://1.14.194.150:3306/sys-db?createDatabaseIfNotExist=true&useUnicode=true&characterEncoding=utf-8&useSSL=false
        username: root
        password: 123456
      slave:
        url: jdbc:mysql://1.14.30.31:3306/sys-db?createDatabaseIfNotExist=true&useUnicode=true&characterEncoding=utf-8&useSSL=false
        username: root
        password: 123456
      max-active: 30
      min-idle: 5
      initial-size: 5
      max-wait: 10000
      validation-query: select 'x'
      validation-query-timeout: 30000
      filters: stat, wall, slf4j
      filter:
        stat:
          slow-sql-millis: 3000
  jpa:
    hibernate:
      ddl-auto: update
    properties:
      hibernate:
        event:
          merge.entity_copy_observer: allow
        database: mysql
        generate-ddl: true
        show_sql: true
        format_sql: true
        use_sql_comments: true
        dialet:
          MySQL5InnoDBDialect
        globally_quoted_identifiers:
          false
logging:
  level:
    com.sxdear.dbsys.dbsysbinlog: debug

 

配置 druid 监控

　　这里配置 druid 监控平台。包括 SQL、spring、session、黑白名单等。

import com.alibaba.druid.filter.Filter;
import com.alibaba.druid.filter.stat.StatFilter;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import com.alibaba.druid.support.spring.stat.DruidStatInterceptor;
import org.springframework.aop.support.DefaultPointcutAdvisor;
import org.springframework.aop.support.JdkRegexpMethodPointcut;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Scope;

import javax.servlet.annotation.WebFilter;
import javax.servlet.annotation.WebInitParam;

/**
 <p style="color:rgb(0,255,0);">TODO</p>
 **/
@Configuration
@WebFilter(filterName="druidWebStatFilter",urlPatterns="/*",
        initParams={
                @WebInitParam(name="exclusions",value="*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*")// 忽略资源
        }
)
public class DruidConfiguration extends WebStatFilter{

    @Value("${spring.datasource.druid.filter.stat.slow-sql-millis}")
    private Long slowSqlMillis;

    /**
     * 注册一个StatViewServlet
     */
    @Bean
    public ServletRegistrationBean DruidStatViewServle(){

        //org.springframework.boot.context.embedded.ServletRegistrationBean提供类的进行注册.
        ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(),"/druid/*");

        //添加初始化参数：initParams

        //白名单：
        servletRegistrationBean.addInitParameter("allow","127.0.0.1");

        //IP黑名单 (存在共同时，deny优先于allow) : 如果满足deny的话提示:Sorry, you are not permitted to view this page.
        servletRegistrationBean.addInitParameter("deny","");

        //登录查看信息的账号密码.
        servletRegistrationBean.addInitParameter("loginUsername","admin");
        servletRegistrationBean.addInitParameter("loginPassword","876v543xcvbwxecrvt67n8m9");

        //是否能够重置数据.
        servletRegistrationBean.addInitParameter("resetEnable","false");
        return servletRegistrationBean;
    }

    /**
     * 注册一个：filterRegistrationBean
     */
    @Bean
    public FilterRegistrationBean druidStatFilter(){
        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());

        //添加过滤规则.
        filterRegistrationBean.addUrlPatterns("/*");

        //添加不需要忽略的格式信息.
        filterRegistrationBean.addInitParameter("exclusions","*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");

        return filterRegistrationBean;
    }

    @Bean
    public Filter statFilter(){
        StatFilter filter = new StatFilter();
        filter.setSlowSqlMillis(slowSqlMillis);
        filter.setLogSlowSql(true);
        filter.setMergeSql(true);
        return filter;

    }

    @Bean
    public DruidStatInterceptor druidStatInterceptor() {
        return new DruidStatInterceptor();
    }

    /** 
     * <p style="color:rgb(255,165,0);">监听 spring</p> 
     */ 
    @Bean
    @Scope("prototype")
    public JdkRegexpMethodPointcut druidStatPointcut() {
        JdkRegexpMethodPointcut pointcut = new JdkRegexpMethodPointcut();
        pointcut.setPattern("cn.emernet.sxphe2.device.*");
        return pointcut;
    }

    @Bean
    public DefaultPointcutAdvisor druidStatAdvisor(DruidStatInterceptor druidStatInterceptor, JdkRegexpMethodPointcut druidStatPointcut) {
        DefaultPointcutAdvisor defaultPointAdvisor = new DefaultPointcutAdvisor();
        defaultPointAdvisor.setPointcut(druidStatPointcut);
        defaultPointAdvisor.setAdvice(druidStatInterceptor);
        return defaultPointAdvisor;
    }
}

 

数据源配置

　　这里直接把配置的数据源信息读取到，由 druid 创建 DataSource 数据源，托管数据库连接。这种方式其实不是很灵活，修改数据库连接时需要改动代码。

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Primary;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

/**
 <p style="color:rgb(0,255,0);">TODO</p>
 **/
@Component
public class DatabaseConfiguration {

    @Primary
    @Bean
    @ConfigurationProperties("spring.datasource.druid.master")
    public DataSource masterDataSource() {
        return DruidDataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties("spring.datasource.druid.slave")
    public DataSource slaveDataSource() {
        return DruidDataSourceBuilder.create().build();
    }

    @Bean
    public DynamicDataSource dynamicDataSource(DataSource masterDataSource, @Qualifier("slaveDataSource") DataSource slaveDataSource) {
        Map<Object, Object> targetDataSources = new HashMap<>(2);
        targetDataSources.put(DynamicDataSource.DatabaseType.Master, masterDataSource);
        targetDataSources.put(DynamicDataSource.DatabaseType.Slave, slaveDataSource);
        DynamicDataSource dataSource = new DynamicDataSource();
        dataSource.setTargetDataSources(targetDataSources);
        dataSource.setDefaultTargetDataSource(masterDataSource);
        dataSource.afterPropertiesSet();
        return dataSource;
    }

}

 

数据源动态配置

　　每个连接的建立都保存在当前线程中，这里把 DataSource 分为 Master 和 Slave，在后面配置的 AOP 拦截可以选择不同的数据源。

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

/**
 <p style="color:rgb(0,255,0);">TODO</p>
 **/
public class DynamicDataSource extends AbstractRoutingDataSource {

    private static final ThreadLocal<DatabaseType> contextHolder = new ThreadLocal<>();

    @Override
    protected Object determineCurrentLookupKey() {
        return contextHolder.get();
    }

    public static void master() {
        contextHolder.set(DatabaseType.Master);
    }


    public static void slave() {
        contextHolder.set(DatabaseType.Slave);
    }

    public static void clear() {
        contextHolder.remove();
    }

    public static DatabaseType getType() {
        return contextHolder.get();
    }

    public enum DatabaseType {
        Master, Slave
    }
}

 

数据源选择

通过 AOP 切面来根据方法名前缀选择不同的数据源。但也有特例，需要强制选择 Master 库，所以配置了根据 @Master 注解主动选择数据源。

import lombok.extern.slf4j.Slf4j;
import org.aspectj.lang.annotation.AfterReturning;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.springframework.core.Ordered;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;

/**
 <p style="color:rgb(0,255,0);">TODO</p>
 **/
@Slf4j
@Aspect
@Order(Ordered.LOWEST_PRECEDENCE - 1)
@Component
public class DataSourceInterceptor {

    @Before("@annotation(com.sxdear.dbsys.dbsysbinlog.annotation.Master) || execution(* *..service.*.select*(..))|| execution(* *..service.*.get*(..))")
    public void setReadDataSourceType() {
        DynamicDataSource.slave();
    }

    @Before("execution(* *..service.*.insert*(..)) || execution(* *..service.*.update*(..)) || execution(* *..service.*.delete*(..)) || execution(* *..service.*.add*(..)) || execution(* *..service.*.save*(..))")
    public void setWriteDataSourceType() {
        DynamicDataSource.master();
    }

    @AfterReturning("execution(* *..service.*.*(..))")
    public void clearDataSourceType() {
        DynamicDataSource.clear();
    }
}

 

@Master 注解

　　根据 @Master 注解主动选择数据源。

/**
 * <p style="color:rgb(0,255,0);">强制使用 Master 数据源</p>
 * @Date 2022/1/5
 * @Author hsx
 **/
public @interface Master {
}

示例代码地址：自定义多数据源读写分离