分布式应用框架搭建及运维二之应用多数据源支持及多数据源下的读写分离

Original 老猿人码农闲谈AI 2024-01-22

概述

假设现在要开发一个基于多租户的项目,每个租户是独立的一个mysql数据库,那么我们就可能需要在项目中根据租户的标识分别路由到不同的库处理业务.项目上线后,随着时间的推移,项目使用用户越来越多,数据库压力也会随之增长,此时我们可以考虑下mysql的主从部署(mysql主从部署在文档中不做特殊讲解,有兴趣的同学可以自行百度部署方式)以及代码端的读写分离.

01新增库表

在第一节框架搭建文档的stduy_1库基础上新增 study_2库,study_common库

-- 创建study_2库，并新增class_info表CREATE SCHEMA study_2;CREATE TABLE `class_info` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `class_num` char(8) NOT NULL, `class_name` varchar(30) NOT NULL, `created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `created_by` varchar(30) NOT NULL, `updated_time` datetime NOT NULL, `updated_by` varchar(30) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='班级表';-- 创建study_common库，并新增class_user_tenant表跟class_user_info表CREATE SCHEMA study_common;

CREATE TABLE `class_user_tenant` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `user_id` bigint(20) NOT NULL, `tenant_code` char(7) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

CREATE TABLE `class_user_info` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `user_name` varchar(30) NOT NULL, `password` varchar(30) NOT NULL, `user_type` char(1) NOT NULL, `mobile` varchar(11) NOT NULL, `salt` varchar(30) NOT NULL, `created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `created_by` varchar(30) NOT NULL, `updated_time` datetime NOT NULL, `updated_by` varchar(30) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

新增tenant模块

tenant模块主要负责study_common库,负责读取用户信息及用户所属的租户信息(读study_1库还是study_2库)

study-parent -study-api -src/main/java -pom.xml -study-base -src/main/java -src/main/resource -pom.xml -study-biz-api -src/main/java -pom.xml -study-biz -src/main/java -src/main/resource -pom.xml -study-tenant -src/main/java -src/main/resources -pom.xml -pom.xml

package com.jw;

import org.springframework.boot.SpringApplication;import org.springframework.boot.autoconfigure.SpringBootApplication;import org.springframework.boot.context.properties.EnableConfigurationProperties;import org.springframework.cloud.client.discovery.EnableDiscoveryClient;import org.springframework.context.annotation.EnableAspectJAutoProxy;

/** * @author ：j * @date ：Created in 2022/8/19 17:33 * @description：tenant启动类 */@SpringBootApplication(scanBasePackages = {"com.jw.*"})@EnableConfigurationProperties@EnableDiscoveryClient@EnableAspectJAutoProxy(exposeProxy = true)public class TenantApplication { public static void main(String[] args) { SpringApplication.run(TenantApplication.class); }

}

study-biz新增登录接口

package com.jw.controller;

import cn.hutool.core.util.StrUtil;import com.jw.api.LoginApi;import com.jw.dto.Resp;import com.jw.dto.UserInfoDTO;import com.jw.dto.UserLoginResultDTO;import com.jw.remote.tenant.UserRemoteClient;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.web.bind.annotation.RestController;

/** * @author ：j * @date ：Created in 2022/8/24 9:45 * @description： */@RestControllerpublic class LoginController extends AbstractController implements LoginApi {

@Autowired private UserRemoteClient userRemoteClient;

@Override public Resp<UserLoginResultDTO> login(String userName) throws Exception{ if (StrUtil.isEmpty(userName)) { return null; } UserInfoDTO userInfoDTO = userRemoteClient.getUserByUserName(userName); if (userInfoDTO == null) { return null; } String token = createToken(userInfoDTO.getId(), userName, userInfoDTO.getTenantCode());

        UserLoginResultDTO userLoginResultDTO = UserLoginResultDTO.builder().userName(userName).id(userInfoDTO.getId()).token(token).build();

return Resp.success(userLoginResultDTO); }}

study-biz模块新增拦截器(拦截所有接口,必须带上token才能访问),并把登录接口置为免拦截

package com.jw.config;

import com.jw.interceptor.RequestInterceptor;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.context.annotation.Configuration;import org.springframework.web.servlet.config.annotation.InterceptorRegistry;import org.springframework.web.servlet.config.annotation.ResourceHandlerRegistry;import org.springframework.web.servlet.config.annotation.WebMvcConfigurer;

/** * @author ：j * @date ：Created in 2022/8/27 17:18 * @description：拦截器配置类 */@Configurationpublic class WebMvcConfig implements WebMvcConfigurer {

@Autowired private RequestInterceptor requestInterceptor;

@Override public void addInterceptors(InterceptorRegistry registry) { registry.addInterceptor(this.requestInterceptor) .addPathPatterns("/**") .excludePathPatterns("/biz/") .excludePathPatterns("/error") .excludePathPatterns("/csrf") .excludePathPatterns("/**/login") //登录免拦截 .excludePathPatterns("/swagger-resources/**") .excludePathPatterns( "/webjars/**") .excludePathPatterns("/v2/**") .excludePathPatterns("/swagger-ui.html/**"); } /** * swagger * @param registry */ @Override public void addResourceHandlers(ResourceHandlerRegistry registry) { registry.addResourceHandler("swagger-ui.html") .addResourceLocations("classpath:/META-INF/resources/"); registry.addResourceHandler("/webjars/**") .addResourceLocations("classpath:/META-INF/resources/webjars/");

}

package com.jw.interceptor;

import cn.hutool.core.util.StrUtil;import com.alibaba.fastjson.JSON;import com.jw.common.CommonConstants;import com.jw.dto.DataSourceHolder;import com.jw.dto.UserInfoDTO;import com.jw.utils.JwtUtil;import io.jsonwebtoken.Claims;import lombok.extern.slf4j.Slf4j;import org.springframework.stereotype.Component;import org.springframework.web.servlet.HandlerInterceptor;

import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;

/** * @author ：j * @date ：Created in 2022/8/27 17:21 * @description：具体拦截器类 */@Component@Slf4jpublic class RequestInterceptor implements HandlerInterceptor {

@Override

    public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) throws Exception {

String token = request.getHeader(CommonConstants.AUTH); if ("/biz/".equals(request.getRequestURI())) { return true; } if (StrUtil.isEmpty(token)) { throw new Exception("您未登录"); } Claims claims = null; try { claims = JwtUtil.authToken(token); } catch (Exception e) { throw new Exception("您未登录"); } if (claims == null) { throw new Exception("登录超时"); } UserInfoDTO userInfoDTO = JSON.parseObject(claims.getSubject(), UserInfoDTO.class); DataSourceHolder.setDataSource(userInfoDTO.getTenantCode()); return true; }

@Override

    public void afterCompletion(HttpServletRequest request, HttpServletResponse response, Object handler, Exception ex) throws Exception {

DataSourceHolder.clearDataSource(); }

03数据源的路由

study-api里新增线程变量传递类

package com.jw.datasource;

/** * @author ：j * @date ：Created in 2022/8/27 17:32 * @description：数据路由线程变量 */public class DataSourceHolder {

private static final ThreadLocal<String> dataSource = new ThreadLocal<String>();

public static void setDataSource(String tenantCode) { dataSource.set(tenantCode); }

/** * 获取tenantCode * @return */ public static String getDataSource() { return dataSource.get(); }

/** * 清除tenantCode */ public static void clearDataSource() { dataSource.remove(); }}

study-api新增feign拦截器及其配置类(当进入base模块才拦截,进入tenant模块不需要拦截)

package com.jw.config;

import org.springframework.context.annotation.Bean;import org.springframework.context.annotation.Configuration;

/** * @author ：j * @date ：Created in 2022/8/27 17:47 * @description：feign拦截器配置类 */@Configurationpublic class FeignInterceptorConfig { @Bean public FeignInterceptor feignInterceptor(){ return new FeignInterceptor(); }}

package com.jw.config;

import com.jw.common.CommonConstants;import com.jw.datasource.DataSourceHolder;import feign.RequestInterceptor;import feign.RequestTemplate;

/** * @author ：j * @date ：Created in 2022/8/27 17:43 * @description：feign拦截器 * 说明：因为feign请求是http，线程变量传递是会失效 * 所以我们在拦截器中把线程变量取出放到header中进行传递 */public class FeignInterceptor implements RequestInterceptor { @Override public void apply(RequestTemplate template) { //从线程变量中取出tenantCode String tenantCode = DataSourceHolder.getDataSource(); template.header(CommonConstants.TENANT_CODE, tenantCode); }}

新增base服务的请求拦截器,要求必须带上tenantCode才能请求)package com.jw.config;

import org.springframework.beans.factory.annotation.Autowired;import org.springframework.context.annotation.Configuration;import org.springframework.web.servlet.config.annotation.InterceptorRegistry;import org.springframework.web.servlet.config.annotation.WebMvcConfigurer;

/** * @author ：j * @date ：Created in 2022/8/27 17:18 * @description：base模块拦截请求配置类 */@Configurationpublic class BaseWebMvcConfig implements WebMvcConfigurer {

@Autowired private BaseFeignInterceptor baseFeignInterceptor;

@Override public void addInterceptors(InterceptorRegistry registry) { registry.addInterceptor(this.baseFeignInterceptor) .addPathPatterns("/**"); }}

package com.jw.config;

import cn.hutool.core.util.StrUtil;import com.jw.common.CommonConstants;import com.jw.datasource.DataSourceHolder;import org.springframework.stereotype.Component;import org.springframework.web.servlet.HandlerInterceptor;

import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;

/** * @author ：j * @date ：Created in 2022/8/27 17:51 * @description：base模块拦截请求类，必须带上tenantCode才能进入 */@Componentpublic class BaseFeignInterceptor implements HandlerInterceptor { @Override

    public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) throws Exception {

String tenantCode = request.getHeader(CommonConstants.TENANT_CODE); if (StrUtil.isEmpty(tenantCode)) { throw new Exception("您未登录"); } //重新设置到线程变量 DataSourceHolder.setDataSource(tenantCode); return true; }

@Override

    public void afterCompletion(HttpServletRequest request, HttpServletResponse response, Object handler, Exception ex) throws Exception {

//请求完销毁线程变量 DataSourceHolder.clearDataSource(); }}

04多数据源配置及测试

study-base模块nacos配置改为

spring: db: name: study_1,study_2 study_1: datasource:

      url: jdbc:mysql://localhost:3306/study_1?useUnicode=true&characterEncoding=utf-8&useSSL=false&autoReconnect=true&allowMultiQueries=true&serverTimezone=GMT%2B8

username: root password: root type: com.zaxxer.hikari.HikariDataSource hikari: connection-init-sql: SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci minimum-idle: 10 maximum-pool-size: 15 idle-timeout: 30000 max-lifetime: 180000 connection-timeout: 30000 connection-test-query: select 'connected' study_2: datasource:

      url: jdbc:mysql://localhost:3306/study_2?useUnicode=true&characterEncoding=utf-8&useSSL=false&autoReconnect=true&allowMultiQueries=true&serverTimezone=GMT%2B8

study-base模块新增数据源读取配置类

package com.jw.utils;

import java.util.Map;import java.util.concurrent.ConcurrentHashMap;

/** * @author ：j * @date ：Created in 2021/4/10 16:31 * @description：数据源单例工具类 */public class DsUtils {

private static Map<Object, Object> dsMap = new ConcurrentHashMap<Object, Object>();

public static void put(String key, Object Object) { dsMap.put(key, Object); }

public static Map<Object, Object> getDsMap() { return dsMap; }}

package com.jw.config;

import com.jw.common.CommonConstants;import com.jw.utils.DsUtils;import com.zaxxer.hikari.HikariDataSource;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.context.annotation.Bean;import org.springframework.context.annotation.Configuration;import org.springframework.core.env.Environment;

/** * @author ：j * @date ：Created in 2022/8/31 10:06 * @description：多数据源加载配置类 */@Configurationpublic class DataSourceConfig { @Autowired private Environment env;

@Bean("dsInit") public void setDataSource() { String datasourcePrefix = env.getProperty(CommonConstants.DB_NAMES); String[] prefixs = datasourcePrefix.split(","); for (String prefix : prefixs) { HikariDataSource hikariDataSource = getDataSource(prefix); DsUtils.put(prefix,hikariDataSource); } }

public HikariDataSource getDataSource(String prefix) { HikariDataSource hikariDataSource = new HikariDataSource(); hikariDataSource.setJdbcUrl(env.getProperty("spring."+prefix + ".datasource.url")); hikariDataSource.setUsername(env.getProperty("spring."+prefix + ".datasource.username")); hikariDataSource.setPassword(env.getProperty("spring."+prefix + ".datasource.password"));

        hikariDataSource.setConnectionInitSql(env.getProperty("spring."+prefix + ".datasource.hikari.connection-init-sql"));

        hikariDataSource.setMinimumIdle(Integer.parseInt(env.getProperty("spring."+prefix + ".datasource.hikari.minimum-idle")));

        hikariDataSource.setMaximumPoolSize(Integer.parseInt(env.getProperty("spring."+prefix + ".datasource.hikari.maximum-pool-size")));

        hikariDataSource.setIdleTimeout(Long.parseLong(env.getProperty("spring."+prefix + ".datasource.hikari.idle-timeout")));

        hikariDataSource.setMaxLifetime(Long.parseLong(env.getProperty("spring."+prefix + ".datasource.hikari.max-lifetime")));

        hikariDataSource.setConnectionTimeout(Long.parseLong(env.getProperty("spring."+prefix + ".datasource.hikari.connection-timeout")));

        hikariDataSource.setConnectionTestQuery(env.getProperty("spring."+prefix + ".datasource.hikari.connection-test-query"));

return hikariDataSource; }}

study-base模块新增spring AbstractRoutingDataSource数据路由类

package com.jw.config;

import com.jw.datasource.DataSourceHolder;import com.jw.utils.DsUtils;import org.springframework.context.annotation.Configuration;import org.springframework.context.annotation.DependsOn;import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

import java.util.Map;

/** * @author ：j * @date ：Created in 2022/8/31 10:14 * @description：spring数据路由类 */@Configuration@DependsOn("dsInit")public class DynamicDataSource extends AbstractRoutingDataSource { @Override protected Object determineCurrentLookupKey() { return DataSourceHolder.getDataSource(); }

public DynamicDataSource() { Map<Object,Object> map = DsUtils.getDsMap(); super.setTargetDataSources(map); }}

测试多数据源路由是否生效,study_1,study2数据库class_info表分别insert一条数据

INSERT INTO `study_1`.`class_info` (`id`, `class_num`, `class_name`, `created_time`, `created_by`, `updated_time`, `updated_by`) VALUES ('1', '1234567', 'study_1班级', '2022-08-31 10:22:02', '0', '2022-08-31 10:21:58', '0');

INSERT INTO `study_2`.`class_info` (`id`, `class_num`, `class_name`, `created_time`, `created_by`, `updated_time`, `updated_by`) VALUES ('1', '2345678', 'study_2班级', '2022-08-31 10:22:02', '0', '2022-08-31 10:21:58', '0');

study_common新增数据

INSERT INTO `study_common`.`class_user_info` (`id`, `user_name`, `password`, `user_type`, `mobile`, `salt`, `created_time`, `created_by`, `updated_time`, `updated_by`) VALUES ('1', 'test1', 'test1', '1', '12345678901', '1234', '2022-08-24 09:20:16', '0', '2022-08-24 09:20:12', '0');

INSERT INTO `study_common`.`class_user_info` (`id`, `user_name`, `password`, `user_type`, `mobile`, `salt`, `created_time`, `created_by`, `updated_time`, `updated_by`) VALUES ('2', 'test2', 'test2', '1', '1234567890', '1234', '2022-08-24 09:20:38', '0', '2022-08-24 09:20:35', '0');

INSERT INTO `study_common`.`class_tenant` (`id`, `user_id`, `tenant_code`) VALUES ('1', '1', 'study_1');INSERT INTO `study_common`.`class_tenant` (`id`, `user_id`, `tenant_code`) VALUES ('2', '2', 'study_2');

点击登录接口用户名传入test1或者test2从study-tenant查询出用户信息及tenantCode放入到token中

点击testBizController的测试方法，传入id跟token获取班级信息,可以看到根据token中的tenantCode信息路由到了不同的数据库中

05多数据源下的读写分离

引入sharding-jdbc maven配置

<dependency> <groupId>io.shardingjdbc</groupId> <artifactId>sharding-jdbc-core</artifactId> <version>2.0.3</version> </dependency>

分别新增跟study_1,study_2相同的study_3,study_4库,study_3为study_1从库,study_4为study_2从库,新增class_info表并分别insert一条测试数据

INSERT INTO `study_3`.`class_info` (`id`, `class_num`, `class_name`, `created_time`, `created_by`, `updated_time`, `updated_by`) VALUES ('1', '567890', 'study_1_slave班级', '2022-08-31 11:13:08', '0', '2022-08-31 11:13:05', '0');

INSERT INTO `study_4`.`class_info` (`id`, `class_num`, `class_name`, `created_time`, `created_by`, `updated_time`, `updated_by`) VALUES ('1', '567890', 'study_2_slave班级', '2022-08-31 11:13:08', '0', '2022-08-31 11:13:05', '0');

修改study-base的nacos配置为

spring: db: name: study_1,study_2 study_1: datasource:

      url: jdbc:mysql://localhost:3306/study_1?useUnicode=true&characterEncoding=utf-8&useSSL=false&autoReconnect=true&allowMultiQueries=true&serverTimezone=GMT%2B8

      url: jdbc:mysql://localhost:3306/study_3?useUnicode=true&characterEncoding=utf-8&useSSL=false&autoReconnect=true&allowMultiQueries=true&serverTimezone=GMT%2B8

      url: jdbc:mysql://localhost:3306/study_2?useUnicode=true&characterEncoding=utf-8&useSSL=false&autoReconnect=true&allowMultiQueries=true&serverTimezone=GMT%2B8

      url: jdbc:mysql://localhost:3306/study_4?useUnicode=true&characterEncoding=utf-8&useSSL=false&autoReconnect=true&allowMultiQueries=true&serverTimezone=GMT%2B8

修改DataSourceConfig配置类

package com.jw.config;

import com.google.common.collect.Lists;import com.google.common.collect.Maps;import com.jw.common.CommonConstants;import com.jw.utils.DsUtils;import com.zaxxer.hikari.HikariDataSource;import io.shardingjdbc.core.api.algorithm.masterslave.MasterSlaveLoadBalanceAlgorithm;import io.shardingjdbc.core.api.algorithm.masterslave.MasterSlaveLoadBalanceAlgorithmType;import io.shardingjdbc.core.api.algorithm.masterslave.RandomMasterSlaveLoadBalanceAlgorithm;import io.shardingjdbc.core.jdbc.core.datasource.MasterSlaveDataSource;import io.shardingjdbc.core.rule.MasterSlaveRule;import io.shardingjdbc.core.yaml.masterslave.YamlMasterSlaveConfiguration;import io.shardingjdbc.core.yaml.masterslave.YamlMasterSlaveRuleConfiguration;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.context.annotation.Bean;import org.springframework.context.annotation.Configuration;import org.springframework.core.env.Environment;

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

/** * @author ：j * @date ：Created in 2022/8/31 10:06 * @description： */@Configurationpublic class DataSourceConfig { @Autowired private Environment env;

@Bean("dsInit") public void setDataSource() throws Exception{ String datasourcePrefix = env.getProperty(CommonConstants.DB_NAMES); String[] prefixs = datasourcePrefix.split(","); for (String prefix : prefixs) { DataSource hikariDataSource = getSlaveDataSource(prefix); DsUtils.put(prefix,hikariDataSource); } }

        hikariDataSource.setConnectionInitSql(env.getProperty("spring."+prefix + ".datasource.hikari.connection-init-sql"));

        hikariDataSource.setMinimumIdle(Integer.parseInt(env.getProperty("spring."+prefix + ".datasource.hikari.minimum-idle")));

        hikariDataSource.setMaximumPoolSize(Integer.parseInt(env.getProperty("spring."+prefix + ".datasource.hikari.maximum-pool-size")));

        hikariDataSource.setIdleTimeout(Long.parseLong(env.getProperty("spring."+prefix + ".datasource.hikari.idle-timeout")));

        hikariDataSource.setMaxLifetime(Long.parseLong(env.getProperty("spring."+prefix + ".datasource.hikari.max-lifetime")));

        hikariDataSource.setConnectionTimeout(Long.parseLong(env.getProperty("spring."+prefix + ".datasource.hikari.connection-timeout")));

        hikariDataSource.setConnectionTestQuery(env.getProperty("spring."+prefix + ".datasource.hikari.connection-test-query"));

return hikariDataSource; }

public DataSource getSlaveDataSource(String masterPrefix) throws Exception{ //读取主库配置 DataSource masterDataSource = getDataSource(masterPrefix); //读取从库配置 DataSource study1SlaveDataSource = getDataSource(masterPrefix+"_slave"); Map<String,DataSource> study1DsMap = Maps.newHashMap(); study1DsMap.put(masterPrefix + "_slave", study1SlaveDataSource); //配置主从规则

        MasterSlaveRule masterSlaveRule = new MasterSlaveRule(masterPrefix + "_ms", masterPrefix, masterDataSource, study1DsMap, new RandomMasterSlaveLoadBalanceAlgorithm());

//返回sharding-jdbc主从数据源 return new MasterSlaveDataSource(masterSlaveRule, Maps.newHashMap()); }}

测试读写分离是否生效,输入用户名为test1，点击登录获取token,拿到token点击testBizController下的test查询接口,查询id为1 ,我们看到返回的数据是study_3库的数据.

testBizController新增save班级的接口,我们看到点击save后数据保存到了study_1库,再次回到查询输入id为2,结果返回是为空的,证明读写分离生效了

@GetMapping("/testSave") @ApiOperation(value = "测试新增") public String testSave(@RequestParam("classNum") String classNum,@RequestParam("className") String className) { return testRemoteClient.testSave(classNum,className); }

分布式应用框架搭建及k8s运维一之应用搭建篇
关注不迷路

继续滑动看下一个

分布式应用框架搭建及运维二之应用多数据源支持及多数据源下的读写分离

Original 老猿人码农闲谈AI

码农闲谈AI

向上滑动看下一个

陈佩斯，这次真悬了！

微博遗存之七

刚刚，我国DUV光刻机实现里程碑式突破！

2年翻10倍？万众期待的2025《山西文物日历》终于来了！一览千年壁画之美

微博遗存之六

分布式应用框架搭建及运维二之应用多数据源支持及多数据源下的读写分离

新增tenant模块

分布式应用框架搭建及运维二之应用多数据源支持及多数据源下的读写分离

您可能也对以下帖子感兴趣

陈佩斯，这次真悬了！

微博遗存之七

刚刚，我国DUV光刻机实现里程碑式突破！

2年翻10倍？万众期待的2025《山西文物日历》终于来了！一览千年壁画之美

微博遗存之六

生成图片，分享到微信朋友圈

分布式应用框架搭建及运维二之应用多数据源支持及多数据源下的读写分离

新增tenant模块

分布式应用框架搭建及运维二之应用多数据源支持及多数据源下的读写分离

您可能也对以下帖子感兴趣