最新MySql8.27主從複製及SpringBoot專案中的讀寫分離實戰教學

2022-08-08 14:02:09

1、MySql主從複製

MySQL主從複製是一個非同步的複製過程，底層是基於MySQL1資料庫自帶的二進位制紀錄檔功能。就是一臺或多臺MySQL資料庫（slave,即從庫)從另一臺ySQL資料庫(master,即主庫)進行紀錄檔的複製然後再解析紀錄檔並應用到自身，最終實現從庫的資料和主庫的資料保持一致。SQL主從複製是MySQL資料庫自帶功能，無需藉助第三方工具。

MySQL複製過程分成三步：
1、master將改變記錄到二進位制紀錄檔(binary log)
2、slave:將master的binary log拷貝到它的中繼紀錄檔（relay log)
3、slave重做中繼紀錄檔中的事件，將改變應用到自己的資料庫中

說明：下面的設定需要準備兩臺伺服器並且都要安裝有MySQL資料庫，同時安裝資料庫的方式不同修改組態檔的位置也不同，需要更具自身電腦來查詢

2、設定-主庫Master

修改MySql資料庫的組態檔/etc/my.cnf，在設定紀錄檔檔案中新增如下程式碼（不要第一行）

[mysqld]
log-bin=mysql-bin # [必須]啟用二進位制紀錄檔
server-id=100 # [必須]伺服器唯一ID

修改後重啟MySql服務

登入MySQL執行下面的SQL語句

create user 'slave'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
grant replication slave on *.* to 'slave'@'%';

注：上面SQL的作用是建立一個使用者xiaoming,密碼為Root@123456,並且給xiaoming使用者授予REPLICATI0 N SLAVE
許可權。常用於建立複製時所需要用到的使用者許可權，也就是slave必須被master授權具有該許可權的使用者，才能通過該使用者複製。

登入MySQL資料庫，執行下面的SQL，記錄下結果中的File和Position的值

show master status

注意：上面的SQL的作用是檢視Master狀態，執行完此SQL後不要再執行任何操作

3、設定-從庫Slave

修改MySQL資料庫中的組態檔/etc/my.cnf

[mysqld]
server-id=101 # [必須]伺服器唯一ID

重啟MySQL服務

systemctl restart mysql;

【重點】登入MySQL資料庫，執行下面SQL語句（與設定主庫最後查詢的表格有關聯）

change master to master_host='主庫IP地址',master_user='slave',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=653;
stop slave;
start slave;

注意：
master_user：是在主庫中建立的許可權賬戶
master_password：是建立賬戶的密碼
master_log_file：是建立主庫最後執行的show master statusSQL語句查詢出來的【檔名稱】
master_log_pos：是檔案的位置，與上一句查詢同出一處

登入MySQL資料庫，執行下面的SQL檢視從資料庫的狀態

show slave status;

3、主從複製測試

1、在主資料庫下建立一個新的資料庫，然後在從庫中重新整理，如果出現主庫設定的資料庫那麼就是成功了

4、讀寫分離案例

面對日益增加的系統存取量，資料庫的吞吐量面臨著巨大瓶頸。對於同一時刻有大量並行讀操作和較少寫操作型別的
應用系統來說，將資料庫拆分為主庫和從庫，主庫負責處理事務性的增刪改操作，從庫負責處理查詢操作，能夠有效
的避免由資料更新導致的行鎖，使得整個系統的查詢效能得到極大的改善。

4.1、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>

案例要求需要完成上面的內容

在主庫中設定一個資料庫以及一個user欄位，並填寫上幾個簡單欄位方便後面程式碼的測試

主庫主要是用來完成增、刪、改操作
從庫主要是用來完成查詢操作

案例目錄結構

基於MP快速開發，下面給出基本的控制器程式碼，在控制中進行二次開發

package com.zcl.controller;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.zcl.entity.User;
import com.zcl.service.UserService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import javax.sql.DataSource;
import java.util.List;

/**
 * 專案名稱：Sharding-JDBC
 * 描述：控制器
 *
 * @author zhong
 * @date 2022-08-06 20:36
 */
@Slf4j
@RestController
@RequestMapping("/user")
public class UserController {
    @Autowired
    private DataSource dataSource;

    @Autowired
    private UserService userService;

    /**
     * 新增使用者
     * @param user
     * @return
     */
    @PostMapping
    public User save(User user){
        userService.save(user);
        return user;
    }

    /**
     * 根據id刪除
     * @param id
     */
    @DeleteMapping("/{id}")
    public void delete(@PathVariable("id") Long id){
        userService.removeById(id);
    }

    /**
     * 根據id修改使用者
     * @param user
     * @return
     */
    @PutMapping
    public User update(User user){
        userService.updateById(user);
        return user;
    }

    /**
     * 根據id查詢使用者
     * @param id
     * @return
     */
    @GetMapping("/{id}")
    public User getById(@PathVariable("id") Long id){
        return userService.getById(id);
    }

    /**
     * 條件查詢
     * @param user
     * @return
     */
    @GetMapping("/list")
    public List<User> list(User user){
        LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(user.getId() != null,User::getId,user.getId());
        queryWrapper.eq(user.getName() != null, User::getName,user.getName());
        return userService.list(queryWrapper);
    }
}

注意：一定需要在pom檔案中引入核心的依賴包，否則無法完成下面application.yml設定的工作

server:
  port: 8080
mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
    map-underscore-to-camel-case: true
  global-config:
    db-config:
      id-type: ASSIGN_ID

spring:
  shardingsphere:
    datasource:
      names: master,slave
      # 主資料來源
      master:
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://192.168.26.131:3306/rw?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: 1234
      # 從資料來源
      slave:
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://192.168.26.131:13306/rw?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: 1234
    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覆蓋

在設定項中設定允許bean定義覆蓋設定項
如果不進行設定就會出現啟動專案報錯，主要的原因是引兩個jar包都會建立資料來源物件，導致報錯，開啟bean設定覆蓋就可以解決問題了