前言
实现mysql读写分离有两种，一种是基于代码实现(spring提供的抽象类AbstractRoutingDataSource)，另外一种是基于中间件实现(MySQL Proxy、Mycat和Atlas等)，本次使用第一种方式实现；
准备
安装mysql参考：centos7.5手动安装mysql5.6
主从复制配置参考：mysql主从复制配置
MySQL版本：mysql5.6
使用三台虚拟机：
- 192.168.1.111 master
- 192.168.1.112 slave
- 192.168.1.113 slave
pom文件
spring-boot-starter-parent版本2.1.0.RELEASE
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.2</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
</dependency>
<dependency>
<groupId>tk.mybatis</groupId>
<artifactId>mapper-spring-boot-starter</artifactId>
<version>2.1.4</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.51</version>
</dependency>
</dependencies>
配置文件application.yml
server:
port: 8088
spring:
datasource:
master:
jdbc-url: jdbc:mysql://192.168.1.111:3306/test
username: root
password: 123456
driver-class-name: com.mysql.cj.jdbc.Driver
slave1:
jdbc-url: jdbc:mysql://192.168.1.112:3306/test
username: read
password: 123456
driver-class-name: com.mysql.cj.jdbc.Driver
slave2:
jdbc-url: jdbc:mysql://192.168.1.113:3306/test
username: read
password: 123456
driver-class-name: com.mysql.cj.jdbc.Driver
mapper:
mappers: tk.mybatis.mapper.common.Mapper,tk.mybatis.mapper.common.IdsMapper
identity: MYSQL
定义数据源全局持有对象(用于主从切换)
@Slf4j
public class DBContextHolder {
private static final ThreadLocal<DBTypeEnum> contextHolder = new ThreadLocal<>();
private static final AtomicInteger counter = new AtomicInteger(-1);
private static final int INCREMENT_MAX_COUNT = 9999;
public static void set(DBTypeEnum dbType) {
contextHolder.set(dbType);
}
public static DBTypeEnum get() {
return contextHolder.get();
}
public static void switchMaster() {
set(DBTypeEnum.MASTER);
log.info("数据源切换到master");
}
/**
* 两个slave轮询
*/
public static void switchSlave() {
int index = counter.getAndIncrement() % 2;
if (counter.get() > INCREMENT_MAX_COUNT) {
counter.set(-1);
}
if (index == 0) {
set(DBTypeEnum.SLAVE1);
log.info("数据源切换到slave1");
} else {
set(DBTypeEnum.SLAVE2);
log.info("数据源切换到slave2");
}
}
}
继承AbstractRoutingDataSource，定义路由数据源
@Slf4j
public class RoutingDataSource extends AbstractRoutingDataSource {
@Nullable
@Override
protected Object determineCurrentLookupKey() {
log.info("当前数据源key: {}", DBContextHolder.get());
return DBContextHolder.get();
}
}
数据源配置
@Configuration
public class DataSourceConfig {
@Bean
@ConfigurationProperties("spring.datasource.master")
public DataSource masterDataSource() {
return DataSourceBuilder.create().build();
}
@Bean
@ConfigurationProperties("spring.datasource.slave1")
public DataSource slave1DataSource() {
return DataSourceBuilder.create().build();
}
@Bean
@ConfigurationProperties("spring.datasource.slave2")
public DataSource slave2DataSource() {
return DataSourceBuilder.create().build();
}
/**
* 由上面的三个数据源组成的一个路由数据源，也就是说后面真正使用的其实是这个路由数据源
* @param masterDataSource master数据源
* @param slave1DataSource slave1数据源
* @param slave2DataSource slave2数据源
* @return
*/
@Bean
public DataSource routingDataSource(@Qualifier("masterDataSource") DataSource masterDataSource,
@Qualifier("slave1DataSource") DataSource slave1DataSource,
@Qualifier("slave2DataSource") DataSource slave2DataSource) {
Map<Object, Object> targetDataSources = new HashMap<>();
targetDataSources.put(DBTypeEnum.MASTER, masterDataSource);
targetDataSources.put(DBTypeEnum.SLAVE1, slave1DataSource);
targetDataSources.put(DBTypeEnum.SLAVE2, slave2DataSource);
RoutingDataSource routingDataSource = new RoutingDataSource();
routingDataSource.setDefaultTargetDataSource(masterDataSource);
routingDataSource.setTargetDataSources(targetDataSources);
return routingDataSource;
}
}
设置mybatis使用路由数据源
@Configuration
@EnableTransactionManagement
public class TkMybatisConfig {
@Resource(name = "routingDataSource")
private DataSource routingDataSource;
@Bean
public SqlSessionFactory sqlSessionFactory() throws Exception {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(routingDataSource);
return sqlSessionFactoryBean.getObject();
}
@Bean
public PlatformTransactionManager platformTransactionManager() {
return new DataSourceTransactionManager(routingDataSource);
}
}
定义master注解，用于强制使用主数据源
public @interface Master {
}
基于注解定义aop切点和切面
@Aspect
@Component
public class DataSourceAspect {
/**
* master注解的切入点(强制使用主数据源)
*/
@Pointcut("@annotation(com.felix.learn.datasource.annotation.Master)")
public void masterPointcut() {}
/**
* 读操作切入点
*/
@Pointcut("(execution(* com.felix.learn.datasource.service..*.select*(..)) " +
"|| execution(* com.felix.learn.datasource.service..*.get*(..)) " +
"|| execution(* com.felix.learn.datasource.service..*.find*(..)))")
public void readPointcut() {}
/**
* 写操作切入点
*/
@Pointcut("execution(* com.felix.learn.datasource.service..*.insert*(..)) " +
"|| execution(* com.felix.learn.datasource.service..*.add*(..)) " +
"|| execution(* com.felix.learn.datasource.service..*.update*(..)) " +
"|| execution(* com.felix.learn.datasource.service..*.edit*(..)) " +
"|| execution(* com.felix.learn.datasource.service..*.delete*(..)) " +
"|| execution(* com.felix.learn.datasource.service..*.remove*(..))")
public void writePointcut() {}
@Before("!masterPointcut() && readPointcut()")
public void read() {
DBContextHolder.switchSlave();
}
@Before("masterPointcut() || writePointcut()")
public void write() {
DBContextHolder.switchMaster();
}
}
写例子测试(只展示service层)
@Service
public class StudentServiceImpl implements StudentService {
@Resource
private StudentMapper studentMapper;
@Override
public Student findById(Integer id) {
return studentMapper.selectByPrimaryKey(id);
}
@Override
public int insertStudent(Student student) {
studentMapper.insertSelective(student);
return student.getId();
}
@Override
public int deleteStudent(Integer id) {
return studentMapper.deleteByPrimaryKey(id);
}
@Override
public int updateStudent(Student student) {
return studentMapper.updateByPrimaryKeySelective(student);
}
@Master
@Override
public List<Student> selectList() {
List<Student> students = studentMapper.selectAll();
return students;
}
}
网友评论