江苏龙网

  1. 主页 > 生活百科 > >

聊聊业务项目如何主动感知mysql是否存活

mysql

前言先前写过一篇文章聊聊如何利用redis实现多级缓存同步,里面讲到业务部门因数据库宕机，有技术提出当数据库宕机，切换到redis，今天我们就来聊聊如何触发这个切换动作？
1、方案一：利用异常机制伪代码如下：

聊聊业务项目如何主动感知mysql是否存活

文章插图
 
首先这个方案是不可行的，因为每次请求，还是先走到数据库逻辑，然后等抛出异常，这个时间会挺长的，业务上是无法接受的
2、方案二：主动进行MySQL探活实现思路： 可以利用数据库连接池检测有效连接的思路
实现方案
1、方式一：利用druid连接池的ValidConnectionChecker进行扩展
核心逻辑如下
@Slf4jpublic class MysqlConnectionCheck extends MySqlValidConnectionChecker {private ApplicationContext applicationContext;public MysqlConnectionCheck(ApplicationContext applicationContext) {this.applicationContext = applicationContext;}@Overridepublic boolean isValidConnection(Connection conn, String validateQuery, int validationQueryTimeout) throws Exception {return checkMySQLCommunications(conn,validateQuery,validationQueryTimeout);}private boolean checkMySQLCommunications (Connection conn, String validateQuery, int validationQueryTimeout) {boolean validConnection = false;try {validConnection = super.isValidConnection(conn, validateQuery, validationQueryTimeout);} catch (Exception e) {}if(validConnection){boolean b = MySQLCommunicationsHolder.isMySQLCommunicationsException.compareAndSet(true, false);if(b){CommunicationsHealthEvent event = CommunicationsHealthEvent.builder().conn(conn).build();applicationContext.publishEvent(event);}}return validConnection;}}复制代码在yml配置我们自定义的检测器
spring:datasource:type: com.alibaba.druid.pool.DruidDataSourcedriverClassName: ${DRIVER_CALSS_NAME:com.mysql.cj.jdbc.Driver}url: ${DATASOURCE_URL:jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai}username: ${DATASOURCE_USERNAME:root}password: ${DATASOURCE_PWD:123456}druid:# 指定连接的有效检查类valid-connection-checker-class-name: com.github.lybgeek.db.check.test.user.check.MysqlConnectionCheck 复制代码这个方案也是不大行的，后面翻了一下druid源码 。当数据库抛出不可恢复的异常时，比如网络抖动，异常断开，druid会触发exceptionSorter，抛弃连接 。而CreateConnectionThread会检测是否需要创建连接，如果不需要，他就会进行等待 。当连接不够时，会调用
com.alibaba.druid.pool.DruidAbstractDataSource#createPhysicalConnection()复制代码进行创建，同时isValidConnection也是在这个方法里面进行连接验证，但这边就有问题，就是当数据库宕机了，就创建不了连接，因此就进入异常流程，isValidConnection是没法执行的
2、方式二：参考druid的检测连接逻辑，额外编写定时器触发检测逻辑
核心代码块：
public class ValidConnectionCheckerAdapter implements ValidConnectionChecker {private DbCheckProperies dbCheckProperies;public ValidConnectionCheckerAdapter(DbCheckProperies dbCheckProperies) {this.dbCheckProperies = dbCheckProperies;}@Overridepublic boolean isValidConnection(Connection conn, String query, int validationQueryTimeout) throws SQLException {boolean valid = checkConnection(conn, query, validationQueryTimeout);// unexcepted branchif (valid && isMysql()) {long lastPacketReceivedTimeMs = MySqlUtils.getLastPacketReceivedTimeMs(conn);if (lastPacketReceivedTimeMs > 0) {long mysqlIdleMillis = System.currentTimeMillis() - lastPacketReceivedTimeMs;if (lastPacketReceivedTimeMs > 0&& mysqlIdleMillis >= dbCheckProperies.getTimeBetweenEvictionRunsMillis()) {return false;}}}return valid;}private boolean checkConnection(Connection conn, String query, int validationQueryTimeout) throws SQLException {if (query == null || query.length() == 0) {return true;}if(conn == null){return false;}Statement stmt = null;ResultSet rs = null;try {stmt = conn.createStatement();if (validationQueryTimeout > 0) {stmt.setQueryTimeout(validationQueryTimeout);}rs = stmt.executeQuery(query);return true;} finally {JdbcUtils.close(rs);JdbcUtils.close(stmt);}}复制代码 @Overridepublic void afterPropertiesSet() throws Exception {executorService.scheduleWithFixedDelay(new DbCheckTask(),0,dbCheckProperies.getTimeBetweenEvictionRunsMillis(), TimeUnit.MILLISECONDS);}private class DbCheckTask implements Runnable{@Overridepublic void run() {SQLException sqlException = null;Connection conn = dbConnManger.getConn();try {boolean validConnection = validConnectionChecker.isValidConnection(conn, dbCheckProperies.getValidationQuery(), dbCheckProperies.getValidationQueryTimeout());if(validConnection){boolean b = MySQLCommunicationsHolder.isMySQLCommunicationsException.compareAndSet(true, false);if(b){CommunicationsHealthEvent event = CommunicationsHealthEvent.builder().conn(conn).build();applicationContext.publishEvent(event);}}else{sqlException = new SQLException("connection is invalid","10040");}} catch (SQLException e) {log.error("{}",e);sqlException = e;dbConnManger.closeConnection();conn = null;}if(sqlException != null){MySQLCommunicationsHolder.isMySQLCommunicationsException.compareAndSet(false, true);CommunicationsUnHealthEvent event = CommunicationsUnHealthEvent.builder().sqlException(sqlException).build();applicationContext.publishEvent(event);}}} 复制代码


推荐阅读


上一篇：Python中的时间序列数据操作总结

下一篇：零基础学Java要学多久？