Mysql 配置主从复制
参考:Mysql主从复制-半同步复制
这里我配置了master主库,slave从库slave0和slave1
创建 Spring boot工程
maven pom
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
<lombok.maven.plugin.encoding>UTF-8</lombok.maven.plugin.encoding>
<lombok.version>1.18.20</lombok.version>
<lombok.maven.plugin.version>1.18.20.0</lombok.maven.plugin.version>
</properties>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.5.1</version>
<relativePath/>
</parent>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.25</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>${lombok.version}</version>
</dependency>
</dependencies>
application.yml
spring:
jpa:
properties:
hibernate:
enable_lazy_load_no_trans: true
show_sql: true
use_sql_comments: true
format_sql: true
hibernate:
ddl-auto: update
naming:
physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
database-platform: org.hibernate.dialect.MySQL8Dialect
open-in-view: false
server:
port: 9300
app:
datasource:
parameters: useUnicode=true&characterEncoding=utf8&autoReconnect=true&failOverReadOnly=false&useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true&rewriteBatchedStatements=true
username: &username root
password: &password 123456
driver-class-name: &driver-class-name com.mysql.cj.jdbc.Driver
master: &default-db-config
url: jdbc:mysql://localhost:3201/test?${app.datasource.parameters}
username: *username
password: *password
driver-class-name: *driver-class-name
configuration:
maximum-pool-size: 30
slave0:
<<: *default-db-config
url: jdbc:mysql://localhost:3202/test?${app.datasource.parameters}
slave1:
<<: *default-db-config
url: jdbc:mysql://localhost:3203/test?${app.datasource.parameters}
数据源相关配置
DataSource
import com.zaxxer.hikari.HikariDataSource;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
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;
/**
* 多数据源配置
*/
@Configuration
public class DataSourceConfig {
/**
* master
*/
@Bean
@Primary
@ConfigurationProperties(prefix = "app.datasource.master")
public DataSourceProperties masterDataSourceProperties() {
return new DataSourceProperties();
}
/**
* slave0
*/
@Bean("slave0DataSourceProperties")
@ConfigurationProperties(prefix = "app.datasource.slave0")
public DataSourceProperties slave0DataSourceProperties() {
return new DataSourceProperties();
}
/**
* slave1
*/
@Bean("slave1DataSourceProperties")
@ConfigurationProperties(prefix = "app.datasource.slave1")
public DataSourceProperties slave1DataSourceProperties() {
return new DataSourceProperties();
}
@Bean
@Primary
@ConfigurationProperties(prefix = "app.datasource.master.configuration")
public DataSource masterDataSource(DataSourceProperties dataSourceProperties) {
return dataSourceProperties.initializeDataSourceBuilder().type(HikariDataSource.class).build();
}
@Bean("slave0DataSource")
@ConfigurationProperties(prefix = "app.datasource.slave0.configuration")
public DataSource slave0DataSource(@Qualifier("slave0DataSourceProperties") DataSourceProperties dataSourceProperties) {
return dataSourceProperties.initializeDataSourceBuilder().type(HikariDataSource.class).build();
}
@Bean("slave1DataSource")
@ConfigurationProperties(prefix = "app.datasource.slave1.configuration")
public DataSource slave1DataSource(@Qualifier("slave1DataSourceProperties") DataSourceProperties dataSourceProperties) {
return dataSourceProperties.initializeDataSourceBuilder().type(HikariDataSource.class).build();
}
}
数据源切换思路
- 利用
ThreadLocal
作为单个请求(每个请求单独一个线程)的全局容器连接Service
方法控制使用哪个数据源和EntityManager
使用的数据源,这样EntityManager
使用的数据源就是在Service
方法上要求的数据源,即可做到写Service
方法时决定使用哪个数据源 - 利用
org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource::determineCurrentLookupKey()
暴露获取DataSource的逻辑 - 在service方法上通过配置Annotation,告诉EntityManager使用哪个数据源
- 自定义Annotation
- 利用Spring AOP识别Annotation,织入设置数据源逻辑
数据源切换配置
- Annotation
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* 主数据源
*/
@Target({ElementType.METHOD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
public @interface Master {
}
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* 从数据源
*/
@Target({ElementType.METHOD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
public @interface Slave {
}
- 多数据源 key
public enum DBInstanceEnum {
MASTER, SLAVE0, SLAVE1;
}
- 数据源容器
import lombok.extern.slf4j.Slf4j;
import lombok.val;
import java.util.concurrent.atomic.AtomicInteger;
/**
* 数据源选择器
*/
@Slf4j
public final class DBContextHolder {
private static final ThreadLocal<DBInstanceEnum> contextHolder = ThreadLocal.withInitial(() -> DBInstanceEnum.MASTER);
private static final AtomicInteger router = new AtomicInteger(-1);
public static void switchToMaster() {
contextHolder.set(DBInstanceEnum.MASTER);
log.info("switch to master db");
}
public static void switchToSlave() {
// 1.3改进:支持多个从库的负载均衡
val next = router.incrementAndGet();
val index = next % 2;
if (index == 0) {
contextHolder.set(DBInstanceEnum.SLAVE0);
log.info("switch to slave0 db");
} else {
contextHolder.set(DBInstanceEnum.SLAVE1);
log.info("switch to slave1 db");
}
if (next > 9999) {
router.set(-1);
}
}
public static DBInstanceEnum get() {
val db = contextHolder.get();
log.info("get db{} from contextHolder", db);
return db;
}
}
- 多数据源AOP配置
import lombok.extern.slf4j.Slf4j;
import org.aspectj.lang.annotation.*;
import org.springframework.stereotype.Component;
/**
* 多数据源切换AOP
*/
@Aspect
@Component
@Slf4j
public class DataSourceSwitchAspect {
/**
* 切换为 master 数据源织入点
*/
@Pointcut("@annotation(cc.gegee.study.week7.db.tags.Master) ")
public void switchToMasterPointcut() {
}
/**
* 切换为 slave 数据源织入点
*/
@Pointcut("@annotation(cc.gegee.study.week7.db.tags.Slave) ")
public void switchToSlavePointcut() {
}
@Before("switchToSlavePointcut()")
public void readBefore() {
DBContextHolder.switchToSlave();
}
/**
* slave 结束后切回到主数据源
*/
@AfterReturning("switchToSlavePointcut()")
public void readAfter() {
DBContextHolder.switchToMaster();
log.info("after use slave db, set to master db");
}
@Before("switchToMasterPointcut()")
public void writeBefore() {
DBContextHolder.switchToMaster();
}
}
动态数据源配置
将上面配置的数据源注入AbstractRoutingDataSource,并将数据源容器获取接入
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import org.springframework.stereotype.Component;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;
@Component
public class RoutingDataSource extends AbstractRoutingDataSource {
public RoutingDataSource(DataSource masterDataSource, @Qualifier("slave0DataSource") DataSource slave0DataSource, @Qualifier("slave1DataSource") DataSource slave1DataSource) {
Map<Object, Object> targetDataSources = new HashMap<>();
targetDataSources.put(DBInstanceEnum.MASTER, masterDataSource);
targetDataSources.put(DBInstanceEnum.SLAVE0, slave0DataSource);
targetDataSources.put(DBInstanceEnum.SLAVE1, slave1DataSource);
this.setDefaultTargetDataSource(masterDataSource);
this.setTargetDataSources(targetDataSources);
}
@Override
protected Object determineCurrentLookupKey() {
return DBContextHolder.get();
}
}
自定义 JPA 的 EntityManager
将动态数据源注入EntityManager
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.SharedEntityManagerCreator;
import javax.persistence.EntityManager;
import java.util.Objects;
import static cc.gegee.study.week7.Application.BASE_PACKAGE;
import static cc.gegee.study.week7.db.JpaEntityManagerMaster.ENTITY_MANAGER_FACTORY_BEAN;
/**
* 自定义 JPA 的 EntityManager
*/
@Configuration
@EnableJpaRepositories(basePackages = {BASE_PACKAGE}, entityManagerFactoryRef = ENTITY_MANAGER_FACTORY_BEAN)
public class JpaEntityManagerMaster {
public final static String ENTITY_MANAGER_FACTORY_BEAN = "masterEntityManagerFactoryBean";
private final RoutingDataSource dataSource;
private final EntityManagerFactoryBuilder builder;
private final HibernateConfiguration hibernateConfiguration;
public JpaEntityManagerMaster(RoutingDataSource dataSource, EntityManagerFactoryBuilder builder, HibernateConfiguration hibernateConfiguration) {
this.dataSource = dataSource;
this.builder = builder;
this.hibernateConfiguration = hibernateConfiguration;
}
@Bean
public LocalContainerEntityManagerFactoryBean masterEntityManagerFactoryBean() {
return builder
.dataSource(dataSource)
.properties(hibernateConfiguration.getVendorProperties(dataSource))
.packages(BASE_PACKAGE)
.persistenceUnit("persistenceUnitMaster")
.build();
}
@Bean
public EntityManager masterEntityManager() {
return SharedEntityManagerCreator.createSharedEntityManager(Objects.requireNonNull(masterEntityManagerFactoryBean().getObject()));
}
}
测试
service添加CRUD方法
@Master
@Override
public void add(DeviceModel model) {
val device = Device.builder()
.serialNumber(model.getSerialNumber())
.deviceCategoryName(model.getDeviceCategoryName())
.build();
deviceRepository.save(device);
}
@Master
@Override
public void edit(UUID id, DeviceModel model) {
val device = deviceRepository.findById(id).orElseThrow(() -> new IllegalArgumentException("invalid id"));
device.setSerialNumber(model.getSerialNumber());
device.setDeviceCategoryName(model.getDeviceCategoryName());
deviceRepository.save(device);
}
@Slave
@Override
public DeviceDto get(UUID id) {
val device = deviceRepository.findById(id).orElseThrow(() -> new IllegalArgumentException("invalid id"));
return DeviceDto.builder()
.serialNumber(device.getSerialNumber())
.deviceCategoryName(device.getDeviceCategoryName())
.build();
}
@Slave
@Override
public Page<DeviceDto> getPage(Pageable pageable) {
return deviceRepository.findAll(pageable).map(x -> DeviceDto.builder()
.serialNumber(x.getSerialNumber())
.deviceCategoryName(x.getDeviceCategoryName())
.build());
}
配置好controller后调用,可以看到如下日志
2021-12-23 09:11:38.926 INFO 8678 --- [nio-9300-exec-1] cc.gegee.study.week7.db.DBContextHolder : switch to master db
2021-12-23 09:11:48.976 INFO 8678 --- [nio-9300-exec-3] cc.gegee.study.week7.db.DBContextHolder : switch to slave1 db
2021-12-23 09:11:48.977 INFO 8678 --- [nio-9300-exec-3] cc.gegee.study.week7.db.DBContextHolder : get dbSLAVE1 from contextHolder