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

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 重做中继日志中的事件，将改变应用到自己的数据库中

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 （必须）
   1
   2
   3

2. 重启 MySQL 服务

   systemctl restart mysqld
   1

3. 登录 MySQL 数据库，执行以下 SQL

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

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

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

   show master status;
   1

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

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

配置从库 slave：

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

   [mysqld]
   server-id=101	# 设置服务器唯一ID （必须）
   1
   2

2. 重启 MySQL 服务

   systemctl restart mysqld
   1

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;
   1
   2
   3

4. 执行以下 SQL，查看从数据库的状态

   show slave status;
   1

2. 数据库读写分离

2.1 读写分离介绍

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

读写不分离的情况：

读写分离的情况：

那么在程序中该如何实现读写分离，如何识别当前的 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.shardingspheregroupId>
       <artifactId>sharding-jdbc-spring-boot-starterartifactId>
       <version>4.0.0-RC1version>
   dependency>
   1
   2
   3
   4
   5

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
   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11
   12
   13
   14
   15
   16
   17
   18
   19
   20
   21
   22
   23
   24
   25
   26
   27
   28
   29
   30
   31

3. 在 application.yaml 文件中配置允许 bean 定义覆盖配置项

   spring:
     main:
       allow-bean-definition-overriding: true 
   1
   2
   3

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