阿里云rds数据库迁移实战_mysql

概述由于某几个业务表数据量太大，数据由业务写，数据部门读。写压力不大，读却很容易导致长时间等待问题（读由单独系统进行读），导致连接被占用，从而容易并发稍稍增长导致全库卡死！于是，就拆库呗。业务系统拆分就不要做了（微服务化），没那工夫。直接原系统拆两个数据源出来，对某几个高压力表的写就单独用这个数据源，从而减轻压力。所以，分库工作就变为了两个步骤：1. 两个数据源读写业务；2. 将新数据库写动作同步回读库；再由于方便性，数据库也是使用阿里的rds数据库，一个变为两个！代码上做两个数据源很简单，尤其是在原有代码就写得比较清晰的情况下；如下是使用springboot和mybatis做的多数据源配置：1. 配置多个数据源类；2. 启用mybatis多数据源，加载不同配置bean；3. 根据扫描路径区别使用的数据源；4. 根据扫描路径将需要拆分的表与原表区别；5. 测试时可使用同同机器上多库形式运行，上线后为多实例同库运行；6. 验证功能可用性；如有问题，及时修改；具体配置如下：// 原数据源配置@Configuration@MapperScan(basePackages = MainDataSourceConfig.SCAN_BASE_PACKAGE, sqlSessionFactoryRef = "sqlSessionFactory")public class MainDataSourceConfig {public static final String SCAN_BASE_PACKAGE = "com.xxx.dao.mapper.main";/*** xml 配置文件扫描路径*/public static final String SCAN_XML_MAPPER_LOCATION = "classpath:mybatis/mappers/mysql/main/**/*Mapper.xml";//jdbcConfig@Value("${jdbc.main.url}")private String jdbcUrl;@Value("${jdbc.main.driver}")private String driverName;@Value("${pool.main.maxPoolSize}")private int maxPoolSize;@Value("${jdbc.main.username}")private String jdbcUserName;@Value("${jdbc.main.password}")private String jdbcPwd;@Value("${pool.main.maxWait}")private int jdbcMaxWait;@Value("${pool.main.validationQuery}")private String validationQuery;@Bean(name = "druidDataSource")@Primarypublic DruidDataSource druidDataSource(){DruidDataSource ds = new DruidDataSource();ds.setUrl(jdbcUrl);ds.setDriverClassName(driverName);ds.setMaxActive(maxPoolSize);ds.setUsername(jdbcUserName);ds.setPassword(jdbcPwd);ds.setRemoveAbandoned(true);ds.setMaxWait(jdbcMaxWait);ds.setValidationQuery(validationQuery);return ds;}@Bean(name = "dataSourceTransactionManager")@Primarypublic DataSourceTransactionManager dataSourceTransactionManager(){DataSourceTransactionManager dm = new DataSourceTransactionManager();dm.setDataSource(druidDataSource());return dm;}@Bean(name="sqlSessionFactory")@Primarypublic SqlSessionFactory sqlSessionFactory() throws Exception {SqlSessionFactoryBean sqlSessionFactory = new SqlSessionFactoryBean();ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();Resource[] mapperXmlResource = resolver.getResources(SCAN_XML_MAPPER_LOCATION);sqlSessionFactory.setDataSource(druidDataSource());sqlSessionFactory.setMapperLocations(mapperXmlResource);return sqlSessionFactory.getObject();}}// 新数据源配置，仅仅改了下配置名，但是还不得不另一个配置类@Configuration@MapperScan(basePackages = ExtraDataSourceConfig.SCAN_BASE_PACKAGE, sqlSessionFactoryRef = "sqlSessionFactoryExt")public class ExtraDataSourceConfig {public static final String SCAN_BASE_PACKAGE = "com.xxx.dao.mapper.ext";/*** xml 配置文件扫描路径*/public static final String SCAN_XML_MAPPER_LOCATION = "classpath:mybatis/mappers/mysql/ext/**/*Mapper.xml";//jdbcConfig@Value("${jdbc.ext.url}")private String jdbcUrl;@Value("${jdbc.ext.driver}")private String driverName;@Value("${pool.ext.maxPoolSize}")private int maxPoolSize;@Value("${jdbc.ext.username}")private String jdbcUserName;@Value("${jdbc.ext.password}")private String jdbcPwd;@Value("${pool.ext.maxWait}")private int jdbcMaxWait;@Value("${pool.ext.validationQuery}")private String validationQuery;@Bean(name = "druidDataSourceExt")public DruidDataSource druidDataSource(){DruidDataSource ds = new DruidDataSource();ds.setUrl(jdbcUrl);ds.setDriverClassName(driverName);ds.setMaxActive(maxPoolSize);ds.setUsername(jdbcUserName);ds.setPassword(jdbcPwd);ds.setRemoveAbandoned(true);ds.setMaxWait(jdbcMaxWait);ds.setValidationQuery(validationQuery);return ds;}@Bean(name = "dataSourceTransactionManagerExt")public DataSourceTransactionManager dataSourceTransactionManager(){DataSourceTransactionManager dm = new DataSourceTransactionManager();dm.setDataSource(druidDataSource());return dm;}@Bean(name="sqlSessionFactoryExt")public SqlSessionFactory sqlSessionFactory() throws Exception {SqlSessionFactoryBean sqlSessionFactory = new SqlSessionFactoryBean();ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();Resource[] mapperXmlResource = resolver.getResources(SCAN_XML_MAPPER_LOCATION);sqlSessionFactory.setDataSource(druidDataSource());sqlSessionFactory.setMapperLocations(mapperXmlResource);return sqlSessionFactory.getObject();}}然后，将需要分离的表 *** 作转移到相应的包路径下，即可实现多数据源 *** 作了！而多数据源配置对于基于xml配置spring来说，可能更加直观更加简单，甚至xml文件都不用分离！<bean name="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close"><property name="url" value="${jdbc.main.url}" /><property name="username" value="${jdbc.main.username}" /><property name="password" value="${jdbc.main.password}" /><property name="maxActive" value="${jdbc.main.maxActive}" /><property name="maxWait" value="${jdbc.main.maxWait}" /></bean><bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"><propert

　　由于某几个业务表数据量太大，数据由业务写，数据部门读。

　　写压力不大，读却很容易导致长时间等待问题（读由单独系统进行读），导致连接被占用，从而容易并发稍稍增长导致全库卡死！

　　于是，就拆库呗。

　　业务系统拆分就不要做了（微服务化），没那工夫。

　　直接原系统拆两个数据源出来，对某几个高压力表的写就单独用这个数据源，从而减轻压力。

所以，分库工作就变为了两个步骤：

　　再由于方便性，数据库也是使用阿里的rds数据库，一个变为两个！

　　代码上做两个数据源很简单，尤其是在原有代码就写得比较清晰的情况下；

如下是使用springboot和mybatis做的多数据源配置：

= MainDataSourceConfig.SCAN_BASE_PACKAGE,sqlSessionFactoryRef = "sqlSessionFactory"

</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;pub<a href="https://m.jb51.cc/tag/li/" target="_blank" >li</a>c</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;static</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;final</span> String SCAN_BASE_PACKAGE = "com.xxx.dao.mapper.main"<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt;;</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #008000"&gt;/**</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #008000"&gt; * xml <a href="https://www.jb51.cc/tag/peizhiwenjian/" target="_blank" >配置文件</a>扫描<a href="https://m.jb51.cc/tag/lujing/" target="_blank" >路径</a> </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #008000"&gt;*/</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;pub<a href="https://m.jb51.cc/tag/li/" target="_blank" >li</a>c</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;static</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;final</span> String SCAN_XML_MAPPER_LOCATION = "cla<a href="https://www.jb51.cc/tag/ssp/" target="_blank" >ssp</a>ath:mybatis/mappers/<a href="https://www.jb51.cc/tag/MysqL/" target="_blank" >MysqL</a>/main/**/*Mapper.xml"<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt;;</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #008000"&gt;//</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #008000"&gt;jdbcCon<a href="https://www.jb51.cc/tag/fig/" target="_blank" >fig</a></span>@Value("${jdbc.main.url}"<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt;)</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;private</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt; String jdbcUrl;@Value(</span>"${jdbc.main.driver}"<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt;)</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;private</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt; String driver<a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a>;@Value(</span>"${po<a href="https://m.jb51.cc/tag/ol/" target="_blank" >ol</a>.main.maxPo<a href="https://m.jb51.cc/tag/ol/" target="_blank" >ol</a>Size}"<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt;)</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;private</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;int</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt; maxPo<a href="https://m.jb51.cc/tag/ol/" target="_blank" >ol</a>Size;@Value(</span>"${jdbc.main.user<a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a>}"<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt;)</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;private</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt; String jdbcUser<a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a>;@Value(</span>"${jdbc.main.password}"<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt;)</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;private</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt; String jdbcPwd;@Value(</span>"${po<a href="https://m.jb51.cc/tag/ol/" target="_blank" >ol</a>.main.maxWait}"<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt;)</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;private</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;int</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt; jdbcMaxWait;@Value(</span>"${po<a href="https://m.jb51.cc/tag/ol/" target="_blank" >ol</a>.main.val<a href="https://m.jb51.cc/tag/ID/" target="_blank" >ID</a>ation<a href="https://m.jb51.cc/tag/query/" target="_blank" >query</a>}"<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt;)</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;private</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt; String val<a href="https://m.jb51.cc/tag/ID/" target="_blank" >ID</a>ation<a href="https://m.jb51.cc/tag/query/" target="_blank" >query</a>;@Bean(<a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a> </span>= "dru<a href="https://m.jb51.cc/tag/ID/" target="_blank" >ID</a>DataSource"<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt;)@Primary</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;pub<a href="https://m.jb51.cc/tag/li/" target="_blank" >li</a>c</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt; Dru<a href="https://m.jb51.cc/tag/ID/" target="_blank" >ID</a>DataSource dru<a href="https://m.jb51.cc/tag/ID/" target="_blank" >ID</a>DataSource(){    Dru<a href="https://m.jb51.cc/tag/ID/" target="_blank" >ID</a>DataSource ds </span>= <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;new</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt; Dru<a href="https://m.jb51.cc/tag/ID/" target="_blank" >ID</a>DataSource();    ds.setUrl(jdbcUrl);    ds.setDriverClass<a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a>(driver<a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a>);    ds.setMaxActive(maxPo<a href="https://m.jb51.cc/tag/ol/" target="_blank" >ol</a>Size);    ds.setUser<a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a>(jdbcUser<a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a>);    ds.setPassword(jdbcPwd);    ds.setRemoveAbandoned(</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;true</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt;);    ds.setMaxWait(jdbcMaxWait);    ds.setVal<a href="https://m.jb51.cc/tag/ID/" target="_blank" >ID</a>ation<a href="https://m.jb51.cc/tag/query/" target="_blank" >query</a>(val<a href="https://m.jb51.cc/tag/ID/" target="_blank" >ID</a>ation<a href="https://m.jb51.cc/tag/query/" target="_blank" >query</a>);    </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;return</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt; ds;}@Bean(<a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a> </span>= "dataSourceTransactionManager"<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt;)@Primary</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;pub<a href="https://m.jb51.cc/tag/li/" target="_blank" >li</a>c</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt; DataSourceTransactionManager dataSourceTransactionManager(){    DataSourceTransactionManager dm </span>= <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;new</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt; DataSourceTransactionManager();    dm.setDataSource(dru<a href="https://m.jb51.cc/tag/ID/" target="_blank" >ID</a>DataSource());    </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;return</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt; dm;}@Bean(<a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a></span>="<a href="https://www.jb51.cc/tag/sql/" target="_blank" >sql</a>SessionFactory"<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt;)@Primary</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;pub<a href="https://m.jb51.cc/tag/li/" target="_blank" >li</a>c</span> <a href="https://www.jb51.cc/tag/sql/" target="_blank" >sql</a>SessionFactory <a href="https://www.jb51.cc/tag/sql/" target="_blank" >sql</a>SessionFactory() <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;throws</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt; Exception {    <a href="https://www.jb51.cc/tag/sql/" target="_blank" >sql</a>Session<a href="https://www.jb51.cc/tag/factorybean/" target="_blank" >factorybean</a> <a href="https://www.jb51.cc/tag/sql/" target="_blank" >sql</a>SessionFactory </span>= <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;new</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt; <a href="https://www.jb51.cc/tag/sql/" target="_blank" >sql</a>Session<a href="https://www.jb51.cc/tag/factorybean/" target="_blank" >factorybean</a>();    ResourcePatternRes<a href="https://m.jb51.cc/tag/ol/" target="_blank" >ol</a>ver res<a href="https://m.jb51.cc/tag/ol/" target="_blank" >ol</a>ver </span>= <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;new</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt; PathMatchingResourcePatternRes<a href="https://m.jb51.cc/tag/ol/" target="_blank" >ol</a>ver();    Resource[] mapperXmlResource </span>=<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt; res<a href="https://m.jb51.cc/tag/ol/" target="_blank" >ol</a>ver.getResources(SCAN_XML_MAPPER_LOCATION);    <a href="https://www.jb51.cc/tag/sql/" target="_blank" >sql</a>SessionFactory.setDataSource(dru<a href="https://m.jb51.cc/tag/ID/" target="_blank" >ID</a>DataSource());    <a href="https://www.jb51.cc/tag/sql/" target="_blank" >sql</a>SessionFactory.setMapperLocations(mapperXmlResource);    </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;return</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt; <a href="https://www.jb51.cc/tag/sql/" target="_blank" >sql</a>SessionFactory.g<a href="https://www.jb51.cc/tag/eto/" target="_blank" >eto</a>bject();}

}

// 新数据源配置，仅仅改了下配置名，但是还不得不另一个配置类
@Configuration
@MapperScan(basePackages = ExTradataSourceConfig.SCAN_BASE_PACKAGE,sqlSessionFactoryRef = "sqlSessionFactoryExt")
public class ExTradataSourceConfig {

</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;pub<a href="https://m.jb51.cc/tag/li/" target="_blank" >li</a>c</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;static</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;final</span> String SCAN_BASE_PACKAGE = "com.xxx.dao.mapper.ext"<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt;;</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #008000"&gt;/**</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #008000"&gt; * xml <a href="https://www.jb51.cc/tag/peizhiwenjian/" target="_blank" >配置文件</a>扫描<a href="https://m.jb51.cc/tag/lujing/" target="_blank" >路径</a> </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #008000"&gt;*/</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;pub<a href="https://m.jb51.cc/tag/li/" target="_blank" >li</a>c</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;static</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;final</span> String SCAN_XML_MAPPER_LOCATION = "cla<a href="https://www.jb51.cc/tag/ssp/" target="_blank" >ssp</a>ath:mybatis/mappers/<a href="https://www.jb51.cc/tag/MysqL/" target="_blank" >MysqL</a>/ext/**/*Mapper.xml"<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt;;</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #008000"&gt;//</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #008000"&gt;jdbcCon<a href="https://www.jb51.cc/tag/fig/" target="_blank" >fig</a></span>@Value("${jdbc.ext.url}"<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt;)</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;private</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt; String jdbcUrl;@Value(</span>"${jdbc.ext.driver}"<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt;)</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;private</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt; String driver<a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a>;@Value(</span>"${po<a href="https://m.jb51.cc/tag/ol/" target="_blank" >ol</a>.ext.maxPo<a href="https://m.jb51.cc/tag/ol/" target="_blank" >ol</a>Size}"<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt;)</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;private</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;int</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt; maxPo<a href="https://m.jb51.cc/tag/ol/" target="_blank" >ol</a>Size;@Value(</span>"${jdbc.ext.user<a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a>}"<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt;)</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;private</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt; String jdbcUser<a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a>;@Value(</span>"${jdbc.ext.password}"<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt;)</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;private</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt; String jdbcPwd;@Value(</span>"${po<a href="https://m.jb51.cc/tag/ol/" target="_blank" >ol</a>.ext.maxWait}"<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt;)</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;private</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;int</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt; jdbcMaxWait;@Value(</span>"${po<a href="https://m.jb51.cc/tag/ol/" target="_blank" >ol</a>.ext.val<a href="https://m.jb51.cc/tag/ID/" target="_blank" >ID</a>ation<a href="https://m.jb51.cc/tag/query/" target="_blank" >query</a>}"<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt;)</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;private</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt; String val<a href="https://m.jb51.cc/tag/ID/" target="_blank" >ID</a>ation<a href="https://m.jb51.cc/tag/query/" target="_blank" >query</a>;@Bean(<a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a> </span>= "dru<a href="https://m.jb51.cc/tag/ID/" target="_blank" >ID</a>DataSourceExt"<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt;)</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;pub<a href="https://m.jb51.cc/tag/li/" target="_blank" >li</a>c</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt; Dru<a href="https://m.jb51.cc/tag/ID/" target="_blank" >ID</a>DataSource dru<a href="https://m.jb51.cc/tag/ID/" target="_blank" >ID</a>DataSource(){    Dru<a href="https://m.jb51.cc/tag/ID/" target="_blank" >ID</a>DataSource ds </span>= <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;new</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt; Dru<a href="https://m.jb51.cc/tag/ID/" target="_blank" >ID</a>DataSource();    ds.setUrl(jdbcUrl);    ds.setDriverClass<a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a>(driver<a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a>);    ds.setMaxActive(maxPo<a href="https://m.jb51.cc/tag/ol/" target="_blank" >ol</a>Size);    ds.setUser<a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a>(jdbcUser<a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a>);    ds.setPassword(jdbcPwd);    ds.setRemoveAbandoned(</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;true</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt;);    ds.setMaxWait(jdbcMaxWait);    ds.setVal<a href="https://m.jb51.cc/tag/ID/" target="_blank" >ID</a>ation<a href="https://m.jb51.cc/tag/query/" target="_blank" >query</a>(val<a href="https://m.jb51.cc/tag/ID/" target="_blank" >ID</a>ation<a href="https://m.jb51.cc/tag/query/" target="_blank" >query</a>);    </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;return</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt; ds;}@Bean(<a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a> </span>= "dataSourceTransactionManagerExt"<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt;)</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;pub<a href="https://m.jb51.cc/tag/li/" target="_blank" >li</a>c</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt; DataSourceTransactionManager dataSourceTransactionManager(){    DataSourceTransactionManager dm </span>= <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;new</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt; DataSourceTransactionManager();    dm.setDataSource(dru<a href="https://m.jb51.cc/tag/ID/" target="_blank" >ID</a>DataSource());    </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;return</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt; dm;}@Bean(<a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a></span>="<a href="https://www.jb51.cc/tag/sql/" target="_blank" >sql</a>SessionFactoryExt"<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt;)</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;pub<a href="https://m.jb51.cc/tag/li/" target="_blank" >li</a>c</span> <a href="https://www.jb51.cc/tag/sql/" target="_blank" >sql</a>SessionFactory <a href="https://www.jb51.cc/tag/sql/" target="_blank" >sql</a>SessionFactory() <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;throws</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt; Exception {    <a href="https://www.jb51.cc/tag/sql/" target="_blank" >sql</a>Session<a href="https://www.jb51.cc/tag/factorybean/" target="_blank" >factorybean</a> <a href="https://www.jb51.cc/tag/sql/" target="_blank" >sql</a>SessionFactory </span>= <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;new</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt; <a href="https://www.jb51.cc/tag/sql/" target="_blank" >sql</a>Session<a href="https://www.jb51.cc/tag/factorybean/" target="_blank" >factorybean</a>();    ResourcePatternRes<a href="https://m.jb51.cc/tag/ol/" target="_blank" >ol</a>ver res<a href="https://m.jb51.cc/tag/ol/" target="_blank" >ol</a>ver </span>= <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;new</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt; PathMatchingResourcePatternRes<a href="https://m.jb51.cc/tag/ol/" target="_blank" >ol</a>ver();    Resource[] mapperXmlResource </span>=<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt; res<a href="https://m.jb51.cc/tag/ol/" target="_blank" >ol</a>ver.getResources(SCAN_XML_MAPPER_LOCATION);    <a href="https://www.jb51.cc/tag/sql/" target="_blank" >sql</a>SessionFactory.setDataSource(dru<a href="https://m.jb51.cc/tag/ID/" target="_blank" >ID</a>DataSource());    <a href="https://www.jb51.cc/tag/sql/" target="_blank" >sql</a>SessionFactory.setMapperLocations(mapperXmlResource);    </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;return</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000"&gt; <a href="https://www.jb51.cc/tag/sql/" target="_blank" >sql</a>SessionFactory.g<a href="https://www.jb51.cc/tag/eto/" target="_blank" >eto</a>bject();}

}

　　然后，将需要分离的表 *** 作转移到相应的包路径下，即可实现多数据源 *** 作了！

classpath:mybatis/mappers/MysqL/main/**/*Mapper.xml

<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #008000"&gt;<!--</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #008000"&gt; 第二个数据源的配置 </span><span &gt;--></span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;<</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #800000"&gt;bean </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000"&gt;<a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a></span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;="dataSourceExt"</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000"&gt; class</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;="com.a<a href="https://m.jb51.cc/tag/li/" target="_blank" >li</a>baba.dru<a href="https://m.jb51.cc/tag/ID/" target="_blank" >ID</a>.po<a href="https://m.jb51.cc/tag/ol/" target="_blank" >ol</a>.Dru<a href="https://m.jb51.cc/tag/ID/" target="_blank" >ID</a>DataSource"</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000"&gt; init-method</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;="init"</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000"&gt; destroy-method</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;="close"</span><span &gt;></span>    <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;<</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #800000"&gt;property </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000"&gt;<a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a></span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;="url"</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000"&gt; value</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;="${jdbc.ext.url}"</span> <span &gt;/></span>    <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;<</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #800000"&gt;property </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000"&gt;<a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a></span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;="user<a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a>"</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000"&gt; value</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;="${jdbc.ext.user<a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a>}"</span> <span &gt;/></span>    <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;<</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #800000"&gt;property </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000"&gt;<a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a></span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;="password"</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000"&gt; value</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;="${jdbc.ext.password}"</span> <span &gt;/></span>    <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;<</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #800000"&gt;property </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000"&gt;<a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a></span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;="maxActive"</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000"&gt; value</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;="${jdbc.ext.maxActive}"</span> <span &gt;/></span>    <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;<</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #800000"&gt;property </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000"&gt;<a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a></span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;="maxWait"</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000"&gt; value</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;="${jdbc.ext.maxWait}"</span> <span &gt;/></span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;</</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #800000"&gt;bean</span><span &gt;></span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;<</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #800000"&gt;bean </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000"&gt;<a href="https://m.jb51.cc/tag/ID/" target="_blank" >ID</a></span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;="<a href="https://www.jb51.cc/tag/sql/" target="_blank" >sql</a>SessionFactoryExt"</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000"&gt; class</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;="org.mybatis.spring.<a href="https://www.jb51.cc/tag/sql/" target="_blank" >sql</a>Session<a href="https://www.jb51.cc/tag/factorybean/" target="_blank" >factorybean</a>"</span><span &gt;></span>  <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;<</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #800000"&gt;property </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000"&gt;<a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a></span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;="dataSource"</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000"&gt; ref</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;="dataSourceExt"</span> <span &gt;/></span>  <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;<</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #800000"&gt;property </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000"&gt;<a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a></span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;="con<a href="https://www.jb51.cc/tag/fig/" target="_blank" >fig</a>Location"</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000"&gt; value</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;="cla<a href="https://www.jb51.cc/tag/ssp/" target="_blank" >ssp</a>ath:con<a href="https://www.jb51.cc/tag/fig/" target="_blank" >fig</a>/mybatis-con<a href="https://www.jb51.cc/tag/fig/" target="_blank" >fig</a>.xml"</span> <span &gt;/></span>  <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;<</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #800000"&gt;property </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000"&gt;<a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a></span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;="mapperLocations"</span><span &gt;></span>      <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;<</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #800000"&gt;<a href="https://m.jb51.cc/tag/List/" target="_blank" >List</a></span><span &gt;></span>        <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;<</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #800000"&gt;value</span><span &gt;></span>cla<a href="https://www.jb51.cc/tag/ssp/" target="_blank" >ssp</a>ath:mybatis/mappers/<a href="https://www.jb51.cc/tag/MysqL/" target="_blank" >MysqL</a>/ext/**/*Mapper.xml<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;</</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #800000"&gt;value</span><span &gt;></span>      <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;</</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #800000"&gt;<a href="https://m.jb51.cc/tag/List/" target="_blank" >List</a></span><span &gt;></span>  <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;</</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #800000"&gt;property</span><span &gt;></span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;</</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #800000"&gt;bean</span><span &gt;></span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;<</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #800000"&gt;bean </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000"&gt;<a href="https://m.jb51.cc/tag/ID/" target="_blank" >ID</a></span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;="transactionManagerExt"</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000"&gt; class</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;="org.springframework.jdbc.datasource.DataSourceTransactionManager"</span><span &gt;></span>    <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;<</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #800000"&gt;property </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000"&gt;<a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a></span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;="dataSource"</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000"&gt; ref</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;="dataSourceExt"</span> <span &gt;/></span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;</</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #800000"&gt;bean</span><span &gt;></span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;<</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #800000"&gt;tx:annotation-driven </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000"&gt;transaction-manager</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;="transactionManagerExt"</span> <span &gt;/></span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;<</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #800000"&gt;bean </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000"&gt;class</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;="org.mybatis.spring.mapper.MapperScannerCon<a href="https://www.jb51.cc/tag/fig/" target="_blank" >fig</a>urer"</span><span &gt;></span>    <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;<</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #800000"&gt;property </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000"&gt;<a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a></span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;="<a href="https://www.jb51.cc/tag/sql/" target="_blank" >sql</a>Session<a href="https://www.jb51.cc/tag/factorybean/" target="_blank" >factorybean</a><a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a>"</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000"&gt; value</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;="<a href="https://www.jb51.cc/tag/sql/" target="_blank" >sql</a>SessionFactoryExt"</span><span &gt;/></span>    <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;<</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #800000"&gt;property </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000"&gt;<a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a></span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;="basePackage"</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000"&gt; value</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;="com.xxx.dao.<a href="https://m.jb51.cc/tag/auto/" target="_blank" >auto</a>mapper.ext"</span><span &gt;/></span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff"&gt;</</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #800000"&gt;bean</span><span &gt;></span></pre>

　　所以，还是那句话：不是所有听起来好的东西就一定是好，在这里转换为不是所有听起来方便的东西用起来就一定方便！

　　如果是自行搭建的MysqL服务，我们很自然地考虑使用binlog同步（主从）来做！具体配置方法也不复杂，自行查找资料即可！

　　如果使用阿里云服务，则不是binlog那样的配置了（但其实质仍然是对binlog的订阅写）。不过倒也都是页面 *** 作！（网上不一定好找资料，但是官网上一定是最全的）

要进行数据库迁移，大体步骤分为：

DTS服务（数据传输服务），专门用于做数据迁移和数据同步！

　　其打开方式为：

　　1. 自然是花钱买服务了，买好后才能进入 *** 作页面；这里服务要分两个：一是新实例rds数据库，二是新实例同步回原实例的同步服务；

　　2. 设置ip白名单，以使MysqL可访问；

　　3. 创建高权限用户，如root，以使后续可高权限 *** 作MysqL，同步时可使用该账号或者另用一个普通读写账号；

　　4. 将全量数据刷入新实例，这里可以选择阿里云免费的数据库迁移服务，也可以自己将数据dump下来，然后自行导致新库；（不过服务既然是免费的，那为啥不用呢！）

　　5. 设置单向同步，从新实例到原实例，此时是不会有数据同步的，因为没有新写入；

　　6. 数据刷入，同步设置完成后，就可以发布新代码了，此时最好将前端入口停止，否则可能出现数据错乱问题；

　　7. 发布代码后，需要自行验证。此时，先选择一台机器进行验证，可以选择两种方式验证：一是自行调用关键接口进行验证；二是将该机器绑定eip外网，使用该外网进行页面访问验证（更完整的验证）；验证的方向主要有两个：1. 接口正常响应，没有错误发生（此处应该要有监控设施，否则只能凭感觉）；2. 数据有没有正常同步（一般同步都是秒级的）；

　　8. 将代码发布到集群中，观察各机器运行情况！此处主要查看数据库连接情况，是否存在连接失败情况，应用监控是主要手段，也可以通过MysqL的show full processList; 进行查看应用连接db情况；

　　9. 观察正常后，此时可以将前端应用入口打开，此时如有条件，应限制ip访问，使变更进行充分测试无误；

　　10. 一切无误后，完全开放访问服务；监控用户数据，迁移完成；

至此，整个迁移就完成了，其实思路是很简单的，关键是要小心 *** 作。一个不小心的 *** 作，就可能带来很大的隐患，毕竟，数据无小事！请保持对数据和代码的敬畏！

临了临了，附几个 *** 作的贴心小技巧，避免入坑！

　　1. 买rds数据库时，尽量买与原实例相同的区域（大区和可用区都相同），否则后期在做同步的时候会花更多的钱，因为跨区的网络通信会让你支付更多；

　　2. 新实例数据库容量可以稍微降配以节省钱，因为毕竟你是将原来的部分功能拆分出来的，没必要一开始就为全部将来买单；

　　3. 买同步服务时，注意几点：1. 按量计算（按小时）比预付费（包月）更贵，但是也更容易订制化，如果仅仅 *** 作两个rds间同步，且短时间内不会下线服务，则建议选择预付费包月形式；2. 将区域选择正确，比如同区域同步将更便宜；3. 能单向同步就不要双向同步了，便宜的同时，也减小了误 *** 作带来的影响；4. 同步性能一般小流量选择small即可，高配的同步用不上关键是贵；

　　4. 同步服务尽早开启，但是后期对于账号密码的变更，一定要及时更改同步配置，否则将带来数据一致性问题；（人工发现往往较晚，尽量设置监控报警）

　　5. 数据库白名单中，需要加入阿里云数据传输服务的白名单，否则无法检查数据库响应性及同步作业；

　　6. 选择同步对象时，尽量以库作为单位！如果选择以表为同步单位，将存在后续新增表时，不会同步回原实例情况。如果实在不能以库作为单位，在后续迭代时，一定记得添加此处同步表；（关注点太多，麻烦）

　　7. 后期做数据变更时，注意 *** 作对象所属实例，别一顿 *** 作猛如虎，然后没什么卵用，因为我们只是选择了单向同步；

　　8. 自己可以不定期地做checksum检查，以确认同步功能正常工作；（checksum table test）

　　9. 代码上分库一定要做准确了，因为这里可能是一定时间内的唯一可信参考资料；（简单但是关键）

最后，我还想说下使用别人服务和自己动手的一些个人感觉：

　　1. 使用自己搭建的服务，最大的好处在于可以做任意的改变不受限，而且不需要付出额外的可见费用；

　　2. 使用自己的服务的可能坏处是：如果你不是这方面的专家，往往会被自己埋下的各种坑难住；遇到问题没能力处理；考虑方面不周全，容易引发安全问题；对未来的因素没办法考虑，使后期运作困难；如果你是专家，那多半这些都不是事儿；

　　3. 使用别人的服务，最大的好处就是简单易用，且有人维护；这些服务往往都是一路填坑过来的，时间越久往往越可靠（百年老字号最佳，哈哈）；安全性、扩展性、性能调优、高可用等等；

　　4. 使用别人的服务，其坏处主要是钱的问题，这个自不必说。还有个不是钱的问题的坏处，那就是你不能随意订制你想要的功能了，你的能力被别人限制住了，这个可能促使你转场到自己提供服务；另外，各家提供的服务都不一样，不像自己搭建的服务，网上会有各种资料可查，所以有一定的学习成本，具体取决于官方设计与官方文档的完整性（当然一般都会很简单）；其实还有一个，就不说了，懂的都懂；

好了，借着数据库迁移的小事，扯了这些淡。只当是抛砖引玉了！欢迎指教！

总结

以上是内存溢出为你收集整理的阿里云rds数据库迁移实战全部内容，希望文章能够帮你解决阿里云rds数据库迁移实战所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。

欢迎分享，转载请注明来源：内存溢出

原文地址: http://outofmemory.cn/sjk/1169103.html

阿里云rds数据库迁移实战

发表评论

评论列表（0条）