MySQL主从的应用

  • 开发
  • 23

说明：本文介绍MySQL主从在实际中的应用。主从搭建和问题参考下面两篇文章：

数据迁移

当我们搭建完MySQL主从，第一步当然是把历史数据导入到主从结构中。有以下两种方式：

  • 开启主从同步，将数据导入主节点，让从节点同步；

  • 将数据分别导入主从节点，让主从节点数据一致后，建立连接，开启同步；

不论哪种方式，都需要导出/导入数据。为了节省时间，可以采用mysql命令的方式导出/导入，而不是用数据库连接工具来操作。以xxl-job数据库为例：

数据导出为sql文件

mysqldump -u username -p xxl_job > xxl-job.sql

在这里插入图片描述

在这里插入图片描述

将xxl-job.sql文件上传到MySQL主从的服务器上，导入数据库，这里我导入到test数据库中

mysql -u username -p test < xxl-job.sql

在这里插入图片描述

使用navicat查看，主库里test数据库里面有xxl-job相关的表了，同样的从库也同步过去了。

在这里插入图片描述

应用

一般来说，MySQL主从可以有以下两个应用。

（1）数据备份

就是啥也不做，从库仅做数据备份，另外当主库宕机时，可以修改配置文件，改为从库。但这又会有一个问题，主从库的数据一致性怎么保证。会不会出现一种情况，主库写入的数据，还没来得及同步到从库，然后宕机了。这段时间内的数据是从库中没有的。切换到从库，从库跑了一段时间后又有数据写入。结果就是，主从库之间各自有对方没有的数据，事后怎么同步数据又是问题。

总之，MySQL主从用来做数据备份是OK的，但如果用来做灾备，需要考虑以下问题：

  • 切换从库后，数据一致性怎么保证（看主库日志，看是怎么时候宕机的，这个时间段有没有会话连接，对数据库操作量大不大）

  • 缺失的数据对系统的影响大不大（经验之谈，可能主库宕机，但binlog都推送给从库了，主从库数据一致，完全没影响）

  • 事后主从数据库之间的数据同步怎么搞（能否将各自的数据导出来，各自导入执行一遍或者导出来做比对，针对缺失的数据做导入）

（2）读写分离

读写分离，顾名思义，就是将对数据库的操作分开，读操作分给从库，主库压力会小很多。在博主前面介绍的主从搭建中，有分别对主从配置文件添加下面这行配置：

（主数据库）

# 是否只读，1 代表只读，0代表读写，主数据库需要读写，设置0
read-only=0

（从数据库）

# 是否只读，1 代表只读，0代表读写，从数据库仅读，设置1
read-only=1

但在我实际操作来看，似乎没有生效。从数据库该添加数据还是能添加数据，真正做到读写分离，需要从MySQL账户入手，设置从库的MySQL账户仅有查询操作，然后项目里配置的就是这个账户才行。相关SQL如下：

MySQL权限相关命令

# 创建admin用户
create user 'admin'@'%' identified by 'MySQL@3306';

# 赋予该账户select权限
GRANT SELECT ON *.* TO 'admin'@'%';

# 刷新
flush privileges;

创建一个仅有select权限的用户，用户名为admin；

在这里插入图片描述

其他命令

# 移除用户的所有权限
REVOKE ALL PRIVILEGES ON *.* FROM 'admin'@'%';

# 根据用户名查询主机设置
select user,host from mysql.user where user = 'admin';

写入测试，提示错误

在这里插入图片描述

读写分离也需要考虑数据一致性问题，为此我们可以考虑如下方案：

  • 当读取返回结果为空时，再读一遍主库；

  • 写操作紧跟着的读操作，访问主库；

  • 核心业务读写主库，非核心业务读从库；

dynamic-datasource组件

该组件提供了多数据源场景的一些功能，包括切换数据源、多数据源事务、多从库的负载均衡策略等，以下介绍一下这个组件的基础功能；

（0）准备工作

首先创建一个Spring Boot项目，文件如下：

（pom.xml文件）

<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 http://maven.apache.org/maven-v4_0_0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.7.12</version>
        <relativePath/>
    </parent>

    <groupId>com.hezy</groupId>
    <artifactId>multiple_databases_demo</artifactId>
    <version>1.0-SNAPSHOT</version>
    <name>Archetype - multiple_databases_demo</name>
    <url>http://maven.apache.org</url>

    <dependencies>
        <!--web-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <!--lombok-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>

        <!--dynamic-datasource-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
            <version>3.5.1</version>
        </dependency>

        <!--druid-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.8</version>
        </dependency>

        <!--mybatis-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.2</version>
        </dependency>

        <!--msyql驱动-->
        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <scope>runtime</scope>
        </dependency>
    </dependencies>
</project>

（创建两个接口，一个读，一个写）

@RestController
@RequestMapping("/user")
public class UserController {

    @Autowired
    private UserService userService;

    @GetMapping("/{id}")
    public User getUser(@PathVariable String id) {
        return userService.getUser(id);
    }

    @PostMapping("/add")
    public void addUser(@RequestBody User user) {
        userService.addUser(user);
    }
}

（1）切换数据库

使用dynamic-datasource组件切换数据库非常简单，首先配置文件里，配置多个数据源，如我搭建的是主从MySQL，则配置如下：

server:
  port: 8080

# 1.数据源的配置
spring:
  datasource:
    dynamic:
      datasource:
        master:
          url: jdbc:mysql://主节点IP:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8
          username: admin
          password: MySQL@3306
          driver-class-name: com.mysql.cj.jdbc.Driver
        slave:
          url: jdbc:mysql://从节点IP:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8
          username: admin
          password: MySQL@3306
          driver-class-name: com.mysql.cj.jdbc.Driver

# 2.mybatis配置
mybatis:
  configuration:
    # 显示SQL日志配置
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
    # 驼峰命名配置
    map-underscore-to-camel-case: true
  # 设置mapper.xml文件所在的路径
  mapper-locations: classpath:mapper/*.xml

使用时，在对应的方法上加上@DS（数据源名称）即可，如下，读操作给从库（slave），写操作给主库（master）

import com.baomidou.dynamic.datasource.annotation.DS;
import com.hezy.pojo.User;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

@Mapper
public interface UserMapper {

    @DS("slave")
    @Select("select * from i_user where id = #{id}")
    User getUser(String id);

    @DS("master")
    @Insert("insert into i_user(id, username, password) values(#{id}, #{username}, #{password})")
    void addUser(User user);
}

（读操作，走从库）

在这里插入图片描述

（写操作，走主库）

在这里插入图片描述

在这里插入图片描述

更换一下，将写操作分给主库，

import com.baomidou.dynamic.datasource.annotation.DS;
import com.hezy.pojo.User;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

@Mapper
public interface UserMapper {

    @DS("master")
    @Select("select * from i_user where id = #{id}")
    User getUser(String id);

    @DS("slave")
    @Insert("insert into i_user(id, username, password) values(#{id}, #{username}, #{password})")
    void addUser(User user);
}

重启测试，读操作，没问题

在这里插入图片描述

写操作，报错

在这里插入图片描述

在这里插入图片描述

多数据源切换实现原理是AOP，官方推荐@DS注解加载Service实现类对应的方法上。

（2）数据库配置加密

使用该组件，可以实现对数据库配置的加密，让配置文件中数据库配置显示的是密文。实现如下：

    public static void main(String[] args) throws Exception {
        // 明文配置
        String password = "MySQL@3306";
        // 加密显示
        String encodePassword = CryptoUtils.encrypt(password);
        System.out.println(encodePassword);
    }

将下面这段密文复制下来；

在这里插入图片描述

在配置文件中，如下：

在这里插入图片描述

实现原理是，私钥加密，公钥解密。用以上方法实际上用的是组件自带的私公钥。

在这里插入图片描述

推荐使用下面的方法，随机生成私公钥；

    public static void main(String[] args) throws Exception {
        // 生成私公钥
        String[] arr = CryptoUtils.genKeyPair(512);
        System.out.println("privateKey:  " + arr[0]);
        System.out.println("publicKey:  " + arr[1]);
        // 使用私钥加密数据库相关配置
        System.out.println("username:  " + CryptoUtils.encrypt(arr[0], "admin"));
        System.out.println("password:  " + CryptoUtils.encrypt(arr[0], "MySQL@3306"));
    }

在这里插入图片描述

把上面两个配置复制到配置文件中，同时附带上public-key，如下：

spring:
  datasource:
    dynamic:
      datasource:
        master:
          url: jdbc:mysql://主库IP:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8
          username: ENC(oM2ceVnn3KOTEv9Ci4yI4QKwfaSzHZpb26SWQuxlgtcMtYpbr5HYK30TT+jtI+IOsZJHqSaWhhPPlfM40rnYrw==)
          password: ENC(TvstWlvbEKqPzJrek8fx3+Si6c6OeoiZVE7Njbf+fYwjqb/Tr3v0YevMfdG8FAB32U3xda5J7AdRhAcWA0A1rg==)
          driver-class-name: com.mysql.cj.jdbc.Driver
        slave:
          url: jdbc:mysql://从库IP:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8
          username: ENC(oM2ceVnn3KOTEv9Ci4yI4QKwfaSzHZpb26SWQuxlgtcMtYpbr5HYK30TT+jtI+IOsZJHqSaWhhPPlfM40rnYrw==)
          password: ENC(TvstWlvbEKqPzJrek8fx3+Si6c6OeoiZVE7Njbf+fYwjqb/Tr3v0YevMfdG8FAB32U3xda5J7AdRhAcWA0A1rg==)
          driver-class-name: com.mysql.cj.jdbc.Driver
      public-key: MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBAL2RM3JnCjtTogh3LrwN/meyWaWpSGCn7CaYRo6spLaeAhcguhi3XQrfLa7W4LQrJTENm+yA52YJfk+mWtjyl0ECAwEAAQ==

如果你主从库是分开生成的，则在每个数据源里写各自的公钥配置，如果是一样的，如上，主从库用户名、密码一样，就在外层全局设置即可。

另外，提醒一点，用上面的方法每次都会生成新的私公钥，所以需要加密的数据库配置需要是同一次操作生成的，不能跑一遍把username、password加密了，后面感觉url也要加密，就单独把url加密，然后复制到配置里，这样启动时会报解密错误的。

（3）更多

dynamic-datasource组件更丰富的功能，参考官方文档：https://www.kancloud.cn/tracy5546/dynamic-datasource/2264611

总结

本文介绍了MySQL主从的应用，及应用时需要考虑的问题。

阅读全部

相关推荐

  3. mysql的主从同步

    2024-04-25 22:16:02       27 阅读

  7. 面试宝典：解决MySQL主从不一致的策略

    2024-04-25 22:16:02       39 阅读

  8. 使用python脚本查询mysql的gtid和主从信息

    2024-04-25 22:16:02       39 阅读

  10. mysql 主从配置流程

    2024-04-25 22:16:02       50 阅读

最近更新

  3. docker php8.1+nginx base 镜像 dockerfile 配置

    2024-04-25 22:16:02       94 阅读

  4. Could not load dynamic library ‘cudart64_100.dll‘

    2024-04-25 22:16:02       100 阅读

  9. 在Django里面运行非项目文件

    2024-04-25 22:16:02       82 阅读

  19. Python语言-面向对象

    2024-04-25 22:16:02       91 阅读

  20. 如何分清楚常见的 Git 分支管理策略Git Flow、GitHub Flow 和 GitLab Flow

    2024-04-25 22:16:02       85 阅读

热门阅读

  20. HOT100与剑指Offer

    2024-04-25 22:16:02       33 阅读

  23. C#中的委托

    2024-04-25 22:16:02       27 阅读

  26. MySql CPU激增原因分析

    2024-04-25 22:16:02       33 阅读

  29. 项目：后台管理系统的开发及自动化部署

    2024-04-25 22:16:02       33 阅读