MySql主从复制和读写分离

五音诗.

已于 2022-09-15 19:11:38 修改

阅读量255

点赞数 1

文章标签：数据库 mysql java

于 2022-09-15 18:31:20 首次发布

本文链接：https://blog.csdn.net/weixin_46685542/article/details/126877226

版权

MySql主从复制

参考：

Docker_安装mysql主从复制_饭吃三分饱的博客-CSDN博客

主从复制是一个异步的复制过程，底层是基于MySQL1数据库自带的二进制日志功能。就是一台或多台MySQL数据库（slave,即从库)从另一台ySQL数据库(master,即主库)进行日志的复制然后再解析日志并应用到自身，最终实现从库的数据和主库的数据保持一致。SQL主从复制是MySQL数据库自带功能，无需借助第三方工具

在这里插入图片描述

三步：
1、master将改变记录到二进制日志(binary log)
2、slave:将master的binary log拷贝到它的中继日志（relay log)
3、slave重做中继日志中的事件，将改变应用到自己的数据库中

要实施复制，首先必须打开Master端的binarylog（bin-log）功能，否则无法实现

因为整个复制过程实际上就是Slave从Master端获取该日志然后再在自己身上完全顺序的执行日志中所记录的各种操作。复制的基本过程如下：

（1）Slave上面的IO进程连接上Master，并请求从指定日志文件的指定位置（或者从最开始的日志）之后的日志内容；

（2）Master接收到来自Slave的IO进程的请求后，通过负责复制的IO进程根据请求信息读取指定日志指定位置之后的日志信息，返回给Slave的IO进程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息已经到Master端的bin-log文件的名称以及bin-log的位置；

（3）Slave的IO进程接收到信息后，将接收到的日志内容依次添加到Slave端的relay-log文件的最末端，并将读取到的Master端的bin-log的文件名和位置记录到master-info文件中，以便在下一次读取的时候能够清楚的告诉Master“我需要从某个bin-log的某个位置开始往后的日志内容，请发给我”；

（4）Slave的Sql进程检测到relay-log中新增加了内容后，会马上解析relay-log的内容成为在Master端真实执行时候的那些可执行的内容，并在自身执行。

复制有三种模式

1 Statement Level复制
该模式是最早的复制模式，主要的流程是Master端将每一条会修改数据的Query记录下来，Slave端在复制的时候会根据二进制文件重新执行相同的Query。这种模式的优点是Master端不需要记录每一行数据的变化，二进制日志文件量小，IO成本低，速度快。

相应的，该模式存在的缺点如下：由于记录的是执行语句，就需要额外的知道每条语句执行的上下文信息，以保证该相同的操作在Slave端执行时能够得到和Master同样的结果。但由于MySQL功能的不断增多，这种复制模式需要考虑的情况也就越来越多，出现bug的几率也就也大。从MySQL 5.0开始，MySQL复制解决了大量的之前版本中出现的无法复制或复制错误的问题，但随着MySQL的发展，这种挑战将会日趋严峻。

2 Row Level复制
MySQL开发人员意识到Statement Level存在的问题，于5.1.5开始提供Row Level模式。该模式的主要流程是，MySQL二级制日志文件会将每一行数据修改都记录下来，然后在Slave端进行同样的修改。这种模式的优点是：日志文件不会将SQL语句执行的上下文记录下来，只是记录哪一条数据修改了，修改成什么样子了；这样做可以避免如某些特定情况下存储过程、trigger的调用和触发没有被正确执行等复制问题。

同样，该模式也存在缺点：日质量的成倍增加。例如：执行alter table之类的语句的时候，由于表结构修改，每条记录都发生改变，那么该表每一条记录都会记录到日志中。这样就大增加了复制过程的IO成本，导致速度下降、性能下降。

3 Mixed Level复制
MySQL从5.1.8开始，提供Mixed Level。该模式结合了之前两种模式的优点，规避了二者的缺点。在该模式下，MySQL会根据执行的每一条语句来区分记录日志文件的格式。举例说明，当涉及到复杂的存储过程时，采用Row Level，规避Statement Level存在的某些场景无法复制的问题；当涉及到Alter table等操作时，采用Statement Level来规避Row Level带来的日志量巨大的问题

docker案例

1、拉取docker的mysql5.7镜像

docker pull mysql:5.7

2、创建容器，并设置文件目录映射

主数据库

在本地的/data/mysql/mysql-master目录映射容器内部的/etc/mysql目录，这个文件夹目录里的文件会和容器内的目录文件自动同步

设置mysql用户root 密码123456

docker run -p 3301:3306 --name mysql-master  -v /data/mysql/mysql-master:/etc/mysql   -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7

从数据库

docker run -p 3302:3306 --name mysql-slave -v /data/mysql/mysql-slave:/etc/mysql   -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7

3、在 /data/mysql/mysql-master 目录下，新建my.cnf 文件并添加内容

注释和语句之前不能有空行，语句结尾不能有空格

[mysqld]
## 设置server_id，同一局域网中需要唯一
server_id=101 
## 指定不需要同步的数据库名称
binlog-ignore-db=mysql  
## 开启二进制日志功能
log-bin=mall-mysql-bin  
## 设置二进制日志使用内存大小（事务）
binlog_cache_size=1M  
## 设置使用的二进制日志格式（mixed,statement,row）
binlog_format=mixed  
## 二进制日志过期清理时间。默认值为0，表示不自动清理。
expire_logs_days=7  
## 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
## 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave_skip_errors=1062

4、重启master库

docker restart mysql-master

5、进入master容器

docker exec -it mysql-master /bin/bash

6、进入容器的mysql

mysql -uroot -p123456

7、创建数据同步的用户

#创建用户slave，密码123456。%表示任意ip，可指定ip。
CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
#为slave用户授权 *.*表示复制所有库.所有表，&表示任意ip
GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'slave'@'%';

8、在/data/mysql/mysql-slave 目录下新建my.cnf 进行从库的设置

[mysqld]
## 设置server_id，同一局域网中需要唯一
server_id=102
## 指定不需要同步的数据库名称
binlog-ignore-db=mysql
## 开启二进制日志功能，以备Slave作为其它数据库实例的Master时使用
log-bin=mall-mysql-slave1-bin
## 设置二进制日志使用内存大小（事务）
binlog_cache_size=1M
## 设置使用的二进制日志格式（mixed,statement,row）
binlog_format=mixed
## 二进制日志过期清理时间。默认值为0，表示不自动清理。
expire_logs_days=7
## 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
## 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave_skip_errors=1062
## relay_log配置中继日志
relay_log=mall-mysql-relay-bin
## log_slave_updates表示slave将复制事件写进自己的二进制日志
log_slave_updates=1
## slave设置为只读（具有super权限的用户除外）
read_only=1

9、重启mysql-slave容器

docker restart mysql-slave

10、在master容器中的mysql服务里查看同步状态

show master status

在这里插入图片描述

11、在从库中设置主从复制

总结一句直接复制，记得改host

change master to master_host='192.168.2.128', master_user='slave',master_password='123456',master_port=3301,master_log_file='mall-mysql-bin.000001',master_log_pos=617,
master_connect_retry=30;

解释:

#主数据库的IP地址

change master to master_host='192.168.2.128', 

#在主数据库创建的用于同步数据的用户账号

master_user='slave',

#在主数据库创建的用于同步数据的用户密码

master_password='123456',

#主数据库的运行端口

master_port=3307,

#指定从数据库要复制数据的日志文件，(通过查看主数据的状态，获取File参数)

master_log_file='mall-mysql-bin.000001',

#指定从数据库从哪个位置开始复制数据，(通过查看主数据的状态，获取Position参数)

master_log_pos=617,

#连接失败重试的时间间隔，单位为秒

master_connect_retry=30;

12、设置从库开始主从同步

start slave;

13、从库查看同步状态

在这里插入图片描述

这样就设置好了

14、外面链接数据库测试

master数据库创建修改，slave库会自动同步

15、给slave设置只读权限账号

用户名：letu2

密码：123456

GRANT SElECT ON *.* TO 'letu2'@'%' IDENTIFIED BY "123456";

16、刷新权限使用户创建、授权生效

flush privileges;

17、使用创建的只读账号登录从库

只有只读权限

读写分离 Sharding-JDBC

主库主要是用来完成增、删、改操作
从库主要是用来完成查询操作

Sharding-JDBC定位为轻量级Java框架，在Java的DBC层提供的额外服务。它使用客户端直连数据库，以jar包形式
提供服务，无需额外部署和依赖，可理解为增强版的DBC驱动，完全兼容JDBC和各种ORM框架。
使用Sharding-JDBC可以在程序中轻松的实现数据库读写分离。

适用于任何基于JDBC的ORM框架，如：JPA,Hibernate,.Mybatis,Spring JDBC Template或直接使用DBC。
支持任何第三方的数据库连接池，如：DBCP,C3PO,BoneCP,Druid,HikariCP等。
支持任意实现DBC规范的数据库。目前支持MySQL,Oracle,SQLServer,PostgreSQL以及任何遵循SQL92标准
的数据库

springboot 项目中引用依赖

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.1.1</version>
</dependency>

数据库创建表

CREATE TABLE `t_user`  (
  `id` int(3) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(80) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `nick_name` varchar(80) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `email` varchar(150) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `create_time` datetime(0) NULL DEFAULT NULL,
  `update_time` datetime(0) NULL DEFAULT NULL,
  `deleted_flag` int(2) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 20001 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of t_user
-- ----------------------------
INSERT INTO `t_user` VALUES (11, 'name133', 'slave', 'name133@123.com', '2022-08-30 10:35:04', '2022-08-30 10:35:04', 0);

mybatis-plus逆向生成

导入依赖

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
          <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.1</version>
        </dependency>

        <dependency>
            <groupId>org.apache.velocity</groupId>
            <artifactId>velocity-engine-core</artifactId>
            <version>2.1</version>
        </dependency>
         <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-generator</artifactId>
            <version>3.4.1</version>
        </dependency>

package com.ung.myBatisPlusTest;

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.core.toolkit.StringPool;
import com.baomidou.mybatisplus.generator.AutoGenerator;
import com.baomidou.mybatisplus.generator.InjectionConfig;
import com.baomidou.mybatisplus.generator.config.*;
import com.baomidou.mybatisplus.generator.config.po.TableInfo;
import com.baomidou.mybatisplus.generator.config.rules.NamingStrategy;

import java.util.ArrayList;
import java.util.List;

/**
 * @author: wenyi
 * @create: 2022/4/26
 * @Description:
 */
public class MyBatisR {
    public static void main(String[] args) {
        // 1、全局配置
        GlobalConfig globalConfig = new GlobalConfig();//构建全局配置对象
        String projectPath = System.getProperty("user.dir");// 获取当前用户的目录
        globalConfig
                .setOutputDir(projectPath + "/test-shardingsphere/src/main/java")// 输出文件路径
                .setAuthor("ung")// 设置作者名字
                .setOpen(false)// 是否打开资源管理器
                .setFileOverride(true)// 是否覆盖原来生成的
                .setIdType(IdType.AUTO)// 主键策略
                .setBaseResultMap(true)// 生成resultMap
                .setBaseColumnList(true)// XML中生成基础列
                .setServiceName("%sService");// 生成的service接口名字首字母是否为I，这样设置就没有I

        // 2、数据源配置
        DataSourceConfig dataSourceConfig = new DataSourceConfig();// 创建数据源配置
        dataSourceConfig
                .setUrl("jdbc:mysql://192.168.2.128:3301/test1?userSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC")
                .setDriverName("com.mysql.cj.jdbc.Driver")
                .setUsername("root")
                .setPassword("123456")
                .setDbType(DbType.MYSQL);

        // 3、包配置
        PackageConfig packageConfig = new PackageConfig();
        packageConfig
                .setParent("com.ung.testshardingsphere")
                .setEntity("entity")
                .setController("controller")
                .setService("service")
                .setMapper("mapper");

        // 4、策略配置
        StrategyConfig strategyConfig = new StrategyConfig();
        strategyConfig
                .setCapitalMode(true)// 开启全局大写命名
                .setInclude("t_user")// 设置要映射的表
                .setNaming(NamingStrategy.underline_to_camel)// 下划线到驼峰的命名方式
                .setColumnNaming(NamingStrategy.underline_to_camel)// 下划线到驼峰的命名方式
                .setEntityLombokModel(true)// 是否使用lombok
                .setRestControllerStyle(true)// 是否开启rest风格
                .setControllerMappingHyphenStyle(true);// localhost:8080/hello_a_2


        // 5、自定义配置（配置输出xml文件到resources下）
        InjectionConfig cfg = new InjectionConfig() {
            @Override
            public void initMap() {
                // to do nothing
            }
        };
        List<FileOutConfig> focList = new ArrayList<>();
        String templatePath = "/templates/mapper.xml.vm";
        // 自定义配置会被优先输出
        focList.add(new FileOutConfig(templatePath) {
            @Override
            public String outputFile(TableInfo tableInfo) {
                // 自定义输出文件名 ， 如果你 Entity 设置了前后缀、此处注意 xml 的名称会跟着发生变化！！
                return projectPath + "/test-shardingsphere/src/main/resources/mapper/"
                        + tableInfo.getEntityName() + "Mapper" + StringPool.DOT_XML;
            }
        });
        cfg.setFileOutConfigList(focList);

        // 6、整合配置
        AutoGenerator autoGenerator = new AutoGenerator();// 构建代码生自动成器对象
        autoGenerator
                .setGlobalConfig(globalConfig)// 将全局配置放到代码生成器对象中
                .setDataSource(dataSourceConfig)// 将数据源配置放到代码生成器对象中
                .setPackageInfo(packageConfig)// 将包配置放到代码生成器对象中
                .setStrategy(strategyConfig)// 将策略配置放到代码生成器对象中
                .setCfg(cfg)// 将自定义配置放到代码生成器对象中
                .execute();// 执行！
    }
}

导入druid的依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.22</version>
</dependency>

application.yml配置

spring:
  shardingsphere:
    datasource:
      names: master,slave
      # 主数据源
      master:
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://192.168.2.128:3301/test1?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&useSSL=false&allowPublicKeyRetrieval=true
        driver-class-name: com.mysql.cj.jdbc.Driver
        username: root
        password: 123456
      # 从数据源
      slave:
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://192.168.2.128:3302/test1?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&useSSL=false&allowPublicKeyRetrieval=true
        driver-class-name: com.mysql.cj.jdbc.Driver
        username: letu2
        password: 123456
    masterslave:
      # 读写分离设置【负载均衡策略】
      load-balance-algorithm-type: round_robin
      # 最终的数据源名称
      name: dataSource
      # 主数据源名称【与上面对应】
      master-data-source-name: master
      # 从数据源名称【与上面对应】
      slave-data-source-names: slave
    props:
      sql:
        show: true # 开启SQL显示，默认false
    main:
      allow-bean-definition-overriding: true # 允许bean覆盖
  # 数据源配置
#  datasource:
#    type: com.alibaba.druid.pool.DruidDataSource
#    druid:
#      driver-class-name: com.mysql.cj.jdbc.Driver
#      url: jdbc:mysql://192.168.2.128:3301/test1?useUnicode=true&characterEncoding=utf-8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&useSSL=false&serverTimezone=Asia/Shanghai
#      username: root
#      password: 123456
#      # 初始连接数
#      initialSize: 5
#      # 最小连接池数量
#      minIdle: 10
#      # 最大连接池数量
#      maxActive: 500
#      # 配置获取连接等待超时的时间
#      maxWait: 60000
#      # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
#      timeBetweenEvictionRunsMillis: 60000
#      # 配置一个连接在池中最小生存的时间，单位是毫秒
#      minEvictableIdleTimeMillis: 300000
#      # 配置一个连接在池中最大生存的时间，单位是毫秒
#      maxEvictableIdleTimeMillis: 900000
#      # 配置检测连接是否有效
#      validationQuery: SELECT 1 FROM DUAL
#      testWhileIdle: true
#      testOnBorrow: false
#      testOnReturn: false
#      statViewServlet:
#        enabled: true
#        # 设置白名单，不填则允许所有访问
#        allow:
#        url-pattern: /druid/*
#      filter:
#        stat:
#        # 慢SQL记录
#        log-slow-sql: true
#        slow-sql-millis: 1000
#        merge-sql: true
#      wall:
#        config:
#          multi-statement-allow: true

# MyBatis Plus
mybatis-plus:
  mapper-locations: classpath*:mapper/**/*.xml
  global-config:
    # 不显示banner
    banner: false
    # 数据库相关配置
    db-config:
      #主键类型  AUTO:"数据库ID自增", INPUT:"用户输入ID",ID_WORKER:"全局唯一ID (数字类型唯一ID)", UUID:"全局唯一ID UUID";
      id-type: AUTO
  configuration:
    call-setters-on-nulls: true
#    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

在springboot的测试类中测试

@SpringBootTest
class TestMybatisplusAutoApplicationTests {

    @Test
    void contextLoads() {
    }

    @Autowired
    private TUserService userService;

    @Test
    public void test1() {
        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.eq("id",11);
        TUser one = userService.getOne(queryWrapper);
        System.out.println(one);
    }
}