SpringBoot 实现mysql数据库读写分离

た入场券 2023-10-08 12:51 17阅读 0赞

#### 文章目录 ####

*  写在前面
 *   *  1、mysql 搭建一主多从数据库集群
     *   *  1.1、主库配置
         *  1.2、从库配置
         *  1.3、遇到的问题
     *  2、代码层面进行读写分离
     *   *  2.1、搭建springboot环境
         *  2.1、配置多个数据源
         *  2.3、测试

## 写在前面 ##

> 本文用的linux是centos8，其他linux可能部分指令不一样

适用工作场景：当数据量比较大时，通常写入数据速度是比较慢的，这时我们利用多台服务器进行读写分离，加快数据查询速度。

下面采用两个虚拟机（linux）模拟2个主从数据库

*  vmware下载地址：[https://www.aiweibk.com/203582.html][https_www.aiweibk.com_203582.html]
 *  centos 8 下载地址：[http://mirrors.aliyun.com/centos/8.4.2105/isos/x86\_64/CentOS-8.4.2105-x86\_64-boot.iso][http_mirrors.aliyun.com_centos_8.4.2105_isos_x86_64_CentOS-8.4.2105-x86_64-boot.iso]

两个虚拟机是克隆出来的，所以数据库的uuid相同，需要需改一下：

*  如何克隆一台虚拟机：[https://blog.csdn.net/fu123123fu/article/details/80951109][https_blog.csdn.net_fu123123fu_article_details_80951109]
 *  linux中Mysql修改server uuid：[https://blog.csdn.net/pratise/article/details/80413198][https_blog.csdn.net_pratise_article_details_80413198]

### 1、mysql 搭建一主多从数据库集群 ###

#### 1.1、主库配置 ####

先查看mysql的3306端口开放没有

[root@localhost ~]# sudo netstat -aptn
    # 或者直接关闭防火墙
    [root@localhost ~]# systemctl stop firewalld

接下来新建用户uuu，并分配权限：

mysql> CREATE USER 'uuu'@'%' IDENTIFIED BY '123456';
    mysql> GRANT REPLICATION SLAVE ON *.* TO 'uuu'@'%';
    mysql> flush privileges;                                        #刷新权限

修改 mysql 配置文件my.cnf，开启二进制日志：

[root@localhost ~]# cd /etc
    [root@localhost etc]# ls
    [root@localhost etc]# vim /etc/my.cnf
    
    # 下面是my.cnf中添加的内容
    [mysqld]
    server-id=1
    log-bin=master-bin
    log-bin-index=master-bin.index
    binlog-do-db=iii
    
    
    
    # 保存后重启mysql服务
    [root@localhost etc]# service mysqld restart     
    [root@localhost etc]# mysql -uroot -p                        # 进入mysql，查看状态
    mysql> show master status;                                   # 将查询得到的File 、Position这两个值记录下来，从库配置的时候需要填写

> 注意：  
> 1、每次重启MySQL，File 、Position这两个值都可能改变  
> 2、在my.cnf中添加的内容里面最后一句：binlog-do-db=iii，表示只同步iii这个库。并且iii这个库不能在设置主从同步之前创建。限定仅配置哪些表的语句如下：
> 
> > 不同步哪些数据库 （下面这些写在server-id=1下面即可）  
> > binlog-ignore-db = mysql  
> > binlog-ignore-db = test  
> > binlog-ignore-db = information\_schema  
> > 只同步哪些数据库，除此之外，其他不同步  
> > binlog-do-db = iii

#### 1.2、从库配置 ####

[root@localhost ~]# vim /etc/my.cnf                   # 修改 mysql 配置文件开启二进制日志
     
    # 新增部分如下：
    [mysqld]
    server-id=2
    relay-log-index=slave-relay-bin.index
    relay-log=slave-relay-bin
    
    
    [root@localhost ~]# service mysqld restart             # 重启mysql
    [root@localhost ~]# mysql -uroot -p
    mysql> SHOW GLOBAL VARIABLES like 'server\_id';        # 查看server-id=2有没有配置成功
    mysql> CHANGE MASTER TO
             MASTER_HOST='192.168.10.1',
             MASTER_USER='root',
             MASTER_PASSWORD='root',
             MASTER_LOG_FILE='master-bin.000001',
             MASTER_LOG_POS=120;
    mysql> start slave;                                    # 开启备份
    mysql> show slave status\G                             # 查看状态

查看状态Slave\_IO\_Running 和 Slave\_SQL\_Running 都为 yes表示正在同步。  
![在这里插入图片描述][20210608151356996.png]  
配置完成之后，在主库里面建库、建表、插入数据，从库里面也会有对应的数据，注意不要往主从配置之前已经存在的库中加数据，否则会报错。  
其他相关指令：

mysql> stop slave;                        # 停止主从备份
    mysql> reset slave all;                   # 删除之前执行的语句

#### 1.3、遇到的问题 ####

问题1：如果Slave\_IO\_Running为Connecting，可能的原因有下面几个：

1.网络不通
    2.账户密码错误
    3.防火墙
    4.mysql配置文件问题
    5.连接服务器时语法
    6.主服务器mysql权限

其他问题：

> 参考文章：https://blog.csdn.net/weixin\_39744606/article/details/113397916  
> 参考文章：https://blog.csdn.net/weixin\_39716971/article/details/113592178

### 2、代码层面进行读写分离 ###

代码环境： springboot+mybatis+druib 连接池

> 如果springboot项目访问不了数据库，考虑一下是不是linux防火墙的问题，可以先把防火墙关闭了

项目git仓库地址：[https://gitee.com/YH0100/springTO2mysql][https_gitee.com_YH0100_springTO2mysql]

#### 2.1、搭建springboot环境 ####

> 新建项目之前保证已建库test、建表user(id,name)

先新建一个springboot项目，并连接数据库获取数据  
![在这里插入图片描述][20210609163003414.png]  
application.yaml

# 应用名称
    spring.application.name: mysql2demo
    server.port: 8080
    
    #MyBatis
    mybatis:
      mapper-locations: classpath:mappers/*.xml
      type-aliases-package: com.example.mysql2demo.entity
    
    spring:
      datasource:
         driver-class-name: com.mysql.cj.jdbc.Driver
         url: jdbc:mysql://192.168.10.129:3306/test?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC
         username: root
         password: Lalala123!

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
    <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
        <modelVersion>4.0.0</modelVersion>
        <groupId>com.example</groupId>
        <artifactId>mysql2demo</artifactId>
        <version>0.0.1-SNAPSHOT</version>
        <name>mysql2demo</name>
        <description>Demo project for Spring Boot</description>
    
        <properties>
            <java.version>1.8</java.version>
            <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
            <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
            <spring-boot.version>2.3.7.RELEASE</spring-boot.version>
        </properties>
        <dependencies>
            
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-aop</artifactId>
            </dependency>
    
            
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-jdbc</artifactId>
            </dependency>
    
            
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
            </dependency>
    
            
            <dependency>
                <groupId>org.mybatis.spring.boot</groupId>
                <artifactId>mybatis-spring-boot-starter</artifactId>
                <version>2.1.3</version>
            </dependency>
    
            
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
            </dependency>
    
            
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-test</artifactId>
                <scope>test</scope>
                <exclusions>
                    <exclusion>
                        <groupId>org.junit.vintage</groupId>
                        <artifactId>junit-vintage-engine</artifactId>
                    </exclusion>
                </exclusions>
            </dependency>
        </dependencies>
    
    
        <dependencyManagement>
            <dependencies>
                <dependency>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-dependencies</artifactId>
                    <version>${spring-boot.version}</version>
                    <type>pom</type>
                    <scope>import</scope>
                </dependency>
            </dependencies>
        </dependencyManagement>
    
        <build>
            <plugins>
                <plugin>
                    <groupId>org.apache.maven.plugins</groupId>
                    <artifactId>maven-compiler-plugin</artifactId>
                    <version>3.8.1</version>
                    <configuration>
                        <source>1.8</source>
                        <target>1.8</target>
                        <encoding>UTF-8</encoding>
                    </configuration>
                </plugin>
                <plugin>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-maven-plugin</artifactId>
                    <version>2.3.7.RELEASE</version>
                    <configuration>
                        <mainClass>com.example.mysql2demo.Mysql2demoApplication</mainClass>
                    </configuration>
                    <executions>
                        <execution>
                            <id>repackage</id>
                            <goals>
                                <goal>repackage</goal>
                            </goals>
                        </execution>
                    </executions>
                </plugin>
            </plugins>
        </build>
    
    </project>

UserMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE mapper
            PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
            "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    <mapper namespace="com.example.mysql2demo.dao.UserDao">
        <insert id="save" parameterType="com.example.mysql2demo.entity.User">
            insert into user(id,name)
            values (#{id},#{name})
        </insert>
        <select id="getById" resultType="com.example.mysql2demo.entity.User" parameterType="int">
            select * from user where id=#{id};
        </select>
    </mapper>

Mysql2demoApplication.java

import org.mybatis.spring.annotation.MapperScan;
    import org.springframework.boot.SpringApplication;
    import org.springframework.boot.autoconfigure.SpringBootApplication;
    
    @SpringBootApplication
    @MapperScan(basePackages = {
        "com.example.mysql2demo.dao"})
    public class Mysql2demoApplication {
        
    
        public static void main(String[] args) {
        
            SpringApplication.run(Mysql2demoApplication.class, args);
        }
    
    }

User.java

public class User {
        
        private int id;
        private String name;
    
        public int getId() {
        
            return id;
        }
    
        public void setId(int id) {
        
            this.id = id;
        }
    
        public String getName() {
        
            return name;
        }
    
        public void setName(String name) {
        
            this.name = name;
        }
    }

UserDao.java

import com.example.mysql2demo.entity.User;
    import org.springframework.stereotype.Repository;
    
    @Repository
    public interface UserDao {
        
        public User getById(int id);
        public void save(User user);
    }

UserService.java

import com.example.mysql2demo.entity.User;
    
    public interface UserService {
        
        public User getById(int id);
        public void save(User user);
    }

UserServiceImpl.java

import com.example.mysql2demo.dao.UserDao;
    import com.example.mysql2demo.entity.User;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    
    @Service
    public class UserServiceImpl implements UserService {
        
        @Autowired
        private UserDao userDao;
    
        @Override
        public User getById(int id) {
        
            return userDao.getById(id);
        }
    
    
        @Override
        public void save(User user) {
        
            userDao.save(user);
        }
    }

UserController.java

import com.example.mysql2demo.entity.User;
    import com.example.mysql2demo.service.UserService;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.PathVariable;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RestController;
    
    @RestController
    @RequestMapping("/user")
    public class UserController {
        
        @Autowired
        private UserService userService;
    
        @RequestMapping("/get/{id}")
        public User queryById(@PathVariable("id") int id){
        
            return userService.getById(id);
        }
    
        @RequestMapping("/save")
        public void addDept(User user){
        
            userService.save(user);
        }
    }

#### 2.1、配置多个数据源 ####

> 如何切换数据源：spring 支持多数据源，多个 datasource 放在一个 HashMapTargetDataSource中，通过dertermineCurrentLookupKey 来决定要使用哪个数据源。

下面是项目目录结构  
![在这里插入图片描述][20210609164621422.png]  
第一步：修改yaml配置文件，写入多个数据源配置  
application.yml

spring:
      datasource:
        master:
          driver-class-name: com.mysql.cj.jdbc.Driver
          jdbc-url: jdbc:mysql://192.168.10.129:3306/test?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC
          username: root
          password: Lalala123!
        slave1:
          driver-class-name: com.mysql.cj.jdbc.Driver
          jdbc-url: jdbc:mysql://192.168.10.128:3306/test?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC
          username: root
          password: Lalala123!
        slave2:
          driver-class-name: com.mysql.cj.jdbc.Driver
          jdbc-url: jdbc:mysql://192.168.10.128:3306/test?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC
          username: root
          password: Lalala123!
    
    #MyBatis
    mybatis:
      mapper-locations: classpath:mappers/*.xml
      type-aliases-package: com.example.mysql2demo.entity

第二步：从yaml中获取数据源配置，返回DataSource  
DBTypeEnum.java

/**
     * 定义一个枚举来代表这三个数据源
     */
    
    public enum DBTypeEnum {
        
        MASTER, SLAVE1, SLAVE2;
    }

DataSourceConfig.java

import org.springframework.beans.factory.annotation.Qualifier;
    import org.springframework.boot.context.properties.ConfigurationProperties;
    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.HashMap;
    import java.util.Map;
    
    /**
     * 关于数据源配置，参考SpringBoot官方文档第79章《Data Access》
     * 79. Data Access
     * 79.1 Configure a Custom DataSource
     * 79.2 Configure Two DataSources
     * 这里配置了4个数据源，1个master，2两个slave，1个路由数据源。前3个数据源都是为了生成第4个数据源，而且后续我们只用这最后一个路由数据源。
     */
    
    @Configuration
    public class DataSourceConfig {
        
    
        @Bean
        @ConfigurationProperties("spring.datasource.master")
        public DataSource masterDataSource() {
        
            return DataSourceBuilder.create().build();
        }
    
        @Bean
        @ConfigurationProperties("spring.datasource.slave1")
        public DataSource slave1DataSource() {
        
            return DataSourceBuilder.create().build();
        }
    
        @Bean
        @ConfigurationProperties("spring.datasource.slave2")
        public DataSource slave2DataSource() {
        
            return DataSourceBuilder.create().build();
        }
    
        @Bean
        public DataSource myRoutingDataSource(@Qualifier("masterDataSource") DataSource masterDataSource,
                                              @Qualifier("slave1DataSource") DataSource slave1DataSource,
                                              @Qualifier("slave2DataSource") DataSource slave2DataSource) {
        
            Map<Object, Object> targetDataSources = new HashMap<>();
            targetDataSources.put(DBTypeEnum.MASTER, masterDataSource);
            targetDataSources.put(DBTypeEnum.SLAVE1, slave1DataSource);
            targetDataSources.put(DBTypeEnum.SLAVE2, slave2DataSource);
            MyRoutingDataSource myRoutingDataSource = new MyRoutingDataSource();
            myRoutingDataSource.setDefaultTargetDataSource(masterDataSource);
            myRoutingDataSource.setTargetDataSources(targetDataSources);
            return myRoutingDataSource;
        }
    
    }

第三步：mybatis配置类（把数据源的选择写成我们自己的类myRoutingDataSource）  
myRoutingDataSource.java

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
    import org.springframework.lang.Nullable;
    
    /**
     * 获取路由key
     */
    public class MyRoutingDataSource extends AbstractRoutingDataSource {
        
        @Nullable
        @Override
        protected Object determineCurrentLookupKey() {
        
            return DBContextHolder.get();
        }
    }

MyBatisConfig.java

import org.apache.ibatis.session.SqlSessionFactory;
    import org.mybatis.spring.SqlSessionFactoryBean;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
    import org.springframework.jdbc.datasource.DataSourceTransactionManager;
    import org.springframework.transaction.PlatformTransactionManager;
    import org.springframework.transaction.annotation.EnableTransactionManagement;
    
    import javax.annotation.Resource;
    import javax.sql.DataSource;
    
    /**
     * 由于Spring容器中现在有4个数据源，所以我们需要为事务管理器和MyBatis手动指定一个明确的数据源。
     */
    @EnableTransactionManagement
    @Configuration
    public class MyBatisConfig {
        
    
        @Resource(name = "myRoutingDataSource")
        private DataSource myRoutingDataSource;
    
        @Bean
        public SqlSessionFactory sqlSessionFactory() throws Exception {
        
            SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
            sqlSessionFactoryBean.setDataSource(myRoutingDataSource);
            sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mappers/*.xml"));
            return sqlSessionFactoryBean.getObject();
        }
    
        @Bean
        public PlatformTransactionManager platformTransactionManager() {
        
            return new DataSourceTransactionManager(myRoutingDataSource);
        }
    }

第四步：使用AOP拦截对应service方法名，去选择对应的数据源

先通过ThreadLocal将数据源设置到每个线程上下文中  
DBContextHolder.java

import java.util.concurrent.atomic.AtomicInteger;
    
    /**
     * 通过ThreadLocal将数据源设置到每个线程上下文中
     */
    public class DBContextHolder {
        
    
        private static final ThreadLocal<DBTypeEnum> contextHolder = new ThreadLocal<>();
    
        private static final AtomicInteger counter = new AtomicInteger(-1);
    
        public static void set(DBTypeEnum dbType) {
        
            contextHolder.set(dbType);
        }
    
        public static DBTypeEnum get() {
        
            return contextHolder.get();
        }
    
        public static void master() {
        
            set(DBTypeEnum.MASTER);
            System.out.println("切换到master");
        }
    
        public static void slave() {
        
            System.out.println("正在选择使用哪个读库");
            //  轮询
            int index = counter.getAndIncrement() % 2;
            if (counter.get() > 9999) {
        
                counter.set(-1);
            }
            if (index == 0) {
        
                set(DBTypeEnum.SLAVE1);
                System.out.println("切换到slave1");
            }else {
        
                set(DBTypeEnum.SLAVE2);
                System.out.println("切换到slave2");
            }
        }
    
    }

再使用aop拦截service实现类的方法  
DataSourceAop.java

import org.aspectj.lang.annotation.Aspect;
    import org.aspectj.lang.annotation.Before;
    import org.aspectj.lang.annotation.Pointcut;
    import org.springframework.stereotype.Component;
    
    /**
     * 默认情况下，所有的查询都走从库，插入/修改/删除走主库。我们通过方法名来区分操作类型（CRUD）
     */
    @Aspect
    @Component
    public class DataSourceAop {
        
        // 拦截Master、select开头的方法、find开头的方法、get...
        @Pointcut("!@annotation(com.example.mysql2demo.annotation.Master) " +
                "&& (execution(* com.example.mysql2demo.service.*.select*(..)) " +
                "|| execution(* com.example.mysql2demo.service..*.find*(..))" +
                "|| execution(* com.example.mysql2demo.service..*.get*(..)))")
        public void readPointcut() {
        
    
        }
    
        // 拦截Master、save、add...等开头的方法
        @Pointcut("@annotation(com.example.mysql2demo.annotation.Master) " +
                "|| execution(* com.example.mysql2demo.service..*.save*(..)) " +
                "|| execution(* com.example.mysql2demo.service..*.add*(..)) " +
                "|| execution(* com.example.mysql2demo.service..*.update*(..)) " +
                "|| execution(* com.example.mysql2demo.service..*.edit*(..)) " +
                "|| execution(* com.example.mysql2demo..*.delete*(..)) " +
                "|| execution(* com.example.mysql2demo..*.remove*(..))")
        public void writePointcut() {
        
    
        }
    
        @Before("readPointcut()")
        public void read() {
        
            System.out.println("读操作");
            DBContextHolder.slave();
        }
    
        @Before("writePointcut()")
        public void write() {
        
            System.out.println("写操作");
            DBContextHolder.master();
        }
    
    
        /**
         * 另一种写法：if...else...  判断哪些需要读从数据库，其余的走主数据库
         */
    //    @Before("execution(* com.cjs.example.service.impl.*.*(..))")
    //    public void before(JoinPoint jp) {
        
    //        String methodName = jp.getSignature().getName();
    //
    //        if (StringUtils.startsWithAny(methodName, "get", "select", "find")) {
        
    //            DBContextHolder.slave();
    //        }else {
        
    //            DBContextHolder.master();
    //        }
    //    }
    
    
    
    
    // aop相关知识：
    //    在Spring AOP中，有3个常用的概念，Advices、Pointcut、Advisor，解释如下，
    //
    //    Advices：表示一个method执行前或执行后的动作。
    //
    //    Pointcut：表示根据method的名字或者正则表达式去拦截一个method。
    //
    //    Advisor：Advice和Pointcut组成的独立的单元，并且能够传给proxy factory 对象。
    }

在service实现类上使用@Master注解，表示使用主库  
![在这里插入图片描述][20210609171432981.png]

#### 2.3、测试 ####

> 需要注意的是所有能拦截的方法都在DataSourceAop里面拦截，所以方法名不对不会拦截，会使用默认数据源

启动项目，访问  
![在这里插入图片描述][20210609170941328.png]  
![在这里插入图片描述][20210609171033908.png]  
后台  
![在这里插入图片描述][20210609171112694.png]

[https_www.aiweibk.com_203582.html]: https://www.aiweibk.com/203582.html
[http_mirrors.aliyun.com_centos_8.4.2105_isos_x86_64_CentOS-8.4.2105-x86_64-boot.iso]: http://mirrors.aliyun.com/centos/7/isos/x86_64/
[https_blog.csdn.net_fu123123fu_article_details_80951109]: https://blog.csdn.net/fu123123fu/article/details/80951109
[https_blog.csdn.net_pratise_article_details_80413198]: https://blog.csdn.net/pratise/article/details/80413198
[20210608151356996.png]: https://img-blog.csdnimg.cn/20210608151356996.png
[https_gitee.com_YH0100_springTO2mysql]: https://gitee.com/YH0100/springTO2mysql
[20210609163003414.png]: https://img-blog.csdnimg.cn/20210609163003414.png
[20210609164621422.png]: https://img-blog.csdnimg.cn/20210609164621422.png
[20210609171432981.png]: https://img-blog.csdnimg.cn/20210609171432981.png
[20210609170941328.png]: https://img-blog.csdnimg.cn/20210609170941328.png
[20210609171033908.png]: https://img-blog.csdnimg.cn/20210609171033908.png
[20210609171112694.png]: https://img-blog.csdnimg.cn/20210609171112694.png