Mysql读写分离以及负载均衡

最新推荐文章于 2025-03-24 20:15:05 发布

weixin_53231767

最新推荐文章于 2025-03-24 20:15:05 发布

阅读量2.7k

点赞数 61

文章标签： mysql 负载均衡数据库

本文链接：https://blog.youkuaiyun.com/weixin_53231767/article/details/140281425

版权

读写分离以及负载均衡

目标：数据库更新操作（insert、update、delete等）全部在主库执行，查询操作在从库执行。

准备：提前搭建Mysql主从集群（一主两从），保证两个从库能正常从主库同步数据。

读写分离

通过mybatis拦截器拦截待执行的SQL，判断SQL类型，根据判断结果切换数据源，实现读写分离。

Mybatis拦截器的拦截点有4种，分别为Executor(执行器)、StatementHandler(sql语法构建处理器)、ParameterHandler(参数处理器)、ResultSetHandler(结果处理器)。可以在不同阶段拦截来完成自己的需求。本文的目标为手动切换数据源，所以可以在Executor处拦截。

1.引入依赖

       <!--spring-boot单元测试依赖-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!--mybatis-plus-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.3.1</version>
        </dependency>
        <!--多数据源依赖-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
            <version>3.2.1</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.46</version>
        </dependency>
        <!--lombok辅助-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.16.10</version>
        </dependency>

配置文件内容(application.yml),数据库信息替换为自己部署的即可。master和slave可自定义，为主库和从库的标识。

spring:
  datasource:
    # MySql
    dynamic:
      primary: master #设置默认的数据源或者数据源组,默认值即为master
      strict: false #设置严格模式,默认false不启动. 启动后在未匹配到指定数据源时候会抛出异常,不启动则使用默认数据源.
      datasource:
        master:
          driver-class-name: com.mysql.jdbc.Driver
          url: jdbc:mysql://192.168.19.7:13306/test_db?useUnicode=true&characterEncoding=utf-8&useSSL=false
          username: root
          password: 1234
        slave:
          driver-class-name: com.mysql.jdbc.Driver
          url: jdbc:mysql://192.168.19.8:13306/test_db?useUnicode=true&characterEncoding=utf-8&useSSL=false
          username: root
          password: 1234
      druid:
        initial-size: 10
        max-active: 100
        min-idle: 10
        max-wait: 60000
        pool-prepared-statements: true
        max-pool-prepared-statement-per-connection-size: 20
        time-between-eviction-runs-millis: 60000
        min-evictable-idle-time-millis: 300000
        test-while-idle: true
        test-on-borrow: false
        test-on-return: false
        validationQuery: "select 1"
        filters: stat,wall
        stat:
          log-slow-sql: true
          slow-sql-millis: 1000
          merge-sql: false
        wall:
          multi-statement-allow: true
mybatis-plus:
  mapper-locations: classpath*:mapper/**/*.xml

2自定义拦截器

import com.baomidou.dynamic.datasource.toolkit.DynamicDataSourceContextHolder;
import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlCommandType;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;


import java.util.Properties;

/**
 * type为拦截点，共有四种Executor(执行器)、StatementHandler(sql语法构建处理器)、ParameterHandler(参数处理器)、ResultSetHandler(结果处理器)
 * method为拦截的方法（为Executor中接口的方法）
 * args为拦截方法的参数类型
 */
@Intercepts({
        @Signature(type = Executor.class, method = "update", args = {MappedStatement.class,Object.class}),
        @Signature(type = Executor.class, method = "query", args = {MappedStatement.class,Object.class, RowBounds.class, ResultHandler.class}),
        @Signature(type = Executor.class, method = "query", args = {MappedStatement.class,Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class}),
})
public class MasterSlaveInterceptor implements Interceptor {

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        System.out.println("拦截器生效！");
        MappedStatement statement = (MappedStatement)invocation.getArgs()[0];
        SqlCommandType sqlCommandType = statement.getSqlCommandType();
        System.out.println("sqlCommandType = " + sqlCommandType);
        System.out.println(DynamicDataSourceContextHolder.peek());
        try{
            String source = "master";
            if(sqlCommandType == SqlCommandType.SELECT){
                source = "slave";
            }
            DynamicDataSourceContextHolder.push(source);
            System.out.println("数据源 = " + DynamicDataSourceContextHolder.peek());
            return invocation.proceed();
        }finally {
            DynamicDataSourceContextHolder.poll();
        }
    }

    @Override
    public Object plugin(Object target) {
        return target instanceof Executor ? Plugin.wrap(target, this) : target;
    }

    @Override
    public void setProperties(Properties properties) {
    }
}

Executor接口的前三个方法

在这里插入图片描述

DynamicDataSourceContextHolder类说明

https://apidoc.gitee.com/baomidou/dynamic-datasource-spring-boot-starter/com/baomidou/dynamic/datasource/toolkit/DynamicDataSourceContextHolder.html<\a>

在这里插入图片描述

3.拦截器注入

import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.example.multidatasource.interceptor.MasterSlaveInterceptor;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@MapperScan(basePackages = "com.example.multidatasource.mapper")
@Configuration
public class MybatisPlusConfig {
    /**
     * 读写分离插件
     */
    @Bean
    public MasterSlaveInterceptor masterMasterSlaveInterceptor(){
        return new MasterSlaveInterceptor();
    }

}

4.测试类

entity、service和mapper代码省略，都是标准的mybatis-plus使用方式。

import com.baomidou.mybatisplus.core.toolkit.IdWorker;
import com.example.multidatasource.entity.Model;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;
import java.util.Random;

@SpringBootTest
public class ModelServiceTest {
    @Autowired
    private IModelService modelService;

    @Test
    public void saveInfo(){
        Model model = new Model();
        Random random = new Random();
        int code = random.nextInt(1000);
        System.out.println("code = " + code);
        model.setModelName("测试数据" + code);
        model.setModelCode("test_model" + code);
        model.setId(IdWorker.get32UUID());
        model.setDatasourceId("13566547845");
        boolean save = modelService.save(model);
        System.out.println("save = " + save);
    }
    @Test
    public void selectInfo(){
        List<Model> list = modelService.list();
        System.out.println("list = " + list);
    }
}

测试结果

开启mysql的通用日志记录（方便测试，生产环境不要开启）

# 开启日志记录
SHOW VARIABLES LIKE 'general_log';
SET GLOBAL general_log = 'ON';
# 日志输出到表
SET GLOBAL log_output='table';
SHOW VARIABLES LIKE 'log_output';
# 查询sql日志
SELECT * FROM mysql.general_log;

执行saveInfo()方法，数据写入主库，从库同步数据。
在这里插入图片描述

主库sql日志
在这里插入图片描述

执行selectInfo()方法，查询从库。
在这里插入图片描述

从库日志
在这里插入图片描述

负载均衡

当存在多个从库时，需要将查询请求平均地分发到多个mysql从库中，本次将使用nginx作为中间层，对请求进行分发。

首先再启动一个mysql服务作为从机，操作可参考上篇(三、Mysql部署(从机))https://blog.youkuaiyun.com/weixin_53231767/article/details/140190997。

1.Nginx部署

拉取镜像

docker pull nginx:latest

创建挂载文件

结构如下
在这里插入图片描述

启动容器

 docker run -d --name nginx -p 8080:80 -v /mydata/nginx/conf/conf.d/default.conf:/etc/nginx/conf.d/default.conf -v /mydata/nginx/conf/nginx.conf:/etc/nginx/nginx.conf -v /mydata/nginx/log/access.log:/var/log/nginx/access.log -v /mydata/nginx/log/error.log:/var/log/nginx/error.log -v /mydata/nginx/html:/usr/share/nginx/html nginx:latest

修改配置文件

/mydata/nginx/conf/default.conf


user  nginx;
worker_processes  auto;

error_log  /var/log/nginx/error.log notice;
pid        /var/run/nginx.pid;


events {
    worker_connections  1024;
}
stream {
# 日志格式设置
  log_format mysql_log '$remote_addr [$time_local] '
                 '$protocol $status $bytes_sent $bytes_received '
                 '$session_time "$upstream_addr" '
                 '"$upstream_bytes_sent" "$upstream_bytes_received" "$upstream_connect_time"';
  # access.log日志位置，此处地址时容器内位置
  access_log /var/log/nginx/access.log mysql_log;
  # 配置文件位置
  include /etc/nginx/conf.d/*.conf;
}

/mydata/nginx/conf/conf.d/default.conf

upstream mysql-slave-cluster{
    server 192.168.19.8:13306 weight=1;# 从库1IP端口和权重
    server 192.168.19.9:13306 weight=1;# 从库2IP端口和权重
}
server{
    listen       80;
    listen  [::]:80;
    proxy_pass mysql-slave-cluster;
}

重启nginx容器

docker restart nginx

开放端口

firewall-cmd --add-port=8080/tcp --permanent
firewall-cmd --reload

数据源配置

修改application.yml文件的slave配置(IP端口改为nginx的IP端口)
在这里插入图片描述

测试类

    @Test
    public void selectInfo(){
        //执行十次查询
        for (int i = 0; i < 10; i++) {
            List<Model> list = modelService.list();
        }
    }