MySQL数据库隔离方案优缺点
| 数据库偏移 | 影子库 | 影子表 | |
|---|---|---|---|
| 优点 | 实现简单，无需改造中间件 | 1.物理隔离； 2.对业务数据无侵入性； 3.安全性高，对生产库务任何影响； 4.扩展性较强; 5. 数据清理简单 |
1.逻辑隔离； 2.中间件容易支持; 3.安全性一般，对生产库有一定影响; 4.扩展性较强; 5.数据清理简单 |
| 缺点 | 1.数据进入生产表； 2.对业务数据侵入性较大，会影响表结构设计; 3.数据清理复杂，需要对每个字段的标记单独定制清理计划； 3.扩展性差，新的资源需要设计新的标记; 4.安全性较差，设计逻辑有纰漏，会影响生产数据; 5.压力大的情况下，会挤占数据库的资源 |
1.数据库资源双倍; 2.需要切换数据库连接中间件改造有成本 |
压力大的情况下，会挤占数据库的资源 |
| 使用场景 | 1.标记流量没打通; 2.技术体系不完整，改造成本和复杂度过高 |
1全服务流量标记. 2.资源充裕 3.中间件技术体系完整 |
1.全服务流量标记; 2.中间件技术体系完整 |
准备工作
application.yml的配置:
···
spring:
datasource:
master:
url: jdbc:mysql://localhost:3306/mall_master?characterEncoding=utf8&useUnicode=true&useSSL=false&serverTimezone=GMT%2B8
username: root
password: 123456
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
initialSize: 5
minIdle: 5
maxActive: 20
maxWait: 60000
timeBetweenEvictionRunMillis: 60000
minEvictableIdleTimeMills: 30000
shadow:
url: jdbc:mysql://localhost:3306/mall_shadow?characterEncoding=utf8&useUnicode=true&useSSL=false&serverTimezone=GMT%2B8
username: root
password: 123456
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
initialSize: 5
minIdle: 5
maxActive: 20
maxWait: 60000
timeBetweenEvictionRunMillis: 60000
minEvictableIdleTimeMills: 30000
···
pom.xml配置的依赖
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>transmittable-thread-local</artifactId>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
</dependency>
<dependency>
<groupId>io.zipkin.brave</groupId>
<artifactId>brave</artifactId>
</dependency>
</dependencies>
两种获取标记方案的技术预演:
- HttpRequest Header:从 Http Request Header中获取标记，适合单体服务、单一HTTP协议的场景
- 数据上下文:从数据上下文对象中获取标记。
HttpRequest Header方案
添加数据库的配置:
package com.edu.link.mysql.config;
import com.alibaba.druid.pool.DruidDataSource;
import com.edu.link.mysql.constant.DataSourceNames;
import lombok.extern.slf4j.Slf4j;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;
@Slf4j
@Configuration
public class DynamicDataSourceConfig {
@Bean(name = "masterDataSource")
@ConfigurationProperties(prefix = "spring.datasource.master")
public DataSource masterDataSource() {
return new DruidDataSource();
}
@Bean(name = "shadowDataSource")
@ConfigurationProperties(prefix = "spring.datasource.shadow")
public DataSource shadowDataSource() {
return new DruidDataSource();
}
@Bean
@Primary
public DynamicDataSource dataSource(DataSource masterDataSource, DataSource shadowDataSource) {
Map<Object, Object> targetDataSource = new HashMap<>(2);
targetDataSource.put(DataSourceNames.MASTER, masterDataSource);
targetDataSource.put(DataSourceNames.SHADOW, shadowDataSource);
return new DynamicDataSource(masterDataSource, targetDataSource);
}
}
动态切换数据库:
package com.edu.link.mysql.config;
import com.alibaba.ttl.TransmittableThreadLocal;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import javax.sql.DataSource;
import java.util.Map;
public class DynamicDataSource extends AbstractRoutingDataSource {
private static final TransmittableThreadLocal<String> contextHolder = new TransmittableThreadLocal<>();
public DynamicDataSource(DataSource defaultTargetDataSource, Map<Object,Object> targetDataSource) {
super.setDefaultTargetDataSource(defaultTargetDataSource);
super.setTargetDataSources(targetDataSource);
super.afterPropertiesSet();
}
@Override
protected Object determineCurrentLookupKey() {
return getDataSource();
}
public static void setDataSource(String dataSource){
contextHolder.set(dataSource);
}
public static String getDataSource(){
return contextHolder.get();
}
public static void clearDataSource(){
contextHolder.remove();
}
}
使用切面保证全局切换:
package com.edu.link.mysql.aspect;
import com.edu.link.mysql.config.DynamicDataSource;
import com.edu.link.mysql.constant.DataSourceNames;
import lombok.extern.slf4j.Slf4j;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.stereotype.Component;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;
import javax.servlet.http.HttpServletRequest;
import java.util.Arrays;
@Slf4j
@Aspect
@Component
public class DataSourceAspect {
@Pointcut("execution (public * com.edu.link.mysql.controller..*.*(..))")
public void controllerAspect() {
}
@Before(value = "controllerAspect()")
public void methodBefore(JoinPoint joinPoint) {
ServletRequestAttributes requestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
HttpServletRequest request = requestAttributes.getRequest();
String header = request.getHeader("dunshan");
log.info("======================");
log.info(request.getRequestURL().toString());
log.info(request.getMethod());
log.info(joinPoint.getSignature().toString());
log.info(Arrays.toString(joinPoint.getArgs()));
log.info("header:" + header);
if (null != header && "7DGroup".equalsIgnoreCase(header)) {
DynamicDataSource.setDataSource(DataSourceNames.SHADOW);
log.info("====shadow====");
} else {
DynamicDataSource.setDataSource(DataSourceNames.MASTER);
log.info("====master====");
}
}
}
数据上下文方案
创建上下文:
package com.edu.link.mysql.config;
import com.alibaba.ttl.TransmittableThreadLocal;
import java.io.Serializable;
public class AppContext implements Serializable {
private static final TransmittableThreadLocal<AppContext> contextDunShan = new TransmittableThreadLocal<>();
private String flag;
public static AppContext getContext(){
return contextDunShan.get();
}
public static void setContext(AppContext appContext){
contextDunShan.set(appContext);
}
public static void removeContext(){
contextDunShan.remove();
}
public String getFlag() {
return flag;
}
public void setFlag(String flag) {
this.flag = flag;
}
}
上下文过滤器
package com.edu.link.mysql.config;
import brave.baggage.BaggageField;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Component;
import javax.servlet.*;
import java.io.IOException;
@Component
@Slf4j
public class ContextFilter implements Filter {
@Override
public void init(FilterConfig filterConfig) throws ServletException {
Filter.super.init(filterConfig);
}
@Override
public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain) throws IOException, ServletException {
String value = BaggageField.getByName("dunshan").getValue();
AppContext appContext = new AppContext();
log.info("contextfilter {}", value);
if (StringUtils.isNotEmpty(value)) {
appContext.setFlag(value);
} else {
appContext.setFlag("");
}
AppContext.setContext(appContext);
chain.doFilter(request, response);
}
@Override
public void destroy() {
AppContext.removeContext();
Filter.super.destroy();
}
}
创建执行切面:
package com.edu.link.mysql.aspect;
import com.edu.link.mysql.config.AppContext;
import com.edu.link.mysql.config.DynamicDataSource;
import com.edu.link.mysql.constant.DataSourceNames;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.slf4j.MDC;
import org.springframework.stereotype.Component;
@Aspect
@Slf4j
@Component
public class AopMyDbSwitch {
@Pointcut("execution (public * com.edu.link.mysql.controller..*.*(..))")
public void controllerAspect() {
}
@Before(value = "controllerAspect()")
public void methodBefore(JoinPoint joinPoint) {
AppContext context = AppContext.getContext();
String flag = context.getFlag();
if (StringUtils.isNotEmpty(flag) && flag.equals(DataSourceNames.HEAD)) {
MDC.put("dunshan","shadow");
DynamicDataSource.setDataSource(DataSourceNames.SHADOW);
log.info("====shadow====");
} else {
MDC.put("dunshan","master");
DynamicDataSource.setDataSource(DataSourceNames.MASTER);
log.info("====master====");
}
}
}
