如何一台服务器上搭建两台mysql请参考一台服务器,两台mysql
<dependency>
<groupId>cn.zhangfusheng</groupId>
<artifactId>paoding-rose-jade</artifactId>
<version>1.0.3</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-dbcp2</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.18</version>
</dependency>
import lombok.Data;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;
import java.util.List;
@Data
@Component
@ConfigurationProperties(prefix = "server.datasource")
public class ServerDataSourceUrlProperties{
/**
* 写 库
*/
private List<String> slaveUrls;
/**
* 读 库
*/
private String masterUrl;
}
spring:
datasource:
dbcp2:
initial-size: 25 # 初始化连接数
min-idle: 5 # 最小空闲数
max-idle: 10 # 最大空闲树
max-total: 50
max-wait-millis: 5000
driver-class-name: com.mysql.cj.jdbc.Driver
username: 1111
password: 11111
type: org.apache.commons.dbcp2.BasicDataSource
url: ${server.datasource.master-url}
server:
datasource:
master-url: jdbc:mysql://211.159.174.169:3306/${spring.application.name}?useSSL=true&serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8
slave-urls:
- jdbc:mysql://211.159.174.169:3307/${spring.application.name}?useSSL=true&serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8
package cn.zhangfusheng.base.server.config;
import cn.zhangfusheng.base.server.annotation.bean.Autowired;
import cn.zhangfusheng.base.server.properties.ServerDataSourceUrlProperties;
import cn.zhangfusheng.util.base.collection.ArrayList;
import lombok.extern.slf4j.Slf4j;
import net.paoding.rose.jade.dataaccess.DataSourceFactory;
import net.paoding.rose.jade.dataaccess.datasource.MasterSlaveDataSourceFactory;
import net.paoding.rose.jade.dataaccess.datasource.RandomDataSourceFactory;
import net.paoding.rose.jade.dataaccess.datasource.SimpleDataSourceFactory;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import javax.sql.DataSource;
import java.util.List;
/**
* 数据库读写分离配置
* 使用spring 默认的 DataSource 为主数据源
* 使用自定义的数据库配置为 读数据源
* 注意: 读写数据源的账号密码必须一致,也可根据自己的情况修改配置类以及配置文件
*
* @author fusheng.zhang
* @Description
* @create 2020-03-16 09:59:00
*/
@Slf4j
@Configuration
public class DataSourceConfig {
/**
* 写 数据库配置
*/
@Autowired
private DataSourceProperties dataSourceProperties;
/**
* 写数据库
*/
@Autowired
private DataSource dataSource;
/**
* 读写 数据库配置
*/
@Autowired
private ServerDataSourceUrlProperties serverDataSourceUrlProperties;
private DataSource masterDataSource() {
log.info("加载 master data source ------> 写 数据库");
return dataSource;
}
private List<DataSource> getSlaveDataSource() {
log.info("加载 slave data source ------> 读 数据库");
ArrayList<DataSource> dataSources = new ArrayList<>(serverDataSourceUrlProperties.getSlaveUrls().size() + 1);
for (String slaveUrl : serverDataSourceUrlProperties.getSlaveUrls()) {
dataSources.add(DataSourceBuilder.create(dataSourceProperties.getClass().getClassLoader())
.type(dataSourceProperties.getType())
.driverClassName(dataSourceProperties.getDriverClassName())
.url(slaveUrl)
.username(dataSourceProperties.getUsername())
.password(dataSourceProperties.getPassword()).build());
}
return dataSources.add(dataSource).getList();
}
@Bean("jade.dataSourceFactory")
public DataSourceFactory dataSourceFactory() {
MasterSlaveDataSourceFactory mainFactory = new MasterSlaveDataSourceFactory();
DataSource master = masterDataSource();
mainFactory.setMasters(new SimpleDataSourceFactory(master));
mainFactory.setSlaves(new RandomDataSourceFactory(getSlaveDataSource()));
return mainFactory;
}
}
写数据源一定要用spring 的数据源,否则事物无法生效
读写分离,需要两台数据库之间实现主从复制
因数据库读写分离,会因为事物,产生读到的数据和写到的数据结果不一致