mysql主从分离
1. mysql主从同步数据库配置
- 两台服务器上分别配置上mysql数据库。
- 打开配置文件 vim /etc/my.cnf
在mysqld下面添加配置
主库配置:
server-id=1 #唯一id
log-bin=master-bin #打开二进制日志
log-bin-index=master-bin.index #打开二进制日志索引
从库配置:
server-id=2 #唯一id注意查检配置文件中server-id的重复
relay-log-index=slave-relay-bin.index #打开二进制日志索引
relay-log=slave-relay-bin #打开二进制日志
如果同一台机器上配置两个mysql则要更改 port重启数据库:service mysqld restart 或 etc/init.d/mysql stop etc/init.d/mysql start
查看主库状态:SHOW MASTER STATUS
连接主从库
在主库添加权限 create user repl; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'115.28.159.6 IDENTIFIED BY 'mysql';
‘repl’@’115.28.159.6从库要连接的账号和从库ip地址
‘mysql’从库要连接的密码
刷新使其生效 flush privileges;
在从库中配置从库连接主库
change master to master_host=’120.24.64.163’ ,master_port=3306 ,master_user=’repl’ ,maser_password=’mysql’ ,master_log_file=’master_bin.000001’ , master_log_pos=0;
开启主从跟踪
start slave;
master_log_pos=0 主库读取位置
2. 代码层读写分离的实现
继承 AbstractRoutingDataSource 实现 determineCurrentLookupKey()方法 根据要求返回不同的数据源
DynamicDataSource.java
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
/**
* @Auther: xiangkai
* @Date: 2018/8/20 10:40
* @Description:
*/
public class DynamicDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
return DynamicDataSourceHolder.getDbType();
}
}
DynamicDataSourceHolder.java
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
* @Auther: xiangkai
* @Date: 2018/8/20 10:43
* @Description:
*/
public class DynamicDataSourceHolder {
private static Logger log=LoggerFactory.getLogger(DynamicDataSourceHolder.class);
/**
* 线程安全的
*/
private static ThreadLocal<String> contextHolder=new ThreadLocal<>();
/**
* 主库源
*/
public static final String DB_MASTER="master";
/**
* 从库源
*/
public static final String DB_SLAVE="slave";
/**返回数据源,默认为master
* @return
*/
public static String getDbType(){
String db = contextHolder.get();
if (db==null) {
db=DB_MASTER;
}
return db;
}
/**设置 线程的Dbtype
* @param db
*/
public static void setDbType(String db){
log.debug("所使用的数据为:"+db);
contextHolder.set(db);
}
/**
* 清理连接类型
*/
public static void clearDbType(){
contextHolder.remove();
}
}
mybatis拦截器
DynamicDataSourceInterceptor.java
import org.apache.ibatis.executor.keygen.SelectKeyGenerator;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlCommandType;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Plugin;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.transaction.support.TransactionSynchronizationManager;
import java.util.Locale;
import java.util.Properties;
import java.util.concurrent.Executor;
/**
* @Auther: xiangkai
* @Date: 2018/8/20 10:51
* @Description:
*/
public class DynamicDataSourceInterceptor implements Interceptor {
private static Logger log= LoggerFactory.getLogger(DynamicDataSourceInterceptor.class);
/**
* 增删改正则
*/
private static final String REGEX = ".*insert\\u0020.*|.*delete\\u0020.*|.*update\\u0020.*";
/**
* 拦截方法
*
* @param invocation
* @return
* @throws Throwable
*/
@Override
public Object intercept(Invocation invocation) throws Throwable {
//判断是否是事物管理
boolean synchronizationActive = TransactionSynchronizationManager.isActualTransactionActive();
Object[] args = invocation.getArgs();
MappedStatement ms = (MappedStatement) args[0];
String lookupKey = DynamicDataSourceHolder.DB_MASTER;
//为是事物管理
if (!synchronizationActive) {
//增删改查的参数
//读方法
if (ms.getSqlCommandType().equals(SqlCommandType.SELECT)) {
//selectKey 为自增id查询主键(SELECT LAST_INSERT_ID())方法,使用主库
if (ms.getId().contains(SelectKeyGenerator.SELECT_KEY_SUFFIX)) {
lookupKey = DynamicDataSourceHolder.DB_MASTER;
} else {
BoundSql boundSql = ms.getSqlSource().getBoundSql(args[1]);
String sql = boundSql.getSql().toLowerCase(Locale.CANADA).replaceAll("[\\t\\n\\r]", " ");
if (sql.matches(REGEX)) {
lookupKey = DynamicDataSourceHolder.DB_MASTER;
} else {
lookupKey = DynamicDataSourceHolder.DB_SLAVE;
}
}
}
} else {
lookupKey = DynamicDataSourceHolder.DB_MASTER;
}
log.debug("设置方法[{}] user[{}]Strategy,SqlCommanType[{}]...",ms.getId(),lookupKey,ms.getSqlCommandType().name());
DynamicDataSourceHolder.setDbType(lookupKey);
return invocation.proceed();
}
/**
* 返回代理对象
*
* @param target
* @return
*/
@Override
public Object plugin(Object target) {
//Executor是支持一系列增删改查的操作的
if (target instanceof Executor) {
return Plugin.wrap(target, this);
} else {
return target;
}
}
/**
* 初始化时做相关设置
*
* @param properties
*/
@Override
public void setProperties(Properties properties) {
}
}
3. 配置
mybatis-config中配置拦截器DynamicDataSourceInterceptor
<plugins>
<plugin interceptor="com.xiang.o2o.dao.split.DynamicDataSourceInterceptor">
</plugin>
</plugins>
在spring-dao中配置主库和从库数据源并加入到DynamicDataSource的targetDataSources属性中
配置懒加载,sql执行时判断用master或slave源
<!--2.数据库连接池-->
<bean id="abstractDataSource" abstract="true" class="com.mchange.v2.c3p0.ComboPooledDataSource"
destroy-method="close">
<!--c3po连接池的私有属性-->
<property name="maxPoolSize" value="30"/>
<property name="minPoolSize" value="10"/>
<!--关闭连接后不自动commit-->
<property name="autoCommitOnClose" value="false"/>
<!--获取连接去超时时间-->
<property name="checkoutTimeout" value="10000"/>
<!--当获取连接失败重试次数-->
<property name="acquireRetryAttempts" value="2"/>
</bean>
<!--主库配置-->
<bean id="master" parent="abstractDataSource">
<!--配置连接池属性-->
<property name="driverClass" value="${jdbc.driver}"/>
<property name="jdbcUrl" value="${jdbc.master.url}"/>
<property name="user" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</bean>
<!--从库配置-->
<bean id="slave" parent="abstractDataSource">
<!--配置连接池属性-->
<property name="driverClass" value="${jdbc.driver}"/>
<property name="jdbcUrl" value="${jdbc.slave.url}"/>
<property name="user" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</bean>
<!--配置动态数据源,这儿targetDataSources就是路由数据源对应的名称-->
<bean id="dynamicDataSource" class="com.xiang.o2o.dao.split.DynamicDataSource">
<property name="targetDataSources">
<map>
<entry value-ref="master" key="master"></entry>
<entry value-ref="slave" key="slave"></entry>
</map>
</property>
</bean>
<!--懒加载targetDataSources-->
<bean id="dataSource" class="org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy">
<property name="targetDataSource">
<ref bean="dynamicDataSource"/>
</property>
</bean>