概述

SQL

• 什么是SQL？

  Structure Query Language(結(jié)構(gòu)化查詢語言)

  它被美國國家標準局(ANSI)確定為關(guān)系型數(shù)據(jù)庫語言的美國標準，后被國際化標準組織(ISO)采納為關(guān)系數(shù)據(jù)庫語言的國際標準。

  數(shù)據(jù)庫管理系統(tǒng)可以通過SQL管理數(shù)據(jù)庫；定義和操作數(shù)據(jù)，維護數(shù)據(jù)的完整性和安全性。

• 優(yōu)點

  • 簡單易學，具有很強的操作性
  • 絕大多數(shù)重要的數(shù)據(jù)庫管理系統(tǒng)均支持SQL
  • 高度非過程化；用SQL操作數(shù)據(jù)庫時大部分的工作由DBMS自動完成

• 分類

  1. DDL(Data Definition Language) 數(shù)據(jù)定義語言，用來操作數(shù)據(jù)庫、表、列等； 常用語句：CREATE、 ALTER、DROP
  2. DML(Data Manipulation Language) 數(shù)據(jù)操作語言，用來操作數(shù)據(jù)庫中表里的數(shù)據(jù)；常用語句：INSERT、 UPDATE、 DELETE
  3. DCL(Data Control Language) 數(shù)據(jù)控制語言，用來操作訪問權(quán)限和安全級別； 常用語句：GRANT、DENY
  4. (Data Query Language) 數(shù)據(jù)查詢語言，用來查詢數(shù)據(jù) 常用語句：SELECT

數(shù)據(jù)庫的三大范式

• 第一范式(1NF)是指數(shù)據(jù)庫表的每一列都是不可分割的基本數(shù)據(jù)線；也就是說：每列的值具有原子性，不可再分割。
• 范式(2NF)是在第一范式(1NF)的基礎(chǔ)上建立起來得，滿足第二范式(2NF)必須先滿足第一范式(1NF)。如果表是單主鍵，那么主鍵以外的列必須完全依賴于主鍵；如果表是復合主鍵，那么主鍵以外的列必須完全依賴于主鍵，不能僅依賴主鍵的一部分。
• 范式(3NF)是在第二范式的基礎(chǔ)上建立起來的，即滿足第三范式必須要先滿足第二范式。第三范式(3NF)要求：表中的非主鍵列必須和主鍵直接相關(guān)而不能間接相關(guān)；也就是說：非主鍵列之間不能相關(guān)依賴。

主從復制

技術(shù)產(chǎn)生原因

• 讀寫分離，主庫出現(xiàn)了鎖表的情景，可以通過讀從庫也可以保證業(yè)務的正常運作。
• 數(shù)據(jù)熱備份
• 架構(gòu)擴展，業(yè)務量大的時候，避免I/O訪問頻率過高，做多庫的存儲

主從形式

• 一主一從、一主多從

  最常見的主從架構(gòu)，實施起來簡單并且有效，不僅可以實現(xiàn)HA，而且還能讀寫分離，進而提升集群的并發(fā)能力。

• 多主一從(5.7往后支持)

  可以將多個mysql數(shù)據(jù)庫備份到一臺存儲性能比較好的服務器上。

• 雙主復制

  互做主從復制，每個master既是master，又是另外一臺服務器的slave。這樣任何一方所做的變更，都會通過復制應用到另外一方的數(shù)據(jù)庫中。

• 級聯(lián)復制

  

  級聯(lián)復制模式下，部分slave的數(shù)據(jù)同步不連接主節(jié)點，而是連接從節(jié)點。因為如果主節(jié)點有太多的從節(jié)點，就會損耗一部分性能用于replication，那么我們可以讓3~5個從節(jié)點連接主節(jié)點，其它從節(jié)點作為二級或者三級與從節(jié)點連接，這樣不僅可以緩解主節(jié)點的壓力，并且對數(shù)據(jù)一致性沒有負面影響。

原理圖

一臺服務器上，利用docker compose部署一主兩從，實現(xiàn)主從復制

復制過程實際上就是Slave 從Master 端獲取該日志然后再在自己身上完全順序的執(zhí)行日志中所記錄的各種操作

主節(jié)點 binary log dump 線程

當從節(jié)點連接主節(jié)點時，主節(jié)點會創(chuàng)建一個log dump 線程，用于發(fā)送bin-log的內(nèi)容。在讀取bin-log中的操作時，此線程會對主節(jié)點上的bin-log加鎖，當讀取完成，甚至在發(fā)動給從節(jié)點之前，鎖會被釋放。

從節(jié)點I/O線程作用

當從節(jié)點上執(zhí)行start slave命令之后，從節(jié)點會創(chuàng)建一個I/O線程用來連接主節(jié)點，請求主庫中更新的bin-log。I/O線程接收到主節(jié)點binlog dump 進程發(fā)來的更新之后，保存在本地relay-log中。

如果在SQL進程執(zhí)行之前從節(jié)點服務停止，至少I/O進程已經(jīng)從主節(jié)點拉取到了最新的變更并且保存在本地relay日志中，當服務再次起來之后，就可以完成數(shù)據(jù)的同步。

從節(jié)點SQL線程作用

SQL線程負責讀取relay log中的內(nèi)容，解析成具體的操作并執(zhí)行，最終保證主從數(shù)據(jù)的一致性。

復制過程

1. 從庫通過手工執(zhí)行change master to 語句連接主庫

   需要提供了連接的用戶一切條件（user 、password、port、ip、指定位置）

2. 從庫的IO線程和主庫的dump線程建立連接

3. 從庫根據(jù)change master to 語句提供的file名和position號，IO線程向主庫發(fā)起binlog的請求。

4. 主庫dump線程根據(jù)從庫的請求，將本地binlog以events的方式發(fā)給從庫IO線程。返回信息中除了日志所包含的信息之外，還包括本次返回的信息的bin-log file 的以及bin-log position；

5. 從庫IO線程接收binlog events，并存放到本地relay-log中，傳送過來的信息，會記錄到master.info中，以便在下一次讀取的時候能夠清楚的告訴Master“我需要從某個bin-log 的哪個位置開始往后的日志內(nèi)容，請發(fā)給我”；

6. 從庫SQL線程應用relay-log，并且把應用過的記錄到relay-log.info中，默認情況下，已經(jīng)應用過的relay 會自動被清理purge

復制模式

MySQL主從復制默認是異步的

異步模式（mysql async-mode）

異步模式如下圖所示，這種模式下，主節(jié)點不會主動push bin log到從節(jié)點，這樣有可能導致failover的情況下，也許從節(jié)點沒有即時地將最新的bin log同步到本地。

半同步模式(mysql semi-sync)

這種模式下主節(jié)點只需要接收到其中一臺從節(jié)點的返回信息，就會commit；否則需要等待直到超時時間然后切換成異步模式再提交；這樣做的目的可以使主從數(shù)據(jù)庫的數(shù)據(jù)延遲縮小，可以提高數(shù)據(jù)安全性，確保了事務提交后，binlog至少傳輸?shù)搅艘粋€從節(jié)點上，不能保證從節(jié)點將此事務更新到db中。性能上會有一定的降低，響應時間會變長。如下圖所示:

全同步模式

全同步模式是指主節(jié)點和從節(jié)點全部執(zhí)行了commit并確認才會向客戶端返回成功。

復制機制

binlog記錄模式

• 基于SQL語句的復制（statement-based replication，SBR）

  Statement-base Replication (SBR)就是記錄sql語句在bin log中，Mysql 5.1.4 及之前的版本都是使用的這種復制格式。優(yōu)點是只需要記錄會修改數(shù)據(jù)的sql語句到binlog中，減少了binlog日質(zhì)量，節(jié)約I/O，提高性能。缺點是在某些情況下，會導致主從節(jié)點中數(shù)據(jù)不一致（比如sleep(),now()等）。

• 基于行的復制（row-based replication，RBR)

  Row-based Relication(RBR)是mysql master將SQL語句分解為基于Row更改的語句并記錄在bin log中，也就是只記錄哪條數(shù)據(jù)被修改了，修改成什么樣。優(yōu)點是不會出現(xiàn)某些特定情況下的存儲過程、或者函數(shù)、或者trigger的調(diào)用或者觸發(fā)無法被正確復制的問題。缺點是會產(chǎn)生大量的日志，尤其是修改table的時候會讓日志暴增,同時增加bin log同步時間。也不能通過bin log解析獲取執(zhí)行過的sql語句，只能看到發(fā)生的data變更。

• 混合模式復制（mixed-based replication,MBR)

  Mixed-format Replication(MBR)，MySQL NDB cluster 7.3 和7.4 使用的MBR。是以上兩種模式的混合，對于一般的復制使用STATEMENT模式保存到binlog，對于STATEMENT模式無法復制的操作則使用ROW模式來保存，MySQL會根據(jù)執(zhí)行的SQL語句選擇日志保存方式。

GTID復制模式

在MySQL 5.6里面，不用再找binlog和pos點，我們只需要知道主節(jié)點的ip，端口，以及賬號密碼就行，因為復制是自動的，MySQL會通過內(nèi)部機制GTID自動找點同步。

1. 主節(jié)點更新數(shù)據(jù)時，會在事務前產(chǎn)生GTID，一起記錄到binlog日志中。
2. 從節(jié)點的I/O線程將變更的bin log，寫入到本地的relay log中。
3. SQL線程從relay log中獲取GTID，然后對比本地binlog是否有記錄（所以MySQL從節(jié)點必須要開啟binary log）。
4. 如果有記錄，說明該GTID的事務已經(jīng)執(zhí)行，從節(jié)點會忽略。
5. 如果沒有記錄，從節(jié)點就會從relay log中執(zhí)行該GTID的事務，并記錄到bin log。
6. 在解析過程中會判斷是否有主鍵，如果沒有就用二級索引，如果有就用全部掃描。

主從復制慢原因分析

• 從庫硬件比主庫差，導致復制延遲
• 主從復制單線程，如果主庫寫并發(fā)太大，來不及傳送到從庫，就會導致延遲。單個庫讀寫分離，一主多從，主寫從讀，分散壓力。這樣從庫壓力比主庫高，保護主庫
• 更高版本的mysql可以支持多線程復制
• 慢SQL語句過多
• 網(wǎng)絡(luò)延遲
• master負載：主庫讀寫壓力大，導致復制延遲，架構(gòu)的前端要加buffer及緩存層
• slave負載：一般的做法是，使用多臺slave來分攤讀請求，再從這些slave中取一臺專用的服務器，只作為備份用，不進行其他任何操作.

讀寫分離

只在主服務器上寫，只在從服務器上讀。

amoeba軟件

部署

docker compose一鍵部署

目錄：/home/mysql

• compose文件

  version: '3.8'
  services:
    mysql_master:
      image: mysql:8.0.33-oracle
      restart: always
      privileged: true
      ports:
        - "3306:3306"
      environment:
        - MYSQL_ROOT_PASSWORD=admin123
        - MYSQL_DATABASE=db01
      volumes:
        - ./3306_log:/var/log/mysql
        - ./3306_data:/var/lib/mysql  
        - ./3306_conf/my.cnf:/etc/mysql/my.cnf
      networks:
        - mysql_network
  
    mysql_slave1:
      image: mysql:8.0.33-oracle
      restart: always
      privileged: true
      ports:
        - "3307:3306"
      environment:
        - MYSQL_ROOT_PASSWORD=admin123
        - MYSQL_DATABASE=db01
      volumes:
        - ./3307_log:/var/log/mysql
        - ./3307_data:/var/lib/mysql  
        - ./3307_conf/my.cnf:/etc/mysql/my.cnf
      networks:
        - mysql_network
  
    mysql_slave2:
      image: mysql:8.0.33-oracle
      restart: always
      privileged: true
      ports:
        - "3308:3306"
      environment:
        - MYSQL_ROOT_PASSWORD=admin123
        - MYSQL_DATABASE=db01
      volumes:
        - ./3308_log:/var/log/mysql
        - ./3308_data:/var/lib/mysql  
        - ./3308_conf/my.cnf:/etc/mysql/my.cnf
      networks:
        - mysql_network
  
  networks:
    mysql_network:
  

• 3306 cnf文件

  [mysqld]
  ## 設(shè)置server_id，同一局域網(wǎng)中需要唯一
  server_id=101 
  ## 指定不需要同步的數(shù)據(jù)庫名稱
  binlog-ignore-db=mysql  
  ## 開啟二進制日志功能
  log-bin=mall-mysql-bin
  ## 允許服務器更新二進制文件
  log-slave-updates=true
  ## 設(shè)置二進制日志使用內(nèi)存大?。ㄊ聞眨?binlog_cache_size=1M  
  ## 設(shè)置使用的二進制日志格式（mixed,statement,row）
  binlog_format=mixed  
  ## 二進制日志過期清理時間。默認值為0，表示不自動清理。
  expire_logs_days=7  
  ## 跳過主從復制中遇到的所有錯誤或指定類型的錯誤，避免slave端復制中斷。
  ## 如：1062錯誤是指一些主鍵重復，1032錯誤是因為主從數(shù)據(jù)庫數(shù)據(jù)不一致
  slave_skip_errors=1062
  character-set-server=utf8
  bind-address=0.0.0.0
  

• 3307

  [mysqld]
  ## 設(shè)置server_id，同一局域網(wǎng)中需要唯一
  server_id=102
  ## 指定不需要同步的數(shù)據(jù)庫名稱
  binlog-ignore-db=mysql  
  ## 開啟二進制日志功能，以備Slave作為其它數(shù)據(jù)庫實例的Master時使用
  log-bin=mall-mysql-slave1-bin  
  ## 設(shè)置二進制日志使用內(nèi)存大?。ㄊ聞眨?binlog_cache_size=1M  
  ## 設(shè)置使用的二進制日志格式（mixed,statement,row）
  binlog_format=mixed  
  ## 二進制日志過期清理時間。默認值為0，表示不自動清理。
  expire_logs_days=7  
  ## 跳過主從復制中遇到的所有錯誤或指定類型的錯誤，避免slave端復制中斷。
  ## 如：1062錯誤是指一些主鍵重復，1032錯誤是因為主從數(shù)據(jù)庫數(shù)據(jù)不一致
  slave_skip_errors=1062  
  ## relay_log配置中繼日志
  relay_log=mall-mysql-relay-bin  
  ## log_slave_updates表示slave將復制事件寫進自己的二進制日志
  log_slave_updates=1  
  ## slave設(shè)置為只讀（具有super權(quán)限的用戶除外）
  read_only=1
  

• 中間操作

  # 主機
  CREATE USER slave@'%' IDENTIFIED WITH mysql_native_password BY '12345678';
  GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%'; 
  """
  參數(shù)說明
  % 表示該用戶可以從任何主機連接到MySQL服務器
  *.* 表示數(shù)據(jù)庫名，支持正則
  REPLICATION SLAVE 允許用戶作為MySQL復制的從服務器，接受來自主服務器的二進制日志并應用到本地數(shù)據(jù)庫
  REPLICATION CLIENT 權(quán)限允許用戶連接到主服務器并檢索復制相關(guān)的信息。
  若想讓用戶擁有只讀權(quán)限：GRANT SELECT ON *.* TO 'readonly'@'%';
  """
  flush privileges;
  
  show master status;
  
  # 從機
  change master to master_host='192.168.128.2', master_user='slave', master_password='12345678', master_port=3306, master_log_file='mall-mysql-bin.000004',master_log_pos=848,master_connect_retry=10;
  
  CHANGE MASTER TO MASTER_HOST='mysql_master', MASTER_PORT=3306, MASTER_USER='slave', MASTER_PASSWORD='12345678';
  
  START SLAVE;
  
  SHOW SLAVE STATUS\G
  
  
  ---- 暫且不看
  CHANGE MASTER TO
    MASTER_HOST='${MYSQL_MASTER_HOST}',
    MASTER_USER='${MYSQL_MASTER_USER}',
    MASTER_PASSWORD='${MYSQL_MASTER_PASSWORD}',
    MASTER_PORT=${MYSQL_MASTER_PORT},
    MASTER_LOG_FILE='${MYSQL_MASTER_LOG_FILE}',
    MASTER_LOG_POS=${MYSQL_MASTER_LOG_POS},
    MASTER_CONNECT_RETRY=${MYSQL_MASTER_CONNECT_RETRY};
    
  master_log_file='mall-mysql-bin.000001',  # 指定從數(shù)據(jù)庫要復制數(shù)據(jù)的日志文件，通過查看主數(shù)據(jù)的狀態(tài)，獲取File參數(shù)
  master_log_pos=769,  # 指定從數(shù)據(jù)庫從哪個位置開始復制數(shù)據(jù)，通過查看主數(shù)據(jù)的狀態(tài)，獲取Position參數(shù)；
  master_connect_retry=30; 連接失敗重試的時間間隔，單位為秒。
  

  Last_IO_Error: Error connecting to source ‘slave@192.168.112.2:3306’. This was attempt 1/86400, with a delay of 10 seconds between attempts. Message: Authentication plugin ‘caching_sha2_password’ reported error: Authentication requires secure connection.文章來源地址http://www.zghlxwxcb.cn/news/detail-479935.html

  • ALTER USER ‘slave’@‘%’ IDENTIFIED WITH mysql_native_password BY ‘12345678’;

到了這里，關(guān)于【MySQL】主從復制&部署的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！