SpringBoot realizes the separation of reading and writing of mysql database

Article Directory

Write in front

The linux used in this article is centos8, other linux may have different instructions

Applicable working scenarios: When the amount of data is relatively large, the speed of writing data is usually relatively slow. At this time, we use multiple servers to separate read and write to speed up data query.

The following uses two virtual machines (linux) to simulate two master-slave databases

The two virtual machines are cloned, so the uuid of the database is the same, you need to change it:

1. mysql builds a master and multiple slave database cluster

1.1, main library configuration

First check if port 3306 of mysql is open

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

Next, create a new user uuu and assign permissions:

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

Modify the mysql configuration file my.cnf and turn on the binary log:

[[email protected] ~]# cd /etc
[[email protected] etc]# ls
[[email protected] 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服务
[[email protected] etc]# service mysqld restart     
[[email protected] etc]# mysql -uroot -p                        # 进入mysql,查看状态
mysql> show master status;                                   # 将查询得到的File 、Position这两个值记录下来,从库配置的时候需要填写
Note:
1. Each time you restart MySQL, the two values ​​of File and Position may change.
2. The last sentence in the content added in my.cnf: binlog-do-db=iii, which means that only the iii library is synchronized. And iii this library cannot be created before setting up master-slave synchronization. The statement to limit which tables are configured is as follows:
Which databases are not to be synchronized (the following can be written under server-id=1)
binlog-ignore-db = mysql
binlog-ignore-db = test
binlog-ignore-db = information_schema
Which databases are only synchronized, other than that
Out of sync binlog-do-db = iii

1.2, configuration from the library

[[email protected] ~]# vim /etc/my.cnf                   # 修改 mysql 配置文件开启二进制日志
 
# 新增部分如下:
[mysqld]
server-id=2
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin


[[email protected] ~]# service mysqld restart             # 重启mysql
[[email protected] ~]# 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                             # 查看状态

View the status Slave_IO_Running and Slave_SQL_Running are both yes, indicating that it is synchronizing.

Insert picture description here


After the configuration is completed, build a database, create a table, and insert data in the main library. There will also be corresponding data in the slave library. Be careful not to add data to the library that already exists before the master-slave configuration, otherwise an error will be reported.
Other related instructions:

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

1.3. Problems encountered

Question 1: If Slave_IO_Running is Connecting, the possible reasons are as follows:

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

other problems:

Reference article: https://blog.csdn.net/weixin_39744606/article/details/113397916
Reference article: https://blog.csdn.net/weixin_39716971/article/details/113592178

2. Separation of reading and writing at the code level

Code environment: springboot+mybatis+druib connection pool

If the springboot project cannot access the database, consider whether it is a linux firewall problem, you can turn off the firewall first

Project git warehouse address: https://gitee.com/YH0100/springTO2mysql

2.1, build a springboot environment

Before creating a new project, ensure that the database test and the table user (id, name) have been built

Create a new springboot project first, and connect to the database to obtain data

Insert picture description here


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>
        <!--SpringBoot集成Aop起步依赖-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-aop</artifactId>
        </dependency>

        <!--SpringBoot集成Jdbc起步依赖-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>

        <!--SpringBoot集成WEB起步依赖-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <!--mybatis集成SpringBoot起步依赖-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.3</version>
        </dependency>

        <!--MySQL驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

        <!--SpringBoot单元测试依赖-->
        <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, configure multiple data sources

How to switch data sources: Spring supports multiple data sources. Multiple datasources are placed in a HashMapTargetDataSource, and the dertermineCurrentLookupKey is used to determine which data source to use.

The following is the project directory structure.

Insert picture description here


Step 1: Modify the yaml configuration file and write multiple data source configuration
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

Step 2: Get the data source configuration from yaml and return to 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;
    }

}

The third step: mybatis configuration class (write the choice of data source as our own class 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);
    }
}

Step 4: Use AOP to intercept the corresponding service method name to select the corresponding data source

First set the data source to each thread context through 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");
        }
    }

}

Then use aop to intercept the method of the service implementation class
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("[email protected](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 对象。
}

Use the @Master annotation on the service implementation class to indicate the use of the main library

Insert picture description here

2.3, test

It should be noted that all methods that can be intercepted are intercepted in DataSourceAop, so the method name will not be intercepted, and the default data source will be used

Start the project, visit the

Insert picture description here


Insert picture description here


background

Insert picture description here