Mysql读写分离

让项目读写分离，写走主库，读走从库，从单一的一台数据库到多台数据库，访问压力降低。将主库的数据同步到从库来这种结构叫Mysql主从复制。

Mysql主从复制

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

介绍

MySQL复制过程分成三步：

  • master将改变记录到二进制日志（binary log）
  • slave将master的binary log拷贝到它的中继日志(relay log)
  • slave重做中继日志中的事件，将改变应用到自己的数据库中

二进制日志：二进制日志（BINLOG）记录了所有的 DDL（数据定义语言）语句和 DML（数据操纵语言）语句，但是不包括数据查询语句。此日志对于灾难时的数据恢复起着极其重要的作用，MySQL的主从复制， 就是通过该binlog实现的。默认MySQL是未开启该日志的。

搭建

在搭建之前首先准备两台服务器，并在两台服务器中部署好mysql数据库，允许远程连接，并启动mysql服务，服务器开放3306端口。

注意事项：在搭建Mysql主从架构过程中，由于从服务器是克隆的主服务器系统，导致主从mysql uuid相同,

解决办法：修改其中一台服务器的server-uuid，并保证server-uuid的格式正确，修改完成之后重启Mysql服务就可以了，最后登录mysql数据库，执行 select uuid(); 查看更改后的uuid。

配置主库Master

第一步：修改Mysql数据库的配置文件 /etc/my.cnf，加入如下两行代码，重启mysql服务。

log-bin=mysql-bin #[必须]启用二进制日志
server-id=100 #[必须]服务器唯一id

第二步：登录 MySql数据库，执行以下SQL。

grant replication slave on . to ‘xiaoming‘@’%’ identified by ‘Root@123456’;

注：上面SQL的作用是创建一个用户xiaoming，密码为Root@123456，并且给用户授予replication slave权限。常用于建立复制时所需要用到的用户权限，也就是slave必须被master授权具有该权限的用户，才能通过该用户复制。密码设置123456报错提示不安全建议应设置复杂一些。这里如果是mysql8版本的不要加 identified by ‘password’，会导致语法错误。

第三步：登录MySQL数据库，执行下面SQL，记录下结果中File和Position的值。

show master status;

注意：设置完毕后不要再执行其他操作，不然当前的File和Position会改变，从库配置需要这两个值。

配置从库Slave

第一步：修改MySQL数据库的配置文件 /etc/my.cnf，加入如下一行代码，并重启mysql服务。

server-id=101 #[必须]服务器唯一id，需要与主库不同

第二步：登录 MySql数据库，执行以下SQL。

change master to master_host=’192.168.231.129’,master_user=’xiaoming’,master_password=‘Root@123456’,master_log_file=’mysql-bin.000006’,master_log_pos=154;

注：上面sql中出现的master_log_file和master_log_pos，要和主库查出来的File和Position对应。用户名和密码与主库配置一致。

第三步：启动从库和停止从库以及查看从库状态信息SQL。

start slave; #启动从库

stop slave; #停止从库

show slave status G; #查看从库状态信息

测试主从复制

在本机上用mysql客户端远程连接两台服务器的mysql数据库，测试主库建库、增删该等操作后从库会不会同步。

注意事项：当我们在从库进行建库、增删改等操作时，会导致主从数据不一致。

解决办法（忽略错误，继续同步）：

项目读写分离

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

Sharding-JDBC介绍

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

使用Sharding-JDBC可以在程序中轻松的实现数据库读写分离：

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

项目读写分离配置

第一步：导入Sharding-JDBC maven坐标：

1
2
3
4
5
<dependency>
   <groupId>org.apache.shardingsphere</groupId>
   <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
   <version>4.0.0-RC1</version>
</dependency>

第二步：在配置文件中配置读写分离规则：

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
32
33
34
spring:
  # mysql主从数据库配置
  shardingsphere:
    datasource: #指定数据源
      names:
        master,slave  #这里的master,slave可以改变，和下面的对应即可
      # 主数据源
      master:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://192.168.231.129:3306/reggie?characterEncoding=UTF-8&useSSL=false # 注意IP地址
        username: root
        password: 123456
      # 从数据源
      slave:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://192.168.231.130:3306/reggie?characterEncoding=UTF-8&useSSL=false
        username: root
        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定义覆盖

第三步：启动项目测试：

在启动项目后控制台输出启动了两个数据源，在执行增删改操作时，日志打印执行的是master数据库，在执行查询操作时，日志打印了执行的是slave数据库。