数据库高可用方案有多种,比如主(读写)备,一主(读写)多从(读),多主(一主负责读写,其他作备)多从(读)等,本文仅以一主(读写)多从(读)为例,分析druid使用的细节
环境:
SQL Server 2012
druid version "1.0.25"
java version "1.7.0_67"
DataSource配置:
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
<!-- 连接串配置 -->
<property name="driverClassName" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
<!-- 初始化配置 -->
<property name="initialSize" value="3"/>
<property name="minIdle" value="3"/>
<property name="maxActive" value="100"/>
<!-- 连接有效性验证配置 -->
<property name="testOnBorrow" value="false" />
<property name="testOnReturn" value="false" />
<property name="testWhileIdle" value="true"/>
<property name="validationQuery" value="SELECT 1"/>
<property name="validationQueryTimeout" value="3000"/>
<property name="timeBetweenEvictionRunsMillis" value="30000"/>
<property name="minEvictableIdleTimeMillis" value="300000"/>
<property name="filters" value="stat"/>
</bean>
MSSQLValidConnectionChecker源码:
/*
* Copyright 1999-2101 Alibaba Group Holding Ltd.
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package com.alibaba.druid.pool.vendor;
import java.io.Serializable;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import com.alibaba.druid.pool.ValidConnectionChecker;
import com.alibaba.druid.pool.ValidConnectionCheckerAdapter;
import com.alibaba.druid.util.JdbcUtils;
/**
* A MSSQLValidConnectionChecker.
*/
public class MSSQLValidConnectionChecker extends ValidConnectionCheckerAdapter implements ValidConnectionChecker, Serializable {
private static final long serialVersionUID = 1L;
public MSSQLValidConnectionChecker(){
}
public boolean isValidConnection(final Connection c, String validateQuery, int validationQueryTimeout) throws Exception {
if (c.isClosed()) {
return false;
}
Statement stmt = null;
try {
stmt = c.createStatement();
stmt.setQueryTimeout(validationQueryTimeout);
stmt.execute(validateQuery);
return true;
} catch (SQLException e) {
throw e;
} finally {
JdbcUtils.close(stmt);
}
}
}
开发环境:一个域名指向不同的ip,实现主从切换
使用场景1:
主数据库宕机,手工或自动将其中一台从服务器切换为主
由于connection.isClosed() == true 或者 stmt.execute(validateQuery)出现异常,造成connection失效,数据连接池会重新创建到新主服务器的连接,达到切换目的
使用场景2:
其中一台从服务器切换为主,原主服务器切为读
以本文的配置,会造成原存活的connection在执行有效性检测时,stmt.execute("SELECT 1")正确执行,数据连接池仍会持有到旧主服务器的连接,而程序仍会使用此connection进行写操作,由于切换,原主服务器不再支持写操作,最终造成程序出现异常;新建的connection,不会出现异常。
而原来持有新主服务器的读连接,因为连接仍有效,会在主服务器进行读操作。
线上环境:不同的域名分别对应主从服务器,解决开发环境出现的问题
环境:
SQL Server 2012
druid version "1.0.25"
java version "1.7.0_67"
DataSource配置:
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
<!-- 连接串配置 -->
<property name="driverClassName" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
<!-- 初始化配置 -->
<property name="initialSize" value="3"/>
<property name="minIdle" value="3"/>
<property name="maxActive" value="100"/>
<!-- 连接有效性验证配置 -->
<property name="testOnBorrow" value="false" />
<property name="testOnReturn" value="false" />
<property name="testWhileIdle" value="true"/>
<property name="validationQuery" value="SELECT 1"/>
<property name="validationQueryTimeout" value="3000"/>
<property name="timeBetweenEvictionRunsMillis" value="30000"/>
<property name="minEvictableIdleTimeMillis" value="300000"/>
<property name="filters" value="stat"/>
</bean>
MSSQLValidConnectionChecker源码:
/*
* Copyright 1999-2101 Alibaba Group Holding Ltd.
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package com.alibaba.druid.pool.vendor;
import java.io.Serializable;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import com.alibaba.druid.pool.ValidConnectionChecker;
import com.alibaba.druid.pool.ValidConnectionCheckerAdapter;
import com.alibaba.druid.util.JdbcUtils;
/**
* A MSSQLValidConnectionChecker.
*/
public class MSSQLValidConnectionChecker extends ValidConnectionCheckerAdapter implements ValidConnectionChecker, Serializable {
private static final long serialVersionUID = 1L;
public MSSQLValidConnectionChecker(){
}
public boolean isValidConnection(final Connection c, String validateQuery, int validationQueryTimeout) throws Exception {
if (c.isClosed()) {
return false;
}
Statement stmt = null;
try {
stmt = c.createStatement();
stmt.setQueryTimeout(validationQueryTimeout);
stmt.execute(validateQuery);
return true;
} catch (SQLException e) {
throw e;
} finally {
JdbcUtils.close(stmt);
}
}
}
开发环境:一个域名指向不同的ip,实现主从切换
使用场景1:
主数据库宕机,手工或自动将其中一台从服务器切换为主
由于connection.isClosed() == true 或者 stmt.execute(validateQuery)出现异常,造成connection失效,数据连接池会重新创建到新主服务器的连接,达到切换目的
使用场景2:
其中一台从服务器切换为主,原主服务器切为读
以本文的配置,会造成原存活的connection在执行有效性检测时,stmt.execute("SELECT 1")正确执行,数据连接池仍会持有到旧主服务器的连接,而程序仍会使用此connection进行写操作,由于切换,原主服务器不再支持写操作,最终造成程序出现异常;新建的connection,不会出现异常。
而原来持有新主服务器的读连接,因为连接仍有效,会在主服务器进行读操作。
线上环境:不同的域名分别对应主从服务器,解决开发环境出现的问题