主備原理

備庫 B 跟主庫 A 之間維持了一個長連接。主庫 A 內(nèi)部有一個線程，專門用于服務備庫 B 的這個長連接。一個事務日志同步的完整過程是這樣的：

站在用戶的角度思考問題，與客戶深入溝通，找到圍場網(wǎng)站設計與圍場網(wǎng)站推廣的解決方案，憑借多年的經(jīng)驗，讓設計與互聯(lián)網(wǎng)技術結合，創(chuàng)造個性化、用戶體驗好的作品，建站類型包括：成都網(wǎng)站建設、成都網(wǎng)站制作、企業(yè)官網(wǎng)、英文網(wǎng)站、手機端網(wǎng)站、網(wǎng)站推廣、域名申請、網(wǎng)站空間、企業(yè)郵箱。業(yè)務覆蓋圍場地區(qū)。

1、在備庫 B 上通過 change master 命令，設置主庫 A 的 IP、端口、用戶名、密碼，以及要從哪個位置開始請求 binlog，這個位置包含文件名和日志偏移量。

2、在備庫 B 上執(zhí)行 start slave 命令，這時候備庫會啟動兩個線程，就是圖中的 io_thread 和 sql_thread 。其中 io_thread 負責與主庫建立連接。

3、主庫 A 校驗完用戶名、密碼后，開始按照備庫 B 傳過來的位置，從本地讀取 binlog， 發(fā)給 B(push模式) 。

4、備庫 B 拿到 binlog 后，寫到本地文件，稱為中轉(zhuǎn)日志（ relay log ）。

5、sql_thread 讀取中轉(zhuǎn)日志，解析出日志里的命令，并執(zhí)行。

這里需要說明，后來由于多線程復制方案的引入， sql_thread 演化成為了多個線程。

binlog 的三種格式對比：

一種是 statement，按語句進行數(shù)據(jù)備份，可能需要聯(lián)系上下文，日志相對較少 ，一種是 row，按行記錄對數(shù)據(jù)的修改，無需關心sql語句或者上下文，邏輯簡單，日志文件巨大，性能相對較低 。還有第三種格式，叫作 mixed ，其實它就是前兩種格式的 混合 。

show variables like ' binlog_format '; 該參數(shù)控制。

1、因為有些 statement 格式的 binlog 可能會導致主備不一致 ，所以要使用 row 格式。

2、但 row 格式的缺點是， 很占空間 。比如你用一個 delete 語句刪掉 10 萬行數(shù)據(jù)，用 statement 的話就是一個 SQL 語句被記錄到 binlog 中，占用幾十個字節(jié)的空間。但如果用 row 格式的 binlog，就要把這 10 萬條記錄都寫到 binlog 中。這樣做，不僅會占用更大的空間，同時寫 binlog 也要耗費 IO 資源，影響執(zhí)行速度。

3、MySQL 就取了個折中方案，也就是有了 mixed 格式的 binlog。mixed 格式的意思是，MySQL 自己會判斷這條 SQL 語句是否可能引起主備不一致，如果有可能，就用 row 格式，否則就用 statement 格式。也就是說，mixed 格式可以利用 statment 格式的優(yōu)點，同時又避免了數(shù)據(jù)不一致的風險。

越來越推薦row格式，因為它方便恢復數(shù)據(jù) 。

備庫： show slave status\G? 查看備庫情況， seconds_behind_master 主備延時，理想狀況為0.

1、機器異構，備庫性能差或者集中在一個機器上。

2、忽視備庫壓力，對備庫使用沒有節(jié)制，比如復雜的讀計算。

1、一主多從。除了備庫外，可以多接幾個從庫，讓這些從庫來分擔讀的壓力。

2、通過 binlog 輸出到外部系統(tǒng)，比如 Hadoop 這類系統(tǒng)，讓外部系統(tǒng)提供統(tǒng)計類查詢的能力。

在主庫上，影響并發(fā)度的原因就是各種鎖了。

在備庫上，就是圖中備庫上 sql_thread 更新數(shù)據(jù) (DATA) 的邏輯。如果是用 單線程 的話，就會導致備庫應用日志不夠快，造成主備延遲。

官方 MySQL5.6 版本 ，支持了并行復制，只是支持的粒度是 按庫并行 。這個策略的并行效果，取決于壓力模型。如果在主庫上有多個 DB，并且 各個 DB 的壓力均衡 ，使用這個策略的效果會很好。

相比于 按表 和 按行 分發(fā)，這個策略有兩個優(yōu)勢：

1、構造 hash 值的時候很快，只需要庫名；而且一個實例上 DB 數(shù)也不會很多，不會出現(xiàn)需要構造 100 萬個項這種情況。

2、不要求 binlog 的格式。因為 statement 格式的 binlog 也可以很容易拿到庫名。

但是，如果你的主庫上的表都放在同一個 DB 里面，這個策略就沒有效果了；或者如果不同 DB 的熱點不同，比如一個是業(yè)務邏輯庫，一個是系統(tǒng)配置庫，那也起不到并行的效果。

理論上你可以創(chuàng)建不同的 DB，把相同熱度的表均勻分到這些不同的 DB 中，強行使用這個策略。不過據(jù)我所知，由于需要特地移動數(shù)據(jù)，這個策略用得并不多。

在 MariaDB 并行復制實現(xiàn)之后， 官方的 MySQL5.7 版本也提供了類似的功能，由參數(shù) s lave-parallel-type 來控制并行復制策略：

1、配置為 DATABASE ，表示使用 MySQL 5.6 版本的按庫并行策略；

2、配置為 LOGICAL_CLOCK ，表示的就是類似 MariaDB 的策略。不過，MySQL 5.7 這個策略，針對并行度做了優(yōu)化。這個優(yōu)化的思路也很有趣兒。

在 2018 年 4 月份發(fā)布的 MySQL 5.7.22 版本里，MySQL 增加了一個新的并行復制策略，基于 WRITESET 的并行復制。

相應地，新增了一個參數(shù) binlog-transaction-dependency-tracking ，用來控制是否啟用這個新策略。這個參數(shù)的可選值有以下三種。

1、COMMIT_ORDER，表示的就是前面介紹的，根據(jù)同時進入 prepare 和 commit 來判斷是否可以并行的策略。

2、WRITESET，表示的是對于事務涉及更新的每一行，計算出這一行的 hash 值，組成集合 writeset。如果兩個事務沒有操作相同的行，也就是說它們的 writeset 沒有交集，就可以并行。

3、WRITESET_SESSION，是在 WRITESET 的基礎上多了一個約束，即在主庫上同一個線程先后執(zhí)行的兩個事務，在備庫執(zhí)行的時候，要保證相同的先后順序。

當然為了唯一標識，這個 hash 值是通過“庫名 + 表名 + 索引名 + 值”計算出來的。如果一個表上除了有主鍵索引外，還有其他唯一索引，那么對于每個唯一索引，insert 語句對應的 writeset 就要多增加一個 hash 值。

你可能看出來了，這跟我們前面介紹的基于 MySQL 5.5 版本的按行分發(fā)的策略是差不多的。不過，MySQL 官方的這個實現(xiàn)還是有很大的優(yōu)勢：

writeset 是在主庫生成后直接寫入到 binlog 里面的，這樣在備庫執(zhí)行的時候，不需要解析 binlog 內(nèi)容（event 里的行數(shù)據(jù)），節(jié)省了很多計算量；

不需要把整個事務的 binlog 都掃一遍才能決定分發(fā)到哪個 worker，更省內(nèi)存；

由于備庫的分發(fā)策略不依賴于 binlog 內(nèi)容，所以 binlog 是 statement 格式也是可以的。

因此，MySQL 5.7.22 的并行復制策略在通用性上還是有保證的。

當然，對于“表上沒主鍵”和“外鍵約束”的場景，WRITESET 策略也是沒法并行的，也會暫時退化為單線程模型。

圖中，虛線箭頭表示的是主備關系，也就是 A 和 A’互為主備， 從庫 B、C、D 指向的是主庫 A。一主多從的設置，一般用于讀寫分離，主庫負責所有的寫入和一部分讀，其他的讀請求則由從庫分擔。

如何用mysql workbench做mysql主備

雙擊或右鍵打開MySQL Workbench，進入軟件主界面。

點擊new connection,會有個彈出框，讓我們填寫user(用戶名)，password(密碼)。

填寫完用戶名和密碼，點擊確定就會出現(xiàn)我們創(chuàng)建的數(shù)據(jù)庫工作空間，例如：local instance MySQL56。

雙擊local instance MySQL56，進入數(shù)據(jù)庫工作空間。

在左上方找到一個圓柱形帶加號的圖標，單擊該圖標，可以顯示出數(shù)據(jù)庫名讓我們填例如：baidu。

創(chuàng)建好數(shù)據(jù)庫，我們在左菜單欄找到baidu這個數(shù)據(jù)庫，雙擊該數(shù)據(jù)庫，可以看到下面有個tables,右鍵table，選擇create table，就能創(chuàng)建表了。

MySQL - 主備

在實際的生產(chǎn)中，為了解決Mysql的單點故障，一般都會采用「主備模式」。

MySQL幾乎所有的高可用架構，都直接依賴于 binlog。雖然這些高可用架構已經(jīng)呈現(xiàn)出越來越復雜的趨勢，但都是從最基本的一主一備演化過來的。

下圖為主備切換流程

在狀態(tài) 1 中，客戶端的讀寫都直接訪問節(jié)點 A，而節(jié)點 B 是 A 的備庫，只是將 A 的更新都同步過來，到本地執(zhí)行。這樣可以保持節(jié)點 B 和 A 的數(shù)據(jù)是相同的。

當需要切換的時候，就切成狀態(tài) 2。這時候客戶端讀寫訪問的都是節(jié)點 B，而節(jié)點 A 是 B 的備庫。

在狀態(tài) 1 中，雖然節(jié)點 B 沒有被直接訪問，但是依然建議把節(jié)點 B（也就是備庫）設置成只讀（readonly）模式。這樣做，有以下幾個考慮：

圖下圖 中畫出的就是一個 update 語句在節(jié)點 A 執(zhí)行，然后同步到節(jié)點 B 的完整流程圖。

備庫 B 跟主庫 A 之間維持了一個長連接。主庫 A 內(nèi)部有一個線程，專門用于服務備庫 B 的這個長連接。一個事務日志同步的完整過程是這樣的：

在備庫 B 上通過 change master 命令，設置主庫 A 的 IP、端口、用戶名、密碼，以及要從哪個位置開始請求 binlog，這個位置包含文件名和日志偏移量。

在備庫 B 上執(zhí)行 start slave 命令，這時候備庫會啟動兩個線程，就是圖中的 io_thread 和 sql_thread。其中 io_thread 負責與主庫建立連接。

主庫 A 校驗完用戶名、密碼后，開始按照備庫 B 傳過來的位置，從本地讀取 binlog，發(fā)給 B。

備庫 B 拿到 binlog 后，寫到本地文件，稱為中轉(zhuǎn)日志（relay log）。

sql_thread 讀取中轉(zhuǎn)日志，解析出日志里的命令，并執(zhí)行。

主庫需要復制新增binlog到從庫才能完成同步，這個同步過程就是同步延遲。主從延遲最直接的表現(xiàn)是，備庫消費中轉(zhuǎn)日志（relay log）的速度，比主庫生產(chǎn) binlog 的速度要慢。

「同步策略」：Master會等待所有的Slave都回應后才會提交，這個主從的同步的性能會嚴重的影響。

「半同步策略」：Master至少會等待一個Slave回應后提交。

「異步策略」：Master不用等待Slave回應就可以提交。

「延遲策略」：Slave要落后于Master指定的時間。

對于不同的業(yè)務需求，有不同的策略方案，但是一般都會采用最終一致性，不會要求強一致性，畢竟強一致性會嚴重影響性能。

MySQL 主從，5 分鐘帶你掌握

MySQL 主從一直是面試常客，里面的知識點雖然基礎，但是能回答全的同學不多。

比如樓哥之前面試小米，就被問到過主從復制的原理，以及主從延遲的解決方案，因為回答的非常不錯，給面試官留下非常好的印象。你之前面試，有遇到過哪些 MySQL 主從的問題呢？

所謂 MySQL 主從，就是建立兩個完全一樣的數(shù)據(jù)庫，一個是主庫，一個是從庫， 主庫對外提供讀寫的操作，從庫對外提供讀的操作 ，下面是一主一從模式：

對于數(shù)據(jù)庫單機部署，在 4 核 8G 的機器上運行 MySQL 5.7 時，大概可以支撐 500 的 TPS 和 10000 的 QPS， 當遇到一些活動時，查詢流量驟然，就需要進行主從分離。

大部分系統(tǒng)的訪問模型是讀多寫少，讀寫請求量的差距可能達到幾個數(shù)量級，所以我們可以通過一主多從的方式， 主庫只負責寫入和部分核心邏輯的查詢，多個從庫只負責查詢，提升查詢性能，降低主庫壓力。

MySQL 主從還能做到服務高可用，當主庫宕機時，從庫可以切成主庫，保證服務的高可用，然后主庫也可以做數(shù)據(jù)的容災備份。

整體場景總結如下：

MySQL 的主從復制是依賴于 binlog 的，也就是記錄 MySQL 上的所有變化并以二進制形式保存在磁盤上二進制日志文件。

主從復制就是將 binlog 中的數(shù)據(jù)從主庫傳輸?shù)綇膸焐希话氵@個過程是異步的，即主庫上的操作不會等待 binlog 同步的完成。

詳細流程如下：

當主庫和從庫數(shù)據(jù)同步時，突然中斷怎么辦？因為主庫與從庫之間維持了一個長鏈接，主庫內(nèi)部有一個線程，專門服務于從庫的這個長鏈接的。

對于下面的情況，假如主庫執(zhí)行如下 SQL，其中 a 和 create_time 都是索引：

我們知道，數(shù)據(jù)選擇了 a 索引和選擇 create_time 索引，最后 limit 1 出來的數(shù)據(jù)一般是不一樣的。

所以就會存在這種情況：在 binlog = statement 格式時，主庫在執(zhí)行這條 SQL 時，使用的是索引 a，而從庫在執(zhí)行這條 SQL 時，使用了索引 create_time，最后主從數(shù)據(jù)不一致了。

那么我們改如何解決呢？

可以把 binlog 格式修改為 row，row 格式的 binlog 日志記錄的不是 SQL 原文，而是兩個 event:Table_map 和 Delete_rows。

Table_map event 說明要操作的表，Delete_rows event用于定義要刪除的行為，記錄刪除的具體行數(shù)。 row 格式的 binlog 記錄的就是要刪除的主鍵 ID 信息，因此不會出現(xiàn)主從不一致的問題。

但是如果 SQL 刪除 10 萬行數(shù)據(jù)，使用 row 格式就會很占空間的，10 萬條數(shù)據(jù)都在 binlog 里面，寫 binlog 的時候也很耗 IO。但是 statement 格式的 binlog 可能會導致數(shù)據(jù)不一致。

設計 MySQL 的大叔想了一個折中的方案，mixed 格式的 binlog，其實就是 row 和 statement 格式混合使用， 當 MySQL 判斷可能數(shù)據(jù)不一致時，就用 row 格式，否則使用就用 statement 格式。

有時候我們遇到從數(shù)據(jù)庫中獲取不到信息的詭異問題時，會糾結于代碼中是否有一些邏輯會把之前寫入的內(nèi)容刪除，但是你又會發(fā)現(xiàn)，過了一段時間再去查詢時又可以讀到數(shù)據(jù)了，這基本上就是主從延遲在作怪。

主從延遲，其實就是“從庫回放” 完成的時間，與 “主庫寫 binlog” 完成時間的差值， 會導致從庫查詢的數(shù)據(jù)，和主庫的不一致 。

談到 MySQL 數(shù)據(jù)庫主從同步延遲原理，得從 MySQL 的主從復制原理說起：

總結一下主從延遲的主要原因 ：主從延遲主要是出現(xiàn)在 “relay log 回放” 這一步，當主庫的 TPS 并發(fā)較高，產(chǎn)生的 DDL 數(shù)量超過從庫一個 SQL 線程所能承受的范圍，那么延時就產(chǎn)生了，當然還有就是可能與從庫的大型 query 語句產(chǎn)生了鎖等待。

我們一般會把從庫落后的時間作為一個重點的數(shù)據(jù)庫指標做監(jiān)控和報警，正常的時間是在毫秒級別，一旦落后的時間達到了秒級別就需要告警了。

解決該問題的方法，除了縮短主從延遲的時間，還有一些其它的方法，基本原理都是盡量不查詢從庫。

具體解決方案如下：

在實際應用場景中，對于一些非常核心的場景，比如庫存，支付訂單等，需要直接查詢從庫，其它非核心場景，就不要去查主庫了。

兩臺機器 A 和 B，A 為主庫，負責讀寫，B 為從庫，負責讀數(shù)據(jù)。

如果 A 庫發(fā)生故障，B 庫成為主庫負責讀寫，修復故障后，A 成為從庫，主庫 B 同步數(shù)據(jù)到從庫 A。

一臺主庫多臺從庫，A 為主庫，負責讀寫，B、C、D為從庫，負責讀數(shù)據(jù)。

如果 A 庫發(fā)生故障，B 庫成為主庫負責讀寫，C、D負責讀，修復故障后，A 也成為從庫，主庫 B 同步數(shù)據(jù)到從庫 A。

如何備份整個mysql數(shù)據(jù)庫

1、首先打開mysql數(shù)據(jù)庫軟件進入軟件主界面。

2、然后再左側(cè)樹里打開自己的的數(shù)據(jù)庫。

3、然后需要點擊需要備份的數(shù)據(jù)庫名。

4、如圖所示為打開數(shù)據(jù)庫后界面。

5、然后需要點擊轉(zhuǎn)儲sql文件選項。

6、然后需要打開選擇存儲文件路徑并選擇保存。

7、點擊保存即可在路徑備份好格式為sql的數(shù)據(jù)庫文件。

名稱欄目：mysql主備怎么操作,mysql主備部署
網(wǎng)站路徑：http://chinadenli.net/article15/dseihdi.html

成都網(wǎng)站建設公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站改版、網(wǎng)站內(nèi)鏈、建站公司、網(wǎng)頁設計公司、品牌網(wǎng)站建設、微信小程序

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)