springboot druid 负载均衡 mysql集群准备2台mysql8数据库
192.168.18.111 mysql8
192.168.18.253 mysql8
用druid配置连接池,AOP实现负载均衡(轮询,用redis存放数据库集群数量下标)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
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
这样,就算关掉其中一台数据库,也是能立马响应的,只是控制台会不断抛出异常。这个留给你们自己优化了。



