springboot主从数据库 spring mysql数据库主从

转载

mob64ca140d96d9 2024-04-22 21:44:03

文章标签 springboot主从数据库数据库 spring mysql 数据 文章分类 架构后端开发

如果一个互联网公司的项目只有一个数据库在支撑，在出现大量访问的时候，页面会无限超时报错，为此，可以尝试去准备组建主从库，进行读写分离的数据库架构。

1.准备：两个数据库，一个主库 master，一个从库 slave，（主从库之间数据同步用的是中间件，这里暂时不讲，如果有兴趣，可以自己去百度下）。

2.在spring中的ApplicationContext.xml 文件中，配置两个数据源：

数据源（主库）1：

<bean id="master" class="org.apache.commons.dbcp2.BasicDataSource">
<property name="url" value="jdbc:mysql://ip:port/dbname" />
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="username" value="username" />
<property name="password" value="password" />
         <!--maxTotal: 可以在这个池中同时被分配的有效连接数的最大值，如设置为负数，则不限制-->    
         <property name="maxTotal" value="500"/>  
         <property name="maxWaitMillis" value="600000" /> 
<property name="maxIdle" value="20" />
<property name="minIdle" value="20" />
<property name="initialSize" value="20" />
<property name="timeBetweenEvictionRunsMillis" value="60000" />
<property name="poolPreparedStatements" value="true" />
<property name="maxOpenPreparedStatements" value="100" />


<property name="removeAbandonedOnBorrow" value="true" />
<!-- 申请连接时执行validationQuery检测连接是否有效，配置为true会降低性能 -->
<property name="testOnBorrow" value="true" />
<!-- 归还连接时执行validationQuery检测连接是否有效，配置为true会降低性能 -->
<property name="testOnReturn" value="false" />
<!-- 建议配置为true，不影响性能，并且保证安全性。申请连接的时候检测，如果空闲时间大于 timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。 -->
<property name="testWhileIdle" value="true" />
<!-- 用来检测连接是否有效的sql，要求是一个查询语句,如果validationQuery为 null，testOnBorrow、testOnReturn、testWhileIdle都不起其作用。 -->
<property name="validationQuery" value="SELECT 1 FROM DUAL" />
 </bean>

数据源（从库）2：

<bean id="readOnlyDataSource" class="org.apache.commons.dbcp2.BasicDataSource">
<property name="url" value="jdbc:mysql://ip:port/dbname? useUnicode=true&autoReconnect=true&failOverReadOnly=false&characterEncoding=UTF-8" />
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="username" value="username" />
<property name="password" value="password" />
<!--maxTotal: 可以在这个池中同时被分配的有效连接数的最大值，如设置为负数，则不限制-->    
<property name="maxTotal" value="500"/>  
<property name="maxWaitMillis" value="600000" /> 
<property name="maxIdle" value="20" />
<property name="minIdle" value="20" />
<property name="initialSize" value="20" />
<property name="timeBetweenEvictionRunsMillis" value="60000" />
<property name="poolPreparedStatements" value="true" />
<property name="maxOpenPreparedStatements" value="100" />
<property name="removeAbandonedOnBorrow" value="true" />
<!-- 申请连接时执行validationQuery检测连接是否有效，配置为true会降低性能 -->
<property name="testOnBorrow" value="true" />
<!-- 归还连接时执行validationQuery检测连接是否有效，配置为true会降低性能 -->
<property name="testOnReturn" value="false" />
<!-- 建议配置为true，不影响性能，并且保证安全性。申请连接的时候检测，如果空闲时间大于 timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。 -->
<property name="testWhileIdle" value="true" />
<!-- 用来检测连接是否有效的sql，要求是一个查询语句,如果validationQuery为 null，testOnBorrow、testOnReturn、testWhileIdle都不起其作用。 -->
<property name="validationQuery" value="SELECT 1 FROM DUAL" />
 </bean>

3.用java写动态数据源相关策略配置。

在书写动态源的时候，需要继承AbstractRoutingDataSource并，实现determineCurrentLookupKey和afterPropertiesSet方法，

package com.core.database.dynamicDataBase;

 import java.util.HashMap;
 import java.util.List;
 import java.util.Map;
 import java.util.concurrent.atomic.AtomicLong;
 import java.util.concurrent.locks.Lock;
 import java.util.concurrent.locks.ReentrantLock;


 import org.apache.commons.collections.CollectionUtils;
 import org.apache.commons.logging.Log;
 import org.apache.commons.logging.LogFactory;
 import org.springframework.beans.factory.annotation.Autowired;
 import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;


 import com.alibaba.druid.util.ThreadLocalRandom;

/**
   * @Function 动态数据源
  */
 public class DynamicDataSource extends AbstractRoutingDataSource {


     private static final Log LOG = LogFactory.getLog(DynamicDataSource.class);
     private static final Long MAX_POOL = Long.MAX_VALUE;


     private Object writeDataSource; // 写数据源
     private List<Object> readDataSources; // 多个读数据源


     private int readDataSourcePollPattern = 0; // 获取读数据源方式，0：随机，1：轮询


     private AtomicLong counter = new AtomicLong(0);
     private final Lock lock = new ReentrantLock();


     @Autowired
     private DataSourceHeartBeat dataSourceHeartBeat;

public void setWriteDataSource(Object writeDataSource) {
         this.writeDataSource = writeDataSource;
     }


     public void setReadDataSources(List<Object> readDataSources) {
         this.readDataSources = readDataSources;
     }


     public void setReadDataSourcePollPattern(int readDataSourcePollPattern) {
         this.readDataSourcePollPattern = readDataSourcePollPattern;
     }

     @Override
     protected Object determineCurrentLookupKey() {
         DataSourceTypeInfoDTO dataSourceTypeInfo = DynamicDataSourceHolder.getDataSource();


         if (dataSourceTypeInfo == null || dataSourceTypeInfo.getDataSourceType() == null
                 || dataSourceTypeInfo.getDataSourceType() == DynamicDataSourceGlobalEnum.WRITE || readDataSources.size() <= 0) {
             return DynamicDataSourceGlobalEnum.WRITE.name();
         }


         int index = 1;
         if (readDataSourcePollPattern == 1) {
             // 轮询方式
             long currValue = counter.incrementAndGet();
             if ((currValue + 1) >= MAX_POOL) {
                 try {
                     lock.lock();
                     if ((currValue + 1) >= MAX_POOL) {
                         counter.set(0);
                     }
                 } finally {
                     lock.unlock();
                 }
             }
             index = (int) (currValue % readDataSources.size());
         } else {
             // 随机方式
             index = ThreadLocalRandom.current().nextInt(0, readDataSources.size());
         }
         return dataSourceTypeInfo.getDataSourceType().name() + index;
     }


     @Override
     public void afterPropertiesSet() {
         if(writeDataSource == null) {  
             throw new IllegalArgumentException("property 'writeDataSource' is required");  
         } 
         setDefaultTargetDataSource(writeDataSource);
         Map<Object, Object> targetDataSources = new HashMap<Object, Object>();
         targetDataSources.put(DynamicDataSourceGlobalEnum.WRITE.name(), writeDataSource);
         if (CollectionUtils.isNotEmpty(this.readDataSources)) {
             for (int i = 0; i < readDataSources.size(); i++) {
                 targetDataSources.put(DynamicDataSourceGlobalEnum.READ.name() + i, readDataSources.get(i));
             }
         }
         setTargetDataSources(targetDataSources);


         super.afterPropertiesSet();
     }


     /**
      * 
       * startDyHeartBeat(启动心跳)
      */
     public void startDyHeartBeat() {
         dataSourceHeartBeat.initDataSources(writeDataSource, readDataSources);
         dataSourceHeartBeat.startHeartBeat();
     }


     /**
      * 
       * refreshDataSources(刷新数据源)
       * @param dataSources
      */
     public void refreshDataSources(List<Object> dataSources) {
         boolean isSame = false;
         if (this.readDataSources.size() == dataSources.size()) {
             if (this.readDataSources.containsAll(dataSources)) {
                 isSame = true;
             }
         }


         if (!isSame) {
             this.readDataSources.clear();
             if (CollectionUtils.isNotEmpty(dataSources)) {
                 for (Object ds : dataSources) {
                     this.readDataSources.add(ds);
                 }
             }
             
             Map<Object, Object> targetDataSources = new HashMap<Object, Object>();
             targetDataSources.put(DynamicDataSourceGlobalEnum.WRITE.name(), writeDataSource);
             if (CollectionUtils.isNotEmpty(this.readDataSources)) {
                 for (int i = 0; i < readDataSources.size(); i++) {
                     targetDataSources.put(DynamicDataSourceGlobalEnum.READ.name() + i, readDataSources.get(i));
                 }
             }
             setTargetDataSources(targetDataSources);
             super.afterPropertiesSet();
             
             logger.info("dataSources has changed");
         }
         logger.info("refersh success");
     }
 }

根据以上的类，注册bean并注入数据源，进行数据源切换。

<bean id="dataSource" class="com.core.database.dynamicDataBase.DynamicDataSource">
<property name="writeDataSource" ref="master" />
<property name="readDataSources">
<list>
<ref bean="readOnlyDataSource" />
</list>
</property>
<!--轮询方式 -->
<property name="readDataSourcePollPattern" value="1" />
<property name="defaultTargetDataSource" ref="master" />
 </bean>

4.数据库心跳配置

package com.core.database.dynamicDataBase;


 import java.util.ArrayList;
 import java.util.HashMap;
 import java.util.List;
 import java.util.Map;
 import java.util.Map.Entry;
 import java.util.ResourceBundle;
 import java.util.Timer;
 import java.util.TimerTask;
 import java.util.concurrent.ExecutorService;
 import java.util.concurrent.Executors;
 import java.util.concurrent.Future;
 import java.util.concurrent.TimeUnit;

 import org.apache.commons.collections.CollectionUtils;
 import org.apache.commons.dbcp2.BasicDataSource;
 import org.apache.commons.logging.Log;
 import org.apache.commons.logging.LogFactory;
 import org.springframework.beans.factory.annotation.Autowired;

 /**
   * @Function 类功能说明
   */
 public class DataSourceHeartBeat {


     /**
      * 日志记录 *
      */
     private static final Log LOG = LogFactory.getLog(DataSourceHeartBeat.class);
     private String[] noticePhones = null;
     
     @Autowired
     private DynamicDataSource dynamicDataSource;
     
     
     private BasicDataSource writeDataSource; // 写数据源
     private List<BasicDataSource> readDataSources; // 多个只读数据源
     
     private ExecutorService executorPool = Executors.newFixedThreadPool(30);
     
     /**
      * 
       * startHeartBeat(开启数据库监控心跳，90s执行一次数据库测试连接)
       * @return
      */
     public boolean startHeartBeat() {
         if (noticePhones == null) {
             ResourceBundle bundle = ResourceBundle.getBundle("system");
             if (bundle.containsKey("db_exception_notice_phones")) {
                 noticePhones = bundle.getString("db_exception_notice_phones").split(",");
             }
         }
         
         Timer timer = new Timer();
         timer.schedule(new TimerTask() {
             
             @Override
             public void run() {
                 //测试写库
                 Future<Boolean> writeFuture = executorPool.submit(new CheckDBConnectCallable(writeDataSource)); 
                 
                 //测试只读数据库
                 Map<Integer, Future<Boolean>> readFutureMap = new HashMap<Integer, Future<Boolean>>();
                 List<Object> normalReadDS = new ArrayList<Object>();
                 for (int i = 0; i < readDataSources.size(); i++) {
                     readFutureMap.put(i, executorPool.submit(new CheckDBConnectCallable(readDataSources.get(i))));
                 }
                 
                 try {
                     if (writeFuture.get()) {
                         LOG.info("测试写库连接成功");
                     }
                 } catch (Exception e) {
                     LOG.error(e.getLocalizedMessage(), e);
                 }
                 
                 for (Entry<Integer, Future<Boolean>> entry : readFutureMap.entrySet()) {
                     try {
                         if (entry.getValue().get()) {
                             normalReadDS.add(readDataSources.get(entry.getKey()));
                         }
                     } catch (Exception e) {
                         LOG.error(e.getLocalizedMessage(), e);
                     }
                 }
                 
                 if (normalReadDS.size() == readDataSources.size()) {
                     LOG.info("测试所有只读库连接成功");
                 }
                 
                 dynamicDataSource.refreshDataSources(normalReadDS);
                 
             }
         }, 5000, 90000);
         
         return true;
     }
     
     /**
      * 
       * initReadOnlyDataSources(初始化只读数据库实例)
       * @param readDataSources
      */
     public void initDataSources(Object writeDataSource, List<Object> readDataSources) {
         this.writeDataSource = (BasicDataSource) writeDataSource;
         this.readDataSources = new ArrayList<BasicDataSource>();
         if (CollectionUtils.isNotEmpty(readDataSources)) {
             for (Object rds : readDataSources) {
                 this.readDataSources.add((BasicDataSource) rds);
             }
         }
     }
     
     /**
      * 
       * destory(停止线程池)
      */
     public void destory() {
         try {
             if (!executorPool.isShutdown()) {
                 executorPool.shutdown();
             }
             if (!executorPool.awaitTermination(60000, TimeUnit.MILLISECONDS)) {
                 executorPool.shutdownNow();
             }
         } catch (Exception e) {
             LOG.error(e.getLocalizedMessage(), e);
             executorPool.shutdownNow();
         }
         LOG.info("executorPool destory success");
     }
 }

在以上的基上，在xml中添加心跳bean

<bean id="dataSourceHeartBeat" class="com.core.database.dynamicDataBase.DataSourceHeartBeat" destroy-method="destory"/>

5.配置事务

<bean name="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">   
         <property name="dataSource" ref="dataSource"></property>
         </bean>
         <tx:annotation-driven transaction-manager="transactionManager"/>

6.配置切面相关数据

package com.core.database.dynamicDataBase;


 import java.lang.reflect.Method;
 import org.apache.commons.lang.StringUtils;
 import org.apache.commons.logging.Log;
 import org.apache.commons.logging.LogFactory;
 import org.aspectj.lang.JoinPoint;
 import org.aspectj.lang.reflect.MethodSignature;


 /**
   * @Function 类功能说明
   */
 public class DataSourceAspect {
     private static final Log LOG = LogFactory.getLog(DynamicDataSource.class);

     /**
      * 
       * pointCut(这里用一句话描述这个方法的作用)
      */
     public void pointCut() {
     };

     /**
      * 
       * before(aop before)
       * @param point
      */
     public void before(JoinPoint point) {
         DataSourceTypeInfoDTO dataSourceTypeInfo = new DataSourceTypeInfoDTO();
         dataSourceTypeInfo.setMethodInfo(point.getSignature().getDeclaringTypeName() + "." + point.getSignature().getName());
         if (isReadOnly(point)) {
             dataSourceTypeInfo.setDataSourceType(DynamicDataSourceGlobalEnum.READ);
         } else {
             dataSourceTypeInfo.setDataSourceType(DynamicDataSourceGlobalEnum.WRITE);
         }
         DynamicDataSourceHolder.putDataSource(dataSourceTypeInfo);
     }


     /**
      * 
       * after(aop after)
       * @param point
      */
     public void after(JoinPoint point) {
         DynamicDataSourceHolder.clearDataSource();
     }

     /**
       * isReadOnly(判断是否为读库)
       * @param point
       * @return
      */
     private Boolean isReadOnly(JoinPoint point) {
         // 若方法上有注解，则直接选择指定的数据源
         Object target = point.getTarget();
         String methodName = point.getSignature().getName();
         Class<?>[] classz = target.getClass().getInterfaces();
         Class<?>[] parameterTypes = ((MethodSignature) point.getSignature()).getMethod().getParameterTypes();
         try {
             Method m = classz[0].getMethod(methodName, parameterTypes);
             if (m != null && m.isAnnotationPresent(DataSource.class)) {
                 return DynamicDataSourceGlobalEnum.READ.equals(m.getAnnotation(DataSource.class).value());
             }
         } catch (Exception e) {
             LOG.error(e.getLocalizedMessage(), e);
         }


         // 方法名以query、find、get开头的方法名走从库
         return StringUtils.startsWithAny(methodName, new String[] { "query", "find", "get"});
     }
 }

在xml中配置相关的切面切点

<aop:aspectj-autoproxy></aop:aspectj-autoproxy>
 <bean id="dynamicDataSourceAspect" class="com.core.database.dynamicDataBase.DataSourceAspect" />
 <aop:config>
<aop:aspect id="cdatasource" ref="dynamicDataSourceAspect">
<aop:pointcut id="tx" expression="execution(* com.zzq.mapper..*.*(..))"/>
<aop:before pointcut-ref="tx" method="before"/>
<aop:after pointcut-ref="tx" method="after"/>
</aop:aspect>
 </aop:config>

读写分离的相关配置和代码流程大概就如上所诉，其实说白了，就是一下几个步骤，了解清楚了每个步骤，这个东西也不是很神秘的东西了。

1.准备：两个数据库

2.在spring中的ApplicationContext.xml 文件中，配置两个数据源

3.用java写动态数据源相关策略配置,注册bean并注入数据源，进行数据源切换。

4.数据库心跳配置,在xml中添加心跳bean

5.配置事务

6.配置切面相关数据,在xml中配置相关的切面切点,

在调用特定类的时候，进入切点，置换数据源，为了线程间不影响，需要启用本地化线程

如果有什么问题，请指正。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。