【MySQL 数据库】MySQL 主从复制和 Sharding-JDBC 实现读写分离

时间:2022-10-10 15:55:24

1. MySQL 主从复制

1.1 MySQL 主从复制介绍

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

MySQL 主从复制是 MySQL 数据库自带的功能,无需借助第三方工具。

MySQL 主从复制过程:

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

【MySQL 数据库】MySQL 主从复制和 Sharding-JDBC 实现读写分离

1.2 MySQL 主从复制配置

前置条件:

准备好至少两台服务器,安装 MySQL 并启动服务成功。其中一台为主库(master),其它为从库(slave)。

配置主库 master:

  1. 修改 MySQL 数据库的配置文件 /etc/my.cnf(Linux)、my.ini(Windows)

    [mysqld]
    log-bin=mysql-bin	# 启用二进制日志(必须)
    server-id=100		# 设置服务器唯一ID (必须)
    
  2. 重启 MySQL 服务

    systemctl restart mysqld
    
  3. 登录 MySQL 数据库,执行以下 SQL

    grant replication slave on *.* to 'slave'@'%' identified by '12345';
    

    上面的 SQL 的作用是创建一个用户 slave,密码为 12345,并且授予这个用户 replication slave 权限。replication slave 是用于建立复制时所需要的权限,只有授予了该权限,从库才能通过这个用户复制主库。

  4. 执行以下 SQL,记录结果中 File 和 Position 的值

    show master status;
    

    上面的 SQL 的作用是查看 master 的状态。File 表示日志文件名称,Position 表示当前日志记录的位置。

    注意:执行完该操作后就不要在主库执行其它的命令,因为 File 和 Position 会发生变化,但是这两个参数需要被 slave 中使用到。

配置从库 slave:

  1. 修改 MySQL 数据库的配置文件 /etc/my.cnf(Linux)、my.ini(Windows)

    [mysqld]
    server-id=101	# 设置服务器唯一ID (必须)
    
  2. 重启 MySQL 服务

    systemctl restart mysqld
    
  3. 登录 MySQL 数据库,执行以下 SQL

    change master to master_host='主库IP',master_user='slave',master_password='12345',master_log_file='mysql-bin.000001',master_log_pos=430;
    
    start slave;
    
  4. 执行以下 SQL,查看从数据库的状态

    show slave status;
    

2. 数据库读写分离

2.1 读写分离介绍

面对日益增加的系统访问量,数据库的吞吐量面临着巨大瓶颈。对于同一时刻有大量并发读操作和较少写操作类型的应用来说,将数据库拆分为主库和从库,主库负责处理事务性的增删改查操作,从库负责处理查询操作,这能够有效的避免数据更新导致的行锁,使得整个系统的查询性能得到极大的改善。

读写不分离的情况:

【MySQL 数据库】MySQL 主从复制和 Sharding-JDBC 实现读写分离

读写分离的情况:

【MySQL 数据库】MySQL 主从复制和 Sharding-JDBC 实现读写分离

那么在程序中该如何实现读写分离,如何识别当前的 SQL 是读还是写,如何根据 SQL 找到对应的主库或者从库?通过 Sharding-JDBC 框架就能帮助我们实现读写分离。

2.2 Sharding-JDBC

2.2.1 Sharding-JDBC 介绍

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

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

2.2.2 Sharding-JDBC 实现读写分离

  1. 导入依赖

    <dependency>
        <groupId>org.apache.shardingsphere</groupId>
        <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
        <version>4.0.0-RC1</version>
    </dependency>
    
  2. 在 application.yaml 文件中配置数据源信息和读写分离规则

    spring:
      shardingsphere:
        datasource:
          names:
            master,slave
          # 主数据源
          master:
            type: com.alibaba.druid.pool.DruidDataSource
            driver-class-name: com.mysql.cj.jdbc.Driver
            url: jdbc:mysql://120.76.203.209:3306/rw?characterEncoding=utf-8
            username: root
            password: root
          # 从数据源
          slave:
            type: com.alibaba.druid.pool.DruidDataSource
            driver-class-name: com.mysql.cj.jdbc.Driver
            url: jdbc:mysql://127.0.0.1:3306/rw?characterEncoding=utf-8
            username: root
            password: root
        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
    
  3. 在 application.yaml 文件中配置允许 bean 定义覆盖配置项

    spring:
      main:
        allow-bean-definition-overriding: true 
    

    配置该项的原因是由于 Sharding-JDBC 框架和 Druid 连接池的配置类在启动时都会创建一个数据源对象,会造成冲突,因此设置允许 bean 定义覆盖则能够解决这个问题。