01-MySQL8主從詳解

来源:https://www.cnblogs.com/wacxpan/archive/2022/10/18/16801925.html
-Advertisement-
Play Games

主從原理 master伺服器將數據的改變記錄二進位binlog日誌，當master上的數據發生改變時，則將其改變寫入二進位日誌中；slave伺服器會在一定時間間隔內對master二進位日誌進行探測其是否發生改變， 當master伺服器數據發生變化，則slave開始一個I/O Thread請求mast ...


主從原理

master伺服器將數據的改變記錄二進位binlog日誌，當master上的數據發生改變時，則將其改變寫入二進位日誌中；slave伺服器會在一定時間間隔內對master二進位日誌進行探測其是否發生改變，

當master伺服器數據發生變化，則slave開始一個I/O Thread請求master二進位事件，同時maser節點為每個I/O線程啟動一個dump線程，用於向其發送二進位事件，並保存至從節點本地的中繼日誌Relay_Log_File中，從節點將啟動SQL Thread從中繼日誌中讀取二進位日誌，在本地重放，使得其數據和主節點的保持一致，最後I/O Thread和SQL Thread將進入睡眠狀態，等待下一次被喚醒。

註意幾點：

1--master將操作語句記錄到binlog日誌中，然後授予slave遠程連接的許可權（master一定要開啟binlog二進位日誌功能；通常為了數據安全考慮，slave也開啟binlog功能）。

2--slave開啟兩個線程：IO線程和SQL線程。其中：IO線程負責讀取master的binlog內容到中繼日誌relay log里；SQL線程負責從relay log日誌里讀出binlog內容，並更新到slave的資料庫里，這樣就能保證slave數據和 master數據保持一致了。

3--Mysql複製至少需要兩個Mysql的服務，當然Mysql服務可以分佈在不同的伺服器上，也可以在一臺伺服器上啟動多個服務。

4--Mysql複製最好確保master和slave伺服器上的Mysql版本相同（如果不能滿足版本一致，那麼要保證master主節點的版本低於slave從節點的版本）

5--master和slave兩節點間時間需同步

1.1 Mysql複製的流程

image

1、第一階段

Mysql複製過程的第一部分就是master記錄二進位日誌。在每個事務更新數據完成之前，master在二日誌記錄這些改變。MySQL將事務串列的寫入二進位日誌，即使事務中的語句都是交叉執行的。在事件寫入二進位日誌完成後，master通知存儲引擎提交事務。

2、第二階段

就是slave將master的binary log拷貝到它自己的中繼日誌。首先，slave開始一個工作線程——I/O線程。I/O線程在master上打開一個普通的連接，然後開始binlog dump process。Binlog dump process從master的二進位日誌中讀取事件，如果已經跟上master，它會睡眠並等待master產生新的事件。I/O線程將這些事件寫入中繼日誌。

3、第三階段

SQL slave thread（SQL從線程）處理該過程的最後一步。SQL線程從中繼日誌讀取事件，並重放其中的事件而更新slave的數據，使其與master中的數據一致。只要該線程與I/O線程保持一致，中繼日誌通常會位於OS的緩存中，所以中繼日誌的開銷很小。

此外，在master中也有一個工作線程：和其它MySQL的連接一樣，slave在master中打開一個連接也會使得master開始一個線程。複製過程有一個很重要的限制——複製在slave上是串列化的，也就是說master上的並行更新操作不能在slave上並行操作。

1.2 主從複製的前提條件

  1. 開啟binlog功能
  2. 主庫master節點建立同步數據賬號
  3. 從庫要配置master.info（CHANGE MASTER to...相當於配置密碼文件和Master的相關信息）
  4. start slave 開啟複製功能

主從配置

2.1 基礎環境

操作系統centos7.5，

節點名稱 IP地址
master 192.168.150.185
slave 192.168.150.25

2.2 基礎配置

關閉selinux

#臨時關閉
setenforce 0

#永久關閉
vi /etc/sysconfig/selinux
...
...
SELINUX=disabled
...
...

關閉防火牆

systemctl stop firewalld&&systemctl stop iptables

時鐘同步配置

#安裝chrony
yum install chrony

#修改配置文件
vi /etc/sysconfig/selinux
...
...
server ntp1.aliyun.com iburst
...
...
#啟動服務
systemctl start chronyd

2.3 master主節點配置

修改配置文件

vim /etc/my.cnf

..........
[mysqld] 
#資料庫唯一ID，主從的標識號絕對不能重覆。
server-id=1
#開啟bin-log，並指定文件目錄和文件名首碼
log-bin=mysql-bin
#同步test資料庫。如果同時同步多個庫，就以此格式另寫幾行即可。如果不指定某個庫同步，刪除此行，表示同步所有庫（除了ignore忽略的庫）
binlog-do-db=test
#不同步mysql系統資料庫。如果是多個不同步庫，就以此格式另寫幾行；也可以在一行，中間逗號隔開。
binlog-ignore-db=mysql
#設置二進位日誌自動刪除/過期的天數，避免占用磁碟空間。預設值為0，表示不自動刪除。
expire_logs_days=7
#確保binlog日誌寫入後與硬碟同步
sync_binlog=1         
#bin-log日誌文件格式
binlog_format=ROW

溫馨提示：在主伺服器上最重要的二進位日誌設置是sync_binlog，這使得mysql在每次提交事務的時候把二進位日誌的內容同步到磁碟上，即使伺服器崩潰也會把事件寫入日誌中。

sync_binlog這個參數是對於MySQL系統來說是至關重要的，他不僅影響到Binlog對MySQL所帶來的性能損耗，而且還影響到MySQL中數據的完整性。對於"sync_binlog"參數的各種設置的說明如下：

sync_binlog=0，當事務提交之後，MySQL不做fsync之類的磁碟同步指令刷新binlog_cache中的信息到磁碟，而讓Filesystem自行決定什麼時候來做同步，或者cache滿了之後才同步到磁碟。

sync_binlog=n，當每進行n次事務提交之後，MySQL將進行一次fsync之類的磁碟同步指令來將binlog_cache中的數據強制寫入磁碟。

在MySQL中系統預設的設置是sync_binlog=0，也就是不做任何強制性的磁碟刷新指令，這時候的性能是最好的，但是風險也是最大的。因為一旦系統Crash，在binlog_cache中的所有binlog信息都會被丟失。而當設置為“1”的時候，是最安全但是性能損耗最大的設置。因為當設置為1的時候，即使系統Crash，也最多丟失binlog_cache中未完成的一個事務，對實際數據沒有任何實質性影響。

從以往經驗和相關測試來看，對於高併發事務的系統來說，“sync_binlog”設置為0和設置為1的系統寫入性能差距可能高達5倍甚至更多。

2.4 數據一致性

在同步前保證master和slave中的數據一致，新環境忽略本步驟

導出資料庫之前先鎖定資料庫

#資料庫只讀鎖定命令，防止導出資料庫的時候有數據寫入,unlock tables命令解除鎖定
mysql> flush tables with read lock;

導出master資料庫中需要同步的庫

#導出需要同步的庫
[root@master ~]#mysqldump -uroot test -p123456 >/opt/test.sql

#如不指定，則導出所有庫
mysqldump -uroot -p123456 --all-databases>/opt/all.sql

將導出數據導入salve中

#傳到slave
scp /opt/all slave:/opt

#在slave導入資料庫
mysql> source /opt/all.sql

2.5 創建數據同步賬號

登錄主資料庫創建一個用於從資料庫複製的賬號

mysql> create user 'rep'@'192.168.150.25' identified with mysql_native_password by 'repl123';
grant replication slave on *.* to 'rep'@'192.168.150.25';

mysql> flush privileges;

查看主伺服器master狀態(註意File與Position項，從伺服器需要這兩項參數)

mysql> show master status;
+-----------+-----------+--------------+------------------+-------------------+
| File      | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------+-----------+--------------+------------------+-------------------+
| mysql-bin.000065 | 186607472 |              |                  |                   |
+-----------+-----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

2.6 配置slave

修改my.cnf配置文件

vim /etc/my.cnf
#設置從伺服器id，必須於主伺服器不同
server-id=2   
#啟動MySQ二進位日誌系統
log-bin=mysql-bin   
#需要同步的資料庫名。如果不指明同步哪些庫，就去掉這行，表示所有庫的同步（除了ignore忽略的庫）

配置主從同步指令

#執行同步前，要先關閉slave
mysql> stop slave;   

mysql> change master to master_host='192.168.150.185',master_user='repl',master_password='repl123',master_log_file='ON.000065',master_log_pos=186607472;

mysql> start slave;

mysql> show slave status \G;

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.150.185
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: ON.000065
          Read_Master_Log_Pos: 187730832
               Relay_Log_File: xmkjoa02-relay-bin.000002
                Relay_Log_Pos: 46151494
        Relay_Master_Log_File: ON.000065
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 187730832
              Relay_Log_Space: 46151706
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 6c3f5abb-1c32-11ec-96ba-fa163e7a46bf
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 1
            Network_Namespace: 
1 row in set (0.00 sec)

如上，當IO和SQL線程的狀態均為Yes，則表示主從已實現同步了！


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • linux 安裝VMware tools
    安裝VMware Tools選項顯示灰色的解決辦法 Linux 1. 解決VMware灰色狀態 ①點擊虛擬機； ②在虛擬機設置分別設置CD/DVD和軟盤中選擇連接【使用物理驅動器】（如有CD/DVD2一樣操作）； ③重啟虛擬機，灰色字即點亮。 image-20221018195444397 2.VM ...
  • Microsoft Teams團隊頻道里共用文件彈出登錄提示解決辦法
    Linux中的文件訪問控制 在Unix系統家族裡，文件或目錄許可權的控制分別以讀取、寫入、執行3種一般許可權來區分，另有3種特殊許可權可供運用【SUID,SGID,SBIT】，再搭配擁有者與所屬群組管理許可權範圍。 SUID： 1、只對二進位可執行程式有效，不能為普通文件; 2、對程式文件必須擁有執行許可權; ...
  • vsftpd服務
    vsftpd服務 ftp是互聯網中進行文件傳輸的一種協議，基於C/S模式，FTP預設有兩個工作埠（20數據傳輸，21FTP服務端就收客戶端發來的指令和） 安裝FTP服務 [root@haha-main-130 ~]# yum -y install vsftpd 配置文件位置及內容 [root@ha ...
  • Windows開啟關閉測試模式的方法（含開啟測試模式失敗的解決辦法）
    前言： 內含：Windows開啟關閉測試模式的方法、開啟測試模式失敗的解決辦法、win10進入bios的方式、BitLocker恢復方式。 對於互聯網從業者來說，尤其是開發人員、測試人員、產品經理，在內部測試或驗收時，軟體未獲得微軟簽名時，需要以系統以測試模式運行，安裝軟體進行測試。 win7系統開 ...
  • nginx反向代理與負載均衡
    nginx反向代理與負載均衡 nginx通常被用作後端伺服器的反向代理，這樣就可以很方便的實現動靜分離以及負載均衡，從而大大提高伺服器的處理能力。 nginx實現動靜分離，其實就是在反向代理的時候，如果是靜態資源，就直接從nginx發佈的路徑去讀取，而不需要從後臺伺服器獲取了。 nginx通過ups ...
  • 親測-ubuntu20.04 安裝 MySQL5.7
    背景介紹 ubuntu 20.04 版本系統自帶的 MySQL 版本是 8.0，普通方法很難安裝 5.7 版本的。由於 8.0 版本較 5.7 版本做了不少改動，筆者比較習慣使用 5.7 版本。 網上搜做了一圈，跟著各種教程試了很多遍，最後終於找到了成功的方法。過程記錄分享出來，供大家參考。 安裝過 ...
  • PostgreSQL 資料庫開發規範
    PostgreSQL的功能非常強大，但是要把PostgreSQL用好，開發人員是非常關鍵的。 下麵將針對PostgreSQL資料庫原理與特性，輸出一份開發規範，希望可以減少大家在使用PostgreSQL資料庫過程中遇到的困惑。 目標是將PostgreSQL的功能、性能發揮好，她好我也好。 ...
  • 京東雲TiDB SQL優化的最佳實踐
    京東雲TiDB SQL層的背景介紹 從總體上概括 TiDB 和 MySQL 相容策略，如下表： SQL層的架構 用戶的 SQL 請求會直接或者通過 Load Balancer 發送到 京東雲TiDB Server，TiDB Server 會解析 MySQL Protocol Packet，獲取請求內 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類