SpringBoot通过JPA连接Mysql集群_springboot连接mysql集群

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

---

为什么要进行Mysql的主从复制

1.可以实现数据库的读写分离，提高对数据的访问效率

2.可以对数据库进行备份

---

一、Mysql主从复制配置

本文使用了一主一从的集群模式，对mysql数据库进行主从复制配置。
本文对于数据库的安装过程不再过多赘述，直接从数据库的配置写起。

1.配置主数据库

（1）首先进入到主数据库的配置文件中

vi /etc/my.cnf #进入到主MySQL的配置文件中
1

（2）添加配置

[mysqld]
skip-name-resolve#禁用DNS解析，避免网络DNS解析服务引发访问Mysql的错误

server_id=1 #配置集群中的id 此id一定要唯一
log-bin=mysql-bin#在对主mysql服务器进行操作时，生成的二进制日志，从服务器主要通过该日志进行数据的同步
read-only=0#可以对该数据库进行读写操作
binlog-do-db=db1#进行主从复制所用到的数据库

#主从复制忽略的数据库
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
1
2
3
4
5
6
7
8
9
10
11
12
13

(3)重启主数据库

systemctl restart mysqld
1

2.配置从数据库

对从数据库配置文件添加配置

[mysqld]
skip-name-resolve

server_id=2
log-bin=mysql-bin
read-only=1#配置成只读模式
binlog-do-db=db1

replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
1
2
3
4
5
6
7
8
9
10
11
12

然后重启从数据库

3.在主数据库上进行用户授权

进行用户授权

grant replication slave on *.* to 'rep'@'%' identified by '12345';#用户名rep和密码12345可以根据自己的需求来起
1

查看主数据库状态

show master status
1

4.在从数据库上进行连接

进行主数据库连接

change master to master_host='172.168.4.18',
master_user='rep',master_password='12345',
master_log_file='mysql-bin.000006',master_log_pos=15353,master_port=3306
1
2
3

启动从数据库

start slave
1

查看从数据库状态

show slave status
1

在从数据库中的状态中，如果Slave_IO_Running和Slave_SQL_Running同时为Yes，则证明主从复制集群配置成功。

二、SpringBoot通过JPA的方式连接使用Mysql集群

1.配置文件

application.yml

spring:
  datasource:   #多数据源配置
    master:
      jdbc-url: jdbc:mysql://ip:port/db1?useUnicode=true&characterEncoding=UTF-8&useSSL=false
      username: root
      password: 12345
      driverClassName: com.mysql.cj.jdbc.Driver
    slave:
      jdbc-url: jdbc:mysql://ip:port/db1?useUnicode=true&characterEncoding=UTF-8&useSSL=false
      username: root
      password: 12345
      driverClassName: com.mysql.cj.jdbc.Driver

  jpa:
    show-sql: true
    hibernate:
      naming:
        physical-strategy: org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

2.数据源对象创建

@Slf4j
@Configuration
public class DataSourceConfiguration {
    
    @Primary
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.master")
    public DataSource masterDataSource() {
        log.info("create master datasource...");
        return DataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.slave")
    public DataSource slaveDataSource() {
        log.info("create slave datasource...");
        return DataSourceBuilder.create().build();

    }

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

3.数据源路由配置

@Slf4j
public class DynamicDataSourceRouter extends AbstractRoutingDataSource {
    /***
     * 决定使用哪个数据源
     */
    @Override
    protected Object determineCurrentLookupKey() {
        return DataSourceContextHolder.getDataSource();
    }
}
1
2
3
4
5
6
7
8
9
10

4.数据源对象缓存

@Slf4j
public class DataSourceContextHolder {
    private static final ThreadLocal<String> holder = new ThreadLocal<>();

    public static void setDataSource(String type) {
        holder.set(type);
    }

    public static String getDataSource() {
        String lookUpKey = holder.get();
        return lookUpKey == null ? "masterDataSource" : lookUpKey;
    }

    public static void clear() {
        holder.remove();
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

5.多数据源配置

主要是配置EntityManagerFactory和PlatformTransactionManager

@Slf4j
@Configuration
@EnableJpaRepositories(value = "com.imooc.dao.repository", entityManagerFactoryRef = "entityManagerFactoryBean", transactionManagerRef = "transactionManagerBean")
public class JpaEntityManager {

    @Autowired
    private JpaProperties jpaProperties;

    @Autowired
    private HibernateProperties hibernateProperties;

    @Resource(name = "masterDataSource")
    private DataSource masterDataSource;

    @Resource(name = "slaveDataSource")
    private DataSource slaveDataSource;

    @Bean(name = "routingDataSource")
    public AbstractRoutingDataSource routingDataSource() {
        DynamicDataSourceRouter proxy = new DynamicDataSourceRouter();
        Map<Object, Object> targetDataSources = new HashMap<>(2);
        targetDataSources.put("masterDataSource", masterDataSource);
        targetDataSources.put("slaveDataSource", slaveDataSource);//需要将可能用到的数据源存储到targetDataSources
                                                                 //后续通过determineCurrentLookupKey()方法匹配出所需要的数据源
        proxy.setDefaultTargetDataSource(masterDataSource);
        proxy.setTargetDataSources(targetDataSources);
        return proxy;
    }

    @Bean(name = "entityManagerFactoryBean")
    public LocalContainerEntityManagerFactoryBean entityManagerFactoryBean(EntityManagerFactoryBuilder builder) {
        Map<String, String> properties = getVendorProperties();
        return builder
                .dataSource(routingDataSource())//注入routingDataSource
                .properties(properties)//注入属性 application.yml中的
                .packages("com.imooc.entity")
                .persistenceUnit("myPersistenceUnit")  //因为只构建一个EntityManagerFactory可以忽略该属性
                .build();
    }

    @Primary
    @Bean(name = "transactionManagerBean")
    public PlatformTransactionManager transactionManagerBean(EntityManagerFactoryBuilder builder) {
        return new JpaTransactionManager(entityManagerFactoryBean(builder).getObject());
    }

    private Map getVendorProperties() {
        return hibernateProperties.determineHibernateProperties(jpaProperties.getProperties(), new HibernateSettings());
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

6.配置AOP

@Slf4j
@Aspect
@Component
public class DynamicDataSourceAspect {

    @Pointcut("execution(* com.imooc.service..*.*(..))")
    private void aspect() {

    }

    @Around("aspect()")
    public Object around(ProceedingJoinPoint joinPoint) throws Throwable {
        String method = joinPoint.getSignature().getName();//获取方法名字

        if (method.startsWith("find") || method.startsWith("select") || method.startsWith("query") || method
                .startsWith("search")) {
            DataSourceContextHolder.setDataSource("slaveDataSource");
            log.info("switch to slave datasource...");
        } else {
            DataSourceContextHolder.setDataSource("masterDataSource");
            log.info("switch to master datasource...");
        }

        try {
            return joinPoint.proceed();
        }finally {
            log.info("清除 datasource router...");
            DataSourceContextHolder.clear();
        }

    }

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

7.在Service中的使用

@Service
public class UserService {

    @Autowired
    private UserRepository userRepository;


    public User addOne(User user) {
        return userRepository.save(user);
    }

    public User findById(Long userId) {

        if(userRepository.findById(userId).isPresent())
        return userRepository.findById(userId).get();
        else
            return null;
    }

    public List<User> findUsers(){
        return userRepository.getUser();
    }

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

8.代码目录结构