前言:
而今咱们对“checkmysqlis”可能比较重视,同学们都想要知道一些“checkmysqlis”的相关资讯。那么小编也在网上网罗了一些关于“checkmysqlis””的相关知识,希望我们能喜欢,同学们快快来学习一下吧!前言
先前写过一篇文章聊聊如何利用redis实现多级缓存同步,里面讲到业务部门因数据库宕机,有技术提出当数据库宕机,切换到redis,今天我们就来聊聊如何触发这个切换动作?
1、方案一:利用异常机制
伪代码如下:
首先这个方案是不可行的,因为每次请求,还是先走到数据库逻辑,然后等抛出异常,这个时间会挺长的,业务上是无法接受的
2、方案二:主动进行mysql探活
实现思路: 可以利用数据库连接池检测有效连接的思路
实现方案
1、方式一:利用druid连接池的ValidConnectionChecker进行扩展
核心逻辑如下
@Slf4jpublic class MysqlConnectionCheck extends MySqlValidConnectionChecker { private ApplicationContext applicationContext; public MysqlConnectionCheck(ApplicationContext applicationContext) { this.applicationContext = applicationContext; } @Override public 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.DruidDataSource driverClassName: ${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; } @Override public boolean isValidConnection(Connection conn, String query, int validationQueryTimeout) throws SQLException { boolean valid = checkConnection(conn, query, validationQueryTimeout); // unexcepted branch if (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); } }复制代码
@Override public void afterPropertiesSet() throws Exception { executorService.scheduleWithFixedDelay(new DbCheckTask(),0,dbCheckProperies.getTimeBetweenEvictionRunsMillis(), TimeUnit.MILLISECONDS); } private class DbCheckTask implements Runnable{ @Override public 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); } } } 复制代码总结
其实mysql的探活实现方式有很多种,本文的实现检测逻辑是直接套用druid的检测连接逻辑,之前对druid的使用,基本上就是停留在配置上,没过多关注。
为了写这篇文章,特地翻了一下druid的源码,主要是因为之前以为扩展ValidConnectionChecker就行了,后面发现行不通。就看了一下源码,发现druid的设计思路挺好的,有些实现思想是我们在日常开发中,可以借鉴使用的。还有druid里面有些跟数据库相关的util,也是可以直接拿过来用的。
原文链接:
标签: #checkmysqlis