前言
先前寫過一篇文章聊聊如何利用redis實(shí)現(xiàn)多級緩存同步,里面講到業(yè)務(wù)部門因數(shù)據(jù)庫宕機(jī)，有技術(shù)提出當(dāng)數(shù)據(jù)庫宕機(jī)，切換到redis，今天我們就來聊聊如何觸發(fā)這個切換動作？
1、方案一：利用異常機(jī)制
偽代碼如下：
首先這個方案是不可行的，因為每次請求，還是先走到數(shù)據(jù)庫邏輯，然后等拋出異常，這個時間會挺長的，業(yè)務(wù)上是無法接受的
2、方案二：主動進(jìn)行MySQL探活
實(shí)現(xiàn)思路： 可以利用數(shù)據(jù)庫連接池檢測有效連接的思路
實(shí)現(xiàn)方案
1、方式一：利用druid連接池的ValidConnectionChecker進(jìn)行擴(kuò)展
核心邏輯如下
@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;
}
}
復(fù)制代碼
在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
復(fù)制代碼
這個方案也是不大行的，后面翻了一下druid源碼。當(dāng)數(shù)據(jù)庫拋出不可恢復(fù)的異常時，比如網(wǎng)絡(luò)抖動，異常斷開，druid會觸發(fā)exceptionSorter，拋棄連接。而CreateConnectionThread會檢測是否需要創(chuàng)建連接，如果不需要，他就會進(jìn)行等待。當(dāng)連接不夠時，會調(diào)用
com.alibaba.druid.pool.DruidAbstractDataSource#createPhysicalConnection()
復(fù)制代碼
進(jìn)行創(chuàng)建，同時isValidConnection也是在這個方法里面進(jìn)行連接驗證，但這邊就有問題，就是當(dāng)數(shù)據(jù)庫宕機(jī)了，就創(chuàng)建不了連接，因此就進(jìn)入異常流程，isValidConnection是沒法執(zhí)行的
2、方式二：參考druid的檢測連接邏輯，額外編寫定時器觸發(fā)檢測邏輯
核心代碼塊：
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);
}
}
復(fù)制代碼
@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);
}
}
}
復(fù)制代碼
總結(jié)
其實(shí)mysql的探活實(shí)現(xiàn)方式有很多種，本文的實(shí)現(xiàn)檢測邏輯是直接套用druid的檢測連接邏輯，之前對druid的使用，基本上就是停留在配置上，沒過多關(guān)注。
為了寫這篇文章，特地翻了一下druid的源碼，主要是因為之前以為擴(kuò)展ValidConnectionChecker就行了，后面發(fā)現(xiàn)行不通。就看了一下源碼，發(fā)現(xiàn)druid的設(shè)計思路挺好的，有些實(shí)現(xiàn)思想是我們在日常開發(fā)中，可以借鑒使用的。還有druid里面有些跟數(shù)據(jù)庫相關(guān)的util，也是可以直接拿過來用的。
原文鏈接：
https://juejin.cn/post/7184240942918926396
