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 { }
示例代码地址:自定义多数据源读写分离
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 分享4款.NET开源、免费、实用的商城系统
· 全程不用写代码,我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了,比商业数据库还牛
· 白话解读 Dapr 1.15:你的「微服务管家」又秀新绝活了
· 上周热点回顾(2.24-3.2)