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

前言

先前写过一篇文章聊聊如何利用 redis 实现多级缓存同步, 外面讲到业务部门因数据库宕机，有技术提出当数据库宕机，切换到 redis，明天咱们就来聊聊如何触发这个切换动作？

1、计划一：利用异样机制

伪代码如下：

首先这个计划是不可行的，因为每次申请，还是先走到数据库逻辑，而后等抛出异样，这个工夫会挺长的，业务上是无奈承受的

2、计划二：被动进行 mysql 探活

实现思路： 能够利用数据库连接池检测无效连贯的思路

实现计划

1、形式一：利用 druid 连接池的 ValidConnectionChecker 进行扩大

外围逻辑如下

@Slf4j
public 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，也是能够间接拿过去用的。

demo 链接

https://github.com/lyb-geek/springboot-learning/tree/master/springboot-db-check