springboot整合多数据源配置

DynamicDataSourceConfig配置

在这里面初始化数据源bean

public class DynamicDataSourceConfig {

    @Bean
    @ConfigurationProperties(prefix = "datasource.master")
    public DataSource master() {
        log.debug("正在初始化数据源master配置......");
        return new DruidDataSource();
    }

    @Bean
    @ConfigurationProperties(prefix = "datasource.slave1")
    public DataSource slave() {
        log.debug("正在初始化数据源slave配置......");
        return new DruidDataSource();
    }

    @Bean
    @Primary
    public DynamicDataSource dynamicDataSource(DataSource master, DataSource slave) {
        log.debug("正在添加主从数据源......");
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put(DynamicDataSource.DataSourceType.Master, master);
        targetDataSources.put(DynamicDataSource.DataSourceType.Slave, slave);
        return new DynamicDataSource(master, targetDataSources);
    }

    /**
     * @Author: Wangzhen
     * @Date: 2019/9/20 0020 16:17
     * @Params:
     * @Description: 配置druid监控，配置一个管理后台的servlet，访问地址：http://localhost:8080/druid/
     */
    @Bean
    public ServletRegistrationBean statViewServlet() {
        ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
        Map<String, String> initParameters = new HashMap<String, String>();
        initParameters.put("loginUsername", "admin");//属性见：com.alibaba.datasource.support.http.ResourceServlet
        initParameters.put("loginPassword", "123456");
        //白名单IP，默认允许所有
        initParameters.put("allow", "");
        //黑名单的IP
        initParameters.put("deny", "");
        bean.setInitParameters(initParameters);
        return bean;
    }
}

DynamicDataSource数据源配置

spring提供AbstractRoutingDataSource抽象类复写determineCurrentLookupKey()方法；

public class DynamicDataSource extends AbstractRoutingDataSource {

    /**
     * 多数据源类型
     */
    public enum DataSourceType {
        Master,
        Slave
    }

    /**
     * ThreadLocal 用于提供线程局部变量，在多线程环境可以保证各个线程里的变量独立于其它线程里的变量。
     * 也就是说 ThreadLocal 可以为每个线程创建一个【单独的变量副本】，相当于线程的 private static 类型变量。
     */
    private static final ThreadLocal<DataSourceType> CONTEXT_HOLDER = new ThreadLocal<>();

    /**
     * 决定使用哪个数据源之前需要把多个数据源的信息以及默认数据源信息配置好
     *
     * @param defaultTargetDataSource 默认数据源
     * @param targetDataSources       目标数据源
     */
    public DynamicDataSource(DataSource defaultTargetDataSource, Map<Object, Object> targetDataSources) {
        super.setDefaultTargetDataSource(defaultTargetDataSource);
        super.setTargetDataSources(targetDataSources);
        super.afterPropertiesSet();
    }

    @Override
    protected Object determineCurrentLookupKey() {
        return getDataSource();
    }

    public static void setDataSource(DataSourceType dataSource) {
        CONTEXT_HOLDER.set(dataSource);
    }

    public static DataSourceType getDataSource() {
        return CONTEXT_HOLDER.get();
    }

    public static void clearDataSource() {
        CONTEXT_HOLDER.remove();
    }
}

多数据源注解

用啦标识那些方法使用哪个数据源

/**
 * 多数据源注解
 * <p/>
 * 指定要使用的数据源
 *
 * @author wangzhen
 */
@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface MultiDataSource {

    /**
     * 多数据源类型
     *
     * @return
     * @see DynamicDataSource.DataSourceType
     */
    DynamicDataSource.DataSourceType dataSourceType() default DynamicDataSource.DataSourceType.Master;

}

aop动态切换数据源

使用aop思想 动态的切换数据源

@Aspect
@Component
public class DataSourceAspect implements Ordered {

    @Override
    public int getOrder() {
        return 1;
    }

    /**
     * 切所有带 @MultiDataSource 注解得方法
     *
     * @param point
     * @return
     * @throws Throwable
     */
    @Around("(@annotation(cn.mztjt.hephaistos.bapi.utils.annotation.MultiDataSource))")
    public Object around(ProceedingJoinPoint point) throws Throwable {
        MethodSignature signature = (MethodSignature) point.getSignature();
        Method method = signature.getMethod();
        MultiDataSource multiDataSource = method.getAnnotation(MultiDataSource.class);
        if (multiDataSource == null) {
            DynamicDataSource.setDataSource(DynamicDataSource.DataSourceType.Master);
            log.debug("dataSource切换:" + DynamicDataSource.DataSourceType.Master);
        } else {
            DynamicDataSource.setDataSource(multiDataSource.dataSourceType());
            log.debug("dataSource切换:" + multiDataSource.dataSourceType());
        }

        try {
            return point.proceed();
        } finally {
            DynamicDataSource.clearDataSource();
            log.debug("======dataSource clean======");
        }
    }
}

配置文件设置

#数据源相关基础配置
datasource:
  master:
    url: jdbc:mysql://192.168.0.201:3306/xx?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&autoReconnect=true&autoReconnectForPools=true&useSSL=false
    username: root
    password: 
    driver-class-name: com.mysql.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource
    initialiSize: 5
    minIdle: 5
    maxActive: 200
    maxWait: 60000
    timeBetweenEvictionRunsMillis: 60000
    minEvictableIdleTimeMillis: 30000
    validationQuery: SELECT 1
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    poolPreparedStatements: true
    filters: stat,wall,log4j2
    maxPoolPreparedStatementPerConnectionSize: 20
    useGlobalDataSourceStat: true
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
  slave1:
    url: jdbc:mysql://192.168.0.202:3306/xx?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&autoReconnect=true&autoReconnectForPools=true&useSSL=false
    username: root
    password: 
    driver-class-name: com.mysql.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource
    initialiSize: 5
    minIdle: 5
    maxActive: 200
    maxWait: 60000
    timeBetweenEvictionRunsMillis: 60000
    minEvictableIdleTimeMillis: 30000
    validationQuery: SELECT 1
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    poolPreparedStatements: true
    filters: stat,wall,log4j2
    maxPoolPreparedStatementPerConnectionSize: 20
    useGlobalDataSourceStat: true
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500

mysql服务器配置

准备上两个服务器，同时装mysql服务

整理笔记

1.主库设置 /etc/my.cnf
server-id=1
log-bin=master-bin
log-bin-index=master-bin.index
重启mysql服务 查看master： show master status;

2.从库设置
server-id=2
relay-log-index=salav=relay-bin.index
relay-log=slave-relay-bin
重启mysql服务 查看slave： show slave status;

3.主库创建一个授权slave权限的用户
create user 'repl'@'%' identified by 'root@123';
# 如果以这个ip这个账号连接，那么就授 replication slave 权限 所有数据库.所有表
grant replication slave on *.* to 'repl'@'%' IDENTIFIED by 'root@123';

4.在从库中修改连接master主数据库配置
# master_log_file指的是主库变动log文件 ，可以执行 show master status 查看
change master to master_host='192.168.0.105',master_port=3306,master_user='repl',master_password='root@123',master_log_file='master-bin.000001',master_log_pos=0;

相关命令
show master status;
show slave status;
?遇到的问题
uuid 相同问题 删除这个自动文件即可，重启mysql服务
rm -rf /var/lib/mysql/auto.cnf