SpringBoot+MybatisPlus实现读写分离，自动切换数据源，主从同步

读写分离有必要吗？

实现读写分离势必要与你所做的项目相关，如果项目读多写少，那就可以设置读写分离，让“读”可以更快，因为你可以把你的“读”数据库的innodb设置为MyISAM引擎，让MySQL处理速度更快。

实现读写分离的步骤

监听MybatisPlus接口，判断是写入还是读取

在这里我使用的是AOP的方式，动态监听MybatisPlus中Mapper的方法。

import com.supostacks.wrdbrouter.DBContextHolder;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.stereotype.Component;@Aspect
@Component
public class MyBatisPlusAop {@Pointcut("execution(* com.baomidou.mybatisplus.core.mapper.BaseMapper.select*(..))")public void readPointCut(){}@Pointcut("execution(* com.baomidou.mybatisplus.core.mapper.BaseMapper.insert*(..))" +"||execution(* com.baomidou.mybatisplus.core.mapper.BaseMapper.update*(..))" +"||execution(* com.baomidou.mybatisplus.core.mapper.BaseMapper.delete*(..))")public void writePointCut(){}@Before("readPointCut()")public void readBefore(){DBContextHolder.setDBKey("dataread");}@Before("writePointCut()")public void writeBefore(){DBContextHolder.setDBKey("datawrite");}
}

定义介绍：
DBContextHolder中使用了ThreadLocal存储数据库名
readPointCut定义读的切点，如果调用的是BaseMapper.select*(…)则判断是读数据，则调用读库。
writePointCut定义写的切点，如果调用的是BaseMapper.insert|update|delete*(…)则判断是写数据，则调用写库

自定义MyBatis的DataSourceAutoConfiguration

DataSourceAutoConfiguration是Mybatis官方使用的SpringBootStarter，因为我这边自定义了Mybatis连接的相关属性名用来切换数据源，所以我需要自构一个DataSourceAutoConfig，代码如下：


@Configuration
public class DataSourceAutoConfig implements EnvironmentAware {private final String TAG_GLOBAL = "global";/*** 数据源配置组*/private final Map<String, Map<String, Object>> dataSourceMap = new HashMap<>();/*** 默认数据源配置*/private Map<String, Object> defaultDataSourceConfig;public DataSource createDataSource(Map<String,Object> attributes){try {DataSourceProperties dataSourceProperties = new DataSourceProperties();dataSourceProperties.setUrl(attributes.get("url").toString());dataSourceProperties.setUsername(attributes.get("username").toString());dataSourceProperties.setPassword(attributes.get("password").toString());String driverClassName = attributes.get("driver-class-name") == null ? "com.zaxxer.hikari.HikariDataSource" : attributes.get("driver-class-name").toString();dataSourceProperties.setDriverClassName(driverClassName);String typeClassName = attributes.get("type-class-name") == null ? "com.zaxxer.hikari.HikariDataSource" : attributes.get("type-class-name").toString();return dataSourceProperties.initializeDataSourceBuilder().type((Class<DataSource>) Class.forName(typeClassName)).build();} catch (ClassNotFoundException e) {throw new RuntimeException(e);}}@Beanpublic DataSource createDataSource() {// 创建数据源Map<Object, Object> targetDataSources = new HashMap<>();for (String dbInfo : dataSourceMap.keySet()) {Map<String, Object> objMap = dataSourceMap.get(dbInfo);// 根据objMap创建DataSourceProperties,遍历objMap根据属性反射创建DataSourcePropertiesDataSource ds = createDataSource(objMap);targetDataSources.put(dbInfo, ds);}// 设置数据源DynamicDataSource dynamicDataSource = new DynamicDataSource();dynamicDataSource.setTargetDataSources(targetDataSources);// db0为默认数据源dynamicDataSource.setDefaultTargetDataSource(createDataSource(defaultDataSourceConfig));return dynamicDataSource;}@Overridepublic void setEnvironment(Environment environment) {String prefix = "wr-db-router.spring.datasource.";String datasource = environment.getProperty(prefix + "db");Map<String, Object> globalInfo = getGlobalProps(environment, prefix + TAG_GLOBAL);assert datasource != null;for(String db : datasource.split(",")){final String dbKey = prefix + db; //数据库列表Map<String,Object> datasourceProps = PropertyUtil.handle(environment,dbKey, Map.class);injectGlobals(datasourceProps, globalInfo);dataSourceMap.put(db,datasourceProps);}String defaultData = environment.getProperty(prefix + "default");defaultDataSourceConfig = PropertyUtil.handle(environment,prefix + defaultData, Map.class);injectGlobals(defaultDataSourceConfig, globalInfo);}public Map getGlobalProps(Environment env, String key){try {return PropertyUtil.handle(env,key, Map.class);} catch (Exception e) {return Collections.EMPTY_MAP;}}private void injectGlobals(Map<String,Object> origin,Map<String,Object> global){global.forEach((k,v)->{if(!origin.containsKey(k)){origin.put(k,v);}else{injectGlobals((Map<String, Object>) origin.get(k), (Map<String, Object>) global.get(k));}});}

DynamicDataSource 这个类继承了AbstractRoutingDataSource，通过获取ThreadLocal中的数据库名，动态切换数据源。

public class DynamicDataSource extends AbstractRoutingDataSource {@Value("wr-db-router.spring.datasource.default")private String defaultDatasource;@Overrideprotected Object determineCurrentLookupKey() {if(null == DBContextHolder.getDBKey()){return defaultDatasource;}else{return DBContextHolder.getDBKey();}}
}

我们通过重写determineCurrentLookupKey方法并设置对应的数据库名称，我们就可以实现切换数据源的功能了。

AbstractRoutingDataSource 主要源码如下：

 public Connection getConnection() throws SQLException {return this.determineTargetDataSource().getConnection();}...protected DataSource determineTargetDataSource() {Assert.notNull(this.resolvedDataSources, "DataSource router not initialized");Object lookupKey = this.determineCurrentLookupKey();DataSource dataSource = (DataSource)this.resolvedDataSources.get(lookupKey);if (dataSource == null && (this.lenientFallback || lookupKey == null)) {dataSource = this.resolvedDefaultDataSource;}if (dataSource == null) {throw new IllegalStateException("Cannot determine target DataSource for lookup key [" + lookupKey + "]");} else {return dataSource;}}

自定义MyBatisPlus的SpringBoot自动配置

MybatisPlus是默认使用的Mybatis的自带的DataSourceAutoConfiguration，但是我们已经将这个自定义了，所以我们也要去自定义一个MyBatisPlusAutoConfig，如果不自定义的话，系统启动将报错。代码如下：

@Configuration(proxyBeanMethods = false
)
@ConditionalOnClass({SqlSessionFactory.class, SqlSessionFactoryBean.class})
@ConditionalOnSingleCandidate(DataSource.class)
@EnableConfigurationProperties({MybatisPlusProperties.class})
@AutoConfigureAfter({DataSourceAutoConfig.class, MybatisPlusLanguageDriverAutoConfiguration.class})
public class MyBatisPlusAutoConfig  implements InitializingBean {
xxx
}

这个代码是直接拷贝了MyBatisPlusAutoConfiguration，只是将@AutoConfigureAfter({DataSourceAutoConfiguration.class, MybatisPlusLanguageDriverAutoConfiguration.class})改为了
@AutoConfigureAfter({DataSourceAutoConfig.class, MybatisPlusLanguageDriverAutoConfiguration.class})

这样启动就不会报错了。

其他步骤

上面这些开发完，就差不多可以实现数据库的动态切换从而实现读写分离了，不过其中有一个方法PropertyUtil，这是自定义的一个可以读取properties某个前缀下的所有属性的一个工具类。代码如下：


import org.springframework.core.env.Environment;
import org.springframework.core.env.PropertyResolver;import java.lang.reflect.Constructor;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;public class PropertyUtil {private static int springBootVersion = 2;public static <T> T handle(final Environment environment,final String prefix,final Class<T> clazz){switch (springBootVersion){case 1:return (T) v1(environment,prefix);case 2:return (T) v2(environment,prefix,clazz);default:throw new RuntimeException("Unsupported Spring Boot version");}}public static Object v1(final Environment environment,final String prefix){try {Class<?> resolverClass = Class.forName("org.springframework.boot.bind.RelaxedPropertyResolver");Constructor<?> resolverConstructor = resolverClass.getDeclaredConstructor(PropertyResolver.class);Method getSubPropertiesMethod = resolverClass.getDeclaredMethod("getSubProperties", String.class);Object resolverObject = resolverConstructor.newInstance(environment);String prefixParam = prefix.endsWith(".") ? prefix : prefix + ".";return getSubPropertiesMethod.invoke(resolverObject, prefixParam);} catch (final ClassNotFoundException | NoSuchMethodException | SecurityException | InstantiationException| IllegalAccessException | IllegalArgumentException | InvocationTargetException ex) {throw new RuntimeException(ex.getMessage(), ex);}}private static Object v2(final Environment environment, final String prefix, final Class<?> targetClass) {try {Class<?> binderClass = Class.forName("org.springframework.boot.context.properties.bind.Binder");Method getMethod = binderClass.getDeclaredMethod("get", Environment.class);Method bindMethod = binderClass.getDeclaredMethod("bind", String.class, Class.class);Object binderObject = getMethod.invoke(null, environment);String prefixParam = prefix.endsWith(".") ? prefix.substring(0, prefix.length() - 1) : prefix;Object bindResultObject = bindMethod.invoke(binderObject, prefixParam, targetClass);Method resultGetMethod = bindResultObject.getClass().getDeclaredMethod("get");return resultGetMethod.invoke(bindResultObject);}catch (final ClassNotFoundException | NoSuchMethodException | SecurityException | IllegalAccessException| IllegalArgumentException | InvocationTargetException ex) {throw new RuntimeException(ex.getMessage(), ex);}}
}

我将路由切换的功能逻辑单独拉成了一个SpringBootStarter，目录如下：
在这里插入图片描述
顺便介绍一下如何将以个项目在SpringBootStarter中自动装配
1.在resources中创建文件夹META-INF
2.创建spring.factories文件
3.在该文件中设置你需要自动装配的类

org.springframework.boot.autoconfigure.EnableAutoConfiguration=\com.xxx.wrdbrouter.config.DataSourceAutoConfig,\com.xxx.wrdbrouter.config.MyBatisPlusAutoConfig

主从同步配置方法

由于我自己只有一个服务器，于是我以本地为从库，云服务器为主库，两台MySQL服务器配置了主从同步。

一、修改主库的配置文件，my.cnf

# log config
log-bin = mysql-bin     #开启mysql的binlog日志功能
sync_binlog = 1         #控制数据库的binlog刷到磁盘上去 , 0 不控制，性能最好，1每次事物提交都会刷到日志文件中，性能最差，最安全
binlog_format = mixed   #binlog日志格式，mysql默认采用statement，建议使用mixed
expire_logs_days = 7                           #binlog过期清理时间
max_binlog_size = 100m                    #binlog每个日志文件大小
binlog_cache_size = 4m                        #binlog缓存大小
max_binlog_cache_size= 512m              #最大binlog缓存大
binlog-ignore-db=mysql #不生成日志文件的数据库，多个忽略数据库可以用逗号拼接，或者 复制这句话，写多行auto-increment-offset = 1     # 自增值的偏移量
auto-increment-increment = 1  # 自增值的自增量
slave-skip-errors = all #跳过从库错误

2、修改后需要重启MySQL服务
3、建复制用户

CREATE USER repl_user IDENTIFIED BY 'repl_root';
CREATE USER 'repl_user'@'192.168.0.136' IDENTIFIED BY 'repl_root';

给复制用户进行复制授权

grant replication slave on *.* to 'repl_user'@'%';
grant replication slave on *.* to 'repl_user'@'192.168.0.136';FLUSH PRIVILEGES;

修改复制用户对应的plugin

alter user 'repl_user'@'%' identified with mysql_native_password by 'repl_root';
alter user 'repl_user'@'192.168.0.136' identified with mysql_native_password by 'repl_root';

二、配置从库
1、修改从库配置文件

[mysqld]
server-id = 2
log-bin=mysql-bin
relay-log = mysql-relay-bin
##不同步的库表
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%
replicate-wild-ignore-table=www_dwurl_site.%
replicate-wild-ignore-table=dragonwealths_co.%

2、进入从库MySQL，创建连接主库

change master to
master_host='xxxxx', ##主库IP地址
master_user='repl_user', ##复制用户
master_password='repl_root',##复制用户密码
master_port=3306,##主库port
master_log_file='mysql-bin.000009', ##主库最新的logbin文件
master_log_pos=21005389,##主库最新的logbin的position
master_retry_count=60,##重连次数
master_heartbeat_period=10000;##心跳