【总结+转载】SpringBoot+JPA +MySQL Cluster集群 读写分离/负载均衡

==写在开始 MySQL 集群+读写分离方案有多种选择，可参考 浅谈MySQL集群高可用架构 , 这里选择的是使用MySQL官方的MySQL Cluster方案==

一、MySQL Cluster 集群配置简介

服务器	Cluster角色	系统角色
192.168.2.150	管理节点	无
192.168.2.151	数据节点(NDBD) & SQL节点(mysqld api)	写服务
192.168.2.152	数据节点(NDBD) & SQL节点(mysqld api)	读服务

为了测试需要，因此在2.151 & 2.152上分别搭建了cluster中的数据节点(ndbd节点服务)和SQL节点(mysqld服务)，如果条件允许可以做出以下类似配置 集群中角色 系统中角色 服务器 | Cluster角色 | 系统角色 ---|--- | --- 192.168.2.150 | 管理节点 | 无 192.168.2.151 | SQL节点(mysqld api) | 写服务 192.168.2.152 | SQL节点(mysqld api) | 读服务 192.168.2.153 | 数据节点(NDBD) | 无 192.168.2.154 | 数据节点(NDBD) | 无

二、项目使用的持久层

在SpringBoot项目上持久层使用的是Spring-Data-JPA，连接的数据库是一个MySQL Cluster搭建的集群环境； 其中

jdbc:mysql://192.168.2.151:3306/fake-store?useSSL=false&useUnicode=yes&characterEncoding=utf-8

连接的SQL节点用作写服务，

jdbc:mysql://192.168.2.152:3306/fake-store?useSSL=false&useUnicode=yes&characterEncoding=utf-8

连接的SQL节点用作读服务。

三、相关关键字

AOP拦截动态切换数据源、AbstractRoutingDataSource、 Spring支持的数据源路由，JPA 作为持久化框架相关的EntityManager、EntityManagerFactory、JpaTransactionManager

四、定义一个数据源类型和数据源上下文工具类

数据源类型用于标示数据源类型及作用

public enum DataSourceType {
    WRITE("write", "写库"), READ("read", "读库");
    String type;
    String name;

    DataSourceType(String type, String name) {
        this.type = type;
        this.name = name;
    }

    public String getName() {
        return name;
    }

    public String getType() {
        return type;
    }
}

数据源上下文工具用于将数据源类型绑定到线程并指示AbstractRoutingDataSource进行数据源切换；

public class DataSourceContextHolder {
    private static final ThreadLocal<DataSourceType> local = new ThreadLocal<>();
    public static ThreadLocal<DataSourceType> getLocal() {
        return local;
    }
    public static void read() {
        local.set(DataSourceType.READ);
    }
    public static void write() {
        local.set(DataSourceType.WRITE);
    }
    public static DataSourceType getDataSourceType() {
        return local.get();
    }
    public static void clear() {
        local.remove();
    }
}

为了能动态切换数据源需要手动对Jpa进行配置，因此需要关闭SpringBoot的 AutoConfiguration 功能, 包括DataSource的设置，在 @SpringBootApplication上进行排除

@SpringBootApplication(exclude = {
        DataSourceAutoConfiguration.class,
        DataSourceTransactionManagerAutoConfiguration.class,
        HibernateJpaAutoConfiguration.class
})
public class Application {

    public static void main(String[] args) {
        SpringApplication springApplication = new SpringApplication(Application.class);
        springApplication.addListeners((ContextRefreshedEvent event) -> {
            if (event.getApplicationContext().getParent() == null) {
                //spring容器启动完成
                System.out.println("容器启动完成");
            }
        });
        springApplication.run(args);
    }
}

自定义RoutingDataSource，继承自AbstractRoutingDataSource

public class CustomRoutingDataSource extends AbstractRoutingDataSource {

    @Override
    protected Object determineCurrentLookupKey() {
        //多存在多个读或者写数据源，如read1、read2、read3；write1、write2、write3；
        //可在此处可以加入负载均衡控制，加入AtomicInteger动态分配数据源
        DataSourceType typeKey = DataSourceContextHolder.getDataSourceType();
        return typeKey == null ? DataSourceType.READ : typeKey;
    }
}

禁用掉 @AutoConfiguration 后需要手动启用 @EnableJpaRepositories、@EnableTransactionManagement 来初始化数据源的设置

@Configuration
@EnableJpaRepositories(
        basePackageClasses = {UserDao.class},
        entityManagerFactoryRef = "customEntityManagerFactory",
        transactionManagerRef = "customTransactionManager")
@EnableTransactionManagement
public class DynamicDataSourceConfig {
...

这里我们使用两个数据源，一个readDataSource、一个writeDataSource；在配置文件内通过前缀进行区分;

#使用log4jdbc 记录SQL日志
spring.write.datasource.driver-class-name=net.sf.log4jdbc.DriverSpy
#替换为log4jdbc的url格式
spring.write.datasource.url=jdbc:log4jdbc:mysql://192.168.2.151:3306/fake-store?useSSL=false&useUnicode=yes&characterEncoding=UTF-8
spring.write.datasource.username=root
spring.write.datasource.password=root

spring.read.datasource.driver-class-name=net.sf.log4jdbc.DriverSpy
spring.read.datasource.url=jdbc:log4jdbc:mysql://192.168.2.152:3306/fake-store?useSSL=false&useUnicode=yes&characterEncoding=UTF-8
spring.read.datasource.username=root
spring.read.datasource.password=root

读写数据源初始化

    @Bean(name = "readDataSource")
    @ConfigurationProperties(prefix = "spring.read.datasource")
    public DataSource readDataSource() {
        return new DruidDataSource();;
    }
    
    @Bean(name = "writeDataSource")
    @ConfigurationProperties("spring.write.datasource")
    public DataSource writeDataSource() {
        return new DruidDataSource();;
    }

实际使用的数据源应由AbstractRoutingDataSource动态切换提供

    @Bean(name = "dynamicDataSource")
    @Primary
    public DataSource dynamicDataSource() {
        final CustomRoutingDataSource dynamicDataSource = new CustomRoutingDataSource();
        final Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put(DataSourceType.READ, readDataSource());
        targetDataSources.put(DataSourceType.WRITE, writeDataSource());
        dynamicDataSource.setTargetDataSources(targetDataSources);
        return dynamicDataSource;
    }

对Jpa需要的EntityManager以及TransactionManager进行配置

    @Autowired(required = false)
    private PersistenceUnitManager persistenceUnitManager;
    
    @Bean
    @Primary
    public LocalContainerEntityManagerFactoryBean customEntityManagerFactory() {
        final JpaProperties jpaProperties = jpaProperties();
        HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        //不设置的话会产生异常 Access to DialectResolutionInfo cannot be null when 'hibernate.dialect'
        vendorAdapter.setDatabasePlatform(jpaProperties.getDatabasePlatform());
        vendorAdapter.setShowSql(jpaProperties.isShowSql());
        vendorAdapter.setDatabase(jpaProperties.getDatabase());
        vendorAdapter.setGenerateDdl(jpaProperties.isGenerateDdl());
        // 使用dynamicDataSource
        DataSource dataSource = dynamicDataSource();
        //设置其他Jpa属性如hibnernate.naming-physical-strategy
        Map<String, ?> properties = jpaProperties.getHibernateProperties(new HibernateSettings());
        EntityManagerFactoryBuilder builder = new EntityManagerFactoryBuilder(vendorAdapter, properties, persistenceUnitManager);
        return builder.dataSource(dataSource).packages(IdEntity.class)
                .persistenceUnit("customEntityManager").build();
    }

    @Bean
    public PlatformTransactionManager customTransactionManager(
            @Qualifier("customEntityManagerFactory") final EntityManagerFactory entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory);
    }

    @Bean
    public JpaProperties jpaProperties() {
        //自动将配置文件中prefix为spring.jpa 的各项注入到JpaProperties
        return new JpaProperties();
    }

接下来配置切面进行拦截并动态设置DataSourceType

@Component
@Aspect
@EnableAspectJAutoProxy
@Order(-1) //在Service内优先切换数据源再开启事务
public class DataSourceSwitchAop {
    @Pointcut("execution(* com.advanced.service..*.find*(..)) ||" +
            "execution(* com.advanced.service..*.list*(..)) || " +
            "execution(* com.advanced.service..*.get*(..)) ||" +
            "execution(* com.advanced.service..*.load*(..)) ||" +
            "execution(* com.advanced.service..*.count*(..))" +
            "execution(* com.advanced.service..*.search*(..))"
    )
    void readJoinPoint() {

    }

    @Pointcut("execution(* com.advanced.service..*.delete*(..)) ||" +
            "execution(* com.advanced.service..*.save*(..)) ||" +
            "execution(* com.advanced.service..*.update*(..)) ||" +
            "execution(* com.advanced.service..*.create*(..)) || " +
            "execution(* com.advanced.service..*.add*(..)) ||" +
            "execution(* com.advanced.service..*.persist*(..))"
    )
    void writeJoinPoint() {

    }

    @Around("readJoinPoint()")
    public Object switchReadDataSource(ProceedingJoinPoint proceedingJoinPoint) throws Throwable {
        DataSourceContextHolder.read();
        Object obj = proceedingJoinPoint.proceed();
        DataSourceContextHolder.clear();
        return obj;
    }
    @Around("writeJoinPoint()")
    public Object switchWriteDataSource(ProceedingJoinPoint proceedingJoinPoint) throws Throwable {
        DataSourceContextHolder.write();
        Object obj = proceedingJoinPoint.proceed();
        DataSourceContextHolder.clear();
        return obj;
    }
}

上述配置存在部分问题，如果在Service中有某个写方法调用Dao(继承自JpaRepository)的读方法，Dao无法切换到读数据源，即Service的写方法切换到写数据源，Dao的读方法使用写数据源完成读取操作。因为这里使用的是MySQL-Cluster，各个SQL节点数据一致因此不会造成实质性错误。

参考

Spring DataSource Routing Routing：http://kimrudolph.de/blog/spring-datasource-routing

转载于:https://my.oschina.net/u/920769/blog/1927336