栏目分类:
子分类:
返回
名师互学网用户登录
快速导航关闭
当前搜索
当前分类
子分类
实用工具
热门搜索
名师互学网 > IT > 软件开发 > 后端开发 > Java

springboot redis druid 负载均衡 mysql集群

Java 更新时间: 发布时间: IT归档 最新发布 模块sitemap 名妆网 法律咨询 聚返吧 英语巴士网 伯小乐 网商动力

springboot redis druid 负载均衡 mysql集群

准备2台mysql8数据库
192.168.18.111 mysql8
192.168.18.253 mysql8
用druid配置连接池,AOP实现负载均衡(轮询,用redis存放数据库集群数量下标)mysql数据库集群

springboot druid 负载均衡 mysql集群

pom.xml

        
        
            org.springframework.boot
            spring-boot-starter-jdbc
        
        
        
            mysql
            mysql-connector-java
            runtime
        
        
        
            org.mybatis.spring.boot
            mybatis-spring-boot-starter
            2.1.4
        
        
        
            org.springframework.boot
            spring-boot-starter-aop
        
        
        
            org.springframework.boot
            spring-boot-starter-data-redis
        
        
        
            com.alibaba
            druid-spring-boot-starter
            1.2.9
        

application.yml

spring:
  redis:
    timeout: 15000
    #单机
    host: localhost
    port: 6379
    password:
  #数据库
  datasource:
    username: x
    password: x
    driver-class-name: com.mysql.cj.jdbc.Driver
    #druid连接池
    type: com.alibaba.druid.pool.DruidDataSource
    #设置成双机热备数据库最好
    mysql1:
      url: jdbc:mysql://192.168.18.111:3306/demo?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=Asia/Shanghai
      # 初始连接数
      initial-size: 10
      # 最小连接池数量
      min-idle: 10
      # 最大连接池数量
      max-active: 100
      # 配置获取连接等待超时的时间
      max-wait: 6000
      # 打开PSCache,并且指定每个连接上PSCache的大小
      pool-prepared-statements: true
      max-pool-prepared-statement-per-connection-size: 20
      # 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
      timeBetweenEvictionRunsMillis: 60000
      # 配置一个连接在池中最小生存的时间,单位是毫秒
      minEvictableIdleTimeMillis: 30000
      # 配置检测连接是否有效
      validationQuery: SELECT 1
      #申请连接的时候检测,如果空闲时间大于timeBetweenEvictionRunsMillis,执行validationQuery检测连接是否有效。
      testWhileIdle: true
      testOnBorrow: false
      testOnReturn: false
      keepAlive: true

    mysql2:
      url: jdbc:mysql://192.168.18.253:3306/demo?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=Asia/Shanghai
      # 初始连接数
      initial-size: 10
      # 最小连接池数量
      min-idle: 10
      # 最大连接池数量
      max-active: 100
      # 配置获取连接等待超时的时间
      max-wait: 6000
      # 打开PSCache,并且指定每个连接上PSCache的大小
      pool-prepared-statements: true
      max-pool-prepared-statement-per-connection-size: 20
      # 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
      timeBetweenEvictionRunsMillis: 60000
      # 配置一个连接在池中最小生存的时间,单位是毫秒
      minEvictableIdleTimeMillis: 30000
      # 配置检测连接是否有效
      validationQuery: SELECT 1
      #申请连接的时候检测,如果空闲时间大于timeBetweenEvictionRunsMillis,执行validationQuery检测连接是否有效。
      testWhileIdle: true
      testOnBorrow: false
      testOnReturn: false
      keepAlive: true
    #监控信息配置
    monitor:
      username: root
      password: root

mybatis:
  mapper-locations: classpath:mapper
    private static final ThreadLocal CONTEXT_HOLDER = new ThreadLocal<>();

    
    public static void setDateSourceType(String dsType) {
        CONTEXT_HOLDER.set(dsType);
    }

    
    public static String getDateSourceType() {
        return CONTEXT_HOLDER.get();
    }

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

DynamicDataSource.java

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import javax.sql.DataSource;
import java.util.Map;

public class DynamicDataSource extends AbstractRoutingDataSource {

    public DynamicDataSource(DataSource defaultTargetDataSource, Map targetDataSources) {
        this.setDefaultTargetDataSource(defaultTargetDataSource);
        this.setTargetDataSources(targetDataSources);
        this.afterPropertiesSet();
    }

    @Override
    protected Object determineCurrentLookupKey() {
        return DynamicDataSourceContextHolder.getDateSourceType();
    }
}

DruidConfig.java

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;

import java.util.HashMap;
import java.util.Map;
import javax.sql.DataSource;


@Configuration
public class DruidConfig {
    
    @Value("${mybatis.type-aliases-package}")
    private String typeAliasesPackage;

    
    @Value("${mybatis.mapper-locations}")
    private String mapperLocations;

    
    @Value("${spring.datasource.monitor.username}")
    private String username;

    @Value("${spring.datasource.monitor.password}")
    private String password;

    @Bean
    @ConfigurationProperties("spring.datasource.mysql1")
    public DataSource mysql1DataSource() {
        return DruidDataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties("spring.datasource.mysql2")
    public DataSource mysql2DataSource() {
        return DruidDataSourceBuilder.create().build();
    }

    @Bean(name = "dynamicDataSource")
    @Primary
    public DynamicDataSource dataSource() {
        Map targetDataSources = new HashMap<>();
        targetDataSources.put("mysql1", mysql1DataSource());
        targetDataSources.put("mysql2", mysql2DataSource());
        return new DynamicDataSource(mysql1DataSource(), targetDataSources);
    }

    
    @Bean
    public SqlSessionFactory sqlSessionFactory(@Qualifier("dynamicDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource);
        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(mapperLocations));
        sqlSessionFactoryBean.setTypeAliasesPackage(typeAliasesPackage);
        return sqlSessionFactoryBean.getObject();
    }

    
    @Bean
    public PlatformTransactionManager transactionManager(@Qualifier("dynamicDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }


    
    @Bean
    public ServletRegistrationBean druidServlet() {
        ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid
    public void robin(){
        String INDEX = "index";
        if (!redisTemplate.hasKey(INDEX)) redisTemplate.opsForValue().set(INDEX, 0);//如果key不存在就创建key,并设置下标初始值为0
        int index = redisTemplate.opsForValue().get(INDEX);//有下标则直接获取
        List list = new ArrayList<>();
        list.add("mysql1");
        list.add("mysql2");
        if (index >= list.size()) index = 0;//超过list集合的值就重新赋值(轮询)
        DynamicDataSourceContextHolder.setDateSourceType(list.get(index));
        redisTemplate.opsForValue().set(INDEX, ++index);//利用redis单线程的特性存放全局index下标
    }

这样,就算关掉其中一台数据库,也是能立马响应的,只是控制台会不断抛出异常。这个留给你们自己优化了。

转载请注明:文章转载自 www.mshxw.com
本文地址:https://www.mshxw.com/it/959026.html
我们一直用心在做
关于我们 文章归档 网站地图 联系我们

版权所有 (c)2021-2022 MSHXW.COM

ICP备案号:晋ICP备2021003244-6号